Prev: Need routine to tell me number of dimensions in array.
Next: $_FILE array being truncated
From: Andrew Ballard on 19 Mar 2010 20:30
On Fri, Mar 19, 2010 at 6:01 PM, Jason Pruim <lists(a)pruimphotography.com> wrote:
>
> On Mar 18, 2010, at 1:26 PM, Andrew Ballard wrote:
>
>> On Thu, Mar 18, 2010 at 1:00 PM, Ashley Sheridan
>> <ash(a)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 :)
>
>

That works - if I'm the first one to open the file. Often I get files
that someone else opened in Excel to "fix" some things then saved back
to CSV and sent merrily along. :-)

Andrew
From: Andrew Ballard on 19 Mar 2010 20:32
On Fri, Mar 19, 2010 at 6:13 PM, Ashley Sheridan
<ash(a)ashleysheridan.co.uk> wrote:
>
> 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(a)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
>
>

I don't think even that works. I think what Jason suggested (going
through the text import wizard -- which does not always launch if you
just open the CSV file since Excel thinks it knows how to handle it --
and specifying to treat the column as text) is the only way to be
sure.

Andrew
From: Paul M Foster on 20 Mar 2010 02:52
On Fri, Mar 19, 2010 at 10:13:54PM +0000, Ashley Sheridan wrote:

<snip>

>
> 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.

ZIP codes are simply five digits. The starting digit (0-9) identifies a
broad region of the country. The beginning 3-digit sequence identifies a
major processing center. A full 5-digit ZIP code can encompass a few
blocks or hundreds of square kilometers, depending on the density of
addresses (population). ZIPs ending in 98 or 99 are often reserved for
the Post Office itself.

Fascinating, huh? ;-}

Paul

--
Paul M. Foster
From: Paul M Foster on 20 Mar 2010 02:58
On Fri, Mar 19, 2010 at 06:01:38PM -0400, Jason Pruim wrote:

<snip>

>
> But if you have a decent CASS software then it should add the zip back
> in hehe :)

For the sake of those in Europe and elsewhere, CASS software is software
certified by the US Postal Service which cleans up addresses to conform
to what the Post Office wants them to look like, including adding
correct ZIP codes where possible. There is an additional kind of
software, called PAVE software, which sorts mailing lists into proper
groupings for simple delivery by the Post Office.

Paul

--
Paul M. Foster
First  |  Prev  | 
Pages: 1 2 3 4 5
Prev: Need routine to tell me number of dimensions in array.
Next: $_FILE array being truncated