I have a varbinary(max) column that I need to bulk import. The data I'm putting into this column is a byte in C#. How do I format the data in the comma-delimited file used for bulk import? In other words, how do I convert from a byte to a string in a way the bcp utility will accept?
A varbinary value is of the form
0x01020304 for SQL Server to read.
So your CSV would be
col1,col2,varbinary foo,1,0x01020304 bar,2,0x988776655443
Edit May 2013,
After testing, I can't get this to work with BULK INSERT with or without a format file.
I will post something more when I get it working. I can't delete this answer
You can use the BCP unicode native format to import/export. Use
bcp -n for the export and the
WITH (DATAFILETYPE='widenative') clause for import. Click link to read more.
I got it to work. Your varbinary data must be in form
424C4F4220434F4E54454E54 in csv file. And you also must specify the right code-page of the csv file.
I have the following table:
CREATE TABLE [dbo].[BlobTable]( [id] [int], [blobValue] [varbinary](max), [textValue] [nvarchar](500) ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
and bulk import query:
bulk insert dbo.BlobTable from 'C:\importBlob.csv' with (DATAFILETYPE = 'char', fieldterminator=',', codepage='1251')
It works nice for the csv file with windows-1251 encoding:
1,424C4F4220434F4E54454E54,english text 2,424C4F4220434F4E54454E54,русский текст
Here the value
424C4F4220434F4E54454E54 is simply
BLOB CONTENT in binary form.