C#: Convert an Excel.Worksheet to CSV at once with KBCsv

Feb 26, 2012 at 6:46 PM
Edited Feb 26, 2012 at 7:59 PM

Hi all,

I've discovered KBCsv while attempting to work around the limited C# Excel.Worksheet.SaveAs() method. So far, it looks like a very interesting project!

I want to create a .csv file from a Excel.Worksheet. The only issue is that I need to force two things:

  • The "field separator" should be semicolon (;)
  • The "text separator" should always be null

Moreover: I'd LOVE to do this in a single instruction (like it's shown for DataTable and MemoryStream in the documentation for the CsvWriter Class), without a foreach() on every single row.

Could someone please provide me the C# code?

Thanks,

Coordinator
Feb 27, 2012 at 11:52 AM

Hi ZaneCEO,

Can you provide more information? Specifically, what is a text separator? I presume the field separator is equivalent to KBCsv's ValueSeparator property, but I have no idea what you mean by separating text with null.

Best,
Kent

Feb 27, 2012 at 12:30 PM

Hi Kent,

first of all, many thanks for your reply!

My names where expressed in OpenOffice.org labels:

  • "field separator" is the character used to separate text field. It is what you call CsvWriter.ValueSeparator Property
  • "text separator" is the character "wrapped around" field. It is what you call CsvWriter.ValueDelimiter Property
Coordinator
Feb 27, 2012 at 1:00 PM

Thanks for the prompt reply, ZaneCEO.

What does it mean to wrap a field with null? Do you literally want the null character (\0) to be used? If so, you can simply do this:

var table = GetDataTable();
var memStream = new MemoryStream();

using (var writer = new CsvWriter(memStream)) {
    writer.ValueSeparator = ';';
    writer.ValueDelimiter = '\0';
    writer.WriteAll(table, true);
}

HTH,
Kent

Feb 27, 2012 at 3:01 PM

I just meant that I don't want any " around the field. This translate into writer.ValueDelimiter = "";

The only problem still to solve is that i don't have a "DataTable", but an Excel.Worksheet instead....

Feb 28, 2012 at 8:44 PM
Since I was just a step away from closing the project, I put together an inelegant but working solution like this:
static public void WorksheetToCSV(Excel.Worksheet origine, string CSVoutPath, string fieldSeparator, string textSeparator)
    {
        Excel.Range rngCurrentRow;
        string lineaDaScrivere;
        TextWriter csv = new StreamWriter(CSVoutPath);


        for (int i = 1; i <= origine.UsedRange.Rows.Count; i++)
        {
            rngCurrentRow = origine.UsedRange.get_Range("A" + i, "A" + i).EntireRow;
            lineaDaScrivere="";

            foreach (Excel.Range cella in rngCurrentRow.Columns)
            {
                try
                {
                    lineaDaScrivere = lineaDaScrivere + textSeparator + cella.Value.ToString() + textSeparator + fieldSeparator;
                }
                catch (NullReferenceException ex)
                {
                    break;
                }
            }
            lineaDaScrivere=lineaDaScrivere.Substring(0, (lineaDaScrivere.Length - fieldSeparator.Length));

           csv.WriteLine(lineaDaScrivere);
        }

        csv.Close();
    }

There are better solution, but this worked as expected for me.

 

Thanks anyway again for your time.

Sep 13, 2013 at 9:23 AM
You can use excel to csv converter for this purpose because its a very common practice nowadays.
Coordinator
Sep 13, 2013 at 9:53 AM
I had forgotten about this thread until @shanaya's post. The latest version of KBCsv has a nullable value delimiter, so you can avoid your hack by just setting it to null. This will ensure KBCsv does not delimit values (an exception will be thrown if your value includes the value separator character - ';' in your case).

HTH,
Kent