?
Solved

When exporting to an Excel csv file (from a query), how can you make the excel sheet not be in a "general" type cast.

Posted on 2005-02-25
10
Medium Priority
?
185 Views
Last Modified: 2013-12-24

I'm using Coldfusion to export a query to an Excel CSV file.  However, I have some large numbers and Excel is converting them to the scientific notation when it opens the CSV file (buty they're really text).    I've placed (2) "" in front of the outputed variabe and it keeps them as text (which is what I want) but it also seems to be wanting to have them behind the variable too, but then they show up in the Excel file (not wanted).

i.e.



<cfheader name="Content-disposition" value="inline; filename=Database2.csv" >
<cfcontent type="application/ms-excel">

<cfoutput query="qryList3">#text1#,#text2#Chr(13)#</cfoutput>

Another idea might be to have call a SQL stored procedure to dump this entire table to Excel.xls file (but not sure how to do that)

 
0
Comment
Question by:tap1732
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
10 Comments
 
LVL 18

Expert Comment

by:Plucka
ID: 13409158
Hi tap1732,

Quote text fields, don't quote numeric.

Not sure what you mean by

" but it also seems to be wanting to have them behind the variable too"

Regards
Plucka
0
 
LVL 18

Expert Comment

by:Plucka
ID: 13409167
Hi tap1732,

<cfheader name="Content-Disposition" value="filename=Employees.xls"> --->
<cfoutput>
    <table cols="4">
        <tr>
            <td>#text1#</td><td>#text2#</td><td>#text3#</td>
        </tr>
    </table>
</cfoutput>

Regards
Plucka
0
 
LVL 18

Expert Comment

by:Plucka
ID: 13409177
Hi tap1732,

Sorry again, missed the cfcontent line. I really should test before posting, this one works I tested it :)

<cfheader name="Content-Disposition" value="filename=Employees.xls">
<cfcontent type="application/msexcel">
<cfoutput>
    <table cols="3">
        <tr>
            <td>one</td><td>two</td><td>33</td>
        </tr>
    </table>
</cfoutput>

Regards
Plucka
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 18

Expert Comment

by:Plucka
ID: 13409186
Hi tap1732,

This method is better as you can do things like, borders, specify widths and formula's.

<cfheader name="Content-Disposition" value="filename=Employees.xls">
<cfcontent type="application/msexcel">
<cfoutput>
    <table cols="5" border="1">
        <tr>
            <td>one</td><td>two</td><td>33</td><td>10</td><td width="100">=c1*d1</td>
        </tr>
    </table>
</cfoutput>

Regards
Plucka
0
 

Author Comment

by:tap1732
ID: 13416916
Thanks for the Hlep Plucka,

Although I'm still having the same problem:

Here's how my data is being displayed, I've tried almost everything to get rid of the scientific notation, even using the "tostring()" function.

So here is how it's displaying.
______________________________

65-6371-3388            65-9830-3839      65-6371-3177      664-3165 Knet
61298704250            419559960      61298704352      
7114062535            1704567277      7114063619      
4.90613E+11            4.9017E+12      4.90613E+11      
7114062708            1704567284      7114063619      
40778231            1704567921      40778299      
7114065691            1704567276      7115282046      
71919141951            1704567351      7114065869      
07 11/4 06-27 08            01 70/4 56 72 84      07 11/4 06-36 19      
8008229390                  7114063566      
4.90711E+12            4.9017E+12      4.90711E+12      
4.97114E+11                  4.97114E+11      
7114065628                  7114065166      
4.90711E+12                  4.90548E+12      4.90548E+12
2111596666                  2111596667      
3613679117                  3613679113      
81 3 5540 2632                  81 3 5540 2271      
81 3 5540 2698                  81 3 5540 2271      
01442 845564            0860 501445      01634 686910      
31 0 347 363 892                  31 0 347 363 902      31 0 651 291 993
            8.86932E+11      8.86229E+11      
27194111                  27194159      
01-49-63-41-23            06-74-68-73-63      01-49-63-41-31      
_________________________



Here's the actual data:

65-6371-3388            65-9830-3839      65-6371-3177      664-3165 Knet
61298704250            419559960      61298704352      
7114062535            1704567277      7114063619      
490612692451            4901704567243      490612692462      
7114062708            1704567284      7114063619      
40778231            1704567921      40778299      
7114065691            1704567276      7115282046      
71919141951            1704567351      7114065869      
07 11/4 06-27 08            01 70/4 56 72 84      07 11/4 06-36 19      
8008229390                  7114063566      
4907114062399            4901704567935      4907114065166      
497114065325                  497114065166      
7114065628                  7114065166      
4907114065326                  4905483749871      4905483749870
2111596666                  2111596667      
3613679117                  3613679113      
81 3 5540 2632                  81 3 5540 2271      
81 3 5540 2698                  81 3 5540 2271      
01442 845564            0860 501445      01634 686910      
31 0 347 363 892                  31 0 347 363 902      31 0 651 291 993
            886932352445      886228938124      
27194111                  27194159      
01-49-63-41-23            06-74-68-73-63      01-49-63-41-31      


________________
<cfheader name="Content-disposition" value="attachment; filename=Nextscan Database2.csv" >
<cfcontent type="application/ms-excel">phone, phone_ext, mobile_phone, fax, alt_phone

<cfoutput query="qryList3">#phone#,#phone_ext#,#mobile_phone#,#fax#,#alt_phone##Chr(13)#</cfoutput>


Do you have any toher ideas that might help?

Please let me know.

Thanks

tap1732
0
 
LVL 18

Expert Comment

by:Plucka
ID: 13416946
I just tried this and don't have any problems.

Try this example.

<cfheader name="Content-Disposition" value="filename=Employees.xls">
<cfcontent type="application/msexcel">
<cfoutput>
    <table cols="3" border="1">
        <tr>
            <td>61298704250</td><td>419559960</td><td>61298704352</td>
        </tr>
    </table>
</cfoutput>
0
 
LVL 7

Accepted Solution

by:
INSDivision6 earned 1000 total points
ID: 13417582
This is just a matter of formatting (how Excel treats the values).  Your values still correct.  CSV file format doesn't allow to specify the exact formatting.  Therefore, Excel does it on its own.  You can specify your own formatting options using "Format" menu and saving result as XLS file to preserve your display options.
0

Featured Post

Plug and play, no additional software required!

The ATEN UE3310 USB3.1 Gen1 Extender Cable allows users to extend the distance between the computer and USB devices up to 10 m (33 ft). The UE3310 is a high-quality, cost-effective solution for professional environments such as hospitals, factories and business facilities.

Question has a verified solution.

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

Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
Lease-to-own eliminates the expenditure of hardware replacement and allows you to pay off the server over time. Usually, this is much cheaper than leasing servers. Think of lease-to-own as credit without interest.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

770 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