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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.