CODEWITHSUNDEEP
sqlsql-serverexportbinary-data
Giffyguy
Giffyguy

Reputation: 21282

How can I export an entire SQL Server table to CSV, when the table contains an Image column?

I have a SQL Server table with the following design:

enter image description here

I need to export this entire table to a CSV file, with the data looking like this in plain-text format:

enter image description here

When I use the Import/Export wizard in SSMS, I get the following results in the CSV:

enter image description here

As you can see, this doesn't contain the actual binary data from the Image columns.
Instead, it just says System.Byte[] in plain-text, in each row.

In the past I've copy/pasted the text directly from the SSMS results window, into notepad, to manually produce the CSV.
However this table is so long, and the images are so large, that my computer runs out of memory if I try this now.

There must be a method to export the binary data into a CSV, in 0x1234ABCD format (hex).
How could I accomplish this?

Upvotes: 0

Views: 1901

Answers (2)

Giffyguy
Giffyguy

Reputation: 21282

Following the advice in Dan Guzman's comment, I created a new view with the following query:

CREATE VIEW vwPhotosMen AS
    SELECT SS,
           CONVERT(varchar(max), CONVERT(varbinary(MAX), PhotoMen, 1), 1) AS PhotoMen,
           CONVERT(varchar(max), CONVERT(varbinary(MAX), Thumbnail, 1), 1) AS Thumbnail
    FROM dbo.PhotosMen

The SSMS Export Wizard was then able to export the data correctly from this view, to a plain-text CSV file.

Upvotes: 0

Omar El Sergany
Omar El Sergany

Reputation: 11

if you are a developed you can create small C# app Useing Base64 encoding for your binary data. It converts any binary data into readable string. The code is as following

Encode

public static string Base64Encode(string plainText) 
{
    var plainTextBytes = System.Text.Encoding.UTF8.GetBytes(plainText);
    return System.Convert.ToBase64String(plainTextBytes);
}

Decode

public static string Base64Decode(string base64EncodedData) 
{
  var base64EncodedBytes = System.Convert.FromBase64String(base64EncodedData);
  return System.Text.Encoding.UTF8.GetString(base64EncodedBytes);
}

Upvotes: 1

Related Questions