Click here to Skip to main content
14,869,378 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a CSV file which i am directly importing to a SQL server table. In the CSV file each column is separated by a comma. But my problem is that i have a column "address", and the data in this column contains commas. So what is happening is that some of the data of the address column is going to the other columns will importing to SQL server.

What shall i do?
Posted
v2
Comments
   
Which server side language you are working on?
Ajeet Malviya 25-Feb-15 2:54am
   
i am just importing data using sql server wizard from csv file.
   
Okay. That means the format of csv is wrong. Those special characters should be handled while creating the file.

This should be handled by the application that generates the CSV file by enclosing cells that contain special characters (column separator character, double quotes, line breaks) with double quotes.

There is no real standard for CSV but common practices. See the Wikipedia article http://en.wikipedia.org/wiki/Comma-separated_values[^].
   
Comments
Ajeet Malviya 25-Feb-15 3:00am
   
i am able to import but problem is that i have a column "address", and the data in this column contains commas. So what is happening is that some of the data of the address column is going to the other columns will importing to SQL server.
Jochen Arndt 25-Feb-15 3:06am
   
The comma is the column separator character. Upon import each comma will move over to the next cell. To avoid this, such cells must be quoted. Then the import parser will not move to the next cell when it finds commas inside quoted cells.

Example for a proper CSV file:
ID, name, address
100,name without comma,"address, with comma"

You can't do anything on the import side. You must have proper CSV files.
Ajeet Malviya 25-Feb-15 3:52am
   
example -
the name of the header is - Address and the values of this header are Bangalore,India
when i import this file Bangalore comes in address header and India comes in another header, but what i need both Bangalore, India in the same header with the comma.???
Jochen Arndt 25-Feb-15 4:04am
   
The CSV file must contain "Bangalore, India" including the quotes.
Ajeet Malviya 25-Feb-15 4:12am
   
there is a "Bangalore, India" in csv file. but in the sql after importing the the csv the value comees like ""Bangalore in 1st cell and India"" in the next cell.
and if i import csv without comma tha the value comes like "Bangalore in 1st cell and India" in next cell???
Jochen Arndt 25-Feb-15 4:32am
   
See http://jessesql.blogspot.de/2010/05/bulk-insert-csv-with-text-qualifiers.html and http://stackoverflow.com/questions/3526136/csv-import-in-sql-server-2008

If you now want to avoid importing the quotes, you must use a format file.

Another approach would be using a different column separator character that will never occur in the source file. Then you don't have to use quotes. A candidate might be the TAB character.
Ajeet Malviya 25-Feb-15 4:49am
   
Thank you Jochen Arndt for the time.
for this problem the solution is very simple.
first select => flat file source => browse your file =>
than go to the "Text qualifier" by defaults its none write here double quote like (") and follow the instruction of wizard.

steps are -
first select => flat file source => browse your file => Text qualifier (write only ") and follow the instruction of wizard.

good luck
   
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900