Re: SOT: showing leading zeros in CSV
From his first Email: Anyone out there have any nuggets of wisdom to do this in excel? on 6/7/02 2:47 PM, Philip Arnold - ASP at [EMAIL PROTECTED] most eloquently stated: Can't you set the column properties to include leading zeroes? I know you can in Access, I had to do it for zip codes. It's a CSV, a text file - how do you set column types in a text file? Philip Arnold Technical Director Certified ColdFusion Developer ASP Multimedia Limited Switchboard: +44 (0)20 8680 8099 Fax: +44 (0)20 8686 7911 www.aspmedia.co.uk www.aspevents.net An ISO9001 registered company. ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. ** __ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SOT: showing leading zeros in CSV
I use listQualify and enclose my values with double quotes and it keeps leading zeros. Stace -Original Message- From: John Venable [mailto:[EMAIL PROTECTED]] Sent: Friday, June 07, 2002 2:33 PM To: CF-Talk Subject: Re: SOT: showing leading zeros in CSV Can't you set the column properties to include leading zeroes? I know you can in Access, I had to do it for zip codes. John Venable on 6/7/02 2:10 PM, BEN MORRIS at [EMAIL PROTECTED] most eloquently stated: List, I am looking for a way to show numeric values in a csv file preserving leading zeros. For instance, say someone's ssn is 005353828 I want it to show up that way instead of 5353828. Anyone out there have any nuggets of wisdom to do this in excel? In last field in the below example (in MS Excel 97), the single quotes show along with the leading zeros, the double quotes make no difference, the =001 shows up as desired when viewed, but the value is '=100' so it might cause more trouble than it is worth. Reformatting all of the fields as text won't do anything because it drops the leading zeros from the value, it isn't a formatting issue. I couldn't find a way to import the info manually, and specify a text qualifier. a,b,c,d,5,001,'001' a,b,c,d,5,001,001 a,b,c,d,5,001,=001 gracias por adelantado - Ben Morris __ Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: SOT: showing leading zeros in CSV
If you're using the HTML2EXCEL tag (or going straight through the COM
object) to create your Excel file, there was a thread a few weeks back
where I ran into this.
Basically you have to use the Excel COM object to create the file and in
you output embed a non-printing character (CHR(3) was what I ended up
using), then use the COM Object to do a replace, changing the CHR(3)
with a single quote ('). This must be done from inside Excel itself,
otherwise it doesn't work.
If you need more, I can find the old thread.
Hatton
BEN MORRIS wrote:
List,
I am looking for a way to show numeric values in a csv file preserving leading
zeros. For instance, say someone's ssn is 005353828 I want it to show up that way
instead of 5353828. Anyone out there have any nuggets of wisdom to do this in
excel?
In last field in the below example (in MS Excel 97), the single quotes show along
with the leading zeros, the double quotes make no difference, the =001 shows up as
desired when viewed, but the value is '=100' so it might cause more trouble than it
is worth. Reformatting all of the fields as text won't do anything because it drops
the leading zeros from the value, it isn't a formatting issue. I couldn't find a way
to import the info manually, and specify a text qualifier.
a,b,c,d,5,001,'001'
a,b,c,d,5,001,001
a,b,c,d,5,001,=001
gracias por adelantado
- Ben Morris
__
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: SOT: showing leading zeros in CSV
Can't you set the column properties to include leading zeroes? I know you can in Access, I had to do it for zip codes. John Venable on 6/7/02 2:10 PM, BEN MORRIS at [EMAIL PROTECTED] most eloquently stated: List, I am looking for a way to show numeric values in a csv file preserving leading zeros. For instance, say someone's ssn is 005353828 I want it to show up that way instead of 5353828. Anyone out there have any nuggets of wisdom to do this in excel? In last field in the below example (in MS Excel 97), the single quotes show along with the leading zeros, the double quotes make no difference, the =001 shows up as desired when viewed, but the value is '=100' so it might cause more trouble than it is worth. Reformatting all of the fields as text won't do anything because it drops the leading zeros from the value, it isn't a formatting issue. I couldn't find a way to import the info manually, and specify a text qualifier. a,b,c,d,5,001,'001' a,b,c,d,5,001,001 a,b,c,d,5,001,=001 gracias por adelantado - Ben Morris __ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SOT: showing leading zeros in CSV
Can't you set the column properties to include leading zeroes? I know you can in Access, I had to do it for zip codes. It's a CSV, a text file - how do you set column types in a text file? Philip Arnold Technical Director Certified ColdFusion Developer ASP Multimedia Limited Switchboard: +44 (0)20 8680 8099 Fax: +44 (0)20 8686 7911 www.aspmedia.co.uk www.aspevents.net An ISO9001 registered company. ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. ** __ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
