# Bulk upload multiple Excel files as tables at once



## Stoolen

I'm new to Access so any help would be appreciated. I have multiple Excel spreadsheets  (seperate files)that I want to upload into Access as separate tables. Is there a way I can bulk upload them all at once? If not, if I'm able to make them all 1 file but seperate tabs, is it possible to upload the file into access and easily make each tab its own table?


----------



## Micron

First, xl sheets pretty much NEVER make for good database tables. Sheets are wide (columnar) while tables should be tall (rows/records) where related data is held in other tables.

You can use transferspreadsheet function to copy sheets into Access as tables, but only 1 at a time. To do a bulk copy would require code that loops thru wb sheets. Or you can link sheets as tables (ribbon>external data>new data source...

Either way, it is highly likely that you'd need to move data into properly related tables from the transfers/linked sheets. If you have not learned db normalization, I strongly suggest you do so, otherwise you will continually encounter roadblocks to getting at data.


----------



## Stoolen

Micron said:


> First, xl sheets pretty much NEVER make for good database tables. Sheets are wide (columnar) while tables should be tall (rows/records) where related data is held in other tables.
> 
> You can use transferspreadsheet function to copy sheets into Access as tables, but only 1 at a time. To do a bulk copy would require code that loops thru wb sheets. Or you can link sheets as tables (ribbon>external data>new data source...
> 
> Either way, it is highly likely that you'd need to move data into properly related tables from the transfers/linked sheets. If you have not learned db normalization, I strongly suggest you do so, otherwise you will continually encounter roadblocks to getting at data.


Thank you. would it be better if i just created the tables in Access then copy and paste the data from Excel into the Access tables instead of uploading the excels as tables?


----------



## Micron

I'll let you decide on creating them in Access (except that copying/pasting into Access tables is pretty much a non-starter).
Do yourself a big favour if you have not already learned this:

Normalization Parts I, II, III, IV, and V





						What Is Normalization, Part I
					

Thoughts, opinions, samples, tips, and tricks about Microsoft Access




					rogersaccessblog.blogspot.com
				



and/or





						Database Normalization – Holowczak.com Tutorials
					






					holowczak.com
				




Entity-Relationship Diagramming: Part I, II, III and IV





						Entity-Relationship Diagramming: Part I
					

Thoughts, opinions, samples, tips, and tricks about Microsoft Access




					rogersaccessblog.blogspot.com


----------

