Working with Dynamic Linked Data From Excel

[marseneault]

Hello Access World Users,

I am working with an online platform that has an poor reporting system. It does, however have an export feature that sends all the data into a multi-tabbed Excel Spreadsheet.

As this data is updated about 3-5 times a week into the same file, I was wanting to make it easy to import the data, run a number of selection and update queries on it and sending it out as the report that I need.

The Main issue that I have is the date Format Type in the Excel file is not useful in Access and I can't redefine that property in a linked table. (Most of it comes in as Short Text, Numbers, or Currency, I need the option to have YES/No and other data options.

Any Help on how to get this info easily in the system while giving me the flexibilty to create my own data definitions?

 

[theDBguy]

Hi. You could create your own custom table definition and simply transfer the data from the linked Excel to your table.

 

[marseneault]

Sooo, I guess this new table would be run through an append querry?

When I tried doing that earlier, It would not allow me to change the data type to a linked table, it has to stay the same throughout the database

 

[isladogs]

Import the data in the linked Excel file to a buffer (AKA staging) table as an intermediary step.
Process it to convert the data to what you require.
Import the processed data to your final table
Empty the buffer table (or delete it if this is a one off operation)

 

[marseneault]

This may sound elementary, but is process done inside Access? So Should I Use the make Table Query, the Append Query, or another type?

 

[isladogs]

This is all done within Access.

First of all link to your Excel file. No need to import it.
Next create your buffer table.
Suggest you do this by copying the structure of the Excel file but not the data (yet)
It should normally have the same fields as the Excel file plus any additional fields you require.
Adding a primary key field will probably be useful and may be essential.

Create an append query to copy the data to the buffer table.
Next run one or more update queries to process the data into the format you require.
Run another append query to copy the processed data to your final table.
If there is an possibility of records being duplicated, adapt the append query to only add unmatched records.

Finally empty your buffer table ready for future use OR delete it if this is only to be done once.

Strongly recommend you test this on a backup copy and tweak as necessary until working correctly.
If you are going to do this regularly, create a procedure to run each step in sequence

HTH