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.