Using a non-printable char (other then \0 \n \t) as fieldterminator argument in a bulk insert query

Question

I cannot believe that this is not answered somewhere, but I have searched MSDN, Stack Exchange and Google. I'm beginning to suspect it is not possible the way I would want to do this.

Does anybody know a way to do this in a query (not using other tools, I have seen answers using SSIS) using SQL-server?

Say I have a CSV file containing fields terminated by SOH (StartOfHeader, char(1)) and \n als row terminator. I thought I could import it using BULK INSERT, something like:

BULK INSERT Fubar
[dbo].[Persovorlage_DE]FROM 'c:\temp\foo.bar'
WITH
(
    FIRSTROW = 2,
    FIELDTERMINATOR = char(1),
    ROWTERMINATOR = '\n',
    TABLOCK,
    CODEPAGE = '1252'
)

That would do it, except that char(1) is not accepted.

Is there any way to set FIELDTERMINATOR to char(1)?

Postscript 1:

JeroenMostert suggested in the comments to use select char(1), and copy paste it between the single quotes of FIELDTERMINATOR which is invisible but works.

Postscript 2:

HoneyBadger suggested using 0x01, which also seems to work:

BULK INSERT Fubar FROM 'c:\temp\Foo.bar'
WITH
(
    FIRSTROW = 2,
    FIELDTERMINATOR = '0x01',
    ROWTERMINATOR = '\n',
    TABLOCK,
    CODEPAGE = '1252'
)

Put it in an answer and I will accept it

Copy and paste the output of `SELECT CHAR(1)`. The resulting character is invisible, but placed between quotes it should still work. (If it does work, don't forget to add a comment as that's a maintenance headache waiting to happen, of course.) — Jeroen Mostert, Feb 13 '18 at 09:32
@JeroenMostert: That works indeed, but is a nightmare for code-maintaining and requires more human interaction than copy-paste ;-) But it works, so for now I'm adding a little comment and use it. — Meine, Feb 13 '18 at 09:39
@Meine, yes. I've often had a row_terminator of `\n`, which is automatically prefixed with `\r`. In this case I've used `0x0a` successfully. It is also much clearer than empty whitespace. — HoneyBadger, Feb 13 '18 at 09:41
If `0x01` doesn't work and you don't like the invisible string, your other option is to create the `BULK INSERT` statement dynamically, where you can explicitly add `CHAR(1)` to the string and then `EXEC` the whole thing. — Jeroen Mostert, Feb 13 '18 at 09:42
Interestingly, the [docs](https://learn.microsoft.com/sql/relational-databases/import-export/specify-field-and-row-terminators-sql-server) make no mention of this support for control characters, and indeed go out of their way to deny they are usable at all. The `0x` syntax isn't mentioned. Fortunately they are editable these days and they really do accept pull requests... but I'm not confident enough to do it. — Jeroen Mostert, Feb 13 '18 at 09:57
@JeroenMostert: Yes, I had read the reference docs too (updated question to reflect that), that especially made me think it would not be possible. — Meine, Feb 13 '18 at 10:13

score 2 · Accepted Answer · answered Feb 13 '18 at 11:06

2

While the docs don't mention it (or even actively deny it), you can use hexadecimal values to specify particular characters. Such as:

BULK INSERT Fubar FROM 'c:\temp\Foo.bar'
WITH
(
    FIRSTROW = 2,
    FIELDTERMINATOR = '0x01',
    ROWTERMINATOR = '\n',
    TABLOCK,
    CODEPAGE = '1252'
)

Similarly, if you have a \n terminator, you can use 0x0a. If you don't, \n will be automatically prefixed with \r (although I can't recall by heart in what circumstances it does), and your terminator won't be recognized.

answered Feb 13 '18 at 11:06

HoneyBadger

14,750
3
34
48

1

The reference [docs](https://learn.microsoft.com/en-us/sql/relational-databases/import-export/specify-field-and-row-terminators-sql-server) say this: "When you use bcp interactively and specify \n (newline) as the row terminator, bcp automatically prefixes it with a \r (carriage return) character, which results in a row terminator of \r\n." So that at least is one instance. – Meine Feb 13 '18 at 12:44
Ah yeah, that rings a bell. – HoneyBadger Feb 13 '18 at 12:45

Using a non-printable char (other then \0 \n \t) as fieldterminator argument in a bulk insert query

1 Answers1