Bulk insert to varbinary field from csv. Bulk load data conversion error

Question

I have a csv document that contains two columns: the object number (int) and its png image in binary form. The table has the following structure:

ID	Body

The ID field contains the object identifier (1, 2, 3, etc.), and the body field contains a binary representation in the following form:

89504E470D0A1A0A0000000D4948445200000319000002340802000000FEC473F40001000049444154789CECFD599364D9751E0AEEE1CC83CF4378CC915365660D59857920C84B0D26F24ABC4DB559DBFD017AD0A34C6F32BDF77D947E80CCD4BA6D46F151EA36C9244A0401168881280228546565E5109991317BF8EC679EF6DEFDF0453812852A80455611C06D6C4BF38CF0703F (and a lot more signs)

I need to insert this data to MS SQL table. So that in the end they are recorded correctly and displayed as an image.

I tried to do BULK INSERT query, but it doesn't work.

bulk insert #TempImageHex 
 FROM 'D:\Images.csv'
 WITH (
  rowterminator = '\n',
  fieldterminator = ';',
  firstrow = 1)

Gives the following error: Bulk load data conversion error (truncation) for row 1, column 2 (images).

Answer 1

You'll need to convert the hex string to varbinary in order to import the binary value.

Import the data into a staging table with varchar(MAX) and then use INSERT...SELECT with a CONVERT function to convert the hex string to binary for the final table.

CREATE TABLE #TempImageHexString(
      ID int NOT NULL
    , BodyHexString varchar(MAX)
);

CREATE TABLE #TempImageHex(
      ID int NOT NULL
    , Body varbinary(MAX)
);

BULK INSERT #TempImageHexString
FROM 'D:\Images.csv'
WITH (
  ROWTERMINATOR = '\n',
  FIELDTERMINATOR = ';',
  FIRSTROW = 1);

INSERT INTO #TempImageHex (ID, Body)
    SELECT ID, CONVERT(varbinary(MAX), BodyHexString, 0)
    FROM #TempImageHexString;