C# Convert Excel to CSV and Ignore Empty Cells

Question

Friends, C# noob here, so apologies in advance!

I have the following method which loops over my Excel workbook sheet as a data table converting it to CSV via the string builder output.

    public static string ToCSV(this DataTable table)
    {
        var result = new StringBuilder();
        for (int i = 0; i < table.Columns.Count; i++)
        {
            result.Append(table.Columns[i].ColumnName);
            result.Append(i == table.Columns.Count - 1 ? "\n" : ",");
        }

        foreach (DataRow row in table.Rows)
        {
            for (int i = 0; i < table.Columns.Count; i++)
            {
                result.Append(row[i].ToString());
                result.Append(i == table.Columns.Count - 1 ? "\n" : ",");
            }
        }
        return result.ToString();
    }

This works fine if the Excel file is clean. Like this:

However, if you add any sort of formatting or anything to the Excel file the C# method treats the cells as having content in and outputs them to the CSV file.

Like this:

I've tried doing this like this and checking the length of the row etc...

if (String.IsNullOrWhiteSpace(row[i].ToString()))
{
 continue;
}

... but it breaks the structure of the file.

Question; what am I doing wrong?! I feel like I'm so close, but its just not behaving.

Thank you in advance for your assistance.

*"but it breaks the constructure of the file"* - what? How do you fill `DataTable`? — Sinatr, Nov 10 '17 at 16:37
What is wrong with the extra commas? This keeps the structure of your table. I would pre-process your table for empty rows/columns instead of anything to remove these commas, but you probably have the tables that way in Excel for a reason — Bijan Rafraf, Nov 10 '17 at 16:40
if (row[i] == DBNull.Value || String.IsNullOrWhiteSpace(row[i].ToString())) — Rob, Nov 10 '17 at 16:41
as @Sinatr asked, how do you fill the DataTable as it's the DataTable that has the extra column by the point you get to the code above — Jack Blackmore, Nov 10 '17 at 16:43
are you using ClosedXML to read excel file? or are you using interop to read excel file? — Prasad Telkikar, Nov 10 '17 at 16:51

score 0 · Accepted Answer · edited Nov 10 '17 at 17:22

0

if (row[i] == DBNull.Value || String.IsNullOrWhiteSpace(row[i].ToString()))
{
    continue;
}

edited Nov 10 '17 at 17:22

aaron

39,695
6
46
102

answered Nov 10 '17 at 16:43

Rob

444
3
10

Prasad Telkikar · Answer 2 · 2017-11-10T16:57:12.023

While creating datatable from excel file, you can check worksheet.FirstColumnUsed, worksheet.LastColumnUsed similarly for row as well. In this way you will get only rows and columns which are containing records.

If you are using ClosedXML to read Excel file, then I guess they provided UsedRange(), FirstRowUsed, LastRowUsed properties. For more details: ClosedXML GITHUB repository

If you are using interop api to read excel file then This answer could be helpful: Stackoverflow:Remove empty row and column

C# Convert Excel to CSV and Ignore Empty Cells

2 Answers2