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