Transfer "messy" excel data to Access

[teser3]

I have a user who has an Excel 2000 Spreadsheet with thousands of lines
similiar to this data (shortened for example):


Name Location

joe james bob smith san diego california
mary ellen wallace neil jones joe smith portland oregon



He wants this transfered into a Access 2000 database.
The Access database that I have is set up like this:

First Name Last Name City State
Joe James San Diego California
bob Smith San Diego California
mary ellen wallace Portland Oregon
neil jones Portland Oregon
joe smith Portland Oregon


Can this be done where I would have to build some script to fetch the
Excel data or something to seperate the names and cities using a reg
expression so it will fit into the database?
It seems the Customer created this Excel spreadsheet and put in a bunch
of names and cities and didnt seperate them yet the user wants this all
to be transferred to a database with the above field name structure and
data.

Is this possible or what would should I advise the customer?

 

[Smartin]

I have a user who has an Excel 2000 Spreadsheet with thousands of lines
similiar to this data (shortened for example):


Name Location

joe james bob smith san diego california
mary ellen wallace neil jones joe smith portland oregon



He wants this transfered into a Access 2000 database.
The Access database that I have is set up like this:

First Name Last Name City State
Joe James San Diego California
bob Smith San Diego California
mary ellen wallace Portland Oregon
neil jones Portland Oregon
joe smith Portland Oregon


Can this be done where I would have to build some script to fetch the
Excel data or something to seperate the names and cities using a reg
expression so it will fit into the database?
It seems the Customer created this Excel spreadsheet and put in a bunch
of names and cities and didnt seperate them yet the user wants this all
to be transferred to a database with the above field name structure and
data.

Is this possible or what would should I advise the customer?

I would advise the customer it will take X hours at Y rows per hour to
parse this Excel sheet manually, row by row.

The sample data you gave presents little if no opportunity for parsing
the data programmatically.

 

[John Vinson]

I have a user who has an Excel 2000 Spreadsheet with thousands of lines
similiar to this data (shortened for example):


Name Location

joe james bob smith san diego california
mary ellen wallace neil jones joe smith portland oregon

Owwwww....

I can't think of any way even in principle to handle this, since there
is no delimiter between the names. Ask the user to correctly parse the
following Name field:

steven james kelly robert smith

Is it "Steven James Kelly" and "Robert Smith", or "Steven James" and
"Kelly Robert Smith"? Without contacting the persons in question, I
have no idea which would be correct; nor would any computer algorithm
that I can imagine. Even a directory lookup would be questionable;
there are undoubtedly many people named Steven James and Steven Kelly
in San Diego.

Garbage in... garbage out!

John W. Vinson[MVP]