Parsing csv records depending upon field values seperation

463 views Asked by At

I am reading csv file records using csvhelper, but I have noticed that sometimes csv fields values are separated by blankspace(\t) and sometimes field values are separated by comma.

Now I have 6 columns, but as field values are separated by blankspace I am getting all records in single column. So while fetching a column value for the second field I get the error : Field at index '1' does not exist.

So I am getting the error when reading records:

DataTable dt = new DataTable();

CsvReader reader = new CsvReader(sr);
reader.Configuration.HasHeaderRecord = true;
reader.Configuration.IgnoreHeaderWhiteSpace = true;
reader.ReadHeader();
var headers = reader.Parser.RawRecord.Split(new[] { ',', '\t' },
    StringSplitOptions.RemoveEmptyEntries).Select(x => x.Trim()).
Where(x => !string.IsNullOrWhiteSpace(x)).ToArray(); //6 columns


foreach (var header in headers) {
    DataColumn col = new DataColumn();
    col.ColumnName = header;
    dt.Columns.Add(col);
}
reader.Read();

do {
    DataRow drRow = dt.NewRow();
    for (int iThColumn = 0; iThColumn < headers.Count(); iThColumn++) {
        var data =  reader.GetField<string>(iThColumn);

        if (string.IsNullOrEmpty(data))
            drRow[iThColumn] = null;
        else
            drRow[iThColumn] = reader.GetField<string>(iThColumn);

    }
    dt.Rows.Add(drRow);

} while (reader.Read()); 

How do I solve this problem?

0

There are 0 answers