Issue importing CSV file using FileHelpers

Question

I'm using FileHelpers library to import csv files into the database table. I'm having an issue importing the file that has the field that starts with the number ('800NUMBER') in the file header.

Code to import:
DataTable data = CommonEngine.CsvToDataTable(file, ',');

Exception:
FileHelpers.FileHelpersException: The string '800NUMBER' not is a valid .NET identifier. at FileHelpers.RunTime.FieldBuilder..ctor(String fieldName, String fieldType) at FileHelpers.RunTime.DelimitedClassBuilder.AddField(String fieldName, String fieldType) at FileHelpers.RunTime.CsvClassBuilder.AddField(String fieldName, String fieldType) at FileHelpers.RunTime.DelimitedClassBuilder.AddField(String fieldName) at FileHelpers.RunTime.CsvClassBuilder..ctor(CsvOptions options) at FileHelpers.CsvEngine.CsvToDataTable(String filename, String classname, Char delimiter, Boolean hasHeader) at FileHelpers.CommonEngine.CsvToDataTable(String filename, Char delimiter)

I'm not sure if there is a way to escape the column name like '[800NUMBER]'.

The column name cannot be changed because that's the way the client supplies it to us.

---

Solved this issue by reading 'header' row separately from the 'data' rows. Then, I change the column names in the 'data' and use SqlBulkCopy to import into the database.

    FileHelpers.CsvOptions options = new FileHelpers.CsvOptions("ImportRecord", ',', file);
    options.HeaderLines = 0;        

    FileHelpers.CsvEngine engine = new FileHelpers.CsvEngine(options);

    engine.Options.IgnoreFirstLines = 0; 
    DataTable header = engine.ReadStringAsDT(FileHelpers.CommonEngine.RawReadFirstLines(file, 1)); 

    engine.Options.IgnoreFirstLines = 1;
    DataTable data = engine.ReadFileAsDT(file); 

    for (int i = 0; i < header.Columns.Count; i++)
        data.Columns[i].ColumnName = header.Rows[0][i].ToString();

Answer 1

Looking at the source of FileHelpers, there's not much you can do about this other than change the column name. However, you could change FileHelpers pretty easily to decorate the CSV field name before making the C# fields.

Of course, CSV is not the world's most complex format - if you know you don't have to deal with escaped commas, then String.Split(',', myLine) is often pretty much all you need. Personally I doubt I'd introduce the hassle of a 3rd-party dependency just to read CSV files.