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 Excel database In a very simple way. It should be noted that both programs are used to data calculations, so perhaps on many occasions it is necessary Transfer information from one program to another.
This procedure can be carried out in different ways, either Copying the data from an open spreadsheet and pasting it into the Access one, import a new either creating a from an Access database. You have to remember that when you import data the user has the opportunity to manipulate them either editing them, while 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 with Access?
Prior to start this procedure It is very important to keep in mind some considerations. In general, when this procedure is performed, you can present 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 for importing data from Microsoft Excel to Microsoft Access
- It is possible for a user to carry long 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 into an existing or new Access database or databases.
- It is also possible that a department or work group is constantly working with Access, but receive information from some clients 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 expedite 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 imports 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 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 linking to a book It is considered as something substantial and different from what the import process is.
What kind of data can I import from an Excel workbook to an Access database?
when you want import data from an excel workbook to an access database you have to take into account what you want save or import there, either if all the information contained in the book or only a 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 there included. When importing this data, Access creates a backup of the data either in one existing table or a new tablein 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 above 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 in detail each of the steps that we are going to explain below:
Import data into 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 link 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 that 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 bear 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 action as described below:
- Number of columns: Keep in mind that the source column number that you want to import cannot exceed 255 fields on a table.
- table format: It is important that the cells are in table format, in the case that 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: In general, only the columns and rows that you want to import into the spreadsheet are included. For the rows” it can’t be done filter or skip rows during the import procedureMeanwhile in the columns” they can’t 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 data that is needed.
- 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, andIn the event that it remains open during the process execution errors may occur in data conversion.
Prepare database
next thing will be prepare the databasefor this you must open access database where do you want them all to be saved the imported data. At this point it is necessary to make sure 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 either create a new database.
In the event that you want to save it in a new one, you simply have to go to the tab “File, Archive” there you select “New” and later you will have your new blank database on screen. Before beginning this import process, you need to choose if you want to save the content to a new or existing table.
For this, these 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 one must be very careful and detailedsince most of the errors occur here, due to the fact that the data does not match the structure, configuration, among others.
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 should 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 most recent versions such as Access 2019 or Microsoft 365 Access subscriptionit will be necessary to go to the tab “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, they should go to the tab “External data”, and in the section “Import and Link” select the option “Excel”.
Now in the window of “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 the imported data.
Now to be able to store the data in a you 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.
If you want to append the data from an existing table, then you must select the option “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 have to click on “To accept”.
With the spreadsheet import wizard
Finally, with the import wizard the import execution can be carried out.
For this follow…
Facebook Comments
Disqus