software

How to import and link data from excel workbook to access database? Step by step guide

Another advantage that this Microsoft program offers to its users is that it allows them to import and link data from an Excel workbook to an Access database in a very simple way. It must be taken into account that both programs are used for data calculations, so perhaps on many occasions it is necessary to transfer information from one program to another.

This procedure can be carried out in different ways, either by copying the data from an open spreadsheet and pasting it into the Access spreadsheet, importing a new table, or creating a link from an Access database. It must be remembered that when data is imported, the user has the opportunity to manipulate them either by  editing them , while the linking process only allows them to be read.

In accordance with all this and the importance of carrying out this procedure, here we are going to show you how you can start importing and linking data from an Excel workbook to an Access database quickly and easily. To do this, follow in detail everything that we will teach you below.

Considerations before you begin What should I keep in mind when linking an Excel workbook with Access?

Before starting this procedure it is very important to take into account some considerations. In general, when this procedure is carried out, different types of scenarios can be presented that are very important to know, as well as taking into account some considerations if it is the first time that you are going to import data from Excel to Access.

To do this, follow each of these points that we will teach you below:

Possible common scenarios for importing data from Microsoft Excel to Microsoft Access

  • It is possible that a user has been working with Excel for a long time, but in order to continue advancing and increasing their knowledge they need to use Access in order to work with databases. However, you want to move your spreadsheets to an existing or new Access database or databases.
  • It is also possible for a department or workgroup to constantly work with Access, but receive information from some clients through Excel. This means that the user wants to move the Excel spreadsheets to an Access database, for this he must start importing each of these sheets as he receives them.
  • On many occasions Access is used to manage different data, but mostly the data that is received is through Excel spreadsheets . In this case, the user needs to speed up the import procedure in order to guarantee that all data can be managed at the specified time.

If this is the first time the user has imported data from Excel

  • It is important to note that there is no way to save an Excel workbook as an Access database . In this case, Excel does not offer any functionality that allows you to create an Access database from Microsoft Excel data.
  • When you open a Microsoft Excel workbook in Access, it creates a link to the workbook instead of importing the data. It should be mentioned that linking to a book is considered substantial and different from the import process.

What kind of data can I import from an Excel workbook to an Access database?

When you want to import data from an Excel workbook to an Access database, you must take into account what you want to save or import there, whether all the information contained in the workbook or only a part of it, you must also specify whether you want everything to appear in a single database or in multiple Access databases.

Keep in mind that here you can transfer any type of data that you have in your spreadsheets, what you cannot pass are the images, other graphic files that you have included there. When importing this data, Access creates a backup copy of the data either in an existing table or a new table, in this process the content of the spreadsheets is never altered .

Learn step by step how to import and link an Excel workbook with an Access database

Considering everything mentioned previously in the post, the following will explain how you can start preparing and executing the import and linking of an Excel workbook to Microsoft Access.

For this, it is necessary that you follow each of the steps in detail that we are going to explain below:

Importing data into Excel

The first method that we are going to teach you next is about importing data into Excel, it is important to bear in mind that the import and linking process are totally different .

Therefore, follow each of the following steps to complete this procedure:

Prepare spreadsheet

At this point the first thing you should do is find the source file and select the spreadsheet that contains the data you want to import into Access. In the case that you don’t want to transfer all the content that appears there, but a part of it, then you can start defining a named range that includes only the cells that you really want to import.

These ranges can be defined as follows:

  • The first thing to do is go to Excel and there open the spreadsheet that contains the information you want to import.
  • The next thing will be to select the range that contains the data you want to import.
  • Now you must click the right button of the mouse on the selected range and there select the option “Assign name to a range” or “Define name”.
  • Finally, in the “New name” window you must specify a name for the range in the “Name” option and then click “OK”.

Here you must bear in mind that you can only import one spreadsheet at a time during the import procedure, this means that to import several Excel sheets it will be necessary to repeat this procedure for each of the sheets.

You need to carefully review the source data and take an action as described below:

  • Number of columns: Keep in mind that the number of source column to be imported cannot be greater than 255 fields in a table.
  • Table format: It is important that the cells are in table format, in the case that the spreadsheet includes combined cells, then the content of the cell will be placed in the field that corresponds to the leftmost column and the others fields there remain blank.
  • Skip columns and rows: In general, you only include the columns and rows that you want to import into the spreadsheet. For “Rows” you cannot filter or omit rows during the import procedure, while for “Columns” they cannot be omitted during the operation if you choose to add the data to an existing table.
  • Blank columns and cells: You can delete all unnecessary rows and columns in the spreadsheet. If it contains blank cells you can add the necessary data.
  • Error values: It is possible that only one or more cells in the table contain error values, some of the most frequent are #DIV and #NUM, it is essential that these are corrected before starting to execute said action.

After defining all this, the following will be to close the source workbook if it is open, in the event that it remains open during the execution of the process, errors may occur in the conversion of the data .

Prepare database

The next thing will be to prepare the database, for this you must open the Access database where you want all the imported data to be saved . At this point it is necessary to make sure that the database is read-only and that it contains the permissions to make changes to the database . Here you can store the data in an existing database or create a new database.

In the event that you want to save it in a new one, simply go to the “File” tab, there you select “New” and then you will have your new blank database on the screen. Before starting this import process, you need to choose whether you want to save the content in a new or existing table .

For this, these two points are taken into account:

  • Create a new table: A picking data into a new table, Access will to create a table and add each of the imported data to that table. If there is a table with the specified name, then the program overwrites the contents of the table with the data from the import.
  • Add to an existing table: Now if you have chosen to add to an existing one, then the rows of the Excel spreadsheets are appended to the specified table. It is also important that at this point you have to be very careful and detailed, since most errors occur here, because the data does not match the structure, configuration, among others.

Once all this has been defined, the next thing will be to execute the import operation .

To do this you must follow the next step:

Start the import procedure

Here you must take into account the location of the wizard for importing or linking, it will vary depending on the version of Access you are using.

In this case you must choose the following path according to the version you are using:

  • For users who are using the most recent versions such as Access 2019 or Microsoft 365 Access subscription, it will be necessary to go to the “External data” tab and in the “Import & link” section you must select the option to “New data source” and then “From file” and finally “Excel”.
  • For users who are using the versions of Access 2010, Access 2013 and Access 2016, they should go to the “External data” tab , and in the “Import and link” section select the “Excel” option .

Now in the window “Obtain external data – Excel spreadsheet” you must select the “File name” and there specify the name of the Excel file that contains the data to be imported. You can also click “Browse” and then in the dialog box open the corresponding file. It is also important that you specify how you want the imported data to be saved .

Now to be able to store the data in a table, you must select the option “Import the data source in a new table of the current database”. There the user must give that table a name.

In the event that you want to append the data of an existing table, then you must select the option “Append a copy of the table’s records” and then select one of the tables in the drop-down list . In the event that the database does not contain any table, this option will not be available. And finally you must click on “OK” .

With the import spreadsheet wizard

Finally, with the import wizard, the import can be carried out.

To do this, follow these steps:

  • Once the wizard has started, on the first page you must select the spreadsheet that contains the data you want to import, then click “Next”.
  • Now on the second page of the import wizard you must select the option “Show spreadsheets” or “Show ranges with names”, here you must select the option as appropriate and then click “Next”.
  • If the first row of the spreadsheet contains the field names, then you must select “The first column contains column heading” and then select the “Next” button.
  • After this the wizard will ask you to review the properties of the fields, here you must click on a column at the bottom of the page so that you can see the properties of the fields . You can also review and change the name and data type of the destination field .
  • On the next screen you must specify a primary key for the table, here you can select the option “Allow Access to add the primary key” this will cause the program to add an  autonumber  field as the first field of the destination table and then fill it automatically with the unique identifier values and then click “Next” again.
  • Finally, in the last screen of the import wizard you must specify the name for the destination table. In the section “Import to the table” you must write a name for the table. In the case that the table already exists, then Access shows a message asking if you want to overwrite the existing content in the table, you can click on the “Yes” option to continue or “No” to specify another name and then click click on “Finish”.
  • After this, the import of the data will begin , once the process is finished you must carefully check that all the data has been transferred correctly .

Link data from Excel to Access

Importing data is one of the most common processes when you want to see data from Excel in Microsoft Access, however, you can also carry out what is the data binding procedure.

For this, it is necessary that you follow each of these steps:

Prepare Microsoft Excel data

In this case, the first thing you should do is look for the Excel file and the spreadsheet where the content you want to link is located. You may not want to link all the content that appears there but only a part of it. So for these cases it is necessary that you create a named range in Excel, keep in mind that this procedure is optional and is only carried out if you do not want to link all the information contained in the spreadsheet.

Let’s see the steps to follow:

  • Here you must go to Excel and view the spreadsheet in which you want to define a named range.
  • Now you must select the range of cells where the data you want is.
  • The next thing will be to right click on the selected cells and in the drop-down menu select the option “Assign name to a range” and then on “Define name”.
  • In the “New name” window you must specify the name for the range in the “Name” box and then click “OK”.

Like the import process, only one worksheet or name range can be linked at a time, so this procedure must be performed multiple times if you want to link multiple sheets. After this it is necessary that you close the source file if it is open, since once the procedure has started it must be closed in order to avoid possible errors during the process.

Prepare the target database

  • At this point it is necessary to open the database in which you want to establish the link, it is important that the database is not read-only and that you have the necessary permissions to make changes to it.
  • In case you don’t want to store the link in an existing database , then you need to create a new database . The procedure for this will depend on the version you are using, but it is basically the same for all of them.

Create the link with Excel

Leave a Reply

Your email address will not be published. Required fields are marked *

Back to top button