Write to CSV file and export it?

Question

In C# ASP.net, could someone show me how I can write entries from an Array/List to a CSV file on the server and then open the file? I think the second part would be something like - Response.Redirect("http://myserver.com/file.csv"), however not sure on how to write the file on the server.

Also if this page is accessed by many users, is it better to generate a new CSV file every time or overwrite the same file? Would there be any read/write/lock issues if both users try accessing the same CSV file etc.?

---

Update:

This is probably a silly question and I have searched on Google but I'm not able to find a definitive answer - how do you write a CSV file to the webserver and export it in C# ASP.net? I know how to generate it but I would like to save it to www.mysite.com/my.csv and then export it.

Solution 1

Rom, you're doing it wrong. You don't want to write files to disk so that IIS can serve them up. That adds security implications as well as increases complexity. All you really need to do is save the CSV directly to the response stream.

Here's the scenario: User wishes to download csv. User submits a form with details about the csv they want. You prepare the csv, then provide the user a URL to an aspx page which can be used to construct the csv file and write it to the response stream. The user clicks the link. The aspx page is blank; in the page codebehind you simply write the csv to the response stream and end it.

You can add the following to the (I believe this is correct) Load event:

string attachment = "attachment; filename=MyCsvLol.csv";
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ClearHeaders();
HttpContext.Current.Response.ClearContent();
HttpContext.Current.Response.AddHeader("content-disposition", attachment);
HttpContext.Current.Response.ContentType = "text/csv";
HttpContext.Current.Response.AddHeader("Pragma", "public");

var sb = new StringBuilder();
foreach(var line in DataToExportToCSV)
  sb.AppendLine(TransformDataLineIntoCsv(line));

HttpContext.Current.Response.Write(sb.ToString());

writing to the response stream code ganked from here.

Solution 2

Here's a very simple free open-source CsvExport class for C#. There's an ASP.NET MVC example at the bottom.

https://github.com/jitbit/CsvExport

It takes care about line-breaks, commas, escaping quotes, MS Excel compatibilty... Just add one short .cs file to your project and you're good to go.

(disclaimer: I'm one of the contributors)

Solution 3

Here is a CSV action result I wrote that takes a DataTable and converts it into CSV. You can return this from your view and it will prompt the user to download the file. You should be able to convert this easily into a List compatible form or even just put your list into a DataTable.

using System;
using System.Text;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Data;

namespace Detectent.Analyze.ActionResults
{
    public class CSVResult : ActionResult
    {
        /// <summary>
        /// Converts the columns and rows from a data table into an Microsoft Excel compatible CSV file.
        /// </summary>
        /// <param name="dataTable"></param>
        /// <param name="fileName">The full file name including the extension.</param>
        public CSVResult(DataTable dataTable, string fileName)
        {
            Table = dataTable;
            FileName = fileName;
        }

        public string FileName { get; protected set; }
        public DataTable Table { get; protected set; }




        public override void ExecuteResult(ControllerContext context)
        {
            StringBuilder csv = new StringBuilder(10 * Table.Rows.Count * Table.Columns.Count);

            for (int c = 0; c < Table.Columns.Count; c++)
            {
                if (c > 0)
                    csv.Append(",");
                DataColumn dc = Table.Columns[c];
                string columnTitleCleaned = CleanCSVString(dc.ColumnName);
                csv.Append(columnTitleCleaned);
            }
            csv.Append(Environment.NewLine);
            foreach (DataRow dr in Table.Rows)
            {
                StringBuilder csvRow = new StringBuilder();
                for(int c = 0; c < Table.Columns.Count; c++)
                {
                    if(c != 0)
                        csvRow.Append(",");

                    object columnValue = dr[c];
                    if (columnValue == null)
                        csvRow.Append("");
                    else
                    {
                        string columnStringValue = columnValue.ToString();


                        string cleanedColumnValue = CleanCSVString(columnStringValue);

                        if (columnValue.GetType() == typeof(string) && !columnStringValue.Contains(","))
                        {
                            cleanedColumnValue = "=" + cleanedColumnValue; // Prevents a number stored in a string from being shown as 8888E+24 in Excel. Example use is the AccountNum field in CI that looks like a number but is really a string.
                        }
                        csvRow.Append(cleanedColumnValue);
                    }
                }
                csv.AppendLine(csvRow.ToString());
            }

            HttpResponseBase response = context.HttpContext.Response;
            response.ContentType = "text/csv";
            response.AppendHeader("Content-Disposition", "attachment;filename=" + this.FileName);
            response.Write(csv.ToString());
        }

        protected string CleanCSVString(string input)
        {
            string output = "\"" + input.Replace("\"", "\"\"").Replace("\r\n", " ").Replace("\r", " ").Replace("\n", "") + "\"";
            return output;
        }
    }
}

Solution 4

A comment about Will's answer, you might want to replace HttpContext.Current.Response.End(); with HttpContext.Current.ApplicationInstance.CompleteRequest(); The reason is that Response.End() throws a System.Threading.ThreadAbortException. It aborts a thread. If you have an exception logger, it will be littered with ThreadAbortExceptions, which in this case is expected behavior.

Intuitively, sending a CSV file to the browser should not raise an exception.

See here for more Is Response.End() considered harmful?

Solution 5

How to write to a file (easy search in Google) ... 1st Search Result

As far as creation of the file each time a user accesses the page ... each access will act on it's own behalf. You business case will dictate the behavior.

Case 1 - same file but does not change (this type of case can have multiple ways of being defined)

• You would have logic that created the file when needed and only access the file if generation is not needed.

Case 2 - each user needs to generate their own file

• You would decide how you identify each user, create a file for each user and access the file they are supposed to see ... this can easily merge with Case 1. Then you delete the file after serving the content or not if it requires persistence.

Case 3 - same file but generation required for each access

• Use Case 2, this will cause a generation each time but clean up once accessed.

View more solutions

Comments

[TheGeekYouNeed]

Is the CSV file going to always be the same for all users?

[Soader03]

Thanks a lot. Helped me with the logic of the csv file and download from the browser.

[deniz]

Use Response.End() with caution. More info: stackoverflow.com/questions/1087777/…

[LikePod]

i personally want to thank you for your share and this great work.