C# - Read delimited text file that contains extra delimiter in rows

Question

I am importing an excel file into DataTable and generating a '~' delimited file text using C#.

And importing the delimited file into DataTable to validate the data.

My Code:

DataTable data = new DataTable()
using(TextFieldParser parser = new TextFieldParser(filePath))
{
     parser.HasFieldsEnclosedInQuotes = true;
     parser.SetDelimiters("~");

     while (!parser.EndOfData)
     {
          if(data.Columns.Count == 0)
          {              
              foreach (string field in parser.ReadFields())
                     data.Columns.Add(field, typeof(string));
          }
          else
          {
               data.Rows.Add(parser.ReadFields());
          }  
     } 
     data.AccepChanges();

If the delimited file contains an extra delimiter '~' in the data, (2nd row in this example)

For eg: if the excel file contains this data:

Account    Name      Age
1000       John      20
2000       ~Doe      23
3000       Jason     25 

The delimited file generated is this:

Account~Name~Age
1000~John~20
2000~~Doe~23
3000~Jason~25

it is giving an exception when adding the row to datatable:

"Input array is longer than the number of columns in this table"

How do I read the file that contains extra delimiter in the data?

Answer 1

Here just use a more complex delimiter to avoid any collisions with text since records are large.

using (TextFieldParser parser = new TextFieldParser("D:/test.txt"))
{
    parser.HasFieldsEnclosedInQuotes = true;
    parser.SetDelimiters("~*^");
    
    string[] fields;
    
    while (!parser.EndOfData)
    {
        fields = parser.ReadFields();
        foreach (string field in fields)
        {
            Console.WriteLine(field);
        }
    }
}

This outputs:

As you can see it keeps the '~Doe' and separates just fine.

This is what the txt file had: 2000~*^~Doe~*^23