Exporting database contents to Excel but missing leading zeroes???

[Burton S.]

I have the following script working well for database exports to Excell.
However, all my zip codes are missing the leading zeros (probably other
fields too). What do I need to change in this code to get Excel to accept &
display leading "0"'s? Thanks!

<%@ Language=VBScript %>
<%
dim Cn,Rs
set Cn=server.createobject("ADODB.connection")
set Rs=server.createobject("ADODB.recordset")
Cn.open "provider=microsoft.jet.oledb.4.0;data source=" &
server.mappath("database/seminars.mdb")
Rs.open "select * from Table1",Cn,1,3
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader "Content-Disposition", "attachment;
filename=LA_Seminars.xls"
if Rs.eof <> true then
response.write "<table border=1>"
response.write "<tr><td>" & "#" & "</td><td>" & "First Name" & "</td><td>" &
"Last Name" & "</td><td>" & "Title" & "</td><td>" & "Company" & "</td><td>"
& "Address" & "</td><td>" & "City" & "</td><td>" & "State" & "</td><td>" &
"Zip Code" & "</td><td>" & "Phone" & "</td><td>" & "Email Address" &
"</td></tr>"
while not Rs.eof
response.write "<tr><td>" & Rs.fields("ID") & "</td><td>" &
Rs.fields("fname") & "</td><td>" & Rs.fields("lname") & "</td><td>" &
Rs.fields("title") & "</td><td>" & Rs.fields("company") & "</td><td>" &
Rs.fields("address") & "</td><td>" & Rs.fields("city") & "</td><td>" &
Rs.fields("state") & "</td><td>" & Rs.fields("zip") & "</td><td>" &
Rs.fields("phone") & "</td><td>" & Rs.fields("email") & "</td></tr>"
Rs.movenext
wend
response.write "</table>"
end if
set rs=nothing
Cn.close
%>

[nisav19]

the only way i was able to find this is to apply an excel forula to format the
nubmer as text, which will keep the leading zeros

=TEXT(0,"<%rs.fields("zip")%>")

this will by default alight the text to the left, unlike the numbers that get
aligned to the right. so if that makes a difference to you just add an
allignment into your html.

post back if this works for you or if you found a better way of doing this

[Joe Makowiec]

On 18 May 2005 in macromedia.dreamweaver.appdev, Burton S. wrote:

> I have the following script working well for database exports to
> Excell. However, all my zip codes are missing the leading zeros
> (probably other fields too). What do I need to change in this code
> to get Excel to accept & display leading "0"'s? Thanks!

> & "</td><td>" & Rs.fields("state") & "</td><td>" & Rs.fields("zip")
> & "</td><td>" &

Well, the ZIP field does look a whole lot like a number...

Try one or both of two things:
- cast the zip as a string:
CStr(Rs.fields("zip"))
- surround it with quotation marks:
.... & '</td><td>"' & Rs.fields("zip") & '"</td><td>' & ...

Make careful note of the location of single and double quotes. I don't
know if either of these will work, but try them.

What is the datatype for the 'ZIP' field in your database? Is it
Text/String, or some sort of Numeric? If it's Numeric, change it to
String. The only time you want to use a numeric datatype is for data
you'll be doing arithmetic on, and you'll never do that with a ZIP code.

--
Joe Makowiec
[url]http://makowiec.net/[/url]
Email: [url]http://makowiec.net/email.php[/url]

[Unregistered]

Exporting data into excel has sometime some issues. Especially when we want to export numbers. In my case I had one field after export was missing leading zeros. Idea for this is to convert that field into Text and then export it.

Following is the code: Hope this helps.

Protected Sub btnExportToExcel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExportToExcel.Click

Dim dtRecRatesData As DataTable = dsRecRates.Tables(0)
Dim strFileName As String = ddlDProject.SelectedItem.Value.ToString & "_RecommendedRatesData" & ".xls"

'Export Rec. Rates data into an Excel
If Not dtRecRatesData Is Nothing Then

Try
Dim style As String = "<style> .text { mso-number-format:\@; } </style> "
Response.Clear()
Response.AddHeader("content-disposition", "attachment;filename=" + strFileName + "")
Response.ContentType = "application/vnd.ms-excel"
Response.Charset = ""

Dim textWriter As New IO.StringWriter()
Dim htmlWriter As New System.Web.UI.HtmlTextWriter(textWriter)

Dim gvRecRatesData As New DataGrid
gvRecRatesData.HeaderStyle.BackColor = Drawing.Color.LightGray
gvRecRatesData.HeaderStyle.ForeColor = Drawing.Color.Blue
gvRecRatesData.GridLines = GridLines.Both
gvRecRatesData.HeaderStyle.Wrap = True
gvRecRatesData.HeaderStyle.Font.Bold = False
gvRecRatesData.ItemStyle.VerticalAlign = VerticalAlign.Top

gvRecRatesData.AllowPaging = False
gvRecRatesData.DataSource = dtRecRatesData 'constructDataSet(dtRecRatesData)
gvRecRatesData.DataBind()

'First column which has API12 value, when we were exporting data into excel, it was missing leading zeros.
'So need to change the cell type to Text so that it will exported properly with leading zeros.
For cnt As Integer = 0 To gvRecRatesData.Items.Count - 1
gvRecRatesData.Items(cnt).Cells(0).Attributes.Add( "class", "text")
Next

Dim control As Control
control = gvRecRatesData
Me.VerifyRenderingInServerForm(control)

gvRecRatesData.RenderControl(htmlWriter)
Response.Write(style)
Response.Write(textWriter.ToString())
Response.End()

Catch ex As Exception
Throw ex
End Try

End If
End Sub