Commas within CSV Data

问题:

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?

回答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'

回答2:

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

回答3:

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)

回答4:

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.

回答5:

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.

回答6:

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

回答7:

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