Importing/Appending multiple versions of Excel into Access table

[Smythe32]

I searched and could not find a viable solution to my issue:

On a daily basis, I can receive a different version of an Excel
spreadsheet to be imported into an Access database. Basically, I want
to take any version of the spreadsheet, look up a column and then copy
the entire column into the master table in my database. I don't want
the user to have to select which version of the spreadsheet that is
being used. For ex:

Spreadsheet 1

ColA ColB ColC etc
12 45 66
33 22 88
44 55 99


Spreadsheet 2

ColAA ColBB ColCC
55 66 77
88 99 22
11 33 44

DB Table

A B C
12 45 66
33 22 88
44 55 99
55 66 77
88 99 22
11 33 44

So basically, I think I need to map all the column names and append
them to my db table. But have no clue how to get there.

Any help on how to handle this would be greatly appreciated

 

[John Nurick]

Do "ColA", "ColB" .. "ColCC" refer to columns A, B , CC in the
worksheet, or do you have columns in the worksheet headed with the
actual characters "ColA", "ColB" etc.?

If the latter, is the data you want to import always in the same columns
even though they are headed differently?

Please explain more clearly.

 

[Smythe32]

Sorry. Let me try another example.

Worksheet 1

Name Street Housenumber
Jones Main 13
Smith Maple 28


Worksheet 2

Hse_Number Cust_Street Cus_Name
48 Peach Sample
55 Apple Williams

Database Table

Cst_Name House_Number Street_Name
Jones 13 Main
Smith 28 Maple
Sample 48 Peach
Williams 55 Apple

I hope this answers your questions. The columns will not be in the
same order throughout any of the sheets or have the same column names.

Thank you again for your help.

 

[John Nurick]

I take it this means I should ignore your earlier statement that you
want to "look up a column and then *copy the entire column* into the
master table in my database" (*emphasis added*). It's a matter of
appending the contents of the worksheet to your main table while mapping
the fields correctly.

Next question: is the field name mapping known in advance? For example,
will the House_Number column always be headed one of "House_Number",
"HouseNumber", "Hse_Number" - or is it open-ended, with the Excel column
headers limited only by the tastes and language skills of the users?

If the former, set up a table containing all the mappings:
tblFieldMappings
AccessName*
ExcelName

with records like
Cst_Name,Name
Cst_Name,Cus_Name
House_Number,Housenumber
House_Number,Hse_Number
...

After that, there are several ways to go. Probably I'd write VBA code
that

1) Launches a hidden instance of Excel, opens the workbook and finds the
right worksheet.
2) Gets the column headers from the worksheet and either looks them up
in the field mappings table or displays a form where the user can
specify the mapping manually.
3) Closes the workbook.
4) Constructs the SQL statement for an append query that gets its data
direct from the worksheet, maps the field names, and appends them to the
table.

By the sound of it - the diverse names and ordering of fields, for
example - these Excel tables are being created manually and are
therefore likely to contain the usual glitches that can screw up the
importing or linking process: for example, a House_Num field containing
a mix of actual integers (e.g. 1,4,23) and real-world "house numbers"
(e.g. "12A", "23 1/2", "17/12", "1-3", "The Willows"), not to mention
using l for 1 and O for 0.

To minimise problems from this source, I'd use step 4 to append the data
to a "staging" table all of whose fields are text type. Further queries
can be used if necesssary to clean up the data there before running a
final append query to move the cleaned data into the main table, and
lastly a delete query to empty the staging table.

I haven't gone into the details because the whole process is too complex
for a single newsgroup post, and because I've no idea how good you are
with VBA and SQL.