CSV appears to be corrupt on Double quotes in Headers - C#

user_19229469 0
Viewed 78

I was trying to read CSV file in C#.

I have tried File.ReadAllLines(path).Select(a => a.Split(';')) way but the issue is when there is \n multiple line in a cell it is not working.

So I have tried below

using LumenWorks.Framework.IO.Csv;

            var csvTable = new DataTable();
            using (TextReader fileReader = File.OpenText(path))
            using (var csvReader = new CsvReader(fileReader, false))
            {
                csvTable.Load(csvReader);
            }
            for (int i = 0; i < csvTable.Rows.Count; i++)
            {
               if (!(csvTable.Rows[i][0] is DBNull))
                {
                    var row1= csvTable.Rows[i][0];
                }
               if (!(csvTable.Rows[i][1] is DBNull))
                {
                    var row2= csvTable.Rows[i][1];
                }
            }

The issue is the above code throwing exception as The CSV appears to be corrupt near record '0' field '5 at position '63' This is because the header of CSV's having two double quote as below

"Header1",""Header2""

Is there a way that I can ignore double quotes and process the CSV's. update

I have tried with TextFieldParser as below

     public static void GetCSVData()
        {
            using (var parser = new TextFieldParser(path))
            {
                parser.HasFieldsEnclosedInQuotes = false;
                parser.Delimiters = new[] { "," };
                while (parser.PeekChars(1) != null)
                {
                    string[] fields = parser.ReadFields();
                    foreach (var field in fields)
                    {
                        Console.Write(field + " ");
                    }
                    Console.WriteLine(Environment.NewLine);
                }
            }
        }

The output: enter image description here

Sample CSV data I have used: enter image description here

Any help is appreciated.

c#
1 Answers

Hope this works!

Please replace two double quotes as below from csv:

using (FileStream fs = new FileStream(Path, FileMode.Open, FileAccess.ReadWrite, FileShare.None))
{
    StreamReader sr = new StreamReader(fs);
    string contents = sr.ReadToEnd();

    // replace "" with "
    contents = contents.Replace("\"\"", "\"");

    // go back to the beginning of the stream
    fs.Seek(0, SeekOrigin.Begin);

    // adjust the length to make sure all original
    // contents is overritten
    fs.SetLength(contents.Length);

    StreamWriter sw = new StreamWriter(fs);
    sw.Write(contents);
    sw.Close();
}

Then use the same CSV helper

using LumenWorks.Framework.IO.Csv;

var csvTable = new DataTable();
using (TextReader fileReader = File.OpenText(path))
using (var csvReader = new CsvReader(fileReader, false))
{
    csvTable.Load(csvReader);
}

Thanks.

user_83935100
Related