Import dynamic excel (or csv) into Access (1 Viewer)

tim_1975

New member
Local time
Today, 18:30
Joined
Oct 24, 2019
Messages
7
Hi,

I download a report weekly for a 10 clients, my aim is to create a mast table with all the data for all the clients to allow me to analyse and create summary reports. (the data is a CSV but I've been saving it to a XLSX)

In my basic world I created a series of import steps to create a table for each client.

Then I run a macro which uses an append query to add the data to a master table.

And there it fell over.

Turns out the downloaded report is dynamic and only contains relevant data for the time frame selected, so, if something didn't happen last week then several columns might be missing from the source spreadsheet.

My question is:
Can Access somehow extract the data I want from the spreadsheet by 'looking' at the column headings and just populating that data?

If there are columns missing in the spreadsheet then access just leaves those fields blank.

My knowledge of access is pretty limited, tables, queries and macro's so anything more than that I'll need idiots guide.

Many thanks

Tim
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:30
Joined
Oct 29, 2018
Messages
21,358
Hi Tim. I think the "usual" approach for something like this is to import the Excel data into a temp table and then use another process to move the necessary data into the permanent table. If it's dynamic enough, you might have to use code to loop through the fields and compare the names.
 

tim_1975

New member
Local time
Today, 18:30
Joined
Oct 24, 2019
Messages
7
Thanks DB.

I kinda get the idea of importing to a temp table so that all the data is then in access....but what's the next step?

Again, I get the principle of selecting data if it's there but how do I work around the null values?

Is there any examples you're aware of that I could be looking at?

Many thanks for the swift response, much appreciated.

Tim
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:30
Joined
Oct 29, 2018
Messages
21,358
Thanks DB.

I kinda get the idea of importing to a temp table so that all the data is then in access....but what's the next step?

Again, I get the principle of selecting data if it's there but how do I work around the null values?

Is there any examples you're aware of that I could be looking at?

Many thanks for the swift response, much appreciated.

Tim
Hi Tim. Let me clarify something first. You just mentioned Null data, that's different than non-existing columns, which was what I thought you meant. For example, if the Excel file always have Col1, Col2, and Col3, but sometimes, in some rows, Col1 or Col2 or Col3 is/are empty, then there's no problem moving it to Access. Access can handle null data without any issue. But, on the other hand, if you're saying the Excel file sometimes have a Col2 and sometime not but might have Col4 instead, then that requires special handling. So, which of the two scenarios above do you actually have? 1. Fixed column names with null data, or 2. Dynamic column names
 

tim_1975

New member
Local time
Today, 18:30
Joined
Oct 24, 2019
Messages
7
Sorry for the poor choice of phrase.

It's the second scenario, dynamic column names

The data sheet can have a max of 38 columns, sometimes it might only have 6, or any number in between.

Does that clarify?

Cheers
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:30
Joined
Oct 29, 2018
Messages
21,358
Sorry for the poor choice of phrase.

It's the second scenario, dynamic column names

The data sheet can have a max of 38 columns, sometimes it might only have 6, or any number in between.

Does that clarify?

Cheers
Hi. Yes, that helps. Thanks for clarifying. Okay, there may be better ways to do it, but here's what I mean by using a loop. For example, let's say the permanent table has Col1, Col2, Col3, Col4, and Col5. Now, if you simply import the Excel file into a temp table, then you might get the following columns instead: Col1, Col2, Col4, Col6, Col7.
Alright, what we can do is loop through the columns from both tables. The way I might do it is use another table to store all the columns from the permanent table. For example, that table might contain the following records:
Col1
Col2
Col3
Col4
Col5
We can then loop through this as a recordset and then use an inner loop through the temp table and compare the field names. As we get a match, we can create a String to list all the columns available in the temp table. For example, in the above sample import, the string might end up something like this: "Col1, Col2, Col4"
Once we know that, we can easily create an APPEND query like so:
Code:
INSERT INTO PermanentTable(Col1,Col2,Col4) SELECT Col1,Col2,Col4 FROM TempTable
Hope that helps...
 

tim_1975

New member
Local time
Today, 18:30
Joined
Oct 24, 2019
Messages
7
Hi,

it does help in as much as the principle, no idea how do create the Loop or the stringset!

Know any tutorials that could point me in the right direction?

Really appreciate your help, thank you

Tim
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:30
Joined
Oct 29, 2018
Messages
21,358
Hi,

it does help in as much as the principle, no idea how do create the Loop or the stringset!

Know any tutorials that could point me in the right direction?

Really appreciate your help, thank you

Tim
No tutorials (other than searching through YouTube). But let's say you want to loop through a recordset, this is the basic structure:
Code:
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("TableName")
With rs
    Do While Not .EOF
        'do something in here while inside the loop

        .MoveNext

    Loop

End With
Set rs = Nothing
 

tim_1975

New member
Local time
Today, 18:30
Joined
Oct 24, 2019
Messages
7
Hi DB,

Thanks so much for this, reassures me that in principle this is do-able.

Sadly your response is way above my understanding!

I'll see what I can do!

Ta

Tim
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:30
Joined
Oct 29, 2018
Messages
21,358
Hi DB,

Thanks so much for this, reassures me that in principle this is do-able.

Sadly your response is way above my understanding!

I'll see what I can do!

Ta

Tim
Hi Tim. You're welcome. If you can't do it in code, then you'll have to do it manually. Import the Excel file in a temp table and then manually create a query to append the new data to the permanent table.
 

Good Looking Bloke

Registered User.
Local time
Tomorrow, 02:30
Joined
Oct 15, 2019
Messages
30
Hi,

I download a report weekly for a 10 clients, my aim is to create a mast table with all the data for all the clients to allow me to analyse and create summary reports. (the data is a CSV but I've been saving it to a XLSX)

In my basic world I created a series of import steps to create a table for each client.

Then I run a macro which uses an append query to add the data to a master table.

And there it fell over.

Turns out the downloaded report is dynamic and only contains relevant data for the time frame selected, so, if something didn't happen last week then several columns might be missing from the source spreadsheet.

My question is:
Can Access somehow extract the data I want from the spreadsheet by 'looking' at the column headings and just populating that data?

If there are columns missing in the spreadsheet then access just leaves those fields blank.

My knowledge of access is pretty limited, tables, queries and macro's so anything more than that I'll need idiots guide.

Many thanks

Tim

Hi Tim

Look I have only got the information displayed here so I am a bit limited on a full understanding, however, having worked with csv and excel from all over the place and with different levels of accuracy and shall we say data input. My final solution most of the time is don't do anything.

Instead I link the data in as a datasource. I then query the datasource for the data I want to get and if its not present I nz or ignore or whatever and move on. Times too precious and I only have so much hair.

Not sure if it will help you or not. May not be a perfectly correct solution, but it does work.
 

Users who are viewing this thread

Top Bottom