Import Excel CSV with leading zeros special formatting

sirjohnson · Newbie Joined: 17 Mar 2011 Posts: 4 Location: New York

Hi,

I have a vendor who is giving us an Excel CSV file formatted with =" " surrounding the number for numbers that contain leading zeros, zip codes, in our case.

So a zipcode is formatted: ="11111"

While OO has no problem importing the data, it's the formatting for that field that's the problem. While I can work around it, our users won't be able to.

Is there an way to tell OO that =" " is a format for numbers?

I've tried importing into Base, but It works the same way in Base too.

Thanks,

Rich

Villeroy · Posted: Tue Mar 22, 2011 7:09 am Post subject:

This has nothing to do with formatting. You want to import text rather than numbers.
Text "123" is another VALUE than number 123 and no formatting (number format, border, font, color) will ever change this matter of fact.
In the dialog mark the column as column type "Text". If you were using version 3.3, there is an option to treat all quoted values "123" as text.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

sirjohnson · Newbie Joined: 17 Mar 2011 Posts: 4 Location: New York

Villeroy,

Thank you for your quick response. I don't think I explained myself well.

Here is a sample of the CSV:

CITY,ST,ZIP,ZIP+4,COUNTRY
Plainsboro,NJ,="08536",="2234",USA
Hollis,NY,="11423",="1825",USA
Staten Island,NY,="10306",="5273",USA
Jamaica,NY,="11436",="1048",USA
Manhasset,NY,="11030",="3707",USA
New Hyde Park,NY,="11040",="2304",USA
Port Washington,NY,="11050",="2827",USA
Manhasset,NY,="11030",="2835",USA

As you can see, the problem area starts with an equal sign, then a double quote, then contains the number and ends in a double quote.

When I import it as text I get ="08536" as the text of the field when I'm only wanting the number. The Excel formatting adds the equals sign, double quote to the number. Wouldn't you agree it's unique formatting that's added?

It imports fine in Excel, but all our new PCs here now only have OO.

I can make the field usable by stripping out the extras with formulas, but my users won't know how to do that.

Thanks,

Rich

jrkrideau · Posted: Tue Mar 22, 2011 9:08 am Post subject:

thomasjk · Super User Joined: 16 Dec 2005 Posts: 2308

Unless your using the postal codes for calculations you actually want them imported as text. I don't know why you would ever use them in calculations though. If your intent is to use them in a mail merge document you definitely want them as text. If I adjust the import of the file:

Under Separator Options I used Comma, Other and set it to =. Merge Delimiters is checked and Text is ".
Under Other options I left the default at "Quoted fields as Text".

DiGro · Posted: Tue Mar 22, 2011 9:21 am Post subject:

Select comma AND Other as separator, choosing the equal sign for Other.
Select the double qoute as separator for the text.

The sample imports perfectly in Calc, without those characters

EDITED: Otherwise you might just delete the equal signs and double qoutes from the
originating csv.
_________________
DiGro

Windows 7 Home Premium and OOo 3.3 NL (Dutch)

Villeroy · Posted: Tue Mar 22, 2011 9:31 am Post subject:

Storing the numeric text as a formula ="01234" is the stupid trick how Excel enforces these strings to be text values as explained in http://www.creativyst.com/Doc/Articles/CSV/CSV01.htm

This way Excel makes even plain text files incompatible with other applications. Database administrators rightfully hate Excel for stunts like this one.
Since Calc is also a spreadsheet application with the same formula syntax like Excel, it is able to import the text formula ="01234" just as it is intended to be imported. More than that, it may even import a regular text value "01234" without = in front of it.

No, I do not see any reason why one should import a zip code or phone number as a numeric value.
But Calc can do:
Add = as additional column separator
Add " as text separator
Check "Merge delimiters"
Uncheck "Quoted fields as text"(since version 3.3).
Now ,= will be treated as one column separator, the quotes will be ignored and you get numeric values for the zip codes. Numeric values are different values than their equal looking text values. Numbers can be formatted to show leading zeros without changing the numeric value and you can happily apply magic math to them (numerology anyone?).
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

Villeroy · Posted: Tue Mar 22, 2011 9:36 am Post subject:

sirjohnson · Newbie Joined: 17 Mar 2011 Posts: 4 Location: New York

Thank you all!

I was missing the merge delimiter in my trials.

It's now importing perfectly.

And I never intended to import as numbers, just as a usable Zip for a mail merge.

Rich

Villeroy · Posted: Tue Mar 22, 2011 10:34 am Post subject:

You did not get it. It's hopeless. I give up.
Sad

_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

sirjohnson · Newbie Joined: 17 Mar 2011 Posts: 4 Location: New York

Villeroy,

Please don't give up! Who would help us? Laughing

What did I miss? I can now blissfully created mail merged docs and have my users do the same without having to do any more fiddling.