Use C# to write array elements from jsonstring to csv or xlsx-CodePudding

I tried to write array of string from JsonString into the 1st column of excel file(either csv or xslx). and the code is creating a csv file as in screen2. How can I get the excel sheet to look like screen1 ?

input JsonResponse =

{"wordAlterations":[{"alterations":["hm","history matching","HM","History Matching"]},{"alterations":["risk taking","Risk Taking","RISK TAKING","risk-taking","Risk-Taking","RISK-TAKING"]}]}

Here is my sample code

 string line = string.Empty;

 List<string> newLines = new List<string>();

 Rooter myDeserializedClass = JsonConvert.DeserializeObject<Rooter>(jsonResponse);

 foreach(var alter in myDeserializedClass.wordAlterations)
 {
     line = string.Join(",", alter.alterations.Select(item =>"'"  item "'"));                         

     newLines.Add(line);
 }

 File.AppendAllLines("sample.csv", newLines);

Expected .csv file - note the array is written into column A, line1 and line3 with each item in double quotes.

Actual CSV I get:

CodePudding user response：

You want to include the double quote but you don't know how to escape it? How about this:

line = string.Join(",", alter.alterations.Select(item =>"\""   item   "\""));

CodePudding user response：

A CSV file is a simple text file, that can also be read with a simple text editor like Notepad.

When you open such a file in Excel, it tries its best to correctly parse the text into the columns.

Two things regulary mess up the Excel parser when reading files:

The used separator. Depending on your system language Excel chooses the corresponding field separator. E.g. on english system, this is normally a comma (hence the name CSV). But for example on german systems, the semicolon is used as a separator. In C# you can read that information from System.Globalization.CultureInfo.CurrentCulture.TextInfo.ListSeparator as first parameter in your join call.
The used encoding. .Net by default uses the UTF-8 encoding and so the file will be written in this encoding. Unfortunately for historic reasons, Excel guesses that a CSV file without a UTF-8-BOM will contain all characters in ANSI encoding, which specially fails on special characters like ä,ö,ü, etc. So be sure that the file you initially create contains the BOM. In that case Excel chooses the correct encoding and everything will be shown correctly.