0

I'm generating the head of different CSV files so my users can see the format

var output = new MemoryStream()
var writer = new StreamWriter(output, Encoding.UTF8);
//this function gets the row depending of the enumerator name in this format a;b;c;d
var header = ModelosCsv.GetCsvByEnum("HeadRowFileLoad");
writer.WriteLine(header);
writer.Flush();
output.Position = 0;
return File(output, "application/csv", "format.csv");

The code is creating the CSV correctly but if they open the CSV with excel and save it, excel will overwrite all the ";" for triple spaces. If I edit the result with notepad++ and put back the ";" excel won't do it again.

I have opened both archives with excel and clicked "save as", the first one (freshly generated by c#) is set as default as "text archive" the second one (edited by notepad++) is set as CSV.

Am I missing something code? How could I do to stop excel messing up my archives?

Marcello B.
  • 4,177
  • 11
  • 45
  • 65
Luis Carretero Fresno
  • 3
  • 1
  • where are you closing the memory stream.. ? why are you setting the output position..? not necessary... – MethodMan Oct 16 '17 at 16:00
  • Excel doesn't corrupt anything. Why don't you create a *real* Excel file with EPPlus? – Panagiotis Kanavos Oct 16 '17 at 16:00
  • As for Excel, it doesn't know what type of field, line, text,decimal, thousand separators your code uses. Nor can it ask, when you pass the file in the command line. It will use the *user's* locale settings, specifically the decimal, thousand and *list* separators to read the unknown file. When you open a CSV through the `Insert Data` dialog you get a preview that allows you to edit the settings if they appear wrong – Panagiotis Kanavos Oct 16 '17 at 16:02
  • What does `GetCsvByEnum` do? What does the generated text file look like? Excel won't replace `;` with triple spaces, even though it *isn't* the common field separator. It may treat it as text, displaying the entire row as a single field, if the user's locale uses `,` as the list separator, ie the US locale used by many developers – Panagiotis Kanavos Oct 16 '17 at 16:05
  • Im sorry , they are using CSV not real excel files and they are really stubborn about this (because the sometimes the csv files comes from other developers programs) so no EPPlus. – Luis Carretero Fresno Oct 16 '17 at 16:06
  • This is how one of the csv looks like referencia;direccion;localidad;provincia;cp;superficie;reference;tipoProducto – Luis Carretero Fresno Oct 16 '17 at 16:07
  • About closing the memory stream and setting position to 0 MethodMan if i dont do that the archive comes empty. – Luis Carretero Fresno Oct 16 '17 at 16:08
  • 1
    Are you sure you're not corrupting up the CSV file when you open it in Excel by not selecting the proper delimiter the first time? – Jacob H Oct 16 '17 at 16:11

1 Answers1

0

I found the solution in this answered question Export to CSV using MVC, C# and jQuery

        var header = ModelosCsv.GetCsvByEnum("HeadRowFileLoad");

        return File(new System.Text.UTF8Encoding().GetBytes(header), "application/csv", "format.csv");

This way Excel identifies the archive as CSV and editing wont break the format.

Luis Carretero Fresno
  • 3
  • 1