Best way to load data from a CSV file into an ADO.Net DataSet

Question

Best way to load data from a CSV file into an ADO.Net DataSet

Smarties 1

Hi,

There's a CSV file, 300k lines, a daily export from an ERP system.
It lives on a network drive.
I need to loop through the 300k lines, analyse, and make decisions (do stuff).
Once in an ADO.Net DataSet I'm off and running.

Do I read the CSV file line by line, split each line based on the known delimitator, and add the fields into a DataTable row by row, or is there a more elegant way with OLE DB for example?

I don't have the experience to know what is the 'right' way to do this.
I'm keen to start 'right', first time!

Thanks.

AgaveJoe 30,126 Reputation points

2022-01-25T20:51:48.843+00:00

Do I read the CSV file line by line, split each line based on the known delimitator

Yes.

You can also look for a nuget package that parses a delimited file.

https://www.nuget.org/packages/CsvHelper/

and add the fields into a DataTable row by row

I feel a strong type is easier to work with than a DataTable. Loop don't need magic string or indexes.

OLE DB works too.

https://stackoverflow.com/questions/6813607/parsing-csv-using-oledb-using-c-sharp
Castorix31 90,681 Reputation points

2022-01-25T21:45:20.857+00:00

As AgaveJoe said, the .csv can be loaded "automatically" with OleDB
I had posted a sample in this thread (in french, but the code is very simple)
Jack J Jun 25,296 Reputation points

2022-02-07T08:52:49.457+00:00

@Smarties , is any update?

Your answer

AgaveJoe 30,126 Reputation points

2022-01-25T20:51:48.843+00:00

Do I read the CSV file line by line, split each line based on the known delimitator

Yes.

You can also look for a nuget package that parses a delimited file.

https://www.nuget.org/packages/CsvHelper/

and add the fields into a DataTable row by row

I feel a strong type is easier to work with than a DataTable. Loop don't need magic string or indexes.

OLE DB works too.

https://stackoverflow.com/questions/6813607/parsing-csv-using-oledb-using-c-sharp
Castorix31 90,681 Reputation points

2022-01-25T21:45:20.857+00:00

As AgaveJoe said, the .csv can be loaded "automatically" with OleDB
I had posted a sample in this thread (in french, but the code is very simple)
Jack J Jun 25,296 Reputation points

2022-02-07T08:52:49.457+00:00

@Smarties , is any update?

Share via

Best way to load data from a CSV file into an ADO.Net DataSet

Your answer