1

I have this code used on my application to convert xls file to csv file.

Excel.IExcelDataReader excelReader = Excel.ExcelReaderFactory.CreateBinaryReader(stream);
DataSet result = excelReader.AsDataSet();
excelReader.Close();
result.Tables[0].TableName.ToString();
string csvfile = "";
int row_no = 0;
int ind = 0;

while (row_no < result.Tables[ind].Rows.Count)
{
    for (int i = 0; i < result.Tables[ind].Columns.Count; i++)
    {
        csvfile += result.Tables[ind].Rows[row_no][i].ToString() + ",";
    }

    row_no++;
    csvfile += "\n";
}

output = System.Web.HttpContext.Current.Server.MapPath("/public/test.csv");
newOutput = Path.GetFileName(output);
StreamWriter csv = new StreamWriter(@output, false, Encoding.Unicode);
csv.Write(csvfile);
csv.Close();
csv.Dispose();

It worked fine but csv doesn't show cell values correctly. See below images and I have pasted my code as well. What can I change on this code to fix this?

This is the exported csv and it shows like this

enter image description here

I have tried replacing on my code, but I don't have changes

StreamWriter csv = new StreamWriter(@output, false, Encoding.ASCII);  
StreamWriter csv = new StreamWriter(@output, false, Encoding.Unicode);  
StreamWriter csv = new StreamWriter(@output, false, Encoding.GetEncoding("iso-8859-1"));
JMP
  • 4,417
  • 17
  • 30
  • 41
Iter Lsic Iealf
  • 133
  • 12
  • Remove all of the dataset stuff and show us the [mcve] where you write a hard-coded string to the CSV. Keep it to 10 lines or less, so it is easy for us to repro. – mjwills Oct 11 '20 at 13:15
  • 1
    also don't open it in Excel since there's a lot of auto "guessing" done to convert certain strings as Excel deems them to be "correct", use notepad to check raw text. – kshkarin Oct 11 '20 at 13:22
  • @kshkarin actually if I open the csv file using Notepad the characters are correct, I don't understand... – Iter Lsic Iealf Oct 11 '20 at 13:33
  • What tool are you using to view the CSV? The tool may be using the wrong encoding or a different FONT. So results are probably correct but the tool you are using to view the results is creating the issue. – jdweng Oct 11 '20 at 13:37
  • @jdweng for view the CSV I use Office 365 Excel – Iter Lsic Iealf Oct 11 '20 at 13:40
  • 1
    Are you sure you want to use `Encoding.Unicode` (which is **actually** UTF-16LE) instead of UTF-8? – Dai Oct 11 '20 at 13:40
  • Unrelated tip: you should use `StringBuilder` instead of using the `+=` operator inside a loop to concatenate strings, because that will cause excessive string allocation in .NET. – Dai Oct 11 '20 at 13:41
  • ASCII encoding will remove non printable characters. iso-8859-1 may be the wrong encoding as well as Unicode. If you do not want the data to change use UTF-8 like Dai suggested. – jdweng Oct 11 '20 at 13:45
  • @jdweng I have use `Encoding.Unicode` instead of `UTF-8`, nothing change... :( – Iter Lsic Iealf Oct 11 '20 at 14:28
  • Try "Encoding.GetEncoding(1252))", does that work for you? – Mario Z Oct 11 '20 at 18:11
  • @MarioZ thank you MarioZ! Using `Encoding.GetEncoding(1252))` the csv file show cell values correctly! :) – Iter Lsic Iealf Oct 12 '20 at 07:22

0 Answers0