How to Import Excel and Text Files in Microsoft Access

Ms Access allows you to import files from other programs to facilitate the user. You can import spreadsheet in Access. Apart from importing excel into Access, it also allows users to import text files as well. In this tutorial, we will show you how to work with different options relating to import in Access.

Step # 1 – Selecting Excel
To import data from Microsoft Excel, go to the “external data” tab in Access and click on the “excel” button in the “import & link” group. Click on the “browse” button to locate the file that is to be imported into “Access”.
Choosing the excel button

Step # 2 – Selecting the file to be imported
Click on “file” and then click on the “open” button. This would be the excel file that would be imported to Access.
Picking the file which is to be used

Step # 3 – Selecting number of sheets
Once the file is selected click “next”, the dialogue box will ask you to select the sheet you want import if you are using more than one sheet.
Number of sheets to be used

Step # 4 – Selecting heading option
Click on the “first row contains column headings” if it is not there by default. You can always remove it if there no headings.
check marking the option

Step # 5 – Changing the data type
The next part of the dialogue box lets you select the data type in every field before it is imported into Access. You can always change the data type afterwards if you want to.
In this tutorial, we changed the “total” field data type from “text” to “currency”.
selecting data types for fields

Step # 6 – Setting the primary key
In the next part of the dialogue box, select the primary key or let Access decide it for you.
You can always select the primary key once the data has been imported but doing it now is recommended.
Assigning primary key

Step # 7 – Data imported into access
Change the name of the file and open it to view the Excel file imported into the database.
This is how you import excel in access.
excel data in access

Step # 8 – “Importing text file”
Next we will import a text file into Access. To import a text file the procedure is absolutely the same with just one change; instead of pressing the “excel” button, press the “text file” button. Click on the browse button and select the file that has to be imported in to access. For this purpose, select a “.CSV” format file.
Selecting a text file

Step # 9 – Naming the table
Now click on the Delimited option if it is not selected from before and then click on “next” to proceed forward. Now you can choose how you want the text to be separated.
Change the data type of the columns before moving ahead. Change the 4th column to “currency” from “long integer”. Once you are done, click “next”.
Again let Access decide the primary key for the table, then change its name and click “finish”.
Giving title to the table

Step # 10 – View of the text file
Double click on the table that you have created and view the data file that you have imported into Access. In this manner, you can import excel in Access.
Look of the text file