How to import and link data from an Excel workbook to an Access database? step by step guide

Another advantage offered by this microsoft program to its users is that it allows them import and link data from an Excel workbook to an Access database In a very simple way. Keep in mind that both programs are used to data calculations, so it may be necessary on many occasions transfer information from one program to another.

This procedure can be carried out in different ways, either copying data from an open spreadsheet and pasting it into Access, import a new table either creating a link from an Access database. You have to remember that when you import data the user has the opportunity to manipulate them either editing themwhile the process of bonding only allows reading them.

In accordance with all this and the importance of carrying out this procedure, here we are going to teach you how you can start importing and linking data from an excel workbook to an access database in a quick and easy way. 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 to Access?

Prior to start this procedure It is very important to take into account some considerations. In general, when this procedure is performed, there may be different types of scenarios What are they very important to knowas well as take into account some dimensions if this is the first time you are importing data from Excel into Access.

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

Possible common scenarios to import data from Microsoft Excel to Microsoft Access

• A user may carry a lot of time working with Excelbut in order to continue advancing and increasing your knowledge you need use Access in order to work with database. However, you want move your spreadsheets to a new or existing Access database or databases.
• It is also possible that a department or workgroup constantly work with Access, but receive information from some customers through Excel. This means that the user wants to move the Excel spreadsheets to an Access databaseto do this you must start importing each of these sheets as you receive them.
• Many times Access It is used to manage different data, but mostly the data that is received is through excel spreadsheets. In this case the user needs streamline the import procedure in order to ensure that all the data can be managed at the specified time.

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

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

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

when you want import data from an excel workbook to an access database you have to keep in mind what you want save or import there, whether if all the information contained in the book or only part of ityou must also specify if you want everything to appear in one single database or across multiple Access databases.

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

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

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

To do this, you need to follow in detail each of the steps that we are going to explain below:

Data import in Excel

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

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

Prepare spreadsheet

At this point the first thing you should do is look for the source file and select spreadsheet containing the data you want to import into Access. In case you don’t want transfer all the content that appears therebut a part of it, then you can start to define a named range that includes only the cells you actually want to import.

These ranges can be defined as follows:

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

Here you must keep in mind that you can only import a spreadsheet at the same time during the import procedurethis means that to import multiple 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 source column number you want to import 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 merged cellsthen the content of the cell will be placed in the field that corresponds to the leftmost column and the other fields there remain blank.
• Skip columns and rows: Typically, you only include the columns and rows that you want to import into your spreadsheet. For the rows” it can’t be done filter or skip rows during the import procedureMeanwhile in the columns” they cannot be skip during operation if you choose Add the data to an existing table.
• Blank columns and cells: You may delete all unnecessary rows and columns in the spreadsheet. If it contains blank cells, you can add the missing data.
• Error values: It is possible that only one or several cells of 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 close the source workbook if it is open, andn the case that it remains open during the execution of the process errors may occur in the conversion of the data.

Prepare database

next thing will be prepare the databasefor this you must open access database where do you want them all to be stored imported data. At this point it is necessary to ensure that the database is read only and that contains the permissions to carry out changes in the database. Here you can store the data in an existing database or be it create a new database.

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

For this, the following two points are taken into account:

• Create a new table: When choosing the data in a new table, access goes a 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 Excel spreadsheets are appended to the specified table. It is also important that at this point you have to be very careful and detailedsince most of the errors occur here, due to the data not matching the structure, configuration, etc.

Defined all this, next will be to execute the import operation.

To do this you must follow the next step:

Start the import procedure

Here it must be taken into account the location of the import or link wizardit will vary according to Access version that you are using.

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

• For users who are using the latest versions as it is Access 2019 or Microsoft 365 Access subscriptionit will be necessary to go to the tab of “External data” and in the section “Import & link” You must select the option “New data source” and then “Archive” and finally “Excel”.
• For users who are using the versions of Access 2010, Access 2013 and Access 2016, should go to the tab “External data”, and in the section “Import and Link” select the option of “Excel”.

Now in the window “Get 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 “Review” and then in the dialog open the corresponding file. It is also important that you specify how you want it to be save imported data.

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

In the case that you want to append the data of an existing table, then you must select the option of “Append a copy of the table records” and then select one of the tables in the dropdown list. In the event that the database does not contain any tables, this option will not be available. And finally you should click on “To accept”.

With the spreadsheet import wizard

Finally, with the import wizard the execution of 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 containing the data you want to import, then click “Following”.
• Now on the second page of the import wizard you must select the option “Show spreadsheets” either “Show ranges with names”, here you must select the option according to your case and then click on “Following”.
• If the first row of the spreadsheet contains the field names, then you must select “First column contains column header” and then select the button “Following”.
• After that the assistent will ask you to review the field properties, here you must click on one column at the bottom of the page so you can see the properties of the fields. You can too review and change the name and data type of the target field.
• On the next screen you must specify a primary key for the table, here you can select the option to “Allow Access to add the primary key” this will make the program add a field autonumber as the first field of the destination table and then automatically fills it with unique identifier values and then click again on “Following”.
• Finally, on the last screen of the import wizard you must specify the name for the destination table. In the section “Import to table” you must write a name for the table. In the case that the table already exists, then Access shows a message where it asks if you want to overwrite the existing content in the table, you can click the option “Yes” for continue or “Nope” to specify another name, and then click “Finish”.
• After this it will start data importonce the process is finished you must check carefully that all data has been transferred correctly.

Link data from Excel to Access

data import is one of the most common processes when wanting view excel data in microsoft accesshowever, the data binding procedure can also be carried out.

To do this you need to follow each of these steps:

Prepare Microsoft Excel data

In this case, the first thing you should do is search for Excel file and spreadsheet where is the content that you want to link. you may not want link all the content that appears there but only a part of it. So for these cases you need to create a named range in Excel, note that this procedure is optional and it is only done if you do not want to link all the information contained in the spreadsheet.

Let’s see the steps to follow:

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

Same as him importation process, only one can be linked spreadsheet or name range at a time, Therefore, this procedure must be carried out several times if you want to link multiple sheets. After this it is necessary that close the source file in case it is open, since once started the procedure this must be closed in order to avoid possible errors during the process.

Prepare the destination database

• At this point you need to open the database in which you want 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 store the link in a database existing, then you need to create one new database. The procedure for this will depend on the version you are using, but it is basically the same for all.

Create the link with Excel

• The first thing you should do is look for the import wizard or bonding, location of it depends on Access version that you are using, but basically it is the same in all of them, for this you need to follow this route: “External data” > “Import and link” > “Excel”.
• After this a new window will open “Get external data – Excel spreadsheet”, in the option of “Filename” you must specify the name of the Excel source file.

• Now you have to select the option “Link to the data source by creating a linked table” and then click “To accept”. the spreadsheet linking wizard will automatically start to help you with everything the bonding process.
• In the first sheet of the wizard you must select a spreadsheet and click on “Following”.
• If the first row of the spreadsheet or source range contains the field names, then you must select the option “First row contains column headers.” The program takes care of using these column headers to assign them as names to the fields of the table. After this click on “To accept” To continue with the process.
• On the final page of the wizard, you must specify a name for the linked table, and then click “To finalize”. If the table with the name you selected already exists, the program system will ask you if you want to overwrite the content in the existing table, here you can click “Yes” to continue execution or select “Nope” and create a different name.
• Here Access will start to create the linked tableif the operation is executed correctly, then the following message will appear on the screen “Finished linking the table.” Then open the table and check each of the fields and data so you can verify that all the content is correct.