Wanted to Replace CSV NewLine or BreakLine with comma in LINQ and C# ASPNET

Question

User-2004582644 posted

Hi, I need your help.

I have two version of CSV file stored from external resource on the server

First version CSV

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;Table;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
N;ID;ANNOTATION;
1;35221;;;
2;35207;;1° 078-564
2° 078-765
my annotation
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

Second version CSV

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;Table;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
N;ID;ANNOTATION;
1;35221;;;
2;35207;;1° 078-564 2° 078-765 my annotation
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

In the first version of CSV the string number 2

2;35207;;1° 078-564 2° 078-765 my annotation

Is splitted in three different lines

2;35207;;1° 078-564
2° 078-765
my annotation

I need to edit and restore on first version to single line the string contents of line number 2, as in the second version, I have tried without success

File.WriteAllLines(output, CSV.Select(x => string.Join(";", x.ToString().Replace(",", "\r\n"))));
sr.Close();

The problem is that the CSV file first version is not imported in table of database. I haven't posted the database connection part because the problem is the CSV format of the first version. Using the second version the CSV file is inserted correctly into the database table...

My code below

string toCheck;
int posNewColumn2 = 4;

string[] CSVDump = File.ReadAllLines(output);

List<List<string>> CSV = CSVDump.Where(x => x.Length > 0 && "0123456789N|".Contains(x[0])).Select(x => x.Split(';').ToList()).ToList();

using (var fs = new FileStream(output, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
using (var sr = new StreamReader(fs, Encoding.Default))
{
    toCheck = sr.ReadToEnd();
    foreach (List<string> line in CSV)
    {
        if (line.Count > 0)
        {
           line.Insert(posNewColumn2, line[0] == "N" ? "NewColumn1" : string.Empty);
           line.Insert(posNewColumn2, line[0] == "N" ? "NewColumn2" : string.Empty);
           line.Insert(posNewColumn2, line[0] == "N" ? "NewColumn3" : string.Empty);
        }
    }
    File.WriteAllLines(output, CSV.Select(x => string.Join(";", x)));
    sr.Close();
}