Steps
In Microsoft Access
- 1Create a spreadsheet in excel.
- Save the spreadsheet to a location on your hard drive. You can also use an existing excel spreadsheet.
- Save the spreadsheet to a location on your hard drive. You can also use an existing excel spreadsheet.
- 2Launch Microsoft Access. Open an existing Microsoft Access database or create a new, blank database.
- Microsoft Access is designed for use with Microsoft Excel and comes bundled with Excel in Microsoft Office Professional.
- You can also purchase Access alone to discover how to create a database from an Excel spreadsheet
- Microsoft Access is designed for use with Microsoft Excel and comes bundled with Excel in Microsoft Office Professional.
- 3Click the "External Data" tab and select the "Excel" icon on the ribbon.
- 4Click the "Browse" button to navigate to the location of the Excel spreadsheet.
- Alternatively, you can type the file path into the field, for example: c:/users/<username>/documents/addresses.xls (or addresses.xlsx).
- Alternatively, you can type the file path into the field, for example: c:/users/<username>/documents/addresses.xls (or addresses.xlsx).
- 5Specify how you would like the information to transfer to the database by selecting one of the following options:
- Import the source data into a new table in the current database: Use this option if you are using a brand new database with no tables or if you want to add a new table to an existing database. By creating a new table you can edit the information in Access.
- Append a copy of the records to the table: Use this option if you are using an existing database and want to add the data to one of the tables in the database. By appending an existing table, you can edit the information in Access.
- Link to the data source by creating a linked table: Use this option to create a hyperlink in the database, which will open the excel database in excel. With this method, you cannot edit the information in Access.
- Click OK after you have selected your transfer method.
- Import the source data into a new table in the current database: Use this option if you are using a brand new database with no tables or if you want to add a new table to an existing database. By creating a new table you can edit the information in Access.
- 6Select the sheet you want to import from the list.
- By default, Excel creates workbooks with three spreadsheets labeled "Sheet 1," "Sheet 2," and "Sheet 3." You can delete, add and edit the names of these sheets in Excel, and whatever changes you make will show up in Access.
- You can only transfer one sheet at a time. If you have information on all three sheets, you must complete the transfer with one sheet then go back to the "External Data" tab and repeat all the steps for each remaining sheet.
- Click "Next" after you have selected the spreadsheet.
- By default, Excel creates workbooks with three spreadsheets labeled "Sheet 1," "Sheet 2," and "Sheet 3." You can delete, add and edit the names of these sheets in Excel, and whatever changes you make will show up in Access.
- 7Leave the checkmark in "First Row Contains Column Headings," if that is the case. If not, remove the checkmark and Access will create its own column headings.
- Click "Next."
- Click "Next."
- 8Edit the field type, if desired, or indicate if you wish to import the field.
- If you are importing all the fields from the spreadsheet, as-is, do not make any changes at this screen and click "Next."
- If you wish to change the nature of one of the fields, click on the column header you wish to change and edit the name of the field, the data type, or whether or not it is indexed. Then click "Next."
- If you want to skip that field, put a check mark next to "Do Not Import Field (Skip)," then click "Next."
- If you are importing all the fields from the spreadsheet, as-is, do not make any changes at this screen and click "Next."
- 9Set the primary key for the database.
- For the best results, let Access set the key. You can also set your own by typing text into the field next to that option, or you can select "No primary key" which is not recommended.
- Click "Next."
- For the best results, let Access set the key. You can also set your own by typing text into the field next to that option, or you can select "No primary key" which is not recommended.
- 10Type the name of the sheet in the "Import to Table" field, or leave it set to the default name.
- Click "Finish" and put a checkmark in "Save These Import Steps" to use the same steps on future imports.
- Click "Close" to create your database.
- Click "Finish" and put a checkmark in "Save These Import Steps" to use the same steps on future imports.
In Third-Party Database Software
- 1Create your spreadsheet in Excel. Save the document as an Excel workbook in a file location that you can easily find later.
- You want to save the original as an excel file to have a master copy on-hand.
- You want to save the original as an excel file to have a master copy on-hand.
- 2Click on "File" and select "Save As." Click the "Save as Type" drop down and select the file format that your database program can translate.
- For example, some programs use the CSV (comma separated values) format, while web-based applications might use XML. Consult the manual for your database program to determine the correct format.
- For example, some programs use the CSV (comma separated values) format, while web-based applications might use XML. Consult the manual for your database program to determine the correct format.
- 3Close out of Excel and launch your database program.
- 4Import the Excel spreadsheet into your database program per the instructions in your manual.
