?
Solved

export to excel some values have commas

Posted on 2009-04-28
8
Medium Priority
?
421 Views
Last Modified: 2013-12-17
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?
0
Comment
Question by:jackjohnson44
8 Comments
 
LVL 15

Expert Comment

by:oobayly
ID: 24253934
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
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 24253953
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
 

Author Comment

by:jackjohnson44
ID: 24253982
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 14

Expert Comment

by:GiftsonDJohn
ID: 24254211
If you are able to create a table then you can directly send the data to excel without even creating a csv format.
0
 

Author Comment

by:jackjohnson44
ID: 24254263
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
 
LVL 14

Expert Comment

by:GiftsonDJohn
ID: 24254377
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
 
LVL 1

Expert Comment

by:MrPhenix
ID: 24254449
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
 
LVL 37

Accepted Solution

by:
gregoryyoung earned 2000 total points
ID: 24255162
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Integration Management Part 2
Loops Section Overview
Suggested Courses
Course of the Month13 days, 19 hours left to enroll

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question