How to Create a Database from an Excel Spreadsheet

Table of contents:

How to Create a Database from an Excel Spreadsheet
How to Create a Database from an Excel Spreadsheet
Anonim

This article explains how to create an Access database using a Microsoft Excel sheet as a data source. Access is the database creation and management software included in the Microsoft Office suite of programs. Alternatively, you can export the data from Excel in a format that allows it to be imported into the most famous and popular database engines. Microsoft Access is a program available only for Windows platforms.

Steps

Method 1 of 2: Use Microsoft Access

Create a Database from an Excel Spreadsheet Step 1
Create a Database from an Excel Spreadsheet Step 1

Step 1. Launch Microsoft Access

It features a red icon in the shape of TO. The main page of the program will be displayed, where you can choose which database model to create.

Access is designed to integrate completely and easily with all products included in the Microsoft Office Professional package, especially with Excel, and is available only for Windows platforms

Create a Database from an Excel Spreadsheet Step 2
Create a Database from an Excel Spreadsheet Step 2

Step 2. Click on Blank Database

This option is located in the upper left of the program window.

If you need to use another database model offered by Access, select the one you are interested in

Create a Database from an Excel Spreadsheet Step 3
Create a Database from an Excel Spreadsheet Step 3

Step 3. Click the Create button when prompted

It is located in the lower right part of the pop-up window that appeared. An empty Access database will be created.

Create a Database from an Excel Spreadsheet Step 4
Create a Database from an Excel Spreadsheet Step 4

Step 4. Click the External Data tab

It is listed at the top of the Access program window.

Create a Database from an Excel Spreadsheet Step 5
Create a Database from an Excel Spreadsheet Step 5

Step 5. Click the New Data Source button

It is located on the left side of the tab toolbar External data. A drop-down menu will appear.

Create a Database from an Excel Spreadsheet Step 6
Create a Database from an Excel Spreadsheet Step 6

Step 6. Select the File item

It is one of the options listed in the drop-down menu that appeared. A submenu will appear next to the first one.

Create a Database from an Excel Spreadsheet Step 7
Create a Database from an Excel Spreadsheet Step 7

Step 7. Click on Excel

It is one of the items in the new menu that appeared. The dialog box for importing Excel data into the database will be displayed.

Create a Database from an Excel Spreadsheet Step 8
Create a Database from an Excel Spreadsheet Step 8

Step 8. Click the Browse button

It is located in the upper right part of the window.

Create a Database from an Excel Spreadsheet Step 9
Create a Database from an Excel Spreadsheet Step 9

Step 9. Select the Excel sheet to use as the data source

Navigate to the folder where it is stored, then click the corresponding icon to select it.

Create a Database from an Excel Spreadsheet Step 10
Create a Database from an Excel Spreadsheet Step 10

Step 10. Click the Open button

It is located in the lower right corner of the window.

Create a Database from an Excel Spreadsheet Step 11
Create a Database from an Excel Spreadsheet Step 11

Step 11. Specify how the data should be imported into the database

Choose one of the following options by clicking the corresponding radio button:

  • Import source data into a new table in the current database- Choose this option if you have created a database without tables or if you want to add a new table to an existing database. By creating a new table you will be able to edit the data directly within Access.
  • Append a copy of the records to the '' 'table: choose this option if you are using an existing database and want the records to be automatically added to a database table. By appending the data to an existing table, you can edit it directly within Access.

  • Connect to the data source by creating a linked table '' ': choose this option to create a direct link to external data. In this case the Excel sheet will be opened within Microsoft Excel. Using this import option, the data cannot be edited within Access.

    Create a Database from an Excel Spreadsheet Step 12
    Create a Database from an Excel Spreadsheet Step 12

    Step 12. Click the OK button

    It is located at the bottom of the window.

    Step 13. Select a workbook sheet

    Click the sheet name of the Excel file you want to import, visible at the top of the window.

    • By default Excel creates workbooks consisting of three sheets named "Sheet 1", "Sheet 2" and "Sheet 3". You can only import data from one sheet at a time. If the data is present in all three sheets you will have to import the data of the first and then repeat the procedure for the other two always using the "External data" tab.
    • In this case, you can delete, add or change the name of the sheets of an Excel workbook. Any changes you follow will be reflected in the Access database.
    Create a Database from an Excel Spreadsheet Step 14
    Create a Database from an Excel Spreadsheet Step 14

    Step 14. Click the Next button

    It is located in the lower right corner of the window.

    Create a Database from an Excel Spreadsheet Step 15
    Create a Database from an Excel Spreadsheet Step 15

    Step 15. Enable detection of column headers

    Select the "Column headings in first row" checkbox if the names of the columns that make up the selected Excel sheet are stored in the first row (for example the row TO).

    Uncheck the indicated check button if you want Access to create the column headings

    Create a Database from an Excel Spreadsheet Step 16
    Create a Database from an Excel Spreadsheet Step 16

    Step 16. Click the Next button

    Create a Database from an Excel Spreadsheet Step 17
    Create a Database from an Excel Spreadsheet Step 17

    Step 17. If necessary, edit the sheet columns and database fields

    If you need to import all the fields of the Excel sheet without any modification, skip this step. If not, follow the instructions below:

    • To change the information of a field, click the corresponding column header. In this way you will have the possibility to change its name, the type of data it contains and define it or not as an index of the table.
    • If you don't need to import a specific field, select the "Don't import field (skip)" checkbox.
    Create a Database from an Excel Spreadsheet Step 18
    Create a Database from an Excel Spreadsheet Step 18

    Step 18. Click the Next button

    Create a Database from an Excel Spreadsheet Step 19
    Create a Database from an Excel Spreadsheet Step 19

    Step 19. Set the primary key of the database

    For best results, do not change the default settings for this aspect of the database, so that Access chooses the primary key.

    You can also set the primary key of the table manually, by choosing the "User-defined primary key" option and selecting the name of the corresponding field using the menu on the right of the indicated item. If the table does not need a primary key, choose the "No Primary Key" option (in the context of databases this is not the recommended choice)

    Create a Database from an Excel Spreadsheet Step 20
    Create a Database from an Excel Spreadsheet Step 20

    Step 20. Click the Next button

    Create a Database from an Excel Spreadsheet Step 21
    Create a Database from an Excel Spreadsheet Step 21

    Step 21. Add a table name to import data into using the "Import into table: text field:

    ".

    Skip this step to have the database use the default name

    Create a Database from an Excel Spreadsheet Step 22
    Create a Database from an Excel Spreadsheet Step 22

    Step 22. Click the Finish button

    It is located in the lower right part of the window.

    Create a Database from an Excel Spreadsheet Step 23
    Create a Database from an Excel Spreadsheet Step 23

    Step 23. Click the Close button

    It is located in the lower right corner of the data import window. This will close the latter and the database table will be created as specified.

    You can select the "Save Import Steps" checkbox to have Access remember the import procedure you used, so that you can reuse it in the future

    Method 2 of 2: Using Third Party Database Software

    Create a Database from an Excel Spreadsheet Step 24
    Create a Database from an Excel Spreadsheet Step 24

    Step 1. Open the Excel document that contains the data to import into the database

    Double-click the icon of the Excel file you have chosen to use.

    If the Excel file has not yet been created, start the program, click the item Blank workbook, then create the dataset before continuing.

    Create a Database from an Excel Spreadsheet Step 25
    Create a Database from an Excel Spreadsheet Step 25

    Step 2. Click the File menu

    It is located on the Excel menu bar at the top of the program window (on Windows) or the screen (on Mac).

    Create a Database from an Excel Spreadsheet Step 26
    Create a Database from an Excel Spreadsheet Step 26

    Step 3. Click the Save As item

    It is one of the options listed in the menu File.

    Create a Database from an Excel Spreadsheet Step 27
    Create a Database from an Excel Spreadsheet Step 27

    Step 4. Double-click the This PC entry

    It is visible in the center of the dialog box that appeared.

    If you are using a Mac, skip this step

    Create a Database from an Excel Spreadsheet Step 28
    Create a Database from an Excel Spreadsheet Step 28

    Step 5. Choose the file format

    Click the "Save As" (on Windows) or "File Format" (on Mac) menu, then select one of the following options:

    • If you are using an application that uses a database installed on your computer, click the format CSV (from the English "comma separated values").

    • If you are using a web application choose the format XML.

      In this case, if the Excel document does not contain any XML data, you will not be able to save it in the indicated format

    Create a Database from an Excel Spreadsheet Step 29
    Create a Database from an Excel Spreadsheet Step 29

    Step 6. Click the Save button

    It is located at the bottom of the window. The document you created will be saved to disk following the specifications provided.

    Create a Database from an Excel Spreadsheet Step 30
    Create a Database from an Excel Spreadsheet Step 30

    Step 7. Create a new database using the software you have selected

    The procedure to follow varies according to the program you are using, however normally you need to start the software, click on the item New one (or open the menu File and select the option New one) and follow the instructions that will be displayed on the screen.

    Create a Database from an Excel Spreadsheet Step 31
    Create a Database from an Excel Spreadsheet Step 31

    Step 8. Locate the Import… option

    It is usually inserted in the menu File, but the correct position varies from program to program.

    Create a Database from an Excel Spreadsheet Step 32
    Create a Database from an Excel Spreadsheet Step 32

    Step 9. Select the Excel file to use as the data source

    Navigate to the folder where it is stored and double-click the icon of the file you created in the previous steps.

    Create a Database from an Excel Spreadsheet Step 33
    Create a Database from an Excel Spreadsheet Step 33

    Step 10. Follow the instructions given to you by the database to be able to import the data correctly

    Create a Database from an Excel Spreadsheet Step 34
    Create a Database from an Excel Spreadsheet Step 34

    Step 11. Save the database

    Normally you can press the key combination Ctrl + S (on Windows) or ⌘ Command + S (on Mac) to bring up the "Save" dialog.

    Advice

    • You can find several websites online that allow you to create a database, however in most cases you will need to register by creating an account.
    • If you don't have full database software available, you may need to use a dedicated program to be able to open database files on a Windows or Mac computer.

Recommended:

How to Create a Customer Database: 8 Steps

How to Create a Customer Database: 8 Steps

A customer database can save you time and effort by allowing you to record very useful business information for use perhaps in market research, customer support and accounting. Although there is a range of professional products on the market for creating and organizing databases easily, we recommend that you read this guide to learn the basics.

How to Create an Inventory Database on Access

How to Create an Inventory Database on Access

Microsoft Access offers a versatile method for creating inventory valuation tools by creating a database that allows you to instantly view inventory numbers. The program's internal documentation, such as tutorials, can help you create a database with Access, but you still need to know a few basic steps.

How to Create a Database in MySQL (with Images)

How to Create a Database in MySQL (with Images)

This article shows you how to create a database with MySQL. In order to create a new database, use the "MySQL" command console and enter all the necessary commands one at a time. In this case the database engine, ie the DBMS, must be running.

How to Create a Graph Using an Electronic Spreadsheet

How to Create a Graph Using an Electronic Spreadsheet

This tutorial shows the steps required to quickly create a chart in Microsoft Excel. Steps Step 1. Insert the table containing the data to be represented in the worksheet Use the following format: Cell '1-a' will contain the header of the abscissa data (x axis).

How to Create a Budget on a Spreadsheet

How to Create a Budget on a Spreadsheet

You can use a spreadsheet to compile your budget and better control your expenses. You will be surprised how easy it is to manage your finances with this tool. You can use it as a guide to paying bills, saving money for retirement or major purchases, or simply getting to your next paycheck without incurring debt.