dog_star
asked on
ASP export to excel problem - excel is changing characters
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.x ls"
%>
<meta http-equiv="Content-Type" content="application/vnd.m s-excel charset=windows-1255">
<% set objconn=server.createobjec t("adodb.c onnection" )
objconn.Open "dsn=xxx"
set objrs=objconn.execute("SEL ECT * 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
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.x
%>
<meta http-equiv="Content-Type" content="application/vnd.m
<% set objconn=server.createobjec
objconn.Open "dsn=xxx"
set objrs=objconn.execute("SEL
%>
<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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
To your export code why not use MID to split it up? (final 'right' to catch longer numbers)
strCCnumExport=MID(strccnu m,1,4) & " " & MID(strccnum,5,4) & " " & MID(strccnum,9,4) & " " & RIGHT(strccnum,LEN(strccnu m)-12)
This is of course, if you want to use spaces - your preference!
You're Welcome! thanx for points
strCCnumExport=MID(strccnu
This is of course, if you want to use spaces - your preference!
You're Welcome! thanx for points
ASKER
thats a very neat solution, far better than manually splitting the cc numbers....
thanks for the code....
thanks for the code....
np :)
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