用C#将数据写入CSV文件

Writing data into CSV file in C#

提问by rampuriyaaa

I am trying to write into a csvfile row by row using C# language. Here is my function

我正在尝试csv使用 C# 语言逐行写入文件。这是我的功能

string first = reader[0].ToString();
string second=image.ToString();
string csv = string.Format("{0},{1}\n", first, second);
File.WriteAllText(filePath, csv);

The whole function runs inside a loop, and every row should be written to the csvfile. In my case, next row overwrites the existing row and in the end, I am getting an only single record in the csv file which is the last one. How can I write all the rows in the csvfile?

整个函数在一个循环中运行，每一行都应该写入csv文件。就我而言，下一行覆盖了现有行，最后，我在 csv 文件中获得了唯一一条记录，这是最后一条记录。如何写入csv文件中的所有行？

采纳答案by Johan

UPDATE

更新

Back in my na?ve days, I suggested doing this manually (it was a simple solution to a simple question), however due to this becoming more and more popular, I'd recommend using the library CsvHelperthat does all the safety checks, etc.

回到我天真的日子，我建议手动执行此操作（这是一个简单问题的简单解决方案），但是由于这变得越来越流行，我建议使用执行所有安全检查的库CsvHelper，等等。

CSV is way more complicated than what the question/answer suggests.

CSV 比问题/答案所暗示的要复杂得多。

Original Answer

原答案

As you already have a loop, consider doing it like this:

由于您已经有了一个循环，请考虑这样做：

//before your loop
    var csv = new StringBuilder();

//in your loop
    var first = reader[0].ToString();
    var second = image.ToString();
    //Suggestion made by KyleMit
    var newLine = string.Format("{0},{1}", first, second);
    csv.AppendLine(newLine);  

//after your loop
    File.WriteAllText(filePath, csv.ToString());

Or something to this effect. My reasoning is: you won't be need to write to the file for every item, you will only be opening the stream once and then writing to it.

或类似的东西。我的理由是：您不需要为每个项目写入文件，您只需打开流一次然后写入它。

You can replace

你可以更换

File.WriteAllText(filePath, csv.ToString());

with

和

File.AppendAllText(filePath, csv.ToString());

if you want to keep previous versions of csv in the same file

如果您想将以前版本的 csv 保留在同一个文件中

C# 6

C# 6

If you are using c# 6.0 then you can do the following

如果您使用的是 c# 6.0，那么您可以执行以下操作

var newLine = $"{first},{second}"

EDIT

编辑

Here is a linkto a question that explains what Environment.NewLinedoes.

这是一个问题的链接，该问题解释了什么Environment.NewLine。

回答by Shadow Wizard is Ear For You

Simply use AppendAllText instead:

只需使用 AppendAllText 代替：

File.AppendAllText(filePath, csv);

The only downside of the AppendAllText is that it will throw error when file does not exist, so this must be checked

AppendAllText 唯一的缺点是当文件不存在时它会抛出错误，因此必须检查

Sorry, blonde moment before reading the documentation. Anyway, the WriteAllText method overwrites anything that was previously written in the file, if the file exists.

抱歉，在阅读文档之前的金发时刻。无论如何，如果文件存在，WriteAllText 方法会覆盖之前写入文件的任何内容。

Note that your current code is not using proper new lines, for example in Notepad you'll see it all as one long line. Change the code to this to have proper new lines:

请注意，您当前的代码没有使用正确的换行符，例如在记事本中，您将看到它全部为一个长行。将代码更改为此以具有适当的新行：

string csv = string.Format("{0},{1}{2}", first, image, Environment.NewLine);

回答by Pavel Murygin

I would highly recommend you to go the more tedious route. Especially if your file size is large.

我强烈建议你走更乏味的路线。特别是如果您的文件很大。

using(var w = new StreamWriter(path))
{
    for( /* your loop */)
    {
        var first = yourFnToGetFirst();
        var second = yourFnToGetSecond();
        var line = string.Format("{0},{1}", first, second);
        w.WriteLine(line);
        w.Flush();
    }
}

File.AppendAllText()opens a new file, writes the content and then closes the file. Opening files is a much resource-heavy operation, than writing data into open stream. Opening\closing a file inside a loop will cause performance drop.

File.AppendAllText()打开一个新文件，写入内容，然后关闭文件。打开文件是一项资源繁重的操作，而不是将数据写入打开的流中。在循环中打开\关闭文件会导致性能下降。

The approach suggested by Johan solves that problem by storing all the output in memory and then writing it once. However (in case of big files) you program will consume a large amount of RAM and even crash with OutOfMemoryException

Johan 建议的方法通过将所有输出存储在内存中然后写入一次来解决该问题。但是（在大文件的情况下）您的程序将消耗大量 RAM 甚至崩溃OutOfMemoryException

Another advantage of my solution is that you can implement pausing\resuming by saving current position in input data.

我的解决方案的另一个优点是您可以通过在输入数据中保存当前位置来实现暂停/恢复。

upd. Placed using in the right place

更新。放置在正确的地方使用

回答by Oliver

Instead of calling every time AppendAllText()you could think about opening the file once and then write the whole content once:

AppendAllText()您可以考虑打开文件一次然后将整个内容写入一次，而不是每次都调用：

var file = @"C:\myOutput.csv";

using (var stream = File.CreateText(file))
{
    for (int i = 0; i < reader.Count(); i++)
    {
        string first = reader[i].ToString();
        string second = image.ToString();
        string csvRow = string.Format("{0},{1}", first, second);

        stream.WriteLine(csvRow);
    }
}

回答by user3495843

I use a two parse solution as it's very easy to maintain

我使用两个解析解决方案，因为它很容易维护

// Prepare the values
var allLines = (from trade in proposedTrades
                select new object[] 
                { 
                    trade.TradeType.ToString(), 
                    trade.AccountReference, 
                    trade.SecurityCodeType.ToString(), 
                    trade.SecurityCode, 
                    trade.ClientReference, 
                    trade.TradeCurrency, 
                    trade.AmountDenomination.ToString(), 
                    trade.Amount, 
                    trade.Units, 
                    trade.Percentage, 
                    trade.SettlementCurrency, 
                    trade.FOP, 
                    trade.ClientSettlementAccount, 
                    string.Format("\"{0}\"", trade.Notes),                             
                }).ToList();

// Build the file content
var csv = new StringBuilder();
allLines.ForEach(line => 
{
    csv.AppendLine(string.Join(",", line));            
});

File.WriteAllText(filePath, csv.ToString());

回答by OneWileyDog

You might just have to add a line feed "\n\r".

您可能只需要添加换行符"\n\r"。

回答by Jeppe Andreasen

Writing csv files by hand can be difficult because your data might contain commas and newlines. I suggest you use an existing library instead.

手动编写 csv 文件可能很困难，因为您的数据可能包含逗号和换行符。我建议您改用现有的库。

This question mentions a few options.

这个问题提到了几个选项。

Are there any CSV readers/writer libraries in C#?

C# 中是否有任何 CSV 读取器/写入器库？

回答by Alejandro Haro

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;

public partial class CS : System.Web.UI.Page
{
    protected void ExportCSV(object sender, EventArgs e)
    {
        string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand("SELECT * FROM Customers"))
            {
                using (SqlDataAdapter sda = new SqlDataAdapter())
                {
                    cmd.Connection = con;
                    sda.SelectCommand = cmd;
                    using (DataTable dt = new DataTable())
                    {
                        sda.Fill(dt);

                        //Build the CSV file data as a Comma separated string.
                        string csv = string.Empty;

                        foreach (DataColumn column in dt.Columns)
                        {
                            //Add the Header row for CSV file.
                            csv += column.ColumnName + ',';
                        }

                        //Add new line.
                        csv += "\r\n";

                        foreach (DataRow row in dt.Rows)
                        {
                            foreach (DataColumn column in dt.Columns)
                            {
                                //Add the Data rows.
                                csv += row[column.ColumnName].ToString().Replace(",", ";") + ',';
                            }

                            //Add new line.
                            csv += "\r\n";
                        }

                        //Download the CSV file.
                        Response.Clear();
                        Response.Buffer = true;
                        Response.AddHeader("content-disposition", "attachment;filename=SqlExport.csv");
                        Response.Charset = "";
                        Response.ContentType = "application/text";
                        Response.Output.Write(csv);
                        Response.Flush();
                        Response.End();
                    }
                }
            }
        }
    }
}

回答by Trevor Nestman

Handling Commas

处理逗号

For handling commas inside of values when using string.Format(...), the following has worked for me:

为了在使用时处理值内的逗号string.Format(...)，以下对我有用：

var newLine = string.Format("\"{0}\",\"{1}\",\"{2}\"",
                              first,
                              second,
                              third                                    
                              );
csv.AppendLine(newLine);

So to combine it with Johan's answer, it'd look like this:

因此，要将其与 Johan 的答案结合起来，它看起来像这样：

//before your loop
var csv = new StringBuilder();

//in your loop
  var first = reader[0].ToString();
  var second = image.ToString();
  //Suggestion made by KyleMit
  var newLine = string.Format("\"{0}\",\"{1}\"", first, second);
  csv.AppendLine(newLine);  

//after your loop
File.WriteAllText(filePath, csv.ToString());

---

---

Returning CSV File

返回 CSV 文件

If you simply wanted to return the file instead of writing it to a location, this is an example of how I accomplished it:

如果您只是想返回文件而不是将其写入某个位置，这是我如何完成它的示例：

From a Stored Procedure

从存储过程

public FileContentResults DownloadCSV()
{
  // I have a stored procedure that queries the information I need
  SqlConnection thisConnection = new SqlConnection("Data Source=sv12sql;User ID=UI_Readonly;Password=SuperSecure;Initial Catalog=DB_Name;Integrated Security=false");
  SqlCommand queryCommand = new SqlCommand("spc_GetInfoINeed", thisConnection);
  queryCommand.CommandType = CommandType.StoredProcedure;

  StringBuilder sbRtn = new StringBuilder();

  // If you want headers for your file
  var header = string.Format("\"{0}\",\"{1}\",\"{2}\"",
                             "Name",
                             "Address",
                             "Phone Number"
                            );
  sbRtn.AppendLine(header);

  // Open Database Connection
  thisConnection.Open();
  using (SqlDataReader rdr = queryCommand.ExecuteReader())
  {
    while (rdr.Read())
    {
      // rdr["COLUMN NAME"].ToString();
      var queryResults = string.Format("\"{0}\",\"{1}\",\"{2}\"",
                                        rdr["Name"].ToString(),
                                        rdr["Address"}.ToString(),
                                        rdr["Phone Number"].ToString()
                                       );
      sbRtn.AppendLine(queryResults);
    }
  }
  thisConnection.Close();

  return File(new System.Text.UTF8Encoding().GetBytes(sbRtn.ToString()), "text/csv", "FileName.csv");
}

From a List

从列表中

/* To help illustrate */
public static List<Person> list = new List<Person>();

/* To help illustrate */
public class Person
{
  public string name;
  public string address;
  public string phoneNumber;
}

/* The important part */
public FileContentResults DownloadCSV()
{
  StringBuilder sbRtn = new StringBuilder();

  // If you want headers for your file
  var header = string.Format("\"{0}\",\"{1}\",\"{2}\"",
                             "Name",
                             "Address",
                             "Phone Number"
                            );
  sbRtn.AppendLine(header);

  foreach (var item in list)
  {
      var listResults = string.Format("\"{0}\",\"{1}\",\"{2}\"",
                                        item.name,
                                        item.address,
                                        item.phoneNumber
                                       );
      sbRtn.AppendLine(listResults);
    }
  }

  return File(new System.Text.UTF8Encoding().GetBytes(sbRtn.ToString()), "text/csv", "FileName.csv");
}

Hopefully this is helpful.

希望这是有帮助的。

回答by NtFreX

Instead of reinventing the wheel a library could be used. CsvHelperis great for creating and reading csv files. It's read and write operations are stream based and therefore also support operations with a big amount of data.

可以使用库而不是重新发明轮子。CsvHelper非常适合创建和读取 csv 文件。它的读写操作是基于流的，因此也支持大量数据的操作。

---

---

You can write your csv like the following.

您可以像下面这样编写 csv。

using(var textWriter = new StreamWriter(@"C:\mypath\myfile.csv"))
{
    var writer = new CsvWriter(textWriter);
    writer.Configuration.Delimiter = ",";

    foreach (var item in list)
    {
        writer.WriteField( "a" );
        writer.WriteField( 2 );
        writer.WriteField( true );
        writer.NextRecord();
    }
}

---

---

As the library is using reflection it will take any type and parse it directly.

由于库使用反射，它将采用任何类型并直接解析它。

public class CsvRow
{
    public string Column1 { get; set; }
    public bool Column2 { get; set; }

    public CsvRow(string column1, bool column2)
    {
        Column1 = column1;
        Column2 = column2;
    }
}

IEnumerable<CsvRow> rows = new [] {
    new CsvRow("value1", true),
    new CsvRow("value2", false)
};
using(var textWriter = new StreamWriter(@"C:\mypath\myfile.csv")
{
    var writer = new CsvWriter(textWriter);
    writer.Configuration.Delimiter = ",";
    writer.WriteRecords(rows);
}

value1,true

value2,false

值1，真

值2，假

---

---

If you want to read more about the librarys configurations and possibilities you can do so here.

如果您想阅读有关库配置和可能性的更多信息，您可以在这里阅读。