TSQL Bulk Insert

Question

Hi all,

I have such csv file, fields delimiter is ,. My csv files are very big, and I need to import it to a SQL Server table. The process must be automated, and it is not one time job.

So I use Bulk Insert to insert such csv files. But today I received a csvfile that has such row

1,12312312,HOME   ,"House, Gregory",P,NULL,NULL,NULL,NULL

The problem is that Bulk Insert creates this row, specially this field "House, Gregory" as two fields one '"House' and second ' Gregory"'.

Is there some way to make Bulk Insert understand that double quotes override behaviour of comma? When I open this csv with Excel it sees this field normally as 'House, Gregory'

Answer 1

You need preprocess your file, look to this answer:

http://stackoverflow.com/questions/782353/sql-server-bulk-insert-of-csv-file-with-inconsistent-quotes

Answer 2

Take a look at this article: http://www.sqlteam.com/article/using-bulk-insert-to-load-a-text-file

Answer 3

If every row in the table has double quotes you can specify ," and ", as column separators for that column using format files

If not, get it changed or you'll have to write some clever pre-processing routines somewhere.

The file format need to be consistent for any of the SQL Server tools to work