Import from Excel into Access

hushens10

Access Naive - Help Needed

I am using Access 2003 and Excel 2003. My goal is to create a database for a school where each student's testing information can be stored and organized so that it can be pulled. Each teacher currently has their own Excel file, into which they have entered their student's data. However, for the purposes of other departments, they would like a database file where information can be culled and queries can be run (thus the need for Access).

I have multiple excel files, each with multiple worksheets. I would like to create an Access database using the information in the various Excel files (all of the files have the same column headings in each of the worksheet.. How do I make this happen? I am sure there is an easier way than I am trying. I do not know technical computer language, but I can follow directions, so ANY help would be appreciated.

Is there a way for multiple worksheet's and multiple file's data to be imported into an Access database?
Thank you!

Aug 20 '08 #1

NeoPa

I've changed the title to something more sensible (conforms to site rules).

It's hard to answer the question though in this form.

What have you tried?

Is all the data in the same format?

Does the data include something that identifies its source (If the data comes from workbook A & sheet 3 are A & 3 in the data anywhere)?

Aug 20 '08 #2

NeoPa

The simplest concept (assuming no complications) is to copy and paste the data in after preparing the table that you want to store it all in.

Aug 20 '08 #3

isladogs

I have written many Access applications for schools and as part of these regularly import data files from other sources including CSV and Excel files.

The approach that I use and recommend you do as well is to
1. Add your Excel files as linked tables
2. Make a backup then import the required data to 'temporary' buffer tables.
3. Do any necessary processing of the data before importing these into your 'final' tables stored in the backend database.
4. Empty your buffer tables

In order to avoid bloating your main BE database by repeatedly populating temporary tables, I strongly recommend that the buffer tables are stored in a separate 'side-end' database

Aug 2 '20 #4

Naheedmir

Numerous ways are there to do it, but the simplest one is to copy and paste the data manually as all other processes are longer.

Aug 6 '20 #5

ADezii

You can automate the entire process of Importing all Excel Spreadsheets, with varying numbers of Worksheets, into an Access Database with Code. Assuming the Spreadsheets are somewhat consistent as you state, you would append each to a MASTER Table which should be a relatively simple operation.

The downsides are that it would involve a small amount of Automation Code and you must ensure the uniqueness, as far as names go, of each Worksheet being Imported.

You would basically select a File(s) via the Office FileDialog and the Code would do the rest. The process would be completely transparent.

This is simply another Option that you may wish to consider.

Aug 14 '20 #6