Extracting Data

[bbeeching]

Does anyone know how to extract parts of data in one field, then move that extracted data to an unpopulated field? I have approx. 1500 records in an Excel spreadsheet that I am importing to a database. The records consist of a list of Names and Addresses. The Address cells have the City, State, and Zip all in one cell. In order to normalize the tables I need to separate the City, State and Zip code from the Address and put those in their own fields. Is there any way to do this and keep the information syncronized so that the correct City, State, and Zip are correct with the correct customer? I've tried a "Len" expression in a query but that didn't work. (I was going to use a create table query if it had, then import that into the Customer Table). If no one has any ideas then it's back to the old highlight, cut, paste, delete method....90,000 times
Ya'lls assistance will be greatly appreciated! As a matter of fact, I'll name my second born after ya!
Thanks in advance!

 

[RichMorrison]

I have done this kind of thing and it is not easy. You can use various string handling functions in queries including
Instr, Mid, Left, Right.

My best advice is look in Access Help for explanations and examples of these functions.

Very briefly;
Instr will return the position of a specific character. If City, State, and ZIP are separated by a comma then you can find the position of the comma. Then you can use the Left function to extract characters up to the comma.

Good Luck,
RichM

[This message has been edited by RichMorrison (edited 05-08-2002).]

 

[ListO]

Here's another thought...

1. Is there a comma separating the city, state and Zip? If so, you could
a) Run a search and replace in Excel, changing all commas to tab characters (^t in the replace field).
b)Export the Excel files as a tab-dilineated text file, close Excel, re-open Excel and open the text-file. Excel will prompt you to parse the file as it imports it, and then you'll have the three things in individual cells. Export it again and you're all done.

if that won't work:

2. If the field consistantly has City - comma- State - space - Zipcode, it shouldn't be too tough to import everything into Access, create a form based on that table, write some code which will a) count through each character looking for a comma, and when found, copy everything on the left of the comma into a new City field, b) copy all consecutive numbers from the right side of the field into a new Zipcode field, and then leave what's left to become the State field.

Sub GetCity
Dim Comma as integer, X as integer
Dim Length as integer

Length = Len ([OriginalField])

For X = 1 to Length
If Mid$([OriginalField],X,1)="," then
Comma = X 'Save the location of the comma
X = Length 'Exit the loop early
End If
Next X

[City]= Left$([OriginalField],Comma - 1)
[OriginalField]= Right$([OriginalField],Length - Comma)

End Sub

Continue this kind of thing for the zipcode, but now you're testing for a space instead of a comma, and you're starting from the right side instead of the left.


It might be faster to re-enter it all by hand, but possibly not, and it would be much less satisfying than making this wonderful code work!

Good Luck

-curt

[This message has been edited by ListO (edited 05-09-2002).]

 

[DataMiner]

Have you tried using the "text to columns" feature in Excel? If you have any sort of field delimiter this should be easy.