Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ASP export to excel problem - excel is changing characters

Posted on 2007-04-10
6
Medium Priority
?
283 Views
Last Modified: 2010-08-05
hi,

i'm using the following script to export an asp recordset as an excel file :

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1255"%>
      <%
       Response.ContentType = "application/vnd.ms-excel"
      Response.AddHeader "Content-Disposition", "attachment;filename=reg.xls"
      %>
<meta http-equiv="Content-Type" content="application/vnd.ms-excel charset=windows-1255">
<% set objconn=server.createobject("adodb.connection")
objconn.Open "dsn=xxx"
set objrs=objconn.execute("SELECT * FROM registration")
%>
     <TABLE BORDER=1>
    <TR>
    <%
   '  Loop through each Field, printing out the Field Names
    For i = 0 to objrs.fields.count - 1
    %>
    <TD><%
varField = objrs(i).name
' varField =  replace(varField,"reg_","")
'varField =  replace(varField,"mailing_","")
Response.Write(varField)
 %></TD>
    <% next %>
</TR>
    <%
     '  Loop through rows, displaying each field
     while not objrs.eof
    %>
   <TR>
    <% For i = 0 to objrs.fields.count - 1
    %>
    <TD VALIGN=TOP><% = objrs(i) %></TD>
    <% Next %>
 </TR>
    <%
    objrs.MoveNext
     wend

    objrs.Close
objconn.close
    %>

in general this works quite well except i have now come across a problem.... one of the fields in the DB is "card number" which is a credit card number.... in the access db the data is taken from this field is defined as text and displays the info correctly (eg 4670210100354419)...

when this colum is exported to the excel file it is displayed something like this : 4.56345E+15 .... when iselect this number it displays the full number in the formula box at the top but this number is incorrect... in the example where the credit card number recorded in the access db is 4670210100354419 the number shown in the excel would be 4670210100354410 .... as you see the final number is changed to a zero...

does anyone have any suggestions as to how not to export to excel without these problems

many thanks

dog
0
Comment
Question by:dog_star
[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
  • 4
  • 2
6 Comments
 
LVL 7

Expert Comment

by:TelnetServices
ID: 18881280
There is no problem with your code -this is an excel oddity...
interestingly, excel does this even if cell type is TEXT - it tries to convert to a number, and this is on the edge of excels handling ability.  

The solution is to force excel to look at it as text.  Put some spaces in the numbers (should be ok cos it's a cc number?) eg 4670 2101 0035 4419

This will work fine... as excel is forced to treat is as text
0
 
LVL 7

Accepted Solution

by:
TelnetServices earned 1000 total points
ID: 18881288
should have mentioned - you can prove this in excel itself - set cell type to "number" "o decimal places" and paste in 4670210100354419 - you will see it comes out as 4670210100354410.

Paste in 4670 2101 0035 4419 - this will work ( as will  #4670210100354419 - but non space characters may affect your data)
0
 

Author Comment

by:dog_star
ID: 18881690
thanks very much for your answer... not exactly the one i was hoping for, but certainly the correct one :-)

i'm going to try playing round with my export code to try adding the "#" to the beginning of the number as i'd rather not have to go through all my admin pages etc and break the CC number into 4 fields...

again, thanks for your help

dog
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 7

Expert Comment

by:TelnetServices
ID: 18881734
To your export code why not use MID to split it up? (final 'right' to catch longer numbers)

strCCnumExport=MID(strccnum,1,4) & " " & MID(strccnum,5,4) & " " & MID(strccnum,9,4) & " " & RIGHT(strccnum,LEN(strccnum)-12)

This is of course, if you want to use spaces - your preference!

You're Welcome! thanx for points
0
 

Author Comment

by:dog_star
ID: 18881863
thats a very neat solution, far better than manually splitting the cc numbers....

thanks for the code....
0
 
LVL 7

Expert Comment

by:TelnetServices
ID: 18881917
np :)
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

688 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