472,961 Members | 1,747 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,961 software developers and data experts.

Leading Zeros truncated when exporting from asp page to excel

9
Hi,

I have the following code where 'recs' is a record set.

For i=0 to recs.Fields.Count - 1
if i = 0 then
pindnt = string(itmlvl*2," ")
response.write pindnt & recs(i)
else
Response.write recs(i)
end if

Response.write CHR(09)
Next

Using this part of code in my application I am exporting data from oracle database to excel, but leading zeros for one column "CURRENT_REVISION" are truncated in the excel sheet.

I am able to see the leading zeros only if I add a string(like ' - single quotes). Please see the below code where I am adding a string.But I don't want any string to the CURRENT_REVISION value in the excel sheet.

For i=0 to recs.Fields.Count - 1
if i = 0 then
pindnt = string(itmlvl*2," ")
response.write pindnt & recs(i)
else
if recs.Fields(i).Name = "CURRENT_REVISION" then
Response.Write "'" & recs(i) ' this is where I concatinated a string
else
Response.write recs(i)
end if
end if
Response.write CHR(09)
Next


My code does not contain any HTML or Javascript code. Using the below code I am exporting to Excel

<%Response.buffer = True
Server.ScriptTimeout = 1800
Response.ContentType ="application/x-msexcel"%>

This seems to be very small issue but eating a lot of my time.
Can anyone help me to resolve this issue.
Dec 3 '07 #1
7 12155
improvcornartist
303 Expert 100+
Excel is probably considering the fields to be numbers, so it removes the leading zero. When you add an extra mark, it then makes the field text, so the zero stays. You could possibly use a decimal at the end of the number (if the field has two decimals, it will be read as text). You may also be able to create your data as a CSV instead of MSExcel, then format the spreadsheet when it is opened in Excel so it will display the version data as text. Otherwise, I'm not really sure how to get around the issue.
Dec 4 '07 #2
leninv
9
Excel is probably considering the fields to be numbers, so it removes the leading zero. When you add an extra mark, it then makes the field text, so the zero stays. You could possibly use a decimal at the end of the number (if the field has two decimals, it will be read as text). You may also be able to create your data as a CSV instead of MSExcel, then format the spreadsheet when it is opened in Excel so it will display the version data as text. Otherwise, I'm not really sure how to get around the issue.

Thank you for the reply. I tried using decimal at the end of the number, but that doesn't work. If I am the only user then I can format the spreadsheet by using CSV, but this will be opened by many users, so I cannot tell every one to format the spreadsheet, so I cannot use CSV instead of MSExcel. I am trying for some alternative using reverse engineering approach. Any how I would like to thank you once again. Any suggestion is always welcome.
Dec 5 '07 #3
leninv
9
Thank you for the reply. I tried using decimal at the end of the number, but that doesn't work. If I am the only user then I can format the spreadsheet by using CSV, but this will be opened by many users, so I cannot tell every one to format the spreadsheet, so I cannot use CSV instead of MSExcel. I am trying for some alternative using reverse engineering approach. Any how I would like to thank you once again. Any suggestion is always welcome.

Hi

I am able to export the content to Excel with Leading zeros now using reverse engineering approach, but I am facing another probelm. The result is displayed on a blank page, I am not getting any cells displayed. I mean no spreadsheet is seen. I used the following statements for Excel

<%Response.buffer = True
Server.ScriptTimeout = 1800
Response.ContentType ="application/vnd.ms-excel"%>

Can you help me to sort out why this problem is coming.
Dec 5 '07 #4
jhardman
3,406 Expert 2GB
I don't know how you got it to work, but the correct way to format numbers as text in excel is to prepend an apostrophe (single quote mark ') to the beginning of the number. Otherwise Excel will always truncate leading zeroes.

Jared
Dec 5 '07 #5
leninv
9
I don't know how you got it to work, but the correct way to format numbers as text in excel is to prepend an apostrophe (single quote mark ') to the beginning of the number. Otherwise Excel will always truncate leading zeroes.

Jared
I tried this but the problem with this is, If I prepend a apostrophe to a value 003 it is showing the result '003. So apostrophe is seen in my result, which I should not get.
Dec 13 '07 #6
This one bit me recently and, finding no solution via Google, I developed my own. I thought I'd share my answer in case it's useful to others.

On columns which are known to be strings that will (or can) contain leading zeroes, do this:

string celltext = "08010203";
celltext = "=concatenate(\"" + celltext + "\")";

Since excel treats the result of a concatenation as a string, even if there was only one element in the concat list, this works. The result appears in excel with the leading zero and with no quotation marks. The obvious disadvantage is the increased number of bytes necessary to represent the data.
Mar 28 '08 #7
sorpigal : this works awesome .... i did it like this : I am working in ASP 1.1

drAddItem(2) = "=concatenate(""" + dtOriginal.Rows(i)(1).ToString() + """)"
or string celltext = "08010203";
celltext = "=concatenate(\"" + celltext + "\")";
Dec 9 '11 #8

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: yanir | last post by:
Hi I use reponse.contenttype = "application/vnd.ms-excel" So the browser will show the data in excel format, but for some fields I use leading zero's, which truncated by the browser, at this...
1
by: BARBARA FLOYD | last post by:
Hi, I have an ASP file which is opened in MS Excel. I have used the "formatNumber" function to set the number of decimals for variables which are displayed. however Excel cuts off any zeros at...
5
by: samik_tanik | last post by:
I need to export a datagrid to Excel. I could did this. But, also need to keep the leading zeros in the data. How can I acheive this? Any help would be appreciated. -- Thanking you in...
1
by: mmmgood1 | last post by:
Help, I'm linking an excel spreadsheet in access and I have datafields with leading zeros (01021). When the file is linked in access, I get a #num in the field with the leading zeros. The zeros...
6
by: Clint Stowers | last post by:
Using A2k Exporting a query to a CSV file. The problem is any text fields (i.e. 000345) lose any leading zeros. Exporting to an excel file this problem does not exist. Tried to create a...
1
by: kscdavefl | last post by:
When I export the contents of a datagrid to excel, the leading 0s of the first column are truncated. Is there a way to keep this from happening. I am using the following code: private void...
3
by: ineedahelp | last post by:
Can anyone help me...I am trying to output my access report to excel. One of my fields is formatted as text, but excel is dropping leading zeros...only on the records where all values are numeric. ...
6
by: JimmyKoolPantz | last post by:
Task: Customer wants a script of the data that was processed in a "CSV" file. Problem: Zip-Code leading zeros are dropped Basically we have a client that has requested a custom script for...
0
by: Monty | last post by:
Hi All, I am having a problem with leading zeros being stripped from fields in a CSV file when I bring them in using Jet/OleDB. In VB.Net/VS 2008, I am accessing a CSV file like so: sSQL =...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
tracyyun
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
2
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
4
NeoPa
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
3
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
1
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
3
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
0
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
2
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.