I'm attempting to automate the importation of excel docs into a database... I've got it to where the database is taking two separate raw data pulls... creating temp tables from that... and sifting through the needed data and discarding the unneeded columns through two separate append queries... (haven't made it past the select queries to append them yet, because I can't get it to pull the data in the proper format yet)
I'm not super happy with the existing Excel doc structure, but if I edit them at all (without spaces, special characters, delete columns, etc), someone will have to manually do that before each data pool... I like the idea of creating tmptblX, tmptblY, and append querying new records, but I hit a couple snags...
First Problem:
Two fields I would like to split are joined under one column in the raw data... I've tried to run the query with "VendorID: Left([Vendor/supplying plant],7)" in "Field:", but it either takes an inordinate amount of time to run or won't finish (I haven't waited around long enough to find out)... and I'm still looking for an answer on how to handle pulling all of the field minus the first 8 characters in the next column...
So e.g.
tmptblImportData:
[Vendor/supplying plant]
1001011 DURAPART MACHINE, LLC
tblMain Data:
[VendorID] [VendorName]
1001011 DURAPART MACHINE, LLC
There are thousands of records that will be updated monthly, so it needs to be fairly clean in implementation
Second problem:
There will be several tables that are imported each update... they will name themselves "tmptbl"*"Data" and "tmptbl"*"OTD... is there a way to set my query up to run on "variable" tables where they meet that criteria or do I need to import them one at a time and change the spreadsheet name to a static value each import for the query to work correctly?... Obviously the ladder is not preferable...
... and if I can, how to delete all the tmp tables after the queries do their thing