Go Back   Access World Forums > Apps and Windows > Excel

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-24-2019, 07:08 AM   #1
tim_1975
Newly Registered User
 
Join Date: Oct 2019
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
tim_1975 is on a distinguished road
Import dynamic excel (or csv) into Access

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

tim_1975 is offline   Reply With Quote
Old 10-24-2019, 07:17 AM   #2
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,537
Thanks: 58
Thanked 1,429 Times in 1,410 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Import dynamic excel (or csv) into Access

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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 10-24-2019, 07:22 AM   #3
tim_1975
Newly Registered User
 
Join Date: Oct 2019
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
tim_1975 is on a distinguished road
Re: Import dynamic excel (or csv) into Access

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

tim_1975 is offline   Reply With Quote
Old 10-24-2019, 07:26 AM   #4
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,537
Thanks: 58
Thanked 1,429 Times in 1,410 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Import dynamic excel (or csv) into Access

Quote:
Originally Posted by tim_1975 View Post
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
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 10-24-2019, 07:30 AM   #5
tim_1975
Newly Registered User
 
Join Date: Oct 2019
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
tim_1975 is on a distinguished road
Re: Import dynamic excel (or csv) into Access

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
tim_1975 is offline   Reply With Quote
Old 10-24-2019, 07:48 AM   #6
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,537
Thanks: 58
Thanked 1,429 Times in 1,410 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Import dynamic excel (or csv) into Access

Quote:
Originally Posted by tim_1975 View Post
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...
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 10-24-2019, 08:05 AM   #7
tim_1975
Newly Registered User
 
Join Date: Oct 2019
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
tim_1975 is on a distinguished road
Re: Import dynamic excel (or csv) into Access

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

tim_1975 is offline   Reply With Quote
Old 10-24-2019, 08:12 AM   #8
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,537
Thanks: 58
Thanked 1,429 Times in 1,410 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Import dynamic excel (or csv) into Access

Quote:
Originally Posted by tim_1975 View Post
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
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 10-25-2019, 01:32 AM   #9
tim_1975
Newly Registered User
 
Join Date: Oct 2019
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
tim_1975 is on a distinguished road
Re: Import dynamic excel (or csv) into Access

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
tim_1975 is offline   Reply With Quote
Old 10-25-2019, 08:27 AM   #10
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,537
Thanks: 58
Thanked 1,429 Times in 1,410 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Import dynamic excel (or csv) into Access

Quote:
Originally Posted by tim_1975 View Post
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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 10-28-2019, 02:01 PM   #11
Good Looking Bloke
Newly Registered User
 
Join Date: Oct 2019
Posts: 15
Thanks: 2
Thanked 1 Time in 1 Post
Good Looking Bloke is on a distinguished road
Re: Import dynamic excel (or csv) into Access

Quote:
Originally Posted by tim_1975 View Post
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.

Good Looking Bloke is offline   Reply With Quote
Reply

Tags
dynamic , excel , import , tables

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Import Access Query Into Excel using button in Excel chrispeers Modules & VBA 1 08-16-2013 02:26 AM
Import Excel workbook into Access table using Excel's format (formulas, values, etc) captdkl02 Modules & VBA 1 12-17-2012 09:03 AM
import multiple excel files with FILTERED Excel spreasheet into an access table WuJu Modules & VBA 5 10-31-2011 04:38 PM
Selecting Excel ranges (dynamic) from Access VBA wiklendt Excel 20 10-26-2010 01:44 PM
Import Dynamic Named Range from Excel Ian Mac Modules & VBA 2 02-05-2005 08:47 PM




All times are GMT -8. The time now is 08:49 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World