Bulk import (Again) with csv files

Question

Bulk import (Again) with csv files

Debbie Edwards

SSCrazy Eights

Points: 9217
More actions
August 8, 2006 at 7:53 am

#173544

Another query about bulk importing.
I have a lot of files to import into SQL Server. At present some of these files contain fields with commas in them. E.g. " 55,Smithstreet road" When you open the files up in text pad you can see that these fields are enclosed with "" and yet they are importing into the SQL table incorrectly. (Being split into 2 seperate columns)
Within the bulk import SP I have the following:
set @Query ='BULK INSERT '+ @Tablename + ' FROM "'+ @Filepath+@Filename+'" WITH ( FIELDTERMINATOR = '','',ROWTERMINATOR = ''\n'',FIRSTROW = 2)'
Is theer anything else I can specifiy to ensure that even the fields mentioned above get imported into the correct columns and not split?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Luke L SSC-Dedicated Points: 35547 More actions · Answer 1

Do you have any control over the way the import file is created? If so, can you change the delimiter from a ',' to something that won't be included in your data? I've used '^' and '~ in the past with pretty good results.

Also you could try to play with the quoted identifier setting and see if that helps.

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]

Debbie Edwards SSCrazy Eights Points: 9217 More actions · Answer 2

Unfortunately not. We get the files in as is and we are trying to sort out the import so we dont have to touch any of the source files.

The quoted identifier setting sounds interesting! Do you have any more information on this? Is it something I can set in the stored procedure?

Thanks for your help

Debbie

Luke L SSC-Dedicated Points: 35547 More actions · Answer 3

After Re-Reading the Section in BOL on SET_QuotedIdentified I decided this probably wouldn't work for you. I believe the real solution will be to use a format file. This way you can specify the proper delimiter on individual columns so that the address columns with the commas could be delimited with """,""" I beleive you'll have to escape the " and that's why I'm using 3 on each side.

You can read about format files here...

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_impt_bcp_2e5s.asp

To help us help you read this[/url]For better help with performance problems please read this[/url]

Debbie Edwards SSCrazy Eights Points: 9217 More actions · Answer 4

Thanks for that,

Im at this very moment trying to get my head round using file formats. Ill keep going with that!

Debbie

Luke L SSC-Dedicated Points: 35547 More actions · Answer 5

also if you receive files withthe same filename everyday you could use a linked server va the Jet 4.0 text driver. It's somewhat slow for large files, but it should handle the issues with the files changing from time to time. all you need to do is add a statement to the Schema.ini file for each text file you need to import.

Although, I'd imagine that if you're doing this, it could just as easily be done with DTS where you can set a text identifier quite easily. Again slow etc etc.. but you could perhaps do this in an sp and reset the file name with a global varibale that you pass in from a stored procedure or whatnot...

To help us help you read this[/url]For better help with performance problems please read this[/url]

Carolyn Richardson SSCrazy Eights Points: 8357 More actions · Answer 6

I've had similar problems in the past, I like to use either a Pipe as a seperator or if I am likely to have a file with lots of horrible text character strings making " or ' or , a problem get the file sent as fixed width, then you should have far less problems.

Regards

Carolyn

Facts are stubborn things, but statistics are more pliable - Mark Twain
Carolyn
SQLServerSpecialists[/url]

Debbie Edwards SSCrazy Eights Points: 9217 More actions · Answer 7

Thanks for that,

I think Im going to put a few of these suggestions into the people that create the files as it seems to be the way forward on this one!