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?