1

I am trying to do the following bulk insert:

BULK INSERT DATABASE.TABLE FROM 'C:\local\output\TESTFILE.txt' WITH (FIELDTERMINATOR = ';', ROWTERMINATOR = '\n').

Receiving the following error:

Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 1, column 2 (BinaryString).

Entire contents of TESTFILE.txt is as follows:

 /STR_8.YOU.Test ; 1C 48 D0 00

NOTE: When I remove the spaces between binary data, so it looks like 

 /STR_8.YOU.Test ; 1C48D000

it works just fine.

My TABLE has the following 2 columns in it:

IDString      varchar(50)
BinaryString  varbinary(MAX)

Is there a particular data type to use to account for not only a binary string, but also the space character? I have tried using all reasonable data types to include varchar(MAX), varbinary(MAX), image (replacement for BLOB?). Or is there a particular argument I need to use to allow for the space character?

ALittleHelpPlease
  • 11
  • 1
  • 4
  • 1
    You could import it to a staging table with a varchar(max) second column, remove the spaces and then import it to your destination table. – Tab Alleman Jan 15 '15 at 14:25
  • Thanks for the response. I am not familiar with staging tables, but can you tell me a little more about how importing to a staging table would differ from importing to a destination table? Is there a little more leniency on the data? I guess I am just assuming the same issue in trying to use varchar with existing spaces would be encountered. And similar to your suggestion, if all else fails I can simply manipulate the data prior to DB import to remove the spaces; was just hoping to not have to do this step. – ALittleHelpPlease Jan 15 '15 at 14:43
  • A staging table is just a regular table, but its purpose is to temporarily hold data so you can cleanse it before moving it to the final destination. I am assuming that the spaces in your data make it invalid for importing to a binary column, but it should be fine to import to a varchar. – Tab Alleman Jan 15 '15 at 14:46
  • With spaces in the data I have had no success in using any data type thus far to import the data, to include varchar. – ALittleHelpPlease Jan 15 '15 at 14:51

1 Answers1

0

I'm completely grabbing at straws here, but there seems to be an issue with bcp and binary columns. I remember reading somewhere that bulkinsert uses bcp in the background, so maybe this will work for you too.

The fix was to specify a format file for the import.

SQL Server BCP export corrupted file?

Hope that helps

Community
  • 1
  • 1
Spock
  • 4,700
  • 2
  • 16
  • 21