Importing Excel Sheet with Varying Column Name 1

[ninfan]

Hello. I am trying to figure out how to automate a process where a user is importing a spreadsheet into Access. I would be doing fine except that a single field in the Excel file changes every day (it contains the date of the file). I need to merge this data into an existing table without rebuilding the append query every day (like I'm doing now).

One idea I had was to switch off the "First Row Contains Column Headings" flag but then Access requires that the data go into a new table. I guess I could import to a new table each time then have a query that appends the records in that table to my accumulation table. I would have to delete the first row since it contains field names, not actual data.

My ideal scenario would be to be able to control the import process so that I could rename the incoming variable column name on the fly. It seems Access doesn't want us to have much control when Excel is the source. If my source was a TXT file, I could use an input spec to deal with this.

Has anyone had any interesting solutions to a problem like this? Any ideas would be appreciated. Thank you!

Troy

 

[JimHorton]

A couple of options. OLE is one, you can skulk about in the Excel sheet from within Access and get an idea of what's in the column headings, and what that date is, etc.
But an easier way is to just import the thing into a temp table, then parse the table. It's no big deal to have the temp table have a bunch of empty or meaningless fields in the first few rows--parse these fields, capture that date, then delete them and any other non-essential rows, then you're left with pure data. I can tell you from experience that this works well, I've been through the same thing, and once you have a reasonable idea of what's in each field.

As an example, I had a spreadsheet that contained about 20 or 30 columns, and 5-6000 rows. From week to week, not only did the text in the column headings change, ie "Executing Firm" one week was "Trading Firm" the next--but the column positions changed as well! So, knowing what the data was, I knew that, ie, column 3 + column 6 should exactly match column 18. When I imported, I'd run a query that told me if there was any mismatch. If there was, I'd look at the temp table, and sure enough, what was column 18 was now in column 19, because someone added either a blank column or a non-essential column in between. It boiled down to there being 2 different people producing this .xls file, and I had the import routine automatically use one final append query if column 18 was correct, and another if it wasn't. Hopefully, you may be able to find a similar way of validating in your case.
--Jim

 

[ninfan]

Thank you very much for your comments, Jim. I will go with the temp table plan.

Troy