How can I BULK INSERT data from TEXT file without losing data after a NUL value is found

Question

question

AngelMartinez asked Apr 20, '21

How can I BULK INSERT data from TEXT file without losing data after a NUL value is found

Hi SSC community, so from the title description I am trying to use the BULK INSERT command as well as BCP to get data in from a text file.

The file is pipe delimited - here is a snippet.

You can see the NUL value in there. Well, this when loaded into SQL only loads up to this point then drops all remaining text for that row. I can trim the NUL out using Notepadd++ by searching for /x00 no problem and it loads fine. However, leaving this in the file I can't for the life of me get this to load using BULK INSERT or BCP, all I get is this in the table output

I've tried the following in SSMS:

CREATE TABLE #DATA (DATA VARCHAR(MAX))
BULK INSERT #DATA
FROM 'C:\FILE.TXT'
WITH (
  DATAFILETYPE = 'char',
  FIELDTERMINATOR = '|',
  KEEPNULLS
);

SELECT * FROM #DATA

...as well as from the CMD prompt where the switch -k command prompt is to keep nulls:

bcp tempdb.dbo.#DATA in 'C:\FILE.TXT' -c -t, -T -k -Sserver_name

I've reviewed this page and exhausted all avenues. Any help's appreciated.

Thanks

bulk-insert null nullvalues

1618846656001.png (3.8 KiB)

1618846832192.png (2.3 KiB)

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley ♦♦ commented · Apr 20, 2021 at 08:12 AM

How are you defining #DATA ?

0 ·

AngelMartinez Kev Riley ♦♦ commented · Apr 20, 2021 at 08:50 AM

Hi Kev - this is defined as VARCHAR(MAX)

0 ·

Answer 1 · 2021-04-20T09:01:01Z

Kev Riley answered Apr 20, '21

If you insert into a table with columns matching the pipe delimited, then the BULK INSERT works fine

For example, I created a text file matching your input file, and then ran the BULK INSERT against a single column table

drop table if exists tempdb..#DATA;
create table #DATA (c1 varchar(max))

bulk INSERT #DATA
FROM 'C:\temp\FILE.TXT'
WITH (
  DATAFILETYPE = 'char',
  FIELDTERMINATOR = '|'
);

SELECT * FROM #DATA

which gave the same result as you see

c1
-----------------------------------------------------------------------------
200995014|202475367|Addendum Letter 30-03-2021| |BasicLetterTemplate

(1 row affected)

but if I define the target table to match the the pipe delimited file, then the data is inserted correctly

drop table if exists tempdb..#DATA;
create table #DATA (c1 varchar(50),c2 varchar(50),c3 varchar(50),
                c4 varchar(50),c5 varchar(50))

bulk INSERT #DATA
FROM 'C:\temp\FILE.TXT'
WITH (
  DATAFILETYPE = 'char',
  FIELDTERMINATOR = '|'
);

SELECT * FROM #DATA

c1            c2             c3                           c4   c5
------------- -------------- ---------------------------- ---- --------------------
200995014     202475367      Addendum Letter 30-03-2021        BasicLetterTemplate

(1 row affected)

4

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

AngelMartinez commented · Apr 20, 2021 at 09:26 AM

Thanks Kev

I can see that your first BULK INSERT is showing the BasicLetterTemplate text after the NUL value. Try using the file attached and you will see that your first query does not work.

FILE.txt

I agree that by specifying the columns the data is inserted correctly but what I want to understand is what is SQL doing when it finds the NUL value and why does it stop inserting text as part of a BULK INSERT insert into a VARCHAR(MAX) field?

Thanks.

0 ·

file.txt (68 B)

Kev Riley ♦♦ AngelMartinez commented · Apr 20, 2021 at 09:41 AM

run the final select with results to text and not to grid - do you see the full data?

0 ·

Kev Riley ♦♦ AngelMartinez commented · Apr 20, 2021 at 09:50 AM

It's an issue with SSMS showing the data, not the insert itself

0 ·

AngelMartinez Kev Riley ♦♦ commented · Apr 20, 2021 at 09:56 AM

Well I'll be damned! Was that it all along? You're a gent Kev! Thanks, the data was there all along. Learn something new every day. Genius. Thanks again!

0 ·

question