Issue importing CSV file using FileHelpers

c# csv filehelpers
OlegR picture OlegR · Apr 22, 2011 · Viewed 7.7k times · Source

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

Will Dean picture Will Dean · Apr 22, 2011

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.

Related questions

Column headers in CSV using fileHelpers library?

Is there a built-in field attribute in the FileHelper library which will add a header row in the final generated CSV? I have Googled and didn't find much info on it. Currently I have this: DelimitedFileEngine _engine = new DelimitedFileEngine(T); _…

Read More
How to use a dynamic CSV delimiter with FileHelpers?

Question: I need to read a CSV file. I use the FileHelpers library to achieve this. The problem is I need a dynamic delimiter (user defined), meaning anything can be delimiter (Comma, semicolon, tab, newline, but also anything else). The …

Read More
Reading CSV file and storing values into an array

I am trying to read a *.csv-file. The *.csv-file consist of two columns separated by semicolon (";"). I am able to read the *.csv-file using StreamReader and able to separate each line by using the Split() function. I want to store …

Read More