Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I'm trying to move data from two of our old systems into a new system. Problem is, somewhere along the line the 'state' column of our DB was corrupted. I have a column of ZIP codes I need to determine to state from. I've found a number of solutions for Excel, but they don't seem to play nicely with OpenOffice. I'm fairly certain there's a way to do it with the CSV from here but I'm not really sure where to start. Any ideas?

share|improve this question
add comment

3 Answers

up vote 1 down vote accepted

This is quite the long way around, but it worked for what I needed. Hopefully someone else can use this info. I could have used additional OR statements for some of the states, but it was easier for me to lay out my dataset without it.

I had this all as one single formula, but it was much too long for OO's tastes, so I had to split it up into 4 formulas, paste the formulas into 4 columns, create a 5th column that concatenates the first 4, then paste the values only from the 5th column into the original 'state' column.

=IF(AND(S3>=99501;S3<=99950);"AK";IF(AND(S3>=35004;S3<=36925);"AL";IF(AND(S3>=71601;S3<=72959);"AR";IF(AND(S3>=75502;S3<=75502);"AR";IF(AND(S3>=85001;S3<=86556);"AZ";IF(AND(S3>=90001;S3<=96162);"CA";IF(AND(S3>=80001;S3<=81658);"CO";IF(AND(S3>=06001;S3<=06389);"CT";IF(AND(S3>=06401;S3<=06928);"CT";IF(AND(S3>=20001;S3<=20039);"DC";IF(AND(S3>=20042;S3<=20599);"DC";IF(AND(S3>=20799;S3<=20799);"DC";IF(AND(S3>=19701;S3<=19980);"DE";IF(AND(S3>=32004;S3<=34997);"FL";IF(AND(S3>=30001;S3<=31999);"GA";IF(AND(S3>=39901;S3<=39901);"GA";IF(AND(S3>=96701;S3<=96898);"HI";IF(AND(S3>=50001;S3<=52809);"IA";IF(AND(S3>=68119;S3<=68120);"IA";IF(AND(S3>=83201;S3<=83876);"ID";0))))))))))))))))))))
=IF(AND(S3>=60001;S3<=62999);"IL";IF(AND(S3>=46001;S3<=47997);"IN";IF(AND(S3>=66002;S3<=67954);"KS";IF(AND(S3>=40003;S3<=42788);"KY";IF(AND(S3>=70001;S3<=71232);"LA";IF(AND(S3>=71234;S3<=71497);"LA";IF(AND(S3>=01001;S3<=02791);"MA";IF(AND(S3>=05501;S3<=05544);"MA";IF(AND(S3>=20331;S3<=20331);"MD";IF(AND(S3>=20335;S3<=20797);"MD";IF(AND(S3>=20812;S3<=21930);"MD";IF(AND(S3>=03901;S3<=04992);"ME";IF(AND(S3>=48001;S3<=49971);"MI";IF(AND(S3>=55001;S3<=56763);"MN";IF(AND(S3>=63001;S3<=65899);"MO";IF(AND(S3>=38601;S3<=39776);"MS";IF(AND(S3>=71233;S3<=71233);"MS";IF(AND(S3>=59001;S3<=59937);"MT";IF(AND(S3>=27006;S3<=28909);"NC";IF(AND(S3>=58001;S3<=58856);"ND";0))))))))))))))))))))
=IF(AND(S3>=68001;S3<=68118);"NE";IF(AND(S3>=68122;S3<=69367);"NE";IF(AND(S3>=03031;S3<=03897);"NH";IF(AND(S3>=07001;S3<=08989);"NJ";IF(AND(S3>=87001;S3<=88441);"NM";IF(AND(S3>=88901;S3<=89883);"NV";IF(AND(S3>=06390;S3<=06390);"NY";IF(AND(S3>=10001;S3<=14975);"NY";IF(AND(S3>=43001;S3<=45999);"OH";IF(AND(S3>=73001;S3<=73199);"OK";IF(AND(S3>=73401;S3<=74966);"OK";IF(AND(S3>=97001;S3<=97920);"OR";IF(AND(S3>=15001;S3<=19640);"PA";IF(AND(S3>=02801;S3<=02940);"RI";IF(AND(S3>=29001;S3<=29948);"SC";IF(AND(S3>=57001;S3<=57799);"SD";IF(AND(S3>=37010;S3<=38589);"TN";IF(AND(S3>=73301;S3<=73301);"TX";IF(AND(S3>=75001;S3<=75501);"TX";IF(AND(S3>=75503;S3<=79999);"TX";0))))))))))))))))))))
=IF(AND(S3>=88510;S3<=88589);"TX";IF(AND(S3>=84001;S3<=84784);"UT";IF(AND(S3>=20040;S3<=20041);"VA";IF(AND(S3>=20040;S3<=20167);"VA";IF(AND(S3>=20042;S3<=20042);"VA";IF(AND(S3>=22001;S3<=24658);"VA";IF(AND(S3>=05001;S3<=05495);"VT";IF(AND(S3>=05601;S3<=05907);"VT";IF(AND(S3>=98001;S3<=99403);"WA";IF(AND(S3>=53001;S3<=54990);"WI";IF(AND(S3>=24701;S3<=26886);"WV";IF(AND(S3>=82001;S3<=83128);"WY";0))))))))))))

The concatenate formula is simply =CONCATENATE(T3;U3;V3;W3)

Hopefully this question will save someone else some time.

share|improve this answer
1  
for the record, you are using a spreadsheet, not a database (you refer to a DB in your original question) –  horatio Oct 27 '11 at 20:52
    
No, I used a CSV dump of the database in question to rebuild a few corrupted columns. I edited the CSV in OpenOffice, added these formulas to a series of new columns, pasted the values that resulted into the original state column, deleted the formula columns, saved out the CSV again, and imported back into the DB. –  SickHippie Nov 2 '11 at 4:26
add comment

In the short term, you could import a fresh and updated listing of zip codes to States using a qualified source such as the Center for Medicare and Medicare Services. This is oriented to payer data, and is actively maintained. After that you could use a Pivot Table or handle the work through Open Office Base, and some SQL join magic to create a view that you could dump out to Calc or a CSV. What type of DB is behind your system? What type of import facility is available?

Another free tool would be to use Quest TOAD for DA Freeware to join the tables. This is very user friendly option with a small learning curve. If you find yourself doing data plumbing/reporting, this is a very nice tool, and worth the cost of a license.

Browse to https://www.cms.gov/prospmedicarefeesvcpmtgen/

Download the following zip archive: Zip Code to Carrier Locality File [ZIP, 4.08MB] - Updated 8/17/11

share|improve this answer
add comment

I haven't looked at the ZIP Code CSV, but I'm fairly certain you can use a VLOOKUP() formula that looks through that data.

=VLOOKUP(zipcode,csvdata,column number for state relative to zip in csvdata,FALSE)
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.