How Do I Import Csv With Commas

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

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 24-Feb-15 20:39pm

Ajeet Malviya

Updated 24-Feb-15 20:44pm

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ)

v2

Add a Solution

Comments

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 25-Feb-15 2:44am

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.

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 25-Feb-15 3:21am

Okay. That means the format of csv is wrong. Those special characters should be handled while creating the file.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Jochen Arndt · Accepted Answer · 2015-02-24T20:53:00

Solution 1

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[^].

Posted 24-Feb-15 20:53pm

Jochen Arndt

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.

Ajeet Malviya · Accepted Answer · 2015-02-25T00:10:00

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