Skip or delete line/row in CSV file if the line/row does not start with established character in c#

Question

This is my CSV file

this file is provided from externally resource and saved in csv format with pipeline separator and on this I have to work

||||||||||||||||||||||||||||||||||||||||||||||||||
|Table1|||||||||||||||||||||||||||||||||||||||||||||||||            
||||||||||||||||||||||||||||||||||||||||||||||||||          
N|IDI  |TEST|START DATE HOUR    |CAUSE|KIND|NUMB|NAMES|         
1|10704|    |21/07/2020 15:05:54|L    |MT  |2786|NAV  |         
2|10660|    |21/07/2020 09:27:31|L    |MT  |4088|PIS  |     
values of names 
values of names .|0|0|1|1|0|0||||
||||||||||||||||||||||||||||||||||||||||||||||||||          
|Table2|||||||||||||||||||||||||||||||||||||||||||||||||            
||||||||||||||||||||||||||||||||||||||||||||||||||          
N|IDI  |TEST|START DATE HOUR    |END DATE HOUR      |LENGHT  |RETURNS         |CAUSE|KIND|NUMB|NAMES|           
1|10710|    |21/07/2020 19:34:00|21/07/2020 20:19:09|00:45:09| -              |L    |MT  |7806|ACC  |
2|10708|    |21/07/2020 18:28:12|21/07/2020 18:28:13|00:00:01| -              |T    |MT  |2600|LIT  |       
3|10700|    |21/07/2020 14:16:37|21/07/2020 15:19:13|01:02:36|21/07/2020 17:00|L    |MT  |4435|UHI  |       
4|10698|    |21/07/2020 14:06:45|21/07/2020 14:07:22|00:00:37|-               |B    |MT  |5789|TYK  |
5|10674|    |21/07/2020 10:21:04|21/07/2020 10:44:41|00:23:37|21/07/2020 12:30|T    |MT  |6699|FGR  |
||||||||||||||||||||||||||||||||||||||||||||||||||

I need delete or skip these rows on csv file because the line not starting with number or N value or pipeline |

values of names 
values of names .|0|0|1|1|0|0||||

this is my code behind in error

Index was out of range. Must be non-negative and less than the size of the collection parameter name:index

if the line does not start with number or N value or pipeline |

int posNewColumn = 4;

string input = @"C:\Temp\SO\import.csv";
string output = @"C:\Temp\SO\out.csv";

string[] CSVDump = File.ReadAllLines(input);
List<List<string>> CSV = CSVDump.Select(x => x.Split('|').ToList()).ToList();
foreach (List<string> line in CSV)
{
    if (line[1] == "Table2")
    {
        break;
    }
    line.Insert(posNewColumn, line[0] == "N" ? "LENGHT" : string.Empty);
    line.Insert(posNewColumn, line[0] == "N" ? "END DATE HOUR" : string.Empty);
}

File.WriteAllLines(output, CSV.Select(x => string.Join("|", x)));

if there is only one element in line ( as in the line values of names) i need skip or delete lines

Can you help me please?

Answer 1

So you want to skip all lines that start with a pipe?

List<List<string>> CSV = CSVDump
  .Where(x => !x.StartsWith('|'))
  .Select(x => x.Split('|').ToList()).ToArray();

So you want to keep anything that starts with a number, an N or a pipe?

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

In response to Steve's concerns about performance etc, perhaps the best route to go is:

int posNewColumn = 3;

string input = @"C:\Temp\SO\import.csv";
string output = @"C:\Temp\SO\out.csv";

using (var dest = File.CreateText(output))
{  
    bool adjust = true;

    foreach (string s in File.ReadLines(input))
    {
        if(line.Length == 0 || !"0123456789N|".Contains(line[0]) //skip zero len or line not begin with number/pipe/N
          continue;

        string line = s; //copy enum variable so we can adjust it

        if(adjust)
        {
          string[] bits = line.Split('|');
          
          if(line.StartsWith("N"))
            bits[posNewColumn] += "|END DATE HOUR|LENGHT";
          else
            bits[posNewColumn] += "||";
          
          line = string.Join("|", bits);
        } 

        if(line.StartsWith("|Table2")
          adjust = false;

        dest.WriteLine(line);
    } 
}

This requires minimal memory and processing; we don't split every line needlessly, thousands of Lists are not created, we don't try to hold the whole file in memory; we just read lines in and maybe write them out, and maybe adjust them if we didn't encounter Table2

Note; I have written it but not debugged/tested it - it might have a typo or a minor logic error; treat it as pseudocode

Answer 2

In my opinion you are overcomplicating the problem trying to update the same line while you loop over the lines collection. A simple approach (given the small file size) is to use another list that contains only the 'approved' lines.

For example:

int posNewColumn = 4; // ???
string input = @"C:\Temp\SO\import.csv";
string output = @"C:\Temp\SO\out.csv";

List<string> outputLines = new List<string>();
foreach (string line in File.ReadLines(input))
{
    var parts = line.Split('|').ToList();
    if (parts.Count > 1)
    {
        if (parts[1] == "Table2")
        {
            break;
        }
        
        // Add here all the conditions that allow a line to be 
        // written in the output file
        char c = parts[0][0];
        if(c == '|' || c == 'N' || char.IsDigit(c))
        {
           parts.Insert(posNewColumn, parts[0] == "N" ? "LENGHT" : string.Empty);
           parts.Insert(posNewColumn, parts[0] == "N" ? "END DATE HOUR" : string.Empty);
           outputLines.Add(string.Join("|", parts);
        }
    }
}
File.WriteAllLines(output, outputLines);

This solution includes also the part where you add the new text into the lines approved for the output. While using Linq resolves with a single line the inclusion check, then you need another loop (in addition to the implicit one required by Linq) to insert the text