Go Back   Access World Forums > Apps and Windows > Excel

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-16-2019, 09:34 AM   #1
marseneault
Newly Registered User
 
Join Date: Jan 2019
Location: Atlanta, GA USA
Posts: 24
Thanks: 4
Thanked 0 Times in 0 Posts
marseneault is on a distinguished road
Working with Dynamic Linked Data From Excel

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?

marseneault is offline   Reply With Quote
Old 01-16-2019, 09:52 AM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,830
Thanks: 50
Thanked 1,103 Times in 1,084 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Working with Dynamic Linked Data From Excel

Hi. You could create your own custom table definition and simply transfer the data from the linked Excel to your 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 online now   Reply With Quote
Old 01-16-2019, 10:02 AM   #3
marseneault
Newly Registered User
 
Join Date: Jan 2019
Location: Atlanta, GA USA
Posts: 24
Thanks: 4
Thanked 0 Times in 0 Posts
marseneault is on a distinguished road
Re: Working with Dynamic Linked Data From Excel

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

marseneault is offline   Reply With Quote
Old 01-16-2019, 10:10 AM   #4
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,461
Thanks: 112
Thanked 2,868 Times in 2,612 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Working with Dynamic Linked Data From Excel

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)
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
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.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
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.
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
marseneault (01-16-2019)
Old 01-16-2019, 10:34 AM   #5
marseneault
Newly Registered User
 
Join Date: Jan 2019
Location: Atlanta, GA USA
Posts: 24
Thanks: 4
Thanked 0 Times in 0 Posts
marseneault is on a distinguished road
Re: Working with Dynamic Linked Data From Excel

This may sound elementary, but is process done inside Access? So Should I Use the make Table Query, the Append Query, or another type?
marseneault is offline   Reply With Quote
Old 01-16-2019, 11:53 AM   #6
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,461
Thanks: 112
Thanked 2,868 Times in 2,612 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Working with Dynamic Linked Data From Excel

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

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
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.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
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.
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
marseneault (01-16-2019)
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Linked dynamic table from excel not working setis Tables 5 11-06-2017 03:52 AM
get data from a linked excel form. Fisky Modules & VBA 2 03-27-2014 02:41 AM
Access data export into Excel as the data linked to excel. vinwin06 General 5 11-08-2012 05:50 PM
Editing data in a linked excel sheet tyoelement Tables 2 03-28-2008 05:39 PM
importing dynamic data from Excel Spreadsheet rvd48 General 19 03-06-2004 02:07 AM




All times are GMT -8. The time now is 07:22 AM.


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