export to excel some values have commas

I had an asp.net page setup that would export to a csv file.  Unfortunately some of the values have commas, so that won't work.

Is there a way to export straight to excel incase there are commas already?
jackjohnson44Asked:
Who is Participating?
 
gregoryyoungCommented:
http://tools.ietf.org/html/rfc4180

   6.  Fields containing line breaks (CRLF), double quotes, and commas
       should be enclosed in double-quotes.  For example:

       "aaa","b CRLF
       bb","ccc" CRLF
       zzz,yyy,xxx



   7.  If double-quotes are used to enclose fields, then a double-quote
       appearing inside a field must be escaped by preceding it with
       another double quote.  For example:

       "aaa","b""bb","ccc"



0
 
oobaylyCommented:
When exporting to CSV, I tend to wrap any DateTime and string values in quotes. Either that or I generate a Tab delimited file.
0
 
käµfm³d 👽Commented:
If the values already have commas, why not just write a regular text file (i.e. StreamWriter / FileStream) and give the file a .csv extension--no need for Excel.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
jackjohnson44Author Commented:
The requirement is to export to excel.  It is actually a page where you can put in a sql string and it should output the content in excel.  I don't know what fields will be coming out.  Is there a way to do it without using commas?
0
 
GiftsonDJohnCommented:
If you are able to create a table then you can directly send the data to excel without even creating a csv format.
0
 
jackjohnson44Author Commented:
Thanks, I agree.  I was wondering how to do that.  I have already ruled out using csv since my columns have commas in them.
0
 
GiftsonDJohnCommented:
Do like this.

I am assuming you have all the data in a DataTable
        DataTable dt = <load the data into datatable>
 
        StringWriter stringWriter = new StringWriter();
 
        using (HtmlTextWriter writer = new HtmlTextWriter(stringWriter))
        {
            writer.RenderBeginTag(HtmlTextWriterTag.Table);
 
            writer.RenderBeginTag(HtmlTextWriterTag.Tr);
            foreach (DataColumn dc in dt.Columns)
            {
                writer.RenderBeginTag(HtmlTextWriterTag.Th);
                writer.Write(dc.ColumnName);
                writer.RenderEndTag();
            }
            writer.RenderEndTag();
 
            foreach (DataRow dr in dt.Rows)
            {
                writer.RenderBeginTag(HtmlTextWriterTag.Tr);
 
                foreach (DataColumn dc in dt.Columns)
                {
                    writer.RenderBeginTag(HtmlTextWriterTag.Td);
                    writer.Write(dr[dc]);
                    writer.RenderEndTag();
                }
 
                writer.RenderEndTag();
            }
        }
 
        Response.ClearContent();
        Response.AddHeader("content-disposition", "attachment; filename=outputfile.xls");
        Response.ContentType = "application/ms-excel";
 
        Response.Write(stringWriter.ToString());
        Response.End(); 

Open in new window

0
 
MrPhenixCommented:
Or why not use ';' as separator instead of ',' that would also work and it will cause less change.

Both ; and , is accepted as csv separator.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.