Re: [PHP] Spreadsheet_Excel_Reader problem

On Fri, 2010-03-19 at 18:01 -0400, Jason Pruim wrote:

> On Mar 18, 2010, at 1:26 PM, Andrew Ballard wrote:
> 
> > On Thu, Mar 18, 2010 at 1:00 PM, Ashley Sheridan
> > <[email protected]> wrote:
> > [snip]
> >> And I believe that when MS Office saves a CSV out with a character  
> >> other
> >> than a comma as the delimiter, it still saves it as a .csv by  
> >> default.
> >
> > Nope. If you save as CSV, it is comma-separated with double-quotes as
> > the text qualifier. There is also an option to save in tab-delimited
> > format, but the default extension for that is .txt.
> >
> > The only issue I have with Excel handling text files is with columns
> > like ZIP code that should be treated as text (they are string
> > sequences that happen to contain only numeric digits where leading
> > zeros are significant) but are interpreted as numbers.
> >
> > Andrew
> 
> 
> Hi Andrew,
> 
> As a fellow mailing list processor I can feel your pain... One thing I  
> have found is when you are importing the data, you can select the zip  
> column and change the format from "general" to "text" and it will  
> maintain the leading zero's. Or a simple filter applied to it  
> afterwards will help to.
> 
> But if you have a decent CASS software then it should add the zip back  
> in hehe :)
> 
> 


It's not really just that. In the csv format, a field value of 00123 (I
don't really know what zip code formats are) is perfectly valid.
Unfortunately, Excel (and Calc) tries to be clever and strips out
leading zeros on a field it recognises as all numbers. This is annoying
for things like zip codes and phone numbers (which in the UK mostly all
start with a 0)

I think short of enclosing the field in quote marks to signify it's a
string and not something that the software should guess at is the only
way to ensure it works effectively.

Thanks,
Ash
http://www.ashleysheridan.co.uk