Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    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:
    lName, and
    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.


  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts