RE: [PCTECH] Exprting data into Excel drops leading zero

[Dennis Munro <DMunro@xxxxxxxxxxxxxxxxxxxx>]

Do I remember correctly that in a csv format the text fields are enclosed in
double or single quotes to distinguish them from numeric fields?


Thanks - Dennis.

Dennis Munro

"I love deadlines. I especially like the whooshing sound they make as they
go flying by."
Dilbert's Words Of Wisdom

Badger Mining Corporation
www.badgerminingcorp.com
dmunro@xxxxxxxxxxxxxxxxxxxx
(920) 361-2388   Ext.141

-----Original Message-----
From: Adam Lang [mailto:aalang@xxxxxxxxxxxxxxxxxxxx]
Sent: Tuesday, February 24, 2004 1:52 PM
To: PC Technical Discussion for iSeries Users
Subject: Re: [PCTECH] Exprting data into Excel drops leading zero

Not really Microsoft's fault.  It is Surveyor/400s fault.  They are
exporting improperly.

When you export to CSV, it just dumps everything as a string.  Excel "auto
formats" the data base don what it reads in.  If everything is numeric, it
assumes it is a number and kills the leading zeros.

Now, if Surveyor was exporting correctly, they would be assigning the cells
as a text format so Excel knows not to treat them as numbers.

If you use the Client Access data transfer utility, and transfer to an XLS
file, the XLS file will keep the proper formatting for fields like zip codes
and such if they are defined as strings on the AS/400.  They coded it
correctly.

This is not something to blame on Microsoft.  Surveyor/400 is doing a poor
job of exporting.  I have written a variety of VB programs that work with
data on the AS/400 and I dump data to a spreadsheet and it is very simple to
programmatically format the cell before you dump the data to it.  I would
consider it a bug in Surveyor's part.

----- Original Message -----
From: "Dennis Munro" <DMunro@xxxxxxxxxxxxxxxxxxxx>
To: "'PC Technical Discussion for iSeries Users'" <pctech@xxxxxxxxxxxx>
Sent: Tuesday, February 24, 2004 2:29 PM
Subject: RE: [PCTECH] Exprting data into Excel drops leading zero


> I'm using Surveyor/400 to do the export & I've talked to them about the
> situation.  I'm guessing it is Uncle Bill because in csv format the data
is
> all there.  Even importing this csv file into Excel the zeros are dropped.
>
> Thanks - Dennis.
>
> Dennis Munro
>
> "I love deadlines. I especially like the whooshing sound they make as they
> go flying by."
> Dilbert's Words Of Wisdom
>
> Badger Mining Corporation
> www.badgerminingcorp.com
> dmunro@xxxxxxxxxxxxxxxxxxxx
> (920) 361-2388   Ext.141

_______________________________________________
This is the PC Technical Discussion for iSeries Users (PcTech) mailing list
To post a message email: PcTech@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/pctech
or email: PcTech-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/pctech.
The information contained in this e-mail message is confidential information
intended only for the use of the individual or entity named above. If the
reader of this message is not the intended recipient, or the employee or
agent responsible to deliver it to the intended recipient, you are hereby
notified that any dissemination, distribution or copying of this
communication is strictly prohibited. If you have received this
communication in error, please immediately notify us by telephone, and
return the original message to us at the below address via the U.S. Postal
Service, and delete it from your computer.  Badger Mining Corporation  P.O.
Box 328  Berlin, WI 54923  920-361-2388