How to write binary data into csv file?

Question

I have a table StaffMaster which is containing staff photograph and the datatype of that table is varbinary. so, i want to export this table into csv file format using c# code. Even i have tried to export that table manually by sql server export task. But it shows following error.

- Validating (Error)
Messages
Error 0xc0208030: Data Flow Task: The data type for "input column 
"StaffPhoto" (376)" is DT_IMAGE, which is not supported. Use DT_TEXT 
or DT_NTEXT instead and convert the data from, or to, DT_IMAGE using    
the data conversion component.
 (SQL Server Import and Export Wizard)

I know the data are stored in binary(byte[]). But, I would like to know is it possible that can i convert it to string and when i want to import it to the sql server database it should be converted into binary. So, my data will not be lost. Image size can be 100 kb to 500 kb.

I cannot store image outside of the csv file.

I am using SQL Server 2005.

why don't you use nvarchar instead you can store data and retrieve it — Just code
– Just code, Commented Feb 15, 2014 at 5:55
Perhaps encode the binary data as a hex string when writing it into the CSV and then decode it back when importing (if you also need to import)? Putting raw binary data into a CSV file can mess (and most likely will) mess up the CSV file. — Ruslan
– Ruslan, Commented Feb 15, 2014 at 5:56
@dholakiyaankit because nvarchar supports unicode data format and each character allocates 2 bytes of data. if i use nvarchar instead of binary then the database size will be increased. for example if an image size is 100 kb then nvarchar datatype may consume 200kb in sql database. so, i am afraid about this issue. if nvarchar does not make any difference then there is no problem to with nvarchar datatype. — Shell
– Shell, Commented Feb 15, 2014 at 6:00
You're not thinking it through - what are you going to do with the binary data? What code or program will consume it? What format does that program require? That's the format you need to write to the CSV. — John Saunders
– John Saunders, Commented Feb 15, 2014 at 6:18
If you are dealing with images the i would suggest you to go for folder option this will always results in a bored and you client will always give you call oh...........its too slow — Just code
– Just code, Commented Feb 15, 2014 at 6:36

The Hungry Dictator · Accepted Answer · 2014-02-15 06:24:11Z

8

Use 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);
}

edited Feb 15, 2014 at 6:24

The Hungry Dictator

3,4845 gold badges41 silver badges56 bronze badges

answered Feb 15, 2014 at 6:14

korhner

7334 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Shell Over a year ago

Thanks for your answer. But, how can i get back data into image datatype. your Base64Decode method returns string and i want data into image format. what happen with image format. for example jpg, bmp etc.

korhner Over a year ago

Just change Decode method to return base64EncodedBytes. This is your binary image data. You can than serialize this back to database or to a image file. Do you need help with this?

Shell Over a year ago

Does Base64 Encoding contains the file format. I mean if i encode a png image into Base64 then i decode it back to image format then i do i know the image format.

korhner Over a year ago

What you are asking is another problem - Can I determine file format just from its data? This can be overkill for your problem, can you consider adding another column to keep file format(jpeg, png, bmp...)?

Collectives™ on Stack Overflow

How to write binary data into csv file?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related