OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Import Excel CSV with leading zeros special formatting

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
sirjohnson
Newbie
Newbie


Joined: 17 Mar 2011
Posts: 4
Location: New York

PostPosted: Tue Mar 22, 2011 6:56 am    Post subject: Import Excel CSV with leading zeros special formatting Reply with quote

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
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue Mar 22, 2011 7:09 am    Post subject: Reply with quote

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 https://forum.openoffice.org
Back to top
View user's profile Send private message
sirjohnson
Newbie
Newbie


Joined: 17 Mar 2011
Posts: 4
Location: New York

PostPosted: Tue Mar 22, 2011 8:55 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
jrkrideau
Super User
Super User


Joined: 08 Aug 2005
Posts: 6732
Location: Kingston ON Canada

PostPosted: Tue Mar 22, 2011 9:08 am    Post subject: Reply with quote

sirjohnson wrote:
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


Why do you want to change ZIP codes to numbers? Conceptually they are text.. Same as a telephone number.
_________________
jrkrideau
Kingston ON Canada
Currently using Windows 7 & OOo 3.4.0 and Ubuntu 12.04 & LibreOffice 3.5.2.2
Back to top
View user's profile Send private message
thomasjk
Super User
Super User


Joined: 16 Dec 2005
Posts: 2374

PostPosted: Tue Mar 22, 2011 9:18 am    Post subject: Reply with quote

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".
Back to top
View user's profile Send private message
DiGro
Super User
Super User


Joined: 02 Jun 2004
Posts: 1415
Location: Hoorn NH, The Netherlands

PostPosted: Tue Mar 22, 2011 9:21 am    Post subject: Reply with quote

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 AOO 4.0.1 NL (Dutch)
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue Mar 22, 2011 9:31 am    Post subject: Reply with quote

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 https://forum.openoffice.org
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue Mar 22, 2011 9:36 am    Post subject: Reply with quote

Villeroy wrote:
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 counterparts. 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 https://forum.openoffice.org
Back to top
View user's profile Send private message
sirjohnson
Newbie
Newbie


Joined: 17 Mar 2011
Posts: 4
Location: New York

PostPosted: Tue Mar 22, 2011 10:07 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue Mar 22, 2011 10:34 am    Post subject: Reply with quote

You did not get it. It's hopeless. I give up.
Sad
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
sirjohnson
Newbie
Newbie


Joined: 17 Mar 2011
Posts: 4
Location: New York

PostPosted: Tue Mar 22, 2011 11:41 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group