Exporting a DataTable to a CSV file

I think I just need a fresh set of eyes to look at the following code.  I've got a page that fills a DataTable with user information from our Active Directory.  I want to have a button that dumps all that information to a .csv file for a user to download.  Unfortunately it doesn't give me anything but the "Open/Save" dialog: clicking either button just gives me an overwhelming sense of nothingness and disappointment.  I'd welcome an obvious error at this point...

Regarding the code, "ds" is a globally-defined DataSet.  The function "DataTableToCSV" just loops through the given table to create the contents of a CSV string; the "true" argument indicates that I want a header row.  

If I comment out the Content-Disposition header, Excel will open but display each line of the CSV in the first cell of the worksheet.  Alternatively, I get the raw data in the browser window if I comment out both the Content-Type and Content-Disposition headers.  I can manually copy-and-paste this raw data into a .csv file and Excel will recognize it, but that defeats the purpose of having the button.  I could just as easily copy-and-paste the GridView into Excel.
protected void CsvButton_Click(object sender, EventArgs e)
    {
        DataTable dt = ds.Tables["users"];
        String csv = DataTableToCSV(dt, true);
 
        // make sure we only send the CSV to the browser
        Response.Clear();
        Response.ClearHeaders();
        Response.ContentType = "application/vnd.ms-excel";
        Response.AddHeader("Content-Disposition", "attachment; filename=phones.csv; size=" + csv.Length);
        Response.Write(csv);
        Response.End();
    }

Open in new window

LVL 2
kevincaseyAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
kevincaseyConnect With a Mentor Author Commented:
Below is the ProcessRequest method that I ended up using.  It has all the logic from my original button handler.  For brevity, I've removed the testing logic that makes sure the request is service-able.
    public void ProcessRequest (HttpContext context) {
        int wc = Int32.Parse(Request["wc"]);
        string formattedFilter = String.Format(filter, formats[wc]);;
        
        Request = context.Request;
        Response = context.Response;
 
 
        // get all users with a phone number
        createTable();
        fillTable();
        
        // limit it to just users near the specified machine, sorted by name
        String csv = CSV4Xerox(FilterSortData(ds.Tables["users"], formattedFilter, sort), WorkCenterIPs[wc]);
        
        // send it all back to the user
        Response.ClearContent();
        Response.ClearHeaders();
        Response.ContentType = "application/vnd.ms-excel";
        Response.AddHeader("Content-Disposition", String.Format(contentDisposition, wc, csv.Length));
        Response.Write(csv);
        Response.End();
    }

Open in new window

0
 
skiltzCommented:

protected void CsvButton_Click(object sender, EventArgs e)
{
DataTable dt = ds.Tables["users"];
 
Response.Clear();
             Response.Buffer= true;
             Response.ContentType = "application/vnd.ms-excel";
             Response.AddHeader("Content-Disposition", "inline;filename=phones.csv.xls"); Response.Charset = "";
             this.EnableViewState = false;
             ProduceCSV(dt, Response.Output, true);
}
 
    public static void ProduceCSV(DataTable dt, System.IO.StreamWriter file, bool WriteHeader)
    {
    if(WriteHeader)
    {
    string[] arr = new String[dt.Columns.Count];
    for(int i = 0; i<dt.Columns.Count; i++)
    {
    arr[i] = dt.Columns[i].ColumnName;
    arr[i] = GetWriteableValue(arr[i]);
    }
 
    file.WriteLine(string.Join(",", arr));
    }
 
    for(int j = 0; j<dt.Rows.Count; j++)
    {
    string[] dataArr = new String[dt.Columns.Count];
    for(int i = 0; i<dt.Columns.Count; i++)
    {
    object o = dt.Rows[j][i];
    dataArr[i] = GetWriteableValue(o);
    }
    file.WriteLine(string.Join(",", dataArr));
    }
    }
 
    public static string GetWriteableValue(object o)
    {
    if(o==null || o == Convert.DBNull)
    return "";
    else if(o.ToString().IndexOf(",")==-1)
    return o.ToString();
    else
    return "\"" + o.ToString() + "\"";
 
       }
    } 
            }
       

Open in new window

0
 
Daniel ReynoldsSoftware Applications Developer / IntegratorCommented:
If you use the following line, does it make a difference?
Response.AddHeader("Content-Disposition", "attachment; filename=phones.csv; ";
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
kevincaseyAuthor Commented:
Skiltz: I set the buffering, view state, charset as specified and changed the Content-Disposition to "inline" instead of "attachment" and it's closer to working.  The "File Download" dialog opens up with the correct filename and size.  

If I click "Open", then Excel warns me that the file is in a different format than specified by the extension and reports the filename as being "phones.aspx".  

If I click "Save", the dialog goes away and nothing gets saved.

Any thoughts?
file-download.png
microsoft-office-excel.png
0
 
kevincaseyAuthor Commented:
Anybody - any ideas?
0
 
kevincaseyAuthor Commented:
So I was finally able to revisit this problem yesterday.  I tried pulling my original code into a generic handler (ASHX) file, and turning the CsvButton into a LinkButton.  

Guess what?  Everything worked exactly as I wanted without changing anything.
0
All Courses

From novice to tech pro — start learning today.