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”.
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.
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.
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.
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”.
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.
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.
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.
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”.
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.