export from csv file problem

Question

export from csv file problem

ali.mahmoud.habib

Mr or Mrs. 500

Points: 500
More actions
August 9, 2010 at 2:28 am

#224393

I have a csv file containning a data like that
,.BAM30,BELTONE A.M,,EGP,08/08/2010,"4,090.5","4,101.96","4,108.68","4,090.5",,,,
the problem is done due to the values like "4,108.68"
I use the code to bulk insert

DECLARE @sqlCommand varchar(1000)
set @sqlCommand = 'BULK INSERT #temp FROM ''' + @Input_File + ''' WITH ( FIELDTERMINATOR = '','' , ROWTERMINATOR ='''') '
EXEC (@sqlCommand)
this code will separate depending on the , so "4,108.68" will be divded into "4, and 108.68" wich is totally wrong
How to avoid that please
Best regards

Viewing 4 posts - 1 through 3 (of 3 total)

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

steveb. SSC-Forever Points: 46733 More actions · Answer 1

Bulk Insert in SQL sever does not support quoted text, a better option would be to use the import/export wizard or SSIS to import the file as this will allow you to use quoted text.

http://msdn.microsoft.com/en-us/library/ms188609.aspx

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 2

steveb. (8/9/2010)
Bulk Insert in SQL sever does not support quoted text, a better option would be to use the import/export wizard or SSIS to import the file as this will allow you to use quoted text.
http://msdn.microsoft.com/en-us/library/ms188609.aspx

Actually, if the quoted text always appears and it's always in the same "field" position, you can use a BCP format file to import the text. If the quoted text is haphazard and you're lucky enough to have a 32 bit version of SQL Server of a 64 bit version of Jet Drivers, then OPENROWSET will even handle those. And, it's actually a heck of a lot easier that using SSIS packages. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 3

Here's what I'm talking about. Let's say you have a file called "ImportTest.txt" on the server in the "C:\Temp" that looks like this...

,.BAM30,BELTONE A.M,,EGP,08/08/2010,"4,090.5","4,101.96",1.5,"1.5",,,,

,.BAM20,BELTONE A.M,,EGP,08/08/2010,"4,090.5","1.5","4,108.68","4,090.5",,,,

,.BAM10,BELTONE A.M,,EGP,08/08/2010,"4,090.5","4,101.96","4,108.68",1.5,,,,

,.BAM40,BELTONE A.M,,EGP,08/08/2010,1.5,"4,101.96","4,108.68","4,090.5",,,,

Try running the following against that text table and see what you get...

SELECT *

FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Text;Database=C:\Temp;HDR=NO;FMT=CSVDelimited',

'SELECT * FROM ImportTest.txt')

Here's what I got...

F1F2F3F4F5F6F7F8F9F10F11F12F13F14

NULL.BAM30BELTONE A.MNULLEGP2010-08-08 00:00:00.0004,090.54,101.961.51.5NULLNULLNULLNULL

NULL.BAM20BELTONE A.MNULLEGP2010-08-08 00:00:00.0004,090.51.54,108.684,090.5NULLNULLNULLNULL

NULL.BAM10BELTONE A.MNULLEGP2010-08-08 00:00:00.0004,090.54,101.964,108.681.5NULLNULLNULLNULL

NULL.BAM40BELTONE A.MNULLEGP2010-08-08 00:00:00.0001.54,101.964,108.684,090.5NULLNULLNULLNULL

Guess what happens when you run the following???