TSQL Bulk Insert

Question

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

Since you are referring to Sql Server, I assume you have Access available as well (Microsoft-friendly environment). If you do have Access, I recommend you use its Import Wizard. It is much smarter than the import wizard of Sql Server (even version 2014), and smarter than the Bulk Insert sql command as well.

It has a widget where you can define the Text seperator to be ", it also makes no problems with string length because it uses the Access data type Text. If you are satisfied with the results in Access you can import them later to Sql Server seamlessly.

The best way to move the data from Access to Sql is using Sql Server Migration Assistant, available here

Answer 2

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

Answer 3

You need preprocess your file, look to this answer:

SQL Server Bulk insert of CSV file with inconsistent quotes