August 8, 2006 at 7:53 am
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?
August 9, 2006 at 6:43 am
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.
August 9, 2006 at 6:46 am
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
August 9, 2006 at 7:32 am
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
August 9, 2006 at 7:35 am
Thanks for that,
Im at this very moment trying to get my head round using file formats. Ill keep going with that!
Debbie
August 9, 2006 at 7:49 am
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...
August 9, 2006 at 8:21 am
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
August 9, 2006 at 8:24 am
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!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply