question

AngelMartinez avatar image
AngelMartinez asked

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-insertnullnullvalues
1618846656001.png (3.8 KiB)
1618846832192.png (2.3 KiB)
2 comments
10 |1200

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

Kev Riley avatar image Kev Riley ♦♦ commented ·

How are you defining #DATA ?

0 Likes 0 ·
AngelMartinez avatar image AngelMartinez Kev Riley ♦♦ commented ·

Hi Kev - this is defined as VARCHAR(MAX)

0 Likes 0 ·

1 Answer

·
Kev Riley avatar image
Kev Riley answered

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 comments
10 |1200

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

AngelMartinez avatar image AngelMartinez commented ·

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 Likes 0 ·
file.txt (68 B)
Kev Riley avatar image Kev Riley ♦♦ AngelMartinez commented ·

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

0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ AngelMartinez commented ·

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

0 Likes 0 ·
AngelMartinez avatar image AngelMartinez Kev Riley ♦♦ commented ·

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 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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