Writing data into CSV file in C#

Question

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

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 csv file. 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 csv file?

Olympium

Rather use a StringBuilder and then make one save? – Rubato

If this is not a task which you need to fulfill daily, I recommend using LinqPad, which comes with a handy function to write data into a csv: Util.WriteCsv (mydatacollection, @"c:\temp\data.csv"); – Tedra

On a side note, make sure your csv values are encoded. I.e. if one of them contains a comma or an end-of-line character it might mess up your file. I usually just use a third party lib for csv stuff. – Foeticide

Possible duplicate of Are there any CSV readers/writer libraries in C#? – Honkytonk

Just use File.AppendAllText - you need APPEND text, not WRITE – Rayraya

Answer 1

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 CsvHelper that does all the safety checks, etc.

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

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

C# 6

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

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

EDIT

Here is a link to a question that explains what Environment.NewLine does.

Rubato

Okay thanks a lot.Which means suppose if I have a 3rd argument it should be string.Format("{0},{1},{2}{3}", first, second, Environment.NewLine)? – Olympium

@Rajat no, that will give an exception as you are only providing 3 parameters and saying there should be 4 (0,1,2,3). Remember to keep the Environment.NewLine the last parameter sent through – Rubato

you can do something like this. string.Format("{0},{1},{2}{3}", first, second, third, Environment.NewLine) – Rubato

Will this use the semi-column in case this is the default delimiter (on a french sytem for instance ) ? – Barrus

@Barrus it will not, you will have to replace the , with the ; if you want to use semi-colons. – Rubato

You can also get rid of {2} and Environment.NewLine and use AppendLine instead of Append – Norris

Instead of csv.AppendLine(string.Format(...)) you can use csv.AppendFormat(...). – Exciseman

And what happens when your CSV content has a comma that needs escaping? You need quotes. And what happens when a quote needs escaping? Correctly building CSV files are far more complex than this answer implies. – Alissaalistair

I got a "exceptionType": "System.UnauthorizedAccessException", – Xenophanes

I've try your solution, but for me (french culture) it works better by using System.Globalization.CultureInfo.CurrentCulture.TextInfo.ListSeparator instead of comma. – Omniscient

Answer 2

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.

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

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

Siloum

You should put your for loop inside the using statement. Otherwise you are going to re-open the file all the time again and again. – Fazeli

Good answer. Correct answer if you remove "{2}" from the format string and replace "image" with "second" in the same line. Also, don't forget to close the writer with w.Close(); w.Flush() is not needed. – Amygdaloid

This answer ignores the need to escape characters. – Alissaalistair

Can we also add that it helps to set your writer like this: new StreamWriter(path, false, Encoding.UTF8) – Ernestineernesto

if you have few hundred million lines...should you flush every line? – Geis

Answer 3

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

This question mentions a few options.

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

Jude

Answer 4

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());

Gambrinus

You may find you encounter memory pressure with this approach when building up large files. If you removed the .ToList then allLines would be an IEnumerbale<object[]>. You then could select on that instead of "for eaching" ie allines,Select(line => csv.AppendLine(string.Join(",", line))) which would give you an IEnumerable<string>. This can now just be passed to the File but the the WriteAllLines method. This now means the whole thing is lazy and you dont need to pull everything into memory, but you still get the syntax you are happy with. – Luciusluck

I'm using your method to write to a csv file. It's great! My latest implementation requires me to use File.AppendAllLines(filePath, lines, Encoding.ASCII); instead of File.WriteAllText(filePath, csv.ToString()); So, I'm doing something very clunky. After building the csv using StringBuilder, I convert it to a List<string> to get an IEnumerable for the call to AppendAllLines, which will not accept csv.ToString() as a parameter: List<string> lines = new List<string>(); lines.Add(csv.ToString(0, csv.Length)); Have you a better way to do this? – Superheterodyne

Answer 5

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

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);
    }
}

Fazeli

This answer includes that the extension must be csv. If it is xls, the data will all appear in one column... csv it will appear correctly in each column. – Intone

Answer 6

You can use AppendAllText instead:

File.AppendAllText(filePath, csv);

As the documentation of WriteAllText says:

If the target file already exists, it is overwritten

Also, 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);

Agree

Answer 7

Instead of reinventing the wheel a library could be used. CsvHelper is 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.

---

You can write your csv like the following.

using(var textWriter = new StreamWriter(@"C:\mypath\myfile.csv"))
{
    var writer = new CsvWriter(textWriter, CultureInfo.InvariantCulture);
    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, CultureInfo.InvariantCulture);
    writer.Configuration.Delimiter = ",";
    writer.WriteRecords(rows);
}

value1,true

value2,false

---

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

Arabella

You might have to pass CultureInfo to StreamWriter for this to work. – Sorce

Answer 8

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();
                    }
                }
            }
        }
    }
}

Zworykin

why no string-builder here? – Tala

Which RFC did you use to write this: .Replace(",", ";") + ',' ? – Mallen

This way takes too long if you have more than 20000 rows and 30 columns – Bunion

Answer 9

Handling Commas

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

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:

//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

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");
}

Cashbook

Answer 10

This is a simple tutorial on creating csv files using C# that you will be able to edit and expand on to fit your own needs.

First you’ll need to create a new Visual Studio C# console application, there are steps to follow to do this.

The example code will create a csv file called MyTest.csv in the location you specify. The contents of the file should be 3 named columns with text in the first 3 rows.

https://tidbytez.com/2018/02/06/how-to-create-a-csv-file-with-c/

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.IO;

namespace CreateCsv
{
    class Program
    {
        static void Main()
        {
            // Set the path and filename variable "path", filename being MyTest.csv in this example.
            // Change SomeGuy for your username.
            string path = @"C:\Users\SomeGuy\Desktop\MyTest.csv";

            // Set the variable "delimiter" to ", ".
            string delimiter = ", ";

            // This text is added only once to the file.
            if (!File.Exists(path))
            {
                // Create a file to write to.
                string createText = "Column 1 Name" + delimiter + "Column 2 Name" + delimiter + "Column 3 Name" + delimiter + Environment.NewLine;
                File.WriteAllText(path, createText);
            }

            // This text is always added, making the file longer over time
            // if it is not deleted.
            string appendText = "This is text for Column 1" + delimiter + "This is text for Column 2" + delimiter + "This is text for Column 3" + delimiter + Environment.NewLine;
            File.AppendAllText(path, appendText);

            // Open the file to read from.
            string readText = File.ReadAllText(path);
            Console.WriteLine(readText);
        }
    }
}

Kroon

Answer 11

public static class Extensions
{
    public static void WriteCSVLine(this StreamWriter writer, IEnumerable<string> fields)
    {
        const string q = @"""";
        writer.WriteLine(string.Join(",",
            fields.Select(
                v => (v.Contains(',') || v.Contains('"') || v.Contains('\n') || v.Contains('\r')) ? $"{q}{v.Replace(q, q + q)}{q}" : v
                )));
    }

    public static void WriteCSVLine(this StreamWriter writer, params string[] fields) => WriteCSVLine(writer, (IEnumerable<string>)fields);
}

This should allow you to write a csv file quite simply. Usage:

StreamWriter writer = new ("myfile.csv");
writer.WriteCSVLine("A", "B"); // A,B

Haddad

Answer 12

(disclaimer: I'm about to recommend a project I'm involved in)

I've been maintaining an open-source CSV writer library since 2015: https://github.com/jitbit/CsvExport

It's Excel-compatible, escapes commas/quotes/multiline, exports dates in timezone-proof format, etc. It's .NET standard 2.0, works with .NET Core, and .NET Framework.

Usage:

var myExport = new CsvExport();

myExport.AddRow();
myExport["Region"] = "Denver, USA";
myExport["Sales"] = 100000;
myExport["Date Opened"] = new DateTime(2003, 12, 31);

myExport.AddRow();
myExport["Region"] = "London \"in\" the UK";
myExport["Sales"] = 50000;
myExport["Date Opened"] = new DateTime(2005, 1, 1, 9, 30, 0);

//ASP.NET MVC action example
return File(myExport.ExportToBytes(), "text/csv", "results.csv");

Available on Nuget:

Install-Package CsvExport

Robson

Answer 13

Here is another open source library to create CSV file easily, Cinchoo ETL

METHOD 1: Dynamic Model

List<dynamic> objs = new List<dynamic>();

dynamic rec1 = new ExpandoObject();
rec1.Id = 10;
rec1.Name = @"Mark";
rec1.JoinedDate = new DateTime(2001, 2, 2);
rec1.IsActive = true;
rec1.Salary = new ChoCurrency(100000);
objs.Add(rec1);

dynamic rec2 = new ExpandoObject();
rec2.Id = 200;
rec2.Name = "Tom";
rec2.JoinedDate = new DateTime(1990, 10, 23);
rec2.IsActive = false;
rec2.Salary = new ChoCurrency(150000);
objs.Add(rec2);

using (var parser = new ChoCSVWriter("emp.csv").WithFirstLineHeader())
{
    parser.Write(objs);
}

METHOD 2: POCO Model

List<Emp> objs = new List<Emp>();

Emp rec1 = new Emp();
rec1.Id = 10;
rec1.Name = @"Mark";
objs.Add(rec1);

Emp rec2 = new Emp();
rec2.Id = 200;
rec2.Name = "Tom";
objs.Add(rec2);

using (var parser = new ChoCSVWriter<Emp>("emp.csv").WithFirstLineHeader())
{
    parser.Write(objs);
}

For more information, please read the CodeProject article on usage.

Scintillant

Answer 14

One simple way to get rid of the overwriting issue is to use File.AppendText to append line at the end of the file as

void Main()
{
    using (System.IO.StreamWriter sw = System.IO.File.AppendText("file.txt"))
    {          
        string first = reader[0].ToString();
        string second=image.ToString();
        string csv = string.Format("{0},{1}\n", first, second);
        sw.WriteLine(csv);
    }
} 

Cammie

Answer 15

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

Frankpledge

Answer 16

 string string_value= string.Empty;

            for (int i = 0; i < ur_grid.Rows.Count; i++)
            {
                for (int j = 0; j < ur_grid.Rows[i].Cells.Count; j++)
                {
                    if (!string.IsNullOrEmpty(ur_grid.Rows[i].Cells[j].Text.ToString()))
                    {
                        if (j > 0)
                            string_value= string_value+ "," + ur_grid.Rows[i].Cells[j].Text.ToString();
                        else
                        {
                            if (string.IsNullOrEmpty(string_value))
                                string_value= ur_grid.Rows[i].Cells[j].Text.ToString();
                            else
                                string_value= string_value+ Environment.NewLine + ur_grid.Rows[i].Cells[j].Text.ToString();
                        }
                    }
                }
            }


            string where_to_save_file = @"d:\location\Files\sample.csv";
            File.WriteAllText(where_to_save_file, string_value);

            string server_path = "/site/Files/sample.csv";
            Response.ContentType = ContentType;
            Response.AppendHeader("Content-Disposition", "attachment; filename=" + Path.GetFileName(server_path));
            Response.WriteFile(server_path);
            Response.End();

Spiritual