Easiest way to get Excel table into Access (1 Viewer)

CNorway

Registered User.
Local time
Today, 15:41
Joined
Feb 13, 2018
Messages
33
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
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:41
Joined
May 7, 2009
Messages
19,169
Use the import wizzard.
 

CNorway

Registered User.
Local time
Today, 15:41
Joined
Feb 13, 2018
Messages
33
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
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:41
Joined
May 7, 2009
Messages
19,169
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.
 

isladogs

MVP / VIP
Local time
Today, 22:41
Joined
Jan 14, 2017
Messages
18,186
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
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:41
Joined
May 21, 2018
Messages
8,463
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.
 

CNorway

Registered User.
Local time
Today, 15:41
Joined
Feb 13, 2018
Messages
33
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
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:41
Joined
May 21, 2018
Messages
8,463
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.
 

CNorway

Registered User.
Local time
Today, 15:41
Joined
Feb 13, 2018
Messages
33
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.
 

rgwfly

Registered User.
Local time
Today, 15:41
Joined
Jun 7, 2016
Messages
49
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.
 

CNorway

Registered User.
Local time
Today, 15:41
Joined
Feb 13, 2018
Messages
33
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.
 

rgwfly

Registered User.
Local time
Today, 15:41
Joined
Jun 7, 2016
Messages
49
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:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:41
Joined
Feb 19, 2002
Messages
42,976
@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.
 

Users who are viewing this thread

Top Bottom