TSQL Bulk Insert

Question

I have such a csv file, field separator ,. My csv files are very large and I need to import them into a SQL Server table. The process must be automated, and this is not one time.

Therefore, I use Bulk Insert to insert such csv files. But today I got csvfile which has such a line

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

The problem is that Bulk Insert creates this line, especially this field is "House, Gregory" as two fields are one "House" and the second "Gregory".

Is there a way to make Bulk Insert understandable that double quotes override comma behavior? When I open this csv using Excel, it usually sees this field as "House, Gregory"

Answer 1

You need to pre-process your file, look at this answer:

SQL Server Bulk Insert CSV File with Inconsistent Quotes

Answer 2

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

If not, change it, or you will need to write some smart preprocessing procedures.

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

Answer 3

Sql Server, , ( Microsoft ). , . Sql Server ( 2014) , sql Bulk Insert.

This one has a widget where you can define the Text Separator as ", it also does not cause string length problems, because it uses the Access Access Text data type. If you are comfortable with the results in Access, you can import them later on Sql Server without any problems.

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