Commas within CSV Data

Question

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 should I do?

Lamarckian

Can't you use another character to separate your values, such as ;? – Acclivity

@romaintaz - that's the wrong way to handle commas. Good csv uses quotes, and then escapes the quote with itself. – Stricklan

I have been provided with CSVs like these and i have to manage with that. – Lamarckian

Answer 1

If there is a comma in a column then that column should be surrounded by a single quote or double quote. Then if inside that column there is a single or double quote it should have an escape charter before it, usually a \

Example format of CSV

ID - address - name
1, "Some Address, Some Street, 10452", 'David O\'Brian'

Mccann

+1 this is the right way to do it. But "CSV" is so informal that people do all sorts of crazy stuff. – Sheritasherj

@gmagana - agreed, I remeber when I first used it, had allsorts of headaches untill I understood the 3 main things. 1) you need a field terminator, usually a comma, 2) you need a field enclosed by usually a double quote, Best to enclose all fields in double quotes unless dealing with integers and 3) you need an escape caracter incase you use a field encloser inside a field, usually a \. Format your csv like this and you life will be so much easier :) – Mccann

How can i check this when i am directly importing CSV to SQL Server. – Lamarckian

@Lamarckian - You can check how your CSV file is formatted by opening it in a basic text editor such as notepad. Check that your data in the CSV is formatted correctly. Then go ahead and import your CSV to your database. Dont know how you are importing and what tool you are using so cant help further there. But if you need to convert your CSV to SQL insert statements then you can use this csv2sql.com – Mccann

I am trying to bulk insert the CSV to SQL server like this – Lamarckian

BULK INSERT CSVTest FROM 'c:\csvtest.txt' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' ) GO – Lamarckian

I tried to put double quote around the column but csv is still messed up... – Macomber

Answer 2

For this problem the solution is very simple. first select => flat file source => browse your file => then go to the "Text qualifier" by default 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

Colley

This is the correct response. The previous answer may work, but it involves editing the source data. That does not always work. – Below

This is the perfect answer. Sometimes you have no control on how the CSV is formatted. Not sure why the one marked as the answer has been marked so. – Slavonic

If you have spaces between your csv entries, all Microsoft products (excel, sql server) fail badly. You need to remove the space first. – Leilaleilah

Woah! Simplest approach. – Illtreat

Answer 3

New version supports the CSV format fully, including mixed use of " and , .

BULK INSERT Sales.Orders
FROM '\\SystemX\DiskZ\Sales\data\orders.csv'
WITH ( FORMAT='CSV');

Pinch

The FORMAT='CSV' is the key information!!! Thanks! – Testamentary

Answer 4

I'd suggest to either use another format than CSV or try using other characters as field separator and/or text delimiter. Try looking for a character that isn't used in your data, e.g. |, #, ^ or @. The format of a single row would become

|foo|,|bar|,|baz, qux|

A well behave parser must not interpret 'baz' and 'qux' as two columns.

Alternatively, you could write your own import voodoo that fixes any problems. For the later, you might find this Groovy skeleton useful (not sure what languages you're fluent in though)

Bedight

Answer 5

Most systems, including Excel, will allow for the column data to be enclosed in single quotes...

col1,col2,col3 'test1','my test2, with comma',test3

Another alternative is to use the Macintosh version of CSV, which uses TAB's as delimiters.

Kennel

Answer 6

The best, quickest and easiest way to resolve the comma in data issue is to use Excel to save a comma separated file after having set Windows' list separator setting to something other than a comma (such as a pipe). This will then generate a pipe (or whatever) separated file for you that you can then import. This is described here.

Vaivode

What if it's too big for Excel? – Nicko

Answer 7

I don't think adding quote could help.The best way I suggest is replacing the comma in the content with other marks like space or something.

replace(COLUMN,',',' ') as COLUMN

Drowsy

Answer 8

Appending a speech mark into the select column on both side works. You must also cast the column as a NVARCVHAR(MAX) to turn this into a string if the column is a TEXT.

SQLCMD -S DB-SERVER -E -Q "set nocount on; set ansi_warnings off; SELECT '""' + cast ([Column1] as nvarchar(max)) + '""' As TextHere, [Column2] As NormalColumn FROM [Database].[dbo].[Table]" /o output.tmp /s "," -W

Emancipator