December 31, 2001 at 11:54 am
First, I apologize for this post that is not really related to SQL Server.
However, I have always had good responses on this forum, and this particular question has left me searching for answers everywhere.
I have an ASP page that downloads a CSV file of data.
In this data are some IDs that should be treated as Text but, when the file is opened in Excel, they are treated as numbers. Leading zeroes are dropped.
I have tried to enclose the IDs in double quotes, but the leading zeroes are still dropped.
I have tried starting each field with a single quote, in an attempt to alert Excel that the field is text, but the single quote appears in the cell with the actual value of the ID. That is unacceptable.
Is there any way to "trick" Excel into treating a numeric field as text - without displaying unwanted characters in the cell?
Thanks for your time
Matthew Mamet
December 31, 2001 at 4:15 pm
In Excel you can use a leading apostrophe to make it display as text, but it doesnt seem to work on import - the apostrophe shows up. Seems like there should be a way - just don't know what it is! Couple ideas for work arounds though:
1) Use Access. It lets you create a "schema" that controls the data type and then save it to re-use later. It's easy to go from Access to Excel, so this might not be hard to do.
2) Let Excel strip the leading zeros,then just format the cell to display them anyway. You could do this with a simple macro (or just do it yourself if it's not too often!).
Sorry nothing better to offer than that.
Andy
December 31, 2001 at 6:33 pm
I tried using a leading or ending space and that didn't produce results that were any different. Since you're doing string manipulation, you may try and convert it into a .htm document, formating as a table. If you want to keep your leading spaces, you'll need to use a style class in order to do so... When I had Excel export a formatted field to .htm, it created a class xl25 which it applied to the cells that needed to maintain the text settings. I have copied the class and created a sample HTML document.
Here's an example which should work:
<html>
<head>
<style>
<!--
.xl25
{mso-style-parent:style0;
mso-number-format:"\@";
white-space:normal;}
-->
</style>
</head>
<body>
<table>
<tr><td class=xl25>001</td><td>001</td></tr>
<tr><td class=xl25>002</td><td>002</td></tr>
</table>
</body>
</html>
The cells that are classed to xl25 keep the leading spaces. The other cells, though there are leading zeroes in the data, they are dropped by Excel.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
Edited by - bkelley on 12/31/2001 6:33:07 PM
K. Brian Kelley
@kbriankelley
January 2, 2002 at 9:06 pm
Here's a really slick control that'll do the trick and more!
January 3, 2002 at 2:34 pm
quote:
Here's a really slick control that'll do the trick and more!http://www.softartisans.com/softartisans/excelwriter.html
for 800 bucks per server, it should serve me coffee too . . .
Matthew Mamet
January 3, 2002 at 2:38 pm
January 4, 2002 at 11:09 am
The office web control, I believe, will instanciate an instance of Excel on the web server to create the object. That's not only resource intensive, but slow as well.
Let me know if they get that coffee thing worked out. I may upgrade to the full version. 😉
January 4, 2002 at 5:12 pm
It is resource intensive (relatively), but it works if you are not under a heavy load. It's also simple.
Steve Jones
January 4, 2002 at 6:32 pm
Deuce,
Are you sure it instantiates an instance of Excel? Reason I ask is that you can install Office Server Extensions/Office Web Components without installing Office. Been there, done that.
Also, it would go against Microsoft's very stringent warning about instantiating Office Automation server side, as per this article:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q257757
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
January 5, 2002 at 1:21 pm
Nope. I'm not sure. This is the quote from http://www.4guysfromrolla.com/webtech/022801-1.shtml that lead me to the conclusion:
(If you are wanting to create Excel spreadsheets and graphs without requiring Excel's presence on the Web server be sure to check out SoftArtisan's ExcelWriter component.)
Are you sure that installing OWC does not install Excel.exe?
January 5, 2002 at 3:25 pm
Just checked the web server OSE2K was installed upon. No Excel.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
July 17, 2002 at 9:54 pm
We did battle with this same problem and came up with the same results. Saving a file in comma or tab delimited format, and naming the file "[something].csv" results in an *Excel-readable* file -- but not Excel. Obvious, I know, but my main client has been calling them Excel files for years and telling their clients they are Excel files...and suddenly they discovered that leading zeros are dropped and wanted me to fix it! No can do. We can't control what Excel does when it opens a text file. They got the idea, and are changing their phrasing...while we search for better answers.
I also built an ActiveX component that fired up Excel on the server, and did various commands using the Excel object model, and got nice results -- but yeah, Microsoft's warning is a sound one. Using desktop software (even if just the OWC portion of it) on a web server is a bad idea. It just won't scale, and it doesn't promise not to lock up in interesting ways.
I'm wondering if we can harness the power of DTS though. Here's my thinking...
Even on a SQL Server machine that doesn't have Excel installed, I think we can make DTS scripts that output bona fide Excel files -- with leading zeros, datetime formats where needed, everything. And DTS scripts can be saved to VB code. And VB code can be converted (usually) to VBscript.
So perhaps there's a way to make a VBscript routine that examines the properties of a recordset and fires off the necessary DTS commands to build a comparable Excel file?
The idea begs the question then...does DTS scale well enough for use on a web server? Anyone?
- Tom
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply