Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 4th, 2006, 03:31 AM
Authorized User
 
Join Date: Dec 2006
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default Importing data from a CSV file?

Hi all,

I've just completed a new web application, using php and mysql.
Its was working 100%, until it was brought to my attention that i have to migrate all the data to MS SQL Server 2000...(a grueling task, seeing that i've never used MS SQL Server 2000 before, and had to use the DTS import to import each table I had exported from mysql)

My current problem is to import data from a flat file, csv to be specific...to temp tables i have setup in the database(they are actually empty tables) which are used just to update a table holding data extracted from various flat files.

I tried using the following:

BULK INSERT [dbo].[actuals]
FROM 'c:\data.csv'
WITH (
      FIELDTERMINATOR = ';',
      ROWTERMINATOR = '\n'
)

but using the above gives me an error...about type conversion.

I would also like to skip the first few rows in the csv file.

How would I achieve my goal of import data into a table in the database?

Much aliged

Kr33




A man's dreams are an index to his greatness!
__________________
A man\'s dreams are an index to his greatness!
 
Old December 4th, 2006, 09:46 AM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

First, you used a ";" and not a "," as the delimiter. Don't know what your import file actually looks like, though. Second, unless your temp tables have exactly the same number of columns as the file, you will need a format file to control the import. Third, you really need to take a look at Books Online for this... Lookup "Bulk Insert" to see all of the options you can use such as skipping the first couple of records.

--Jeff Moden
 
Old December 4th, 2006, 10:05 AM
Authorized User
 
Join Date: Dec 2006
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I am using ";" as a delimiter, below is the first line of what my data file looks like:

Code:
;TOTAL DEPOT ISSUES;>;145960.26;716.21;3809.23;7102.92;137598.24;-8362.02;1;0;1
i extracted the data from MySQL...Simply because i need to migrate the data....I've done the tables which always have data, but the table that the above data is placed, is just temporary, so after i use the data i issue a TRUNCATE TABLE query.

My "temp" table has one extra column at the end of the table, used for indexing...but i need this column to auto increment its value as data is populated into the table...Any ideas for the auto increment, in MySQL the in an auto increment property i normaly used when creating the table and set the default value to 1 and then as data is inserted, the value increments by 1.

I have looked up BULK INSERT and have found out how to use a format file, thanks for that, makes this much easier.

let me know if you have any ideas on the above mentioned wrt auto increment property.

Thanks

A man's dreams are an index to his greatness!
 
Old December 5th, 2006, 03:43 PM
Friend of Wrox
 
Join Date: Aug 2004
Posts: 385
Thanks: 0
Thanked 0 Times in 0 Posts
Default

My suggestion is to use ascii characters as delimiters that are not found on the keyboard. Say for example ascii value 250. You can create this character by holding down the alt key and typing "250" on the keypad. This minimizes the chance you will select a delimiter that exists in your data. When you bulk insert do it to a special bulk insert table. Then take the data from that table and put it into the table you want your data to reside in with an identity column if you wish. Bulk loading does not work well with operational tables. It tends to do a table lock on the table when your doing it.

 
Old December 6th, 2006, 02:39 AM
Authorized User
 
Join Date: Dec 2006
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for that jeff, i will look into your suggestion.

I have written a stored procedure listed below, to help with the import...
Code:
CREATE PROC Csv_File_Bulk_Import
@SrcFilePath varchar(50),
@FormatFilePath varchar(50),
@IntoTable varchar(50),
@RowNumber int
as
SET ANSI_WARNINGS OFF
DECLARE @str_command nvarchar(150)
SET @str_command = 'BULK INSERT ['+@IntoTable+'] FROM '''+@SrcFilePath
+''' WITH (formatfile = '''+@FormatFilePath+''', firstrow = ' + cast(@RowNumber as nvarchar) + ')'
EXEC SP_EXECUTESQL @str_command

GO
The parameters required are, in order: <src file>, <formatfile>, <destination table>, <number of rows to skip>

Viola...It works great!

I've learnt how fussy MS SQL Server can be, so when creating tables, unlike how i did in MySQL , for my bulk insert to work, I would have to make sure that (for the fields that it applies to) i set COLLATE SQL_Latin1_General_CP1_CI_AS and more often than not, it works.

But on the odd occassion is gives me an error when running the query, it tells me that on a specific column the collation type doesn't exist or something to tht effect.

Can anyone shed light on this error?

Ciao

A man's dreams are an index to his greatness!
 
Old December 6th, 2006, 03:10 AM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

It's usually bad data... and it can be a bugger to find. That is one advantage BCP has over Bulk Insert... it can detect and route bad records to an errata file without messing up the good records.

--Jeff Moden
 
Old December 6th, 2006, 03:15 AM
Authorized User
 
Join Date: Dec 2006
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default

ok...thanks, how would i use bcp? Im using PHP as my scripting language, so how would I use bcp with php?

You've been of great help

Thanks

A man's dreams are an index to his greatness!
 
Old December 6th, 2006, 11:28 AM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

You'd have to make a trip to the operating system... I don't know PHP because I'm a data-troll and not a Gui-guy but you would make a call to "Bcp.Exe"... It works VERY similar to Bulk Insert and the format file you made will still work with it. It IS command line based and the parameters, although similar to Bulk Insert as far as what they do, are quite different. And, there's a lot more of them. Probably the best thing for you to do is to cuddle up with Books Online (BOL) and read about what all the parameters can do. You can find them in BOL by doing a search on "bcp utility, overview" and expanding the "arguments" section.

--Jeff Moden
 
Old December 14th, 2006, 03:22 PM
Friend of Wrox
 
Join Date: Aug 2004
Posts: 385
Thanks: 0
Thanked 0 Times in 0 Posts
Default

PHP works basically like asp. If you have a php connection to your sql server you can use xp_cmdshell to shell out to the command prompt and run a BCP command. (I agree with Jeffs suggestion using BCP is preferred). But you have to have the appropriate rights setup on your sql server to allow this. In general in web coding this is considered very dangerous because of the way MS security works. When you open this up to you, you also open it up to hackers though.


 
Old December 15th, 2006, 03:02 AM
Authorized User
 
Join Date: Dec 2006
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for the comments guys, I've learnt alot!!!!

My final question(hopefully) is....
I was given a pc to use at work, which has already had SQL Server 2000 setup, although my suspicions say it could be permissions, but i'm not sure....the problem being that given then ffg directory structure(starting from the website root folder excluding (c:\inetpub\wwwroot)) : kznnet --> update --> formats

the above is where i have stored format files for the import of my CSV files, but for some reason when i issue the query with the following code
Code:
$sql="CSV_File_Bulk_Insert 'c:\\\Windows\\\Temp\\\\$csvFile', '\\formats\\$formatFile', 'tmpTbl', 0";
I get the error that the format file cannot be found. Why would this be? Since it works 100% on my home pc and another pc i use as a server.

Any Idea's?

Thanks...

Ciao

A man's dreams are an index to his greatness!





Similar Threads
Thread Thread Starter Forum Replies Last Post
Importing CSV file into SQL from Web daniel SQL Server 2005 6 December 15th, 2006 09:48 AM
Importing a CSV file into SQL rsmuts ASP.NET 1.0 and 1.1 Professional 2 July 24th, 2006 10:14 AM
Importing data from CSV file g_vamsi_krish ASP.NET 1.0 and 1.1 Basics 2 May 23rd, 2006 01:29 AM
Importing data from .csv Spitzbucket PHP Databases 0 February 20th, 2005 01:42 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.