CSV import (variable fields)

Question

I have to import a series of CSV files and place the records into an Access database. This, in itself, is not too much of an issue.

The complication comes from the fact that the format of each CSV file is variable in that the number of fields may vary.

The best way to think about this is that there are 80 master fields, and each CSV file may contain ANY subset of those 80 fields. The only way of telling what you are dealing with is to look at the field headers in the CSV file. The data written to the Access file must contain all 80 fields (so missing fields need to be written as null values).

Rather than re-inventing the wheel, does anyone know of any code that handles this variable mapping/import?

Or any pointers in the right direction would be appreciated.

Answer 1

The best CSV reader library for .NET is LumenWorks'. It will not insert the data into Access, and I think you will still need to write some code to handle column differences but it'll make it easier than rolling your own parser and it'll be faster.

Answer 2

Generally, a basic automated CSV import for a repeated data transfer should expect a consistent input file to an agreed specification.

If the CSV file is being imported into an application by a user, then a basic mapping can be done by listing the fields in the CSV file (eg reading in the values from the first row) and having a drop down field next to these with the list of master (db) fields.

You could save the mapping for future reference by saving the list of Input Field Index or names with the corresponding Master Field Index/ID/name)