question

ssalun avatar image
0 Votes"
ssalun asked ssalun commented

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

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.


sql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


[...] The value is converted to 0xE972600.

Did you mean 0x0E972600 or 0xE9726000?

0 Votes 0 ·
Viorel-1 avatar image
0 Votes"
Viorel-1 answered ssalun commented

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


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Viorel-1 ,
I tried this and it worked .. Thank you very much for the solution.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog commented

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

In addition to my post above (which I wrote just before bedtime), I tried this with this data file:

ABCD9876
e97260f

and this table:

CREATE TABLE bin (b binary(4))

With BCP the result was indeed

0xABCD9876
0xE9726000

That is, BCP decided to simlpy ignore the last odd nibble. 00 was filled in, since the column is fixed length.

I also tried to load the file with BULK INSERT. This produced the error mesage:

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

And only the value 0xABCD9876 was loaded into the table.

So you need to track down where this odd nibble is coming from, and if you can avoided, you will need to preprocess the file to make sure that all hex strings have an even number of bytes.

0 Votes 0 ·
ssalun avatar image
0 Votes"
ssalun answered MelissaMa-msft converted comment to answer

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..

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

You rarely have much luck with bad data. What result do you want from this misfit?

0 Votes 0 ·

I want final output as e97260f and 0xE97260F. As i will be using this value to join another table and fetch records from it.

0 Votes 0 ·

In such case you should insert the value into a varchar column and forget all about binary. 0xE97260F is not a possible binary value.

What is the data type of that other column?

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered ssalun commented

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you for the help!

0 Votes 0 ·