Create a Database from an Excel Spreadsheet

Microsoft Excel is a spreadsheet program that allows you to list and categorize information across several sheets within a document called a workbook. In addition to creating lists, you can also make charts and graphs from the data in the sheet. However, for more advanced data functions you need to import the Excel spreadsheet into desktop database builders like Access, online database builders or into a third-party database program.

Steps

In Microsoft Access

  1. Create a spreadsheet in excel.
    • Save the spreadsheet to a location on your hard drive. You can also use an existing excel spreadsheet.
  2. Launch 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
  3. Click the "External Data" tab and select the "Excel" icon on the ribbon.
  4. Click 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).
  5. Specify 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.
  6. Select 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.
  7. Leave 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."
  8. Edit 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."
  9. Set 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."
  10. Type 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.

In Ragic spreadsheet database software

  1. Create a spreadsheet in excel.
  2. Go to your Ragic account (if you don't have one, get one for free), and click on the create new database sheet button at the top right to create a new database sheet.
  3. Enter the name of your database form, and remember to check that you want to "Create new sheet with my Excel file"
  4. Upload your file. Ragic supports .xls .xlsx and .csv files.
  5. Determine if your first row is the header. If so, Ragic will automatically determine how the data in this row will be mapped to your fields for the next step.
  6. Ragic will automatically determine the type of each fields, you can change them if you don't like the system defaults.
  7. Just click on import and Ragic will create your database.
  8. Now you have created an online database right out of your Excel spreadsheet on Ragic.
  9. You can use the top full text search engine to search for records.
  10. Or use the search sidebar to search for records with any combination of fields.

In Third-Party Database Software

  1. Create 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.
  2. Click 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.
  3. Close out of Excel and launch your database program.
  4. Import the Excel spreadsheet into your database program per the instructions in your manual.

Related Articles

  • Change the Default File Extension from .Xlsx to .Xls on Microsoft Excel 2007

Sources and Citations

Retrieved from "https://kipkis.com/index.php?title=Create_a_Database_from_an_Excel_Spreadsheet&oldid=316409"