trouble with BULK INSERT from .csv

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

trouble with BULK INSERT from .csv

Author

Topic

lcsgeek
Starting Member

38 Posts

Posted - 2012-05-24 : 12:01:26

Having a bit of trouble inserting records into table from csv file. Primarily when there is a text identifier of double-quotes and secondly where the first row contains column names. I need the BULK INSERT to ignore the first row as well as the double-quote text identifiers.


BULK
 INSERT CSVTest
 FROM 'c:\csvtest.txt'
 WITH
 (
 FIELDTERMINATOR = ',',
 ROWTERMINATOR = '\n'
 )
 GO

This code inserts column heads into my table as well as double-quotes around text fields. Any help is appreciated. Thanks much.

lcsgeek
Starting Member

38 Posts

Posted - 2012-05-24 : 14:52:10

Don't want to have to manipulate flat file before import but looks like my only option. I opened the flat file in notepad and removed column heads as well as deleted all double-quote marks. Now when I run the import my columns are all screwed up becaues I have comma's in my data which are processing as field terminators. Is this Bulk Insert function not very well developed or am I missing something? I've looked over the documentation and I'm not seeing where text identifier can be designated like the Access data import wizard asks. Seems like this would be a good argument to add to this function as it would allow commas to be embedded in the data without being confused with field terminators. If I've totally overlooked a solution I would so appreciate some direction - thanks.

LarryC74
Yak Posting Veteran

94 Posts

Posted - 2012-05-25 : 09:34:38

Hey lcsgeek,
you can specify which row to start with.


BULK
 INSERT CSVTest
 FROM 'c:\csvtest.txt'
 WITH
 (
 FIELDTERMINATOR = ',',
 FIRSTROW = 2, --this will start on the second row...not your column names.
 ROWTERMINATOR = '\n'
 )
 GO

I'm not real sure how to get the double quotes out without manipulating the data after you import in the table. Maybe a stored procedure to clear those out. Maybe use the REPLACE function for that after it is imported in the table?

[url]http://msdn.microsoft.com/en-us/library/ms186862.aspx[/url]

Everyday life brings me back to reality

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-25 : 10:35:03

why cant you specify the column delimiters by means of format file. Here you can specify " also part of delimiter to get only required data in tables

see last example in

http://www.nigelrivett.net/SQLTsql/BCP_quoted_CSV_Format_file.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources