Problem with bulk insert from cvs using escape character '\;'

Question

Hi,

I have a table called fb containing the columns A, B, C, D, E. I make a bulk insert from a file fb.txt.

First, here is an example of the content of the file fb.txt:

2012;0203;68753;blabla;something;
2012;0203;68753;blablu;something else;
2012;0203;68753;bla\; or bli;another something;

My column separator is ';' but in the last row in "bla\;or bli" I have an '\;' escape character so that the semicolon should be ignored. However, T-SQL does NOT recognize the escape char and generates the last column like "or bli;another something".

How can I make this escape character work?

Here the insert statement:

BULK INSERT fb
FROM 'D:\FSV_Import\fb.txt'
WITH
 (
 CODEPAGE = 'ACP',
 FIELDTERMINATOR = ';',
 ROWTERMINATOR = ';\n'
 )
GO

---

Greetings from No Planet!

Answer 1

Sorry, you will not be able to load that file with BULK INSERT.

BULK INSERT and BCP read a binary stream of bytes and consumes one field at a time. A field can be defined by one of three means:

o  The field is preceded by 1, 2 4 bytes holding the length.
o  Fixed width.
o  Terminator.

When you have specified a terminator, BULK INSERT consumes all bytes until it finds the terminator for that field. In the command you have FIELDTERMINATOR and ROWTERMINATOR, but the ROWTERMINATOR is just the field terminator for the last field. There are no means to escape terminators´, but a terminator is a terminator.

There is one special case: if you have semicolons in the last field, they will not be interpreted as terminators, since at this point BULK INSERT is looking for end of line. But if there is any \ preceding the semicolon it will be included.

If you are generating this file yourself, you need to consider a different format, if you want to use BULK INSERT.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 2

Thank you Erland, is there another way to insert this file into a table in T-SQL?

---

Greetings from No Planet!

Answer 3

You may try SSIS as an option for such cases.

---

Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

Answer 4

Hi ,

I agree with lateesh, u can use flat file source and OLE DB as destination give a flat file connection to the file where u have placed and map the respective feilds

and inbetween Flat file source and OLE DB destination probably u can use derived column to overcome the issue as u have mentioned in ur thread

thanks,

Santhosh

---

Please have look on the comment

Answer 5

Hi Anarchipur,

I suggest you to use SSIS instead of BULK INSERT, because it is more reliable, robust and quiker. Below are the quick instructions how you can perform this task:

SQL SERVER – Import CSV File into Database Table Using SSIS

---

Regards,

Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

http://basitaalishan.com

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Answer 6

Hi Basit, I doubt on "QUIKER".

BCP should be ideally faster than the SSIS if the file format supports due to less loging and less validations. 

Please corrct me if am wrong.

---

Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

Answer 7

Hi Latesh,

SSIS is definately faster if you use Fast Load option when bluk loading the data. This is because Fast Load option is really fast on clustered indexed tables with SSIS. See the following articlesfor more information on bukl loading the data:

Speeding Up SSIS Bulk Inserts into SQL Server
Using SQL Server Integration Services to Bulk Load Data

---

Regards,

Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

http://basitaalishan.com

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.