<

Watch import an Excel spreadsheet into an Access database

Posted on
6,122 Points
22 Views
1 Endorsement
Last Modified:
Published
Experience Level: Beginner
14:16
crystal (strive4peace) - Microsoft MVP, Access
Love empowering people by teaching and helping them develop applications.
Learn how to Import data from Excel into a Microsoft Access database using the wizard. Change data types and modify table design. Add another field and create an update query to fill it out. Also see a neat trick to copy the full path and filename of the active workbook in Excel.

Video Steps

1. To import data from Excel using the wizard, click the Excel icon in the Import & Link group of the EXTERNAL DATA ribbon tab


      (You might have to click the drop-down arrow for more choices to see the Excel icon.)

2. Choose the Excel file you want to import from

3. Choose the worksheet or range

4. Verify that the first row contains the column headings

5. change data types

6. let Access add a primary key


this will be an AutoNumber field, which is a good idea to have in every table.

7. change the table name to be logical, and preface with 'import_'

8. modify the table design (to add a field for date)


      In this example, we just added another field. In reality, you would also change Data Type and Size, remove tag-along properties like Format, set text to have Unicode Compression, and more

9. make update query to create values in the new field

10. save the query


      (so you can modify it for another worksheet of data)
1
2 Comments
LVL 53

Expert Comment

by:Subodh Tiwari (Neeraj)
Very helpful tutorial Crystal! 👍
1
LVL 26
thank you, Subodh!
1
To rank something isn't that difficult. Basically, it is just to sort on the values to rank - points, goals, sales, citizens, whatever - in descending order or (typically for time) ascending order. But how to rank dupes? Five methods exist, and th…
Obtaining and using time zones in Windows is not simple. It involves API calls and reading the Registry. Some information is localised, and some is not. Here you will find a complete set of functions that wraps the difficult steps, eases common task…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month