I have an Excel with columns for Name, Address, and contact number of a person. I want this data to be transferred in Microsoft Access because I am making a program that will handle all this data and I’m using MS Access as the back end. Can someone guide me on how to import or transfer data from Excel to Access without manual encoding?
Importing data from Microsoft Excel is very easy. Just create a field name in Microsoft Access that is the same on the column heading in Excel.
Microsoft Office products or Windows product has its support to every program that they own. For example you want to Import data in notepad to excel, you just use the Import feature of excel and browse for any .txt file that will be imported. This is the same as Excel to Access; you just need to use the import feature of it.
1. Open Microsoft Access and create new blank database.
2. Right click it and click “Import”.
1. Select the Excel that you want to import.
2. Select the worksheet and click next.
5. The column heading in Excel will be the field names for your table in Access. Click next.
6. The data will be stored in a new table. Click next.
7. You can specify information about the fields. In this case, the fields are already specified. Click next.
8. Choose if you want to create a primary key that will serve as transaction number. Click the “No primary key” if you don’t want it. Click next.
9. Specify a table name; the default name is Sheet1 wherein it is the name of the Sheet in Excel. Click Finish.
10. If successful you will be prompted by the wizard that importing has finished.
Hope you are using MS Access 2007. Then these are the steps to import data from MS excel.
Create your Access database first. Get into Table Design view. Now click on the External Data tab.
You can see the Excel icon in Import group. Click on the Excel icon. Then you will be asked to specify the source of the data. If you cannot type the exact path and correct file name click on the Browse button. Now select the excel file. As you have not created the table earlier leave the option selected ‘Import source data into a new table in the current database and press Ok.
Then you will have to set some features of the table.