Re: 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 > > <ash@ashleysheridan.co.uk> 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