Convert excel to csv with new line and line break inside a column

Question

Trying to convert xlsx file to csv via C# code. I am able to convert file successfully but it could not handle newline and line break properly. Please advice which separator to use so that my csv is generated correctly

csv conversion code could not convert correctly. It should complete the conversion of a column completely but didn't.

using the below code to convert xlsx file to csv

string file= @"fakepath\abc.xlsx";

DataSet result = new DataSet();

//------To read the xlsx file
if (file.EndsWith(".xlsx"))
            {
                // Reading from a binary Excel file (format; *.xlsx)
                FileStream stream = File.Open(file, FileMode.Open, FileAccess.Read);
                IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
                result = excelReader.AsDataSet();
                excelReader.Close();
            }

//-------To convert the file into csv format
while (row_no < result.Tables[0].Rows.Count)
            {
                for (int i = 0; i < result.Tables[0].Columns.Count; i++)
                {
                    a += result.Tables[0].Rows[row_no][i].ToString() + ",";
                }
                row_no++;
                a += "\r\n";
            }

string output = @"fakepath\abc.csv";
            StreamWriter csv = new StreamWriter(@output, false);
            csv.Write(a);
            csv.Close();

column in excel holds data as

First Line

Second Line

Third Line

excel point i.e. 1,2 and 3 are in same column but different lines

Expected output csv formmat 1. First Line 2. Second Line 3. Third Line

Please help

How does that relate to SQL Server? If it doesn't please remove the tag. — sticky bit, Jan 05 '19 at 05:30
Multi-line cell values use 0x010 char (new line) not 0x013, 0x010 char pairs (full carriage returns). — , Jan 05 '19 at 06:17
@RossBush, I believe a CRLF is actually #13#10, not the other way around but in any event, an excel cel use #10 only; the #13 is stripped by overhead. — , Jan 05 '19 at 06:18

Mohamed Elrashid · Answer 1 · 2019-01-06T21:23:37.703

if what you want is

         +-------+-------+-----+-----+-----+
         |  a    |       |     |     |     |
         |  b    |       |     |     |     |
         |  c    |       |     |     |     |
         +---------------------------------+
         |  d    |       |     |     |     |
         |  e    |       |     |     |     |
         |  f    |       |     |     |     |
         +---------------------------------+
         |  g    |       |     |     |     |
1        |  h    |       |     |     |     |
         |       |       |     |     |     |
         +---------------------------------+
         |       |       |     |     |     |


         +-------+------+------+-----+-----+
         |  a    |   b  |   c  |     |     |
         |       |      |      |     |     |
         +---------------------------------+
         |  d    |  e   |  f   |     |     |
         |       |      |      |     |     |
         +---------------------------------+
         |  g    |  h   |      |     |     |
 2       |       |      |      |     |     |
         +---------------------------------+
         |       |      |      |     |     |
         |       |      |      |     |     |


         +-------+------+------+-----+-----+
         |  abc  |      |      |     |     |
         |       |      |      |     |     |
         +---------------------------------+
         |  def  |      |      |     |     |
 3       |       |      |      |     |     |
         +---------------------------------+
         |  gg   |      |      |     |     |
         |       |      |      |     |     |
         +---------------------------------+
         |       |      |      |     |     |

for 1 to 2 USE

var column = result.Tables[0].Rows[row_no][i].ToString();
column.Split(new[] { '\r', '\n' }).ToList()
.ForEach(column_line => { a += column_line + ","; });

for 1 to 3 USE

var column = result.Tables[0].Rows[row_no][i].ToString();
a += String.Join(" ", column.Split(new[] { '\r', '\n' }));

also for 1 to 3 you can USE

var column = result.Tables[0].Rows[row_no][i].ToString();
column = column.Replace("\r", " ");
a+= column.Replace("\n", " ");

Full code

string file = @"fakepath\abc.xlsx";

DataSet result = new DataSet();

//------To read the xlsx file
if (file.EndsWith(".xlsx"))
{
    // Reading from a binary Excel file (format; *.xlsx)
    FileStream stream = File.Open(file, FileMode.Open, FileAccess.Read);
    IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
    result = excelReader.AsDataSet();
    excelReader.Close();
}

//-------To convert the file into csv format
while (row_no < result.Tables[0].Rows.Count)
{
    for (int i = 0; i < result.Tables[0].Columns.Count; i++)
    {
        var column = result.Tables[0].Rows[row_no][i].ToString();
        //column.Split(new[] { '\r', '\n' }).ToList().ForEach(column_line => { a += column_line + ","; });
        or
        a+=String.Join(" ", column.Split(new[] { '\r', '\n' }));
        //or
        //column = column.Replace("\r", " ");
        //a+= column.Replace("\n", " ");

    }
    row_no++;
    a += Environment.NewLine;
    //or
    // a += "\r\n";
}

string output = @"fakepath\abc.csv";
StreamWriter csv = new StreamWriter(@output, false);
csv.Write(a);
csv.Close();

More

also

a +=Environment.NewLine

instead of

 a +="\r\n";

Current Output 1. First Line,2. Second Line,3. Third Line Expected output 1. First Line 2. Second Line 3. Third Line In excel all these three points are in 1 column, if all values will be separated with comma then if I import it in SQL then I will need 3 columns but it is 1 column value — Hrishikesh Bagchi, Jan 05 '19 at 06:36

Richardissimo · Answer 2 · 2019-01-05T22:29:53.350

The main issue here is the most common issue people have when processing CSV files: they think all that is required is to concatenate the values with commas, and put a newline at the end of each line. There are fringe cases they don't think about like:

What if a value contains newline?
What if a value contains comma?
Once they've overcome 1 and 2, what if the value contains "?

I'd suggest the best approach would be to use a library to help create the CSV file. But if you don't want to do that, here are some suggestions which may improve things:

Each value needs to be enclosed in " characters.
As a result of point 1, " characters within the value need to be escaped with a backslash character before them.
The declaration of a is not shown, but I assume it to be a string. Strings are immutable, so you should use System.Text.StringBuilder to construct the string.
The code in the question will always end up with an extra comma on the end of each line. Consider using string.Join to put commas between the values, so you don't get one on the end.
Streams are IDisposable, so each one should be in a using block. Once you've done that, you don't need to Close the stream, as it will be closed by the implicit Dispose when it exits the block.
Rather than while (row_no < result.Tables[0].Rows.Count) prefer foreach (DataRow dataRow in result.Tables[0].Rows). It's easier to read what the loop is doing, and puts the row in a local variable, rather than having to do the property lookup for Tables and the indexer lookup for the table, and the property lookup for Rows and the indexer lookup each time within the loop. And it makes the contents of the loop easier to read.
Use System.Environment.NewLine in preference to "\r\n". This is just cosmetic, again, to make it human-readable.

So that would give you...

string file= @"fakepath\abc.xlsx";

DataSet result = new DataSet();

//------To read the xlsx file
if (file.EndsWith(".xlsx"))
{
    // Reading from a binary Excel file (format; *.xlsx)
    using (FileStream stream = File.Open(file, FileMode.Open, FileAccess.Read))
    {
        IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
        result = excelReader.AsDataSet();
        excelReader.Close();
    }
}

//-------To convert the file into csv format
var a = new StringBuilder();
int columnCount=result.Tables[0].Columns.Count;
foreach (DataRow dataRow in result.Tables[0].Rows)
{
    var values=new List<string>(columnCount);
    for (int i = 0; i < columnCount; i++)
    {
        values.Add(dataRow[i].ToString());
    }
    string line=string.Join(",", values.Select(v=>$"\"{Escape(v)}\"");
    a.AppendLine(line); // even neater than Environment.NewLine
}

string output = @"fakepath\abc.csv";
using(StreamWriter csv = new StreamWriter(@output, false))
{
    csv.Write(a.ToString());
}

I'm not at my PC, so cut me some slack if there are typos or autocorrect errors in the above. Also: I have omitted the definition of method string Escape(string v), which needs to do what is described in my point 2.

Sir, Can u edit the part of the code in the manner you suggested please — Hrishikesh Bagchi, Jan 05 '19 at 13:41

Convert excel to csv with new line and line break inside a column

2 Answers2