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#.
我试图从数据库中获取数据并将其导出为.csv文件格式，这与ETL过程类似。但我想在C＃中做到这一点。
- SQL Query to fetch data from database.
- SQL Query从数据库中获取数据。
- Format the data to a specified file specification.
- 将数据格式化为指定的文件规范。
- Convert it to .csv file.
- 将其转换为.csv文件。
I know that 1st step is easy to do, I am struggling to find a way for 2nd and 3rd step.
我知道第一步很容易做到，我很难找到第二步和第三步的方法。
2 个解决方案
#1
4
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.
我无法对文件规范发表评论，因为您还没有描述它，但编写CSV文件非常简单。与XML等不同，格式非常简单，您可以使用WriteLine直接写入StreamWriter。您所需要的只是输出包含用逗号分隔的列名的第一行，然后对于从SQL查询返回的每一行，以逗号分隔的相同顺序写入列值。唯一真正的问题是逃避，例如处理逗号，引号等，用引号括起每个值并转义值中的任何引号。
The example below does just that for a DataTable:
以下示例仅适用于DataTable：
http://dotnetguts.blogspot.co.nz/2007/01/exporting-datatable-to-csv-file-format.html
http://dotnetguts.blogspot.co.nz/2007/01/exporting-datatable-to-csv-file-format.html
#2
0
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.
1.SQL查询从数据库中获取数据。 2.将数据格式化为指定的文件规范。
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
LISTby which I fulfilled the file specifications.对于前两个步骤，我使用了Dapper.NET，它负责数据库部分以及格式化。 Dapper帮助将SQL结果转换为LIST，通过它我实现了文件规范。
3.Convert it to .csv file.
3.将其转换为.csv文件。
For converting the SQL results to CSV file, I have used FileHelpers Library which is more simple than I expected.
为了将SQL结果转换为CSV文件，我使用了FileHelpers Library，它比我预期的更简单。
Hope it will help someone.
希望它会帮助某人。
#1
4
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.
我无法对文件规范发表评论，因为您还没有描述它，但编写CSV文件非常简单。与XML等不同，格式非常简单，您可以使用WriteLine直接写入StreamWriter。您所需要的只是输出包含用逗号分隔的列名的第一行，然后对于从SQL查询返回的每一行，以逗号分隔的相同顺序写入列值。唯一真正的问题是逃避，例如处理逗号，引号等，用引号括起每个值并转义值中的任何引号。
The example below does just that for a DataTable:
以下示例仅适用于DataTable：
http://dotnetguts.blogspot.co.nz/2007/01/exporting-datatable-to-csv-file-format.html
http://dotnetguts.blogspot.co.nz/2007/01/exporting-datatable-to-csv-file-format.html
#2
0
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.
1.SQL查询从数据库中获取数据。 2.将数据格式化为指定的文件规范。
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
LISTby which I fulfilled the file specifications.对于前两个步骤，我使用了Dapper.NET，它负责数据库部分以及格式化。 Dapper帮助将SQL结果转换为LIST，通过它我实现了文件规范。
3.Convert it to .csv file.
3.将其转换为.csv文件。
For converting the SQL results to CSV file, I have used FileHelpers Library which is more simple than I expected.
为了将SQL结果转换为CSV文件，我使用了FileHelpers Library，它比我预期的更简单。
Hope it will help someone.
希望它会帮助某人。