Skip to content
Advertisement

Bulk insert csv file with semicolon as delimiter

I’m trying to import data from semicolon separated csv file into a SQL Server database. Here is the table structure

The csv file is shown below:

There are some columns that I don’t need, so I create a format file to import the data. The format file is shown as below

Then I tried both bulk insert and bcp in – neither of them works.

Here is the bulk insert command

But unlucky some error file were generated. Here is what myRubbishData.log error says:

Row 2 File Offset 1993 ErrorFile Offset 0 – HRESULT 0x80004005

And the actual row stored in myRubbishData.txt:

As you can see, it seems like rows are not correctly separated. So I tried to change the row delimiter to “n”,”r”,”nr”,”rn”, none of them work.

And I tried bcp. It did not work either.

Here is the bcp command I used:

Then I get an error said somehow the same thing

SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 17 for SQL Server]Unexpected EOF encountered in BCP data-file

0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1

One interesting things is, if I create a new excel and choose “Get data” option to import the csv file, the file can be literally correctly parsed.

Basically I can’t find what I did wrong here. Can someone help me on this one?

Advertisement

Answer

The SQL Server import facilities are very intolerant of bad data and even just formatting variations or options. In my career, I have literally spent thousands of work-hours trying to develop and debug import procedures for customers. I can tell you right now, that trying to fix this with SQL alone is both difficult and time-consuming.

When you have this problem (bad data and/or inconsistent formatting) it is almost always easier to find or develop a more flexible tool to pre-process the data into the rigid standard that SQL expects. So I would say that if Excel can parse it then just use Excel automation to pre-process them and then use SQL to import the Excel output. If that’s not practical for you, then I’d advise writing your own tool in some client language (C#, Vb, Java, Python, etc.) to pre-process the files.

You can do it in SQL (and I have done it many times), but I promise you that it is a long complicated trek.

SSIS has more flexible error-handling for problems like this, but if you are not already familiar and using it, it has a very steep learning curve and your first SSIS project is likely to be very time-consuming also.

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement