sql-serversql-server-2008bulkinsert

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


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


Solution

  • 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;