5

I've created a .csv file using C# and when I open this file in Excel, it shows the double quotes in the cells.

When I open the csv in Notepad++ I see that are quotes are there and everything looks right to me. Here's a part of my csv:

"CertificaatNummer", "Data", "Inleg", "Getekend", "Ingangsdatum", "Betaalmethode", "Status", "Bonus", "Aankoopkoers", "Korting", "Garantiewaarde", "Betaalde termijnen", "Inleg nominaal", "Tweede naam", "Recht op bonus maand", "Begunstigde", "Product", "Incasserekening", "Uitbetaalrekening"
"126136", "some data with, a comma", "118.34", "True", "28-1-1999 00:00:00", "Cash", "C02", "0.00", "531,940", "0,000", "0.00", "0", "0.00", "", "False", "P.H. Bloemink", "Cash-Click", "", "260952095"
"137190", "other data", "0.00", "True", "23-12-1999 00:00:00", "Cash", "C02", "0.00", "660,620", "0,000", "0,00"

When I open this file in Excel it treats the comma in some data with, a comma as a new column. So I get "Some data with in one cell and a comma" in antother cell. As you see, Excel doesn't care about the double quotes.

Here's what my code looks like (simplified):

var content = new List<string>();

// Add headers
content.Add("\"Header 1\", \"Header 2\", \"Header 3\", \"Header 4\"");

// Add content
content.Add("\"123456\", \"some data with, a comma\", \"118.34\", \"True\"");
// etc..

// I let a function create my file content
using (var stream = new MemoryStream())
using (var writer = new StreamWriter(stream))
{
    foreach (var line in this.content)
    {
        writer.WriteLine(line);
    }

    writer.Flush();
    return stream.ToArray();
}

// finally I return the file content to the browser with 
// HttpContext.Response.AddHeader("content-disposition", "file;name=" + fileName);

The question is, which part of my code do I have to edit so Excel will display my file properly? I rather want to edit my code then executing some tricks in Excel.

Martijn
  • 24,441
  • 60
  • 174
  • 261
  • Seems more of an Excel import problem. CSV is a mess. – H H Nov 11 '13 at 13:05
  • take a look a [this](http://stackoverflow.com/questions/769621/dealing-with-commas-in-a-csv-file) I think it may solve your issues. – Squirrel5853 Nov 11 '13 at 13:08
  • What happens if you remove the `"` quote characters from the header line? Creating CSVs like this and importing into Excel should work, so something odd is going wrong with your example. The quotes in the headers is the only thing I can think of. – David Arno Nov 11 '13 at 13:09
  • 4
    If I copy your CSV into notepad, I see that the separators include not only commas but also . In other words, your code is generating a after each . Remove those spaces and Excel should be able to handle things OK without "tricks" – Ron Rosenfeld Nov 11 '13 at 13:10
  • Oh, the other thing is to ensure you save as ASCII encoding. Excel is pretty shit when it comes to CSVs. I know it can't export to ASCII, not eg UTF8, so it may only be able to import ASCII too. – David Arno Nov 11 '13 at 13:11
  • Thanks all for the quick replies! @RonRosenfeld That was it! I removed all the spaces and now it works. Could you post an answer so I can mark it as answered? – Martijn Nov 11 '13 at 13:13
  • @RonRosenfeld That's the problem. I tried stripping out the spaces between the `,` and `"` characters and it imports into Excel just fine. – David Arno Nov 11 '13 at 13:14
  • I'll post that as an answer so you can mark it. – Ron Rosenfeld Nov 11 '13 at 13:14
  • Apparently I was wrong and Excel can deal with commas inside of double quotes. But in any case I want to highlight that "writing to Excel" should be done by relying on the adequate means (.NET alternatives or external libraries). Generating a CSV file to be opened by Excel shouldn't be an acceptable way to generate an "Excel file". Also, as proven by this Q/A, the Excel CSV importing capabilities are not necessarily perfect. – varocarbas Nov 11 '13 at 13:29

3 Answers3

13

The problem seems to be spaces after the comma separators.
Instead of 

"some, text","Other text"

your code is generating: 

"some, text",<space>"Other text"

So the Excel CSV importer is getting flummoxed. Remove those extra spaces and you should be OK.

Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
1

Some time ago, I found out that Excel likes its CSV files UTF-16LE encoded, with tabs (ASCII code 9) as delimiters. It even accepts (and removes) double quotes around the values.

fero
  • 6,050
  • 1
  • 33
  • 56
0

Just reflect comma with "\" symbol.

Or if you doing it programmatically just fire Replace(",","\,") method

Aidanpraid
  • 112
  • 1
  • 11