Import data file with non-printable field delimiter FS (0x1c)

Question

I am trying to bulk insert a data file into a SQL table. The data files structure is:

Col1FSCol2FSCol3
Val1FSVal2FSVal3

FS is a control character (File Separator), corresponding to Byte 1C.

However, the following bulk insert code in SQL does not work:

bulk insert schema.table
from 'filepath'
with (
  datafiletype = 'char'
, codepage = 'ACP'
, firstrow = 2
, fieldterminator = '0x1C'
, rowterminator = '\n'
, tablock
)

Which dbms? (Product specific question.) – jarlh Dec 21 '20 at 14:53 — jarlh, Dec 21 '20 at 14:53

score 0 · Answer 1 · answered Dec 21 '20 at 14:51

We just solved the problem: If fieldterminator is specified in Hex-Notation, rowterminator also has to be specified in Hex-Notation.

Therefore, the solution is:

 bulk insert schema.table
from 'filepath'
with (
  datafiletype = 'char'
, codepage = 'ACP'
, firstrow = 2
, fieldterminator = '0x1C'
, rowterminator = '0x0A'
, tablock
)

Import data file with non-printable field delimiter FS (0x1c)

1 Answers1