Results 1 to 2 of 2
Thread: import challenges (a2003)
2004-04-23, 03:25 #1
- Join Date
- Jul 2002
- Cedar Falls, Iowa, USA
- Thanked 0 Times in 0 Posts
import challenges (a2003)
I have a consumer table with these fields:
I'm going to be importing a file that has lumped names together. It has entries like Dr. Donna Thompson and Heather J. Olsen and Leo Tyler, Jr. There are 27,000 entries. What's the best way to handle the situation?
I also have the following fields in the address table:
address1 for number and street, e.g. 722 Newman
a list box with all the street designations like Blvd, Ave, Rd, St, etc.
address3 for oddities like College Square Mall,
a list box with secondary address units like Ste, Apt, etc.
address5 for secondary unit number like 12a, or 1100-12
The import file has lumped all this together in one field. How would you approach this if you were doing the importing?
I've also got a zip table with associated cities and states that I'd like to compare to the city, state and zip fields of the import file. I'd like to identify any discrepancies.
Any insight for any of the above appreciated.
2004-04-23, 06:58 #2
- Join Date
- Mar 2002
- Thanked 28 Times in 28 Posts
Re: import challenges (a2003)
I tend to do this kind of thing in Excel, because there will almost always be exceptions that have to be handled manually; this is easier in Excel than in Access. Once the data look OK in Excel, it is very easy to import them into the Access table.
See <post#=257866>post 257866</post#> for a VBA function that might be useful in splitting the names into parts. You can use this function in Access or in Excel, it is a general VBA function. It might be possible to create a similar function for addresses, but I don't have one handy.
You can also use third-party tools, such as Splitter and ParseRat.