CODEWITHSUNDEEP
c#.netado.net
Praveen
Praveen

Reputation: 56519

Fetch Data from database and export it to a .csv file

I am trying to fetch data from database and export it to .csv file format, which is similar to ETL process. But I want to do this in C#.

  1. SQL Query to fetch data from database.
  2. Format the data to a specified file specification.
  3. Convert it to .csv file.

I know that 1st step is easy to do, I am struggling to find a way for 2nd and 3rd step.

Upvotes: 1

Views: 13689

Answers (2)

Praveen
Praveen

Reputation: 56519

I figured out a simple and efficient way to do this:

1.SQL Query to fetch data from database.
2. Format the data to a specified file specification.

For the first 2 steps, I have used the Dapper.NET, which took care of the database part as well as formatting. Dapper helped to convert the SQL results to a LIST by which I fulfilled the file specifications.

3.Convert it to .csv file.

For converting the SQL results to CSV file, I have used FileHelpers Library which is more simple than I expected.

Hope it will help someone.

Upvotes: 0

David Cummins
David Cummins

Reputation: 988

I can't comment on the file specification, as you haven't described it, but writing CSV files is pretty easy. Unlike XML etc the format is so simplistic you can write directly to a StreamWriter using WriteLine. All you need is to output a first line that contains the column names separated with commas, then for each row returned from your SQL Query write the column values in the same order separated by commas. The only real gotcha is escaping, e.g. dealing with commas, quotes, etc by surrounding each value with quotes and escaping any quotes in the value.

The example below does just that for a DataTable:

http://dotnetguts.blogspot.co.nz/2007/01/exporting-datatable-to-csv-file-format.html

Upvotes: 4

Related Questions