BCP file into Sql server table having binary values in the column

Question

Hi ,

I have a data file in which one column has binary values ..I am doing sed and removing "0x" so the value to be imported is e97260f. I am able to import all binary values into a table which has column datatype as binary(4) except this value. The value is converted to 0xE972600.

Could you please help.

Thanks.

Answer 1

It seems that you did not try yet all of suggestions. If you have an intermediate table with hexadecimal strings of various length, then check this example:

declare @temp_table table( hex_string varchar(8) )

insert @temp_table values 
( 'e97260f' ),
( 'A' ),
( 'AB' ),
( 'ABC' ),
( 'ABCD' )

declare @bin table ( b binary(4) )

insert @bin (b)
select convert( binary(4), iif(len(hex_string) % 2 = 1, '0' + hex_string, hex_string), 2)
from @temp_table

select * from @bin

Answer 2

e97260f is not a correct text representation of binary value, since there is an odd number of hex characters.

I find that if I do:

DECLARE @b binary(4) = 0xe97260f
SELECT @b

The result is 0x0E97260F. On the other hand, if I do:

SELECT convert(binary(4), '0xe97260f', 1)

I get the error message:

Msg 8114, Level 16, State 5, Line 48
Error converting data type varchar to varbinary

It appears that BCP applied yet a variaton and simply ignore the last odd nibble.

Answer 3

Hi ErlandSommarskog ,

Thanks for your input, I have ran into same situation.
CREATE TABLE bin (b binary(4))
With BCP the result was indeed

0xE9726000

The old nibble is been written by binary file . I tried loading into a varchar field and after try moving to a binary field using convert function.. but still no luck..

Answer 4

Hi @SwapnilSalunke-4731,

Agreed with other experts, you could validate all the suggestions and find out a possible soltution.

0xE97260F is actually not a valid binary value. You may check the source of this value and avoid appearing again.

If you would like to import the data whatever it is valid or not, you could consider to change the column type of the table from binary(4) to varchar as below:

declare @output table( hex_string varchar(8) )  
      
 insert @output values   
 ( 'e97260f' )  
  
 declare @input table ( b varchar(100) )  
  
 insert into @input   
 select '0x'+convert(varchar(20),hex_string)  
 from @output  
  
 select * from @input  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.