Buzz
This article provides a detailed guide on building a database using data from a Microsoft Excel spreadsheet. It explains how to import data directly into Access, Microsoft's database management software, or export Excel data into a format compatible with most database applications. Microsoft Access is a program included in the Microsoft Office suite and is exclusive to Windows.
Steps
Using Microsoft Access
Open Microsoft Access. It's the red app with the A icon. This action opens the Access home screen.
- Access is designed to work seamlessly with Excel and is integrated with Excel in the Microsoft Office Professional suite, available only on Windows.
Click on Blank database. This option is located at the top-left corner of the window.
- If you prefer using a different template to create an Access database, feel free to select the one that suits your needs.
Click on Create when prompted. This option is found at the bottom-right corner of the displayed window. The Access database will open after this action.
Click on the External Data tab. This tab is located in the menu bar at the top of the Access window.
Click on Saved Imports. You can find this option on the far-left side of the External Data toolbar. A menu will immediately appear here.
Select File. This is an option in the displayed menu. Clicking here will bring up another menu.
Click on Excel. This option is available in the recently displayed menu. The data import window will open after this click.
Click on Browse in the top-right corner of the window.
Select the Excel spreadsheet. Navigate to the folder where your Excel file is stored, then click on the spreadsheet you wish to open.
Click on Open. This option is located at the bottom-right corner of the window.
Choose the data transfer method. Click the radio button next to one of the following options:
- Import the source data into a new table in the current database - Select this if you've created a new database without tables or 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 - Choose this if you're using an existing database and want to add data to a table within it. By appending to an existing table, you can modify the information in Access.
- Link to the data source by creating a linked table - Select this to create a pathway in the database, allowing you to open the Excel database in Excel. With this method, you cannot edit the information in Access.
Click on OK. This button is located at the bottom of the window.
Select the worksheet. At the top of the window, click on the name of the worksheet you want to import from the selected Excel file.
- By default, each Excel file contains three worksheets named "Sheet 1", "Sheet 2", and "Sheet 3". You can only import one worksheet at a time; if all three worksheets contain data, you'll need to import one, then return to the "External Data" tab and repeat the process for the remaining sheets.
- You can delete, add, or rename these worksheets in Excel, and any changes you make will be reflected in the Access database.
Click on Next. This button is located at the bottom-right corner of the window.
Enable column headers. Check the box labeled "First Row Contains Column Headings" if your Excel worksheet includes headers in the first row (e.g., row A).
- Leave this box unchecked if you want Access to generate column headers for you.
Click on Next.
Modify columns and fields of the spreadsheet if necessary. If you wish to import all fields from the spreadsheet without making any changes, you can skip this step:
- To edit a field, click on the column header you want to modify, then adjust the field name, data type, and/or decide whether to include it in the index.
- If you prefer not to import a specific field, check the box labeled "Do Not Import Field (Skip)".
Click on Next.
Set a primary key for the database. For optimal results, retain the default settings and allow Access to assign the primary key automatically.
- Alternatively, you can specify your own primary key by selecting "Choose my own primary key" and entering the relevant information in the adjacent field, or opt for "No primary key", though this is not recommended.
Click on Next.
Add a name. Enter a name for the spreadsheet in the "Import to Table" field.
- Skip this step if you prefer to use the default database name.
Click on Finish. This option is located at the bottom-right corner of the window.
Click on Close. This option is found at the bottom-right corner of the window. It closes the data import window and finalizes your database creation.
- You can first check the box labeled "Save import steps" if you want Access to remember the settings for this database.
Using third-party database software
Open the Excel document. Double-click the Excel file you want to convert into a database.
- If you haven't created the document yet, open Excel, click on Blank workbook, and create the document before proceeding.
Click on File. This option is located in the menu bar at the top of the Excel window (on Windows) or at the top of the screen (on Mac).
Click on Save As. You'll find this option in the File menu.
Double-click on This PC. This option is located in the middle of the page.
- Skip this step on Mac.
Select a file format. Click the dropdown menu labeled "Save as type" on Windows or "File Format" on Mac, then choose one of the following options:
- If you're using a desktop database application, select the .CSV (comma-separated values) format.
- For web-based database applications, choose the .XML format.
- If your Excel document doesn't contain XML data, you won't be able to select XML.
Click on Save. This button is located at the bottom of the window. It saves your document using the selected settings.
Create a new database in your database application. The process will vary depending on the application you're using, but typically you'll open the app, click on New or File > New, and follow the on-screen instructions.
Look for the Import… button. This option is usually found in the File section, but its location may vary depending on the application.
Select the Excel file. Locate and double-click the file you exported from Excel.
Follow the database application's instructions to import the data.
Save the database. You can usually access the "Save" menu by pressing Ctrl+S (on Windows) or ⌘ Command+S (on Mac).
Tips
- You can use online database websites to create a database for free, but you'll need to create an account to access these services.
- If you don't have full-featured database software, you'll require another program to open database files on a Windows or Mac computer.
Warnings
- Transferring Excel data to a database isn't always a seamless process.
