Export to Excel from SQL Server - Wrong Data Type - Classic ASP

Posted on 2014-08-12
1 Endorsement
Last Modified: 2014-08-12
Hi Experts,
I'm trying to export data from my SQL Server database to an Excel file. I am using Classic ASP.
The problem is with the Emp_Id column. The data type is Varchar(6) . Some Emp_Id strart with a zero. After the export the 0 disappears from the beginning of these employee numbers. How could I prevent this?
 'Build & Execute Your SQL Command

 strSQL = "SELECT * FROM Workgroups order by Last_Name"
 Set rs = Conn.Execute(strSQL)

 'Set the HTTP Header
 Response.ContentType = "application/"
 Response.AddHeader "Content-Disposition", "filename=""AllEmployees.xls"""

 'Output Table %>
<table border="1" cellspacing="0" cellpadding="5">
<tr bgcolor="#000080">
<th align="center"><font color="#ffffff"><b>Emp#</b></font></th>
<th align="center"><font color="#ffffff"><b>Last Name</b></font></th>
<th align="center"><font color="#ffffff"><b>First Name</b></font></th>
<th align="center"><font color="#ffffff"><b>Work Area</font></th>
<th align="center"><font color="#ffffff"><b>Assignment</font></th>
<th align="center"><font color="#ffffff"><b>Supervisor</font></th>
  <% Do While Not rs.EOF %>
 if color = "#fffff0" then
 color = "#D6E3E6"
 color = "#fffff0"
 end if
<tr style="background-color:<%=color%>">
<td align="center"><%=rs("Emp_Id")%></td>
<td align="left" nowrap><%=rs("Last_Name")%></td>
<td align="left"><%=rs("First_Name")%></td>
<td align="center"><%=rs("Work_Area")%></td>
<td align="center"><%=rs("Assignment")%></td>
<td align="left"><%=rs("Supervisor")%></td>

  <% rs.MoveNext : Loop %>

 'Stop Processing Page Now

Open in new window

Please take a look at my code; I would appreciate your help.
Question by:romsom
    LVL 52

    Expert Comment

    by:Scott Fell, EE MVE
    For the field with zero padding.  


    Author Comment

    Hi Padas,
    I tried it but unfortunately the zeroes still disappear after the export.
    First I thought it was because of the browser's history, and I went to a different computer. I didn't work either.
    LVL 52

    Expert Comment

    by:Scott Fell, EE MVE
    In other words, the number could be 10, 11, 12, 100.  But you want 000010, 000011,000012,000100?

    The code I provided should be adding 6 zeros to the left, then only giving you the right 6.

    What about
    LVL 52

    Accepted Solution

    Ah, you mean the zeros are being lost in excel? or the screen.

    Try this, add a space to the right and left.  

    <%=" "&cstr(right("000000"&rs("Emp_Id"),6))&" "%>

    or try a single quote to the left.
    LVL 32

    Expert Comment

    by:Big Monty
    it wasn't a duplicate comment, it may not work because the " " may be ignored, that's why I suggested adding &nbsp;.

    don't give me points for it, i was just mentioning it

    Author Comment

    Thank you guys.
    Both the single quote and the space trick worked.
    I actually used this code:
    <td align="center">&nbsp;<%=rs("Emp_Id")%></td>
    LVL 52

    Expert Comment

    by:Scott Fell, EE MVE
    In excel, the single quote is what you use to designate text and not a number.  If that works, that would probably be the cleanest route.  If you are going to add spaces, I would use a space at both ends.  The reason is because you want it centered.  With one space, you will not have it perfectly centered.  With the single quote trick or space on either end, it will be centered.  

    Glad it worked.

    Author Comment

    First I tried hard coding the zero, like this:
    <td align="center">0<%=rs("Emp_Id")%></td>
    After the export the 0 disappeared. Adding the spaces didn't help either.
    <%=" "&cstr(right("000000"&rs("Emp_Id"),6))&" "%>
    It worked only after I typed &nbsp;
    LVL 52

    Expert Comment

    by:Scott Fell, EE MVE
    Yea, hard coding the zero would still make it a number for excel.  That's why I thought of the space.  Did the single quote work?  It sounds like the actual number was in the emp_id and you didn't need the leading zeros.

    Author Comment

    The single quote also worked. Yes, the employee numbers I had problems with were like these:
    Thank you very much again. Now I have a different question, I'll be posting it now.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
    Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    732 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now