Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
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
?
189 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
  • 5
7 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
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

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

This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
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…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…
Suggested Courses

564 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