C# DataTable

[ejbiker93ss]

I'm struggling to find an example of loading a .csv file to a DataTable in c#. Can you point me in the right direction?

[MarkPflug]

Loading a data table could be as simple as:

var reader = CsvDataReader.Create("mydata.csv");
var dt = new DataTable();
dt.Load(reader);

However, loading the data this way will result in everything being interpreted as a string, which is likely not what you want. You can provide a schema for the data if you know what you're processing ahead of time.

This uses the Sylvan.Data package which provides the Schema type.

var schema =
    new Schema.Builder()
    .Add<string>("FirstName")
    .Add<string>("LastName")
    .Add<DateTime>("DateOfBirth")
    .Add<decimal>("Height")
    .Build();

var csvOptions = new CsvDataReaderOptions
{
    Schema = new CsvSchema(schema)
};

var reader = CsvDataReader.Create("mydata.csv", csvOptions);
var dt = new DataTable();
dt.Load(reader);

If you don't know the schema ahead of time, you can use the SchemaAnalyzer type, also from the Sylvan.Data package, which can inspect the data and attempt to determine the "shape" of the data. This requires two passes over the CSV data, one to determine the schema, then a second pass to process the data using the determined schema.

var file = "myData.csv";

// analyze the schema of the file.
var analyzer = new SchemaAnalyzer();
var reader = CsvDataReader.Create(file);
var result = analyzer.Analyze(reader);
var schema = result.GetSchema();
reader.Close();


// load the data using the determined schema
var csvOptions = new CsvDataReaderOptions { Schema = new CsvSchema(schema) };
reader = CsvDataReader.Create(file, csvOptions);

var dt = new DataTable();
dt.Load(reader);

[ejbiker93ss]

Hi,

Thanks for the response! I haven't had time to try this yet. What I'm trying to do is set up a system to where a non-programmer can set up new imports into our database by mapping fields. So I don't know the schema before hand

[ejbiker93ss]

BTW, I would suggest making a CsvDataReader.ToDatatable method. It would be a lot easier for people to discover 😉

[MarkPflug]

No. That would encourage people to use DataTable, and you should be avoiding it. If you're trying to load data into a database, you should be using a bulk copy operation, which consumes the DbDataReader directly. Loading into a DataTable first will slow things down tremendously, and require that the entire dataset be loaded into memory at once.

Here is an example of bulk loading Excel data (using Sylvan.Data.Excel) into a SqlServer instance. The same pattern can be used with the CsvDataReader: https://github.com/MarkPflug/SylvanSamples/blob/issue_92/ExcelBulkCopy/ExcelBulkCopy/Program.cs

[ejbiker93ss]

So the focus here is on speed, eh? Most people aren't loading millions of records to the database, and using a datatable won't slow it down enough for a human to comprehend. 😉😉😉

What if I want to massage the data before sending it on to my SQL database. What method do you suggest?