12

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();
halfer
  • 18,701
  • 13
  • 79
  • 158
OlegR
  • 194
  • 1
  • 9

1 Answers1

2

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.

Will Dean
  • 37,648
  • 10
  • 84
  • 116
  • 6
    Parsing CSV with .Split() is just asking for problems. – Patonza Oct 29 '12 at 12:08
  • If you aren't dealing with a file that has escaped commas, carriage returns, linefeeds, doesn't have double quotes in any of the fields, and always uses the same delimiters/quotes, never starts any column with a double quote, and you never have to deal with large CSV files, then perhaps String.Split may work for you...until it doesn't. – Robert McKee Oct 19 '18 at 19:52
  • @RobertMcKee I was only proposing that individual lines were split with `string.split()` - at which point none of the stuff about line-breaks and file size matters. Like you say, quotes are another issue which one would need to consider. – Will Dean Oct 23 '18 at 20:45