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?

Answer 1

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

Answer 2

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

Answer 3

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'

Answer 4

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

Answer 5

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)

Answer 6

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.