5

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?

Michael Hedgpeth
  • 1,361
  • 4
  • 15
  • 21

3 Answers3

5

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

gbn
  • 70,237
  • 8
  • 167
  • 244
1

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.

Alexander Titov
  • 11
  • 1
1

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.

StanleyJohns
  • 5,982
  • 2
  • 25
  • 44