How to Import from (quoted CSV) to SQL 2008? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2008 Forums

SSIS and Import/Export (2008)

How to Import from (quoted CSV) to SQL 2008?

Author

Topic

NickStan
Starting Member

36 Posts

Posted - 2009-10-23 : 10:44:14

I have a for each loop container that loops around a list of csv files.

Each csv file I need to import into my SQL server.

I first tried a data flow task in my for loop with a flat file source and a SQL destination, but this did not work either because I kept loosing the field definitions because I was setting the connection string (filename) at run time and it kept loosing the formatting that I had defined.

I then tried using the BULK insert command, but this does not work because I have commas in some of my descriptions.

So I got the field delimiter changed from comma to pipe, but this did not help either because I have pipes in some of my descriptions.

This should be so simple to do - but I have had so many issues.

I have also seem that Log Parser is also useful, but how do I use it?

Any suggestions will be welcome as I am all out of ideas.

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-10-23 : 12:12:42

Can you post a sample/example of the data?
Is it like this:
1,2,Carrot
1,3,"Apple, Orange"
2,1,Milk

Did you specify the Text Qualifier?

NickStan
Starting Member

36 Posts

Posted - 2009-10-23 : 13:00:42

Hi Yellowbug

There are about 50 columns in the csv file and yes there are commas in the descriptions.

This is the SQL code that I used for the bulk insert:

DECLARE @bulk_cmd varchar(1000)

SET @filepath = 'c:\shares\iwant\data\22_10_2009.csv'

SET @bulk_cmd = 'BULK INSERT dbo.product_import FROM '''
+ @filepath + ''' WITH (FIRSTROW = 2, ROWTERMINATOR = '''+CHAR(10)+''', FIELDTERMINATOR = '''+'"|"'+''')'

EXEC(@bulk_cmd)

So to answer your question - no, I did not specify the text qualifier - I thought that bulk insert did not support quoted CSV with commas in description fields.

Where would I specify the textdelimiter?

Thanks

Nick

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-10-23 : 15:20:07

The Text Qualifier is on the screen in SSIS where you setup the source flat file. To do the same in BULK INSERT you'd probably need a format file.

Do the files have different columns/structure? How do they differ?
Is each file going into a different table?
Are you open to using a script task to import the data?

NickStan
Starting Member

36 Posts

Posted - 2009-10-26 : 08:42:35

Hi Yellowbug

Thanks for the advice - I will try using a format file with Bulk Insert.

I have 2 questions about the format file:

1) How do I let the bulk insert know that a field is quoted csv?

2) How do I let the bulk insert know that I am using Unix line breaks (if that differs from the way I am currently specifying line breaks)?

Many thanks

Nick

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-26 : 08:47:01

See if the last point helps you
http://www.nigelrivett.net/SQLTsql/BCP_quoted_CSV_Format_file.html

Madhivanan

Failing to plan is Planning to fail

NickStan
Starting Member

36 Posts

Posted - 2009-10-26 : 15:45:39

Hi Madhivanan

Thanks for the URL - it helped clear up what line terminators to use.

I have setup my format file, but am still having problems running the bulk insert.

I am getting the following error:

Arithmetic overflow error for data type smallint, value = 3421745.

So I changed all my smallint fields to int - still the same error.

Is this error happening because the bulk insert thinks that one of my fields should be a smallint?

Very confused and frustrated (because importing from csv to sql should be so simple)

Thanks

Nick

NickStan
Starting Member

36 Posts

Posted - 2009-10-27 : 13:47:09

Hi Guys

I am giving up on trying to use Bulk insert, I have wasted so much time on it and am not getting anywhere.

So back to my original question: how do I import a csv file into sql in a for loop?

The list of files to import are in my database.

Any suggestions helpful at this point - I am under so much pressure to get this done.

Thanks

Nick

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-10-27 : 17:19:58

You can use a script task to import the csv files.

Previously, I've done something similiar using a VB script, with reading the files into a DataTable and loading into SQL Server tables with SqlBulkCopy.
And of course: http://msdn.microsoft.com/en-us/library/microsoft.visualbasic.fileio.textfieldparser.aspx

NickStan
Starting Member

36 Posts

Posted - 2009-10-28 : 13:47:03

Hi Yellowbug

Thanks for the reply (again) :)

I managed to eventually get the csv files imported using Logparser.

Here is an example of the command that I was using:

Logparser -i:CSV "SELECT * INTO dbo.product_import_temp FROM C:\data\feed_28_10_2009.csv" -o:SQL -server:UK-Serv01 -database:mydb -driver:\"SQL Server\" -username:i_writer -password:i_writer777* -createTable:OFF

Cheers

Nick

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources