3

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'

gbn
  • 422,506
  • 82
  • 585
  • 676
Andriy Kizym
  • 1,756
  • 2
  • 14
  • 29

3 Answers3

3

You need preprocess your file, look to this answer:

SQL Server Bulk insert of CSV file with inconsistent quotes

Community
  • 1
  • 1
Michael Pakhantsov
  • 24,855
  • 6
  • 60
  • 59
  • So what does size have to with it? Preprocessing is the answer if you can;t change the file. Or get your provider to properly give you the data with all the columns being surrounded by quotes or using something other than a comma for a delimeter (I send back any file where they try to use that, it is simply unaccepatble to send a comma delimited file.) – HLGEM Sep 03 '10 at 14:08
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

gbn
  • 422,506
  • 82
  • 585
  • 676
0

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

Michael Bahig
  • 748
  • 8
  • 17