Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-14-2018, 02:04 AM   #1
CNorway
Newly Registered User
 
Join Date: Feb 2018
Posts: 21
Thanks: 18
Thanked 0 Times in 0 Posts
CNorway is on a distinguished road
Easiest way to get Excel table into Access

Hi,

I have for the past several years just used a datasheet to pull in data from Excel into Access. However, I was wondering if there is a better way to do it?

The advantages I have seen is that if the columns are dirty in Excel, the user will get an error. Also, the end user can see immediately that the paste went correctly.

Though, maybe there is a better way of getting an Excel file into a table? Any help would be greatly appreciated.

Thanks,
Chris

CNorway is offline   Reply With Quote
Old 06-14-2018, 02:12 AM   #2
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,136
Thanks: 54
Thanked 1,968 Times in 1,881 Posts
arnelgp is a jewel in the rough arnelgp is a jewel in the rough arnelgp is a jewel in the rough arnelgp is a jewel in the rough
Re: Easiest way to get Excel table into Access

Use the import wizzard.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
CNorway (06-14-2018)
Old 06-14-2018, 02:29 AM   #3
CNorway
Newly Registered User
 
Join Date: Feb 2018
Posts: 21
Thanks: 18
Thanked 0 Times in 0 Posts
CNorway is on a distinguished road
Re: Easiest way to get Excel table into Access

Hi,

The reason I ask is because it is not I who is doing the import, but end users. So, they won't have access to the import wizard... or will they? No, I think they would have to know exactly which table the data was being imported into.

Thanks,
Chris

CNorway is offline   Reply With Quote
Old 06-14-2018, 04:18 AM   #4
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,136
Thanks: 54
Thanked 1,968 Times in 1,881 Posts
arnelgp is a jewel in the rough arnelgp is a jewel in the rough arnelgp is a jewel in the rough arnelgp is a jewel in the rough
Re: Easiest way to get Excel table into Access

you need VBA if you want to hide the process from the End Users.
you use:

DoCmd.TransferSpreadsheet TransferType:=acImport, SpreadsheetType:=acSpreadsheetTypeExcel12Xml, TableName:="nameOfTable", FileName:="complete path + filename.xlsm"", HasFieldNames:=True

the excel file you are importing must have same fieldtype and field count as with the table.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
CNorway (06-14-2018)
Old 06-14-2018, 04:20 AM   #5
ridders
Newbee moderator
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 5,301
Thanks: 77
Thanked 1,294 Times in 1,209 Posts
ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough
Re: Easiest way to get Excel table into Access

You can also make the spreadsheet a linked table in Access.
It will of course be read only.

The import process can then be done from that linked table though I would restrict that to specified users
__________________
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.


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

New example databases:
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.
ridders is offline   Reply With Quote
The Following User Says Thank You to ridders For This Useful Post:
CNorway (06-14-2018)
Old 06-14-2018, 04:31 AM   #6
MajP
Newly Registered User
 
Join Date: May 2018
Posts: 137
Thanks: 1
Thanked 22 Times in 21 Posts
MajP is on a distinguished road
Re: Easiest way to get Excel table into Access

Quote:
The reason I ask is because it is not I who is doing the import, but end users. So, they won't have access to the import wizard... or will they? No, I think they would have to know exactly which table the data was being imported into.
There are many options, but you will have to provide some details on your business process. How is the database deployed? How are the spreadsheets used? Type and frequency of import, etc. Maybe you can simply train the users how to use the native import capability, or you might have to automate some processes for them.
MajP is offline   Reply With Quote
The Following User Says Thank You to MajP For This Useful Post:
CNorway (06-14-2018)
Old 06-14-2018, 05:19 AM   #7
CNorway
Newly Registered User
 
Join Date: Feb 2018
Posts: 21
Thanks: 18
Thanked 0 Times in 0 Posts
CNorway is on a distinguished road
Re: Easiest way to get Excel table into Access

Quote:
Originally Posted by MajP View Post
There are many options, but you will have to provide some details on your business process. How is the database deployed? How are the spreadsheets used? Type and frequency of import, etc. Maybe you can simply train the users how to use the native import capability, or you might have to automate some processes for them.
The main use is when doing an export from our ERP system SAP to Excel. Then, the excel report is pasted into the Access database datasheet. That's pretty much it. The Access database itself is using SQL Server as its backend. I have just wondered if this is really the best option. The other challenge with the SAP reports is that they sometimes have redundant fields that must be renamed and characters like "." and "/" which don't work well when importing into Access.

Thanks,
Chris

CNorway is offline   Reply With Quote
Old 06-14-2018, 07:39 AM   #8
MajP
Newly Registered User
 
Join Date: May 2018
Posts: 137
Thanks: 1
Thanked 22 Times in 21 Posts
MajP is on a distinguished road
Re: Easiest way to get Excel table into Access

I am not familiar with SAP so no idea if there is a smarter direct way. From what I understan you download a report into Excel. I am going to assume that this excel is not always in the same location once downloaed. The report needs some level of cleaning before the records can be appended to existing table/s. Currently you are actually pasting rows from the report into an existing table/s. I am going to assume that all data from the excel goes into a single table and you do not have to create information into other related tables. You would like to users to be able to do this with some automation.
My guess on how I would do this.
1) Provide a command button to launch the file browser to find your SAP Excel Report
2) This will import into either a linked table or Temp Table. Preferably just a link is created.
3) Then run some queries to do the house cleaning or if needed do with a recordset. Rename columns if necessary; remove redundancy, clean-up names, etc.
4) Run the append query on the cleaned up data or if needed add records through a recordset.
5) Show some confirmation of what has been appended and allow the user to review those records in case any final cleanup is needed.
So all of this should happen from a button click. As long as there are good primary keys and or indices there should be no problem if records have already been updated you cannot append them again.
MajP is offline   Reply With Quote
The Following User Says Thank You to MajP For This Useful Post:
CNorway (06-15-2018)
Old 06-15-2018, 02:29 AM   #9
CNorway
Newly Registered User
 
Join Date: Feb 2018
Posts: 21
Thanks: 18
Thanked 0 Times in 0 Posts
CNorway is on a distinguished road
Re: Easiest way to get Excel table into Access

Quote:
Originally Posted by MajP View Post
I am not familiar with SAP so no idea if there is a smarter direct way. From what I understan you download a report into Excel. I am going to assume that this excel is not always in the same location once downloaed. The report needs some level of cleaning before the records can be appended to existing table/s. Currently you are actually pasting rows from the report into an existing table/s. I am going to assume that all data from the excel goes into a single table and you do not have to create information into other related tables. You would like to users to be able to do this with some automation.
My guess on how I would do this.
1) Provide a command button to launch the file browser to find your SAP Excel Report
2) This will import into either a linked table or Temp Table. Preferably just a link is created.
3) Then run some queries to do the house cleaning or if needed do with a recordset. Rename columns if necessary; remove redundancy, clean-up names, etc.
4) Run the append query on the cleaned up data or if needed add records through a recordset.
5) Show some confirmation of what has been appended and allow the user to review those records in case any final cleanup is needed.
So all of this should happen from a button click. As long as there are good primary keys and or indices there should be no problem if records have already been updated you cannot append them again.
Yes you are exactly correct. Though, now that I see it laid out like that, it is still probably easiest for the user to just paste the table in there. Otherwise, when they download the file, they would then have to navigate to even figure out where the file is and then we have all of the cleaning of the spreadsheet anyways. At this point, it may just be fastest to paste the data in. Thanks again.
CNorway is offline   Reply With Quote
Old 06-15-2018, 03:49 AM   #10
rgwfly
Newly Registered User
 
Join Date: Jun 2016
Posts: 36
Thanks: 14
Thanked 2 Times in 2 Posts
rgwfly is on a distinguished road
Re: Easiest way to get Excel table into Access

I usually export the SAP data into a text file and then link to the database. I found sometimes the excel export from SAP caused errors. Then you can just overwrite the same file when you do future exports. There may be better ways doing it out there.
In SAP I use variants and defined layouts to ensure the data is consistent.
rgwfly is offline   Reply With Quote
The Following User Says Thank You to rgwfly For This Useful Post:
CNorway (06-15-2018)
Old 06-15-2018, 05:15 AM   #11
CNorway
Newly Registered User
 
Join Date: Feb 2018
Posts: 21
Thanks: 18
Thanked 0 Times in 0 Posts
CNorway is on a distinguished road
Re: Easiest way to get Excel table into Access

Quote:
Originally Posted by rgwfly View Post
I usually export the SAP data into a text file and then link to the database. I found sometimes the excel export from SAP caused errors. Then you can just overwrite the same file when you do future exports. There may be better ways doing it out there.
In SAP I use variants and defined layouts to ensure the data is consistent.
Thanks. I haven't tried text files. So, I will give it a try and see if that simplifies the process. Thanks.
CNorway is offline   Reply With Quote
Old 06-15-2018, 06:32 AM   #12
rgwfly
Newly Registered User
 
Join Date: Jun 2016
Posts: 36
Thanks: 14
Thanked 2 Times in 2 Posts
rgwfly is on a distinguished road
Re: Easiest way to get Excel table into Access

If you use the wizard just be careful in your data types. I usually use text type for everything except where calculations need to be performed. More than once I had to redo it because I imported one field as text and the other as a double only to get the dreaded mismatch data type in a query.

Last edited by rgwfly; 06-15-2018 at 09:16 AM.
rgwfly is offline   Reply With Quote
Old 06-15-2018, 09:12 AM   #13
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,123
Thanks: 13
Thanked 1,350 Times in 1,286 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: Easiest way to get Excel table into Access

@rgwfly,

The point of linking to the spreadsheet and using an append query to add the data to the permanent tables is that you get to do some validating and data conversion if necessary.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Easiest way to transfere table to other db. ggodwin Tables 2 02-10-2009 07:57 AM
Easiest way to filter data in a table? nechtress Tables 1 02-05-2008 07:14 PM
What is the easiest wayto do a daily calculation for a field in a table!! myme General 3 05-03-2004 01:46 PM
What is the easiest wayto do a daily calculation for a field in a table!! myme Queries 1 05-02-2004 11:20 PM
[SOLVED] World's Easiest MS-Access/VBA Question RookieIB Modules & VBA 3 11-01-2001 02:29 PM




All times are GMT -8. The time now is 10:59 PM.


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

Sponsored Links

How to advertise

Media Kit


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