Click here to Skip to main content
15,077,137 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Please i want to COnvert DataTable thta has Blob as part of its fields to CSV.
so i can do MySqlBulkCopy to MySql Database.
I have sucessfully copy all columns except the image data.
I need help please.
Thak you.

What I have tried:

I have successfully bulkcopy all columns except the blob coumn it copies it and inserts "System.Byte[]" in the database

This is the code to insert to Database

C#
IGRDal dl = SQLDalFactory.GetDal(GrConnector.AccessSQLDal);

             string tempCsvFileSpec = APP_PATH+ @"\dump.csv";

             using (StreamWriter writer = new StreamWriter(tempCsvFileSpec))
             {
                 DBHelper.WriteDataTable(dl.CreateDataTable(), writer, false);
             }
             dbConection.Open();
             var msbl = new MySqlBulkLoader(dbConection);
             msbl.TableName = "tb_templates";
             msbl.FileName = tempCsvFileSpec;
             msbl.FieldTerminator = ",";
             msbl.FieldQuotationCharacter = '"';
             msbl.Load();
             File.Delete(tempCsvFileSpec);



this is the code to make CSV
public static void WriteDataTable(DataTable sourceTable, TextWriter writer, bool includeHeaders)
        {
            if (includeHeaders)
            {
                IEnumerable<string> headerValues = sourceTable.Columns
                    .OfType<datacolumn>()
                    .Select(column => QuoteValue(column.ColumnName));

                writer.WriteLine(String.Join(",", headerValues));
            }

            IEnumerable<string> items = null;

            foreach (DataRow row in sourceTable.Rows)
            {
                items = row.ItemArray.Select(o => QuoteValue(o.ToString()));
                writer.WriteLine(String.Join(",", items));
            }

            writer.Flush();
        }

        private static string QuoteValue(string value)
        {
            return String.Concat("\"",
            value.Replace("\"", "\"\""), "\"");
        }</string></datacolumn></string> 
Posted
Updated 2-May-16 23:19pm
v4
Comments
F-ES Sitecore 3-May-16 4:58am
   
If the BLOB contains binary data how you do you expect that to be represented in a CSV file? What does, for example, an image or video look like in ASCII?

When you ask the field to convert itself ToString

items = row.ItemArray.Select(o => QuoteValue(o.ToString()));

as there is no string representation of an array of byte it returns the name of the type instead which is why you see System.Byte[].

You'll need to example the type of each field and either ignore BLOB fields, or do something like convert the BLOB fields to base64 rather than just ToString on them, as base64 can be represented as text for storage in your CSV file. Google for converting to base64 if you don't know how.

1 solution

That's because you are using string concatenation, either when you create the CSV data, or when you save to the DB (could be the original store or the new version - without the code we can't tell)
It's a similar problem to the one described here: Why do I get a "Parameter is not valid." exception when I read an image from my database?[^] - and it's the same solution or very much like it!
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900