Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Bulk Insert - CSV with and without quotes Expand / Collapse
Author
Message
Posted Wednesday, September 25, 2013 10:39 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:13 AM
Points: 215, Visits: 426
Hello

I need to Bulk Insert a .csv file into an SQL table. The problem is within the .csv file some fields have quotes some have commas within the fields. Does anyone have a solution or a workaround of how I can handle this?

.csv file as follows:

"1 High Street","London","United Kingdom"
"1,High Street","London","United Kingdom"
"1,High Street",,United Kingdom


SQL Code as follows:
BULK INSERT test FROM 'c:\test\test.csv' WITH (FIELDTERMINATOR=',',ROWTERMINATOR = '')
select * from test


You should be able to see all the issues I am experiencing. I tried importing with the quotes specified but due to not all fields having these present it imported incorrectly again.

Any ideas?

Thanks
Post #1498484
Posted Thursday, September 26, 2013 2:20 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 9:09 AM
Points: 3,088, Visits: 3,329
Deleted - re-read the question and I was wrong

-------------------------------
Posting Data Etiquette - Jeff Moden
Smart way to ask a question

There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
Post #1498714
Posted Thursday, September 26, 2013 7:32 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 21, 2014 1:54 PM
Points: 1,430, Visits: 3,229
You will need to use BCP with a format file. That way you can specify which columns are quoted and which are not. The format file has a way to specify each column, including separate delimiters which is the case when you have quoted columns.


edit: actually I may have misunderstood you dilemma also. If some columns have quotes and others don't, I would suggest reformatting the file to strip the quotes and replace the column delimiter with something else ( like pipe or any other character that would not show up in any of the columns). I have done this in the past and it works well. It does require to write a simple reformatting utility in .NET or some other scripting language but that's pretty simple.






The probability of survival is inversely proportional to the angle of arrival.
Post #1498837
Posted Thursday, September 26, 2013 11:34 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:13 AM
Points: 215, Visits: 426
Thanks for the replies, I'll have a look into reformatting the file with .net

Post #1498981
Posted Friday, September 27, 2013 6:54 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 25, 2014 5:42 AM
Points: 136, Visits: 252
Yuck.

You can also import each entire line into a table then use some kind of multi-pass replace() construct or a REGEX via CLR to change out the delimiters.


____________
Just my $0.02 from over here in the cheap seats of the peanut gallery - please adjust for inflation and/or your local currency.
Post #1499356
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse