0

I am loading CVS files for data import. The files come from various sources so the header names and location are often changing. I searched and found helpful libs like CsvHelper & FileHelpers

Question: either using FileHelper.net or CsvHelper, how do we extract both the Header names & the Column datatype? so that I can create a drop for each col, to map between .NET type <==> to a SQL type

Transformer
  • 6,963
  • 2
  • 26
  • 52

1 Answers1

1

Just read in the first line of the file with, say, 

string headers = File.ReadLines("MyFile.txt").First();

And then use a class builder to build whatever CSV spec you need.

DelimitedClassBuilder cb = new DelimitedClassBuilder("MyProduct", delimiter: ",");

cb.AddField("Name", typeof(string));
cb.LastField.TrimMode = TrimMode.Both;

cb.AddField("Description", typeof(string));
cb.LastField.FieldQuoted = true;
cb.LastField.QuoteChar = '"';
cb.LastField.QuoteMode = QuoteMode.OptionalForBoth;

// etc... e.g., add a date field
cb.AddField("SomeDate", typeof(DateTime));

engine = new FileHelperEngine(cb.CreateRecordClass());

DataTable dt = engine.ReadFileAsDT("test.txt");
shamp00
  • 11,106
  • 4
  • 38
  • 81
  • thanks, I see your suggestion to make it as typeof string, I may follow this suggestion. My concern is that, I would _loose date and number cols like price/cost_. Also, _how to go about getting the headers in the first row?_ – Transformer Nov 05 '18 at 17:04
  • Get the headers with `File.ReadLines("MyFile.txt").First();` – shamp00 Nov 07 '18 at 09:20
  • Then iterate through those headers and build your record class as shown in the sample code. You can specify any type for each column. Notice the _SomeDate_ field is being created with `typeof(DateTime)`. – shamp00 Nov 07 '18 at 09:23
  • yes, but how would i know during coding time if that field was datetime...? because here it assumes i know some col x to be of `typeof(DateTime)` ... but I dont. Header- BTW I get it now, with the header. After calling first will the pointer/cursor move to the second row? – Transformer Nov 08 '18 at 01:45
  • FileHelpers does not magically know what type of data a column contains. You have to tell it. – shamp00 Nov 08 '18 at 12:39
  • You could assume everything is `typeof(string)` and then write a routine to detect and convert a column to something more specific. – shamp00 Nov 08 '18 at 12:41