importing zip codes from Excel

[Reva]

I have an excel file that I need to import on a regular basis. We have Enterprise Edition, so I'm using the AlphaDAO option on the import genie. I'm importing into an existing table. The zip column has some 5-digit zips, some 5+4 zips in the format 00000-0000, and a few Canadian postal codes w. letters in them. The 5 digit zips come over just fine. Everything else is blank. I've tried formatting the Excel column as character, general, number, and even custom/zip. Nothing works. This is driving me NUTS, the more so because I had it working a few months ago. Is this maybe a bug that was fixed, then un-fixed? Have I forgotten something?

[Stan Mathews]

Re: importing zip codes from Excel

Is this maybe a bug that was fixed, then un-fixed? Have I forgotten something?

Not sure about before but in my testing, rows with zipcodes matching the structure of the first row get imported values.

IOW if the first row has a five digit zip code, all five digit zip codes are imported, others are blank. If the first row has a 00000-0000 zip code, all zip codes matching that pattern are imported, others are blank.

Not sure if that will help you.

If you can live with formatting the excel file so that 5 digit zip codes actually appear as

12345-0000

you should have no problems importing all rows. In Alpha you could define a calculated field to print the significant portion of the zip code.

[Reva]

Re: importing zip codes from Excel

Not sure about before but in my testing, rows with zipcodes matching the structure of the first row get imported values.

IOW if the first row has a five digit zip code, all five digit zip codes are imported, others are blank. If the first row has a 00000-0000 zip code, all zip codes matching that pattern are imported, others are blank.

Not sure if that will help you.

The first row in this case, after the column heading, is a 9 digit code. I've carefully formatted the column as TEXT in Excel (character in A5), and the A5 column width is 34. I guess I could go back to saving as a v4 worksheet, which was how it was working in v.7, but that's a nuisance & the user keeps forgetting to do it. It just occurred to me that I'm using Office2007. I'm saving in 2003 compatible mode, but I wonder if it makes a difference.
No. I tried it with Excel2003. same results. I can get it to come over as a .dbf file, but I'd like the end user to be able to push a button rather than having to do all the prep to make sure it comes over properly as a .dbf. It just seems that this shouldn't be a big deal. The field is formatted as text. Just take the text & put it where it's supposed to go! Heavy sigh.

[Reva]

Re: importing zip codes from Excel

Something very weird is happening here. I saved my Excel file with a different name & separated the 2 parts of the zip code into 2 columns. Then I imported into an A5 table with the extra column and that was fine. Then I decided to try your idea, Stan, and add "-0000" to the end of the zips in the original column. I did that for the first 3 zips in my modified Excel table, and re-imported. All the zips came in fine: the 3 at thebeginning w. -0000 added, and the rest with only 5 digits. So then I went back to my original file, where the first 2 zips were 9 digit, and added -0000 to the next 2, so that I had 4 9-digit zips starting the column. Now the first 4 zips don't import where before the first 2 zips didn't import.
So I'm thinking that something about manipulating the data in Excel made A5 see the zip column as character data.
I guess I have a few acceptable solutions now, but it appears that formatting a column as text in Excel is not enough to convince A5 it's really text. It's almost like you've got to re-create the column. If anyone understands this or has an easy way to force Excel to REALLY make the column text, I'd like to hear about it. Thanks, Stan, for pointing me in the right direction.

[Doug Page]

Re: importing zip codes from Excel

Using Excel 2003, I created a sheet that had the following entries for Zip Code:

12345
12345-1234
V6B 3K2

I had set the column they were entered into as Text before entering as sometimes Excel doesn't really seem to convert things well. I then used Alpha to import them via AlphaDAO and received exactly what I entered and all were character fields. I had the Alpha field set up as Character*34 as you did. Reimported several times and each time it worked OK.

Suggestions:
1. Check to make sure that the data in your Excel worksheet truly is Text.
2. Perhaps try recreating your Import Operation incase something funny has been introduced to it.

[MoGrace]

Re: importing zip codes from Excel

You could also create all the zipcodes to fit the 00000-0000 format by adding the last 4 digits of the address or P O Box to the string. 123 Main St would have to add 0123 to the zip.

[WaltMorgan]

Re: importing zip codes from Excel

This may or may not help:

EXCEL has under formatting cells --> Special --> ZIP Code +4 as a formatting option.

I tested it by exporting to a CSV file and even zipcodes with leading zeros came across as expected.

Just a thought...

[Reva]

Re: importing zip codes from Excel

I appreciate your suggestions. I've ruled out csv because I'm trying to make this as easy as possible for the end user, an intelligent woman with little patience & even less time. Ditto on altering the zip codes. I like the idea of creating the column as text to start out with. I finally got this to work, & here is what I learned:
If you just highlight the column with the zips in it & format it as text, A5 does not know it's text. I could probably have replaced the column with a new one formatted as text from the start & copied the zips into it, but what I did was to use one of the options on the Data tab (Excel2007) called "text to columns" and this kind of re-created the column in place so that it was recognizable to A5 as text. I can give the details of this if anyone is interested.
One thing people should be aware of is that in Excel2007 you can no longer save files as Db IV (.dbf) or v.4 worksheet (.xlw), so that limits options for import.

[Doug Page]

Re: importing zip codes from Excel

If you really want to make things easy to import to any other program, start to use .csv files. Since you have not really specified, I do not know what it is that makes this difficult for your user. They do not need to know a thing if you create a simple macro that does the job. They only need to run the macro (Alt-F8 then select the macro OR assign a keystroke pair OR even easier, put a button on the worksheet and then they just need to click that). All this functionality has been around for well over a decade in Excel so if you are supporting someone using Excel it would be advisable to have a working knowledge of these things to make their life easier. And since saving data as a .csv is so common, I'll bet a quick Google would get you an answer with code and have this working in a few minutes. Probably a lot less time than you have spent on this problem already.

[Brad Chesnut]

Re: importing zip codes from Excel

I was faced with this same problem today. Also not wanting my clients to have to do very much more than just press a button, I ultimately solved this by using the "Text To Columns" function in Excel. Yes, this does require my clients to have to do some work in Excel but from everything I've read and tested, this appears to be the best option, at least for me.

Instructions:

If your zipcodes have any that are 00000-0000 mixed in with 00000 formatted zipcodes, they must be converted in Excel. That's because zipcodes in the 00000-0000 can't be imported.

Block column (click on Column Letter to block whole column)
Menu: Data/Text To Columns
Step 1: Delimited
Step 2: Other= -
Step 3:
Choose Text
Destination=$Z$1 (first open column in excel spreadsheet)
Column Names: Z1=Zip, AA1=Zip4

Additional comments about this:
I tried to just import the CSV but with field lengths changing from CSV file to CSV file I couldn't figure out how to make it push button. It is push button with the AlphaDAO Import Genie with the exception of the one modification with the zipcode field after resaving the CSV file to a XLS file.

I hope this helps others faced with this problem.

[Stan Mathews]

Re: importing zip codes from Excel

this does require my clients to have to do some work in Excel but from everything I've read and tested, this appears to be the best option

You can code that with Alpha's ole methods.