C# - How to write a List<List<string>> to CSV file?

Question

I'd like to write some data to a CSV file using C#.

Here's my code at the moment :

int length = readData.Split(new string[] { Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries).Length;
List<List<string>> dataList = new List<List<string>>();
foreach (string line in readData.Split(new string[] { Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries))
{
 List<string> partsLine = new List<string>();
 partsLine.AddRange(line.Split('\t'));
 dataList.Add(partsLine);
} 

So, I'm splitting all the data in lines.

Then I split each line with '\t' separator, and add each part of the line in a list.

And at the end I have a list containing all the lists of split lines.

So the list looks like :

• List1 {txt1, txt 2} = first line
• List2 {txt3, txt 4, txt 5, txt 6} = second line
• List3 {txt7, txt 8, txt 9, txt 10} = third line
• List4 {txt 11, txt 12} etc.

All of the lists don't have the same lenght as you can see.

What I'd like to do is writing all the lists to a CSV file.

Each list would fill a row and a certain number of columns, depending of the lenght of the list (i.e. txt1 should be (row1, col1), txt2 should be (row1, col2), txt3 should be (row2, col1), txt4 would be (row2, col2)...).

How can I do that ?

Thanks

Answer 1

List<List<String>> dataList = new List<List<string>>();
//...
const char SEPARATOR = ",";
using (StreamWriter writer = new StreamWriter("file.csv"))
{
 dataList.ForEach(line =>
 {
 var lineArray = line.Select(c =>
 c.Contains(SEPARATOR) ? c.Replace(SEPARATOR.ToString(), "\\" + SEPARATOR) : c).ToArray();
 writer.WriteLine(string.Join(SEPARATOR, lineArray));
 });
}

• Whatever: With this code it tells me that 'System.Collections.Generic.List<string>' does not contain a definition for 'Select'.
• Whatever: My bad, the project was using an old Framework, it couldn't find Linq. I tested the code, it does exactly what I want, thanks. :)

Answer 2

CSV format by itself does not intend to be formatted with rows with variable columns length. CSV (Comma separated values) is just "table like" format, so if you want to follow its convention, you have to first define all possible columns and fill rows one-by-one, so several cells on several rows will remain empty.

If this is not what you want, just choose your custom format.

Answer 3

I'm coming back on this topic because I realized that the .csv format won't suit my needs.

I need to plot some charts using all the data that I have, and I read that .csv files don't support chart integration. So, instead of exporting my List<List<string>> to .csv, I'd like to export it to an .xls file.

I tried to do it just by changing the type of the file to .xls in the StreamWriter parameter, but the data doesn't fit like in the .csv. What should I change in my code ?

 // Write data to a .csv file
 private void WriteToCSV(string data)
 {
 int length = data.Split(new string[] { Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries).Length;
 // List of lists containing each line split by part
 List<List<string>> dataList = new List<List<string>>();
 List<string> valuesA = new List<string>();
 // Building the list
 foreach (string line in data.Split(new string[] { Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries))
 {
 List<string> partsLine = new List<string>();
 partsLine.AddRange(line.Split('\t'));
 dataList.Add(partsLine);
 }
 const string separator = ";";
 // Writing the list to the .csv file
 try
 {
 using (StreamWriter writer = new StreamWriter(Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "\\output.xls", false))
 {
 dataList.ForEach(line =>
 {
 var lineArray = line.Select(c => c.Contains(separator) ? c.Replace(separator.ToString(), "\\" + separator) : c).ToArray();
 writer.WriteLine(string.Join(separator, lineArray));
 });
 }
 }
 catch (Exception ex)
 {
 Console.WriteLine("Error while writing data to .csv file (" + ex.Message + ")");
 }

On the picture you can see the previous format (.csv on the right) and the new one (.xls on the left). http://imageshack.us/photo/my-images/850/cax.png/