If you have data for your project defined in an external database, we recommend you to import data in the built-in AnyLogic database, and then use data in the model by querying the AnyLogic database tables.

Click one of the links on the right sidebar (In this section) to learn more about settings specific to your type of database.

If data in the external database have been changed, you can update data in the imported database. If you expect data to change constantly, you can set your database to update automatically on each model startup.

To import data from MS Excel or MS Access file

1. In the Projects view, right-click the model’s  Database item, and choose Import database tables... from the context menu.

   

2. You will see the Import database tables... dialog box. First, choose the database file in the left pane, Setup connection settings.
3. In the Type drop-down list, leave the default setting: Excel/Access.
4. Click the Browse button. The Open dialog box is displayed. Browse for the MS Excel or MS Access file that stores the data you want to import. Double-click the file or select it and click the Open button to select the file. The button to the right allows to switch between the absolute and relative file paths.
5. When finished, establish the connection with the selected file. Click the Show list of tables button. AnyLogic will try to connect to the specified database, and show the list of external database tables in the right pane of the dialog.
6. In the Select table(s) you want to import table, select the checkboxes for the database tables you want to import into your model’s database. Here you can modify the auto-generated names of the tables that will be created in AnyLogic database (in the Name in AnyLogic DB column).

   

7. If you expect data in external database to be updated and want to always have the up-to-date data in your model, select the checkbox Update data on the model startup. Having set this option, you tell AnyLogic to update data from the external database every time the user starts running the model. You can switch the auto-update off in the database properties.
8. If tables with the same names already exist in AnyLogic database, you will see the warning as shown at the bottom of the figure below. You can choose whether you want to replace the existing tables with the tables from the external database, or keep the existing tables, and use different names for the imported tables.

   

9. Click OK button. In the Projects view, expand the  Database item branch, and you will see the list of tables added into the AnyLogic database.

   

To import data from an Excel table into the model’s database programmatically

You can command AnyLogic to populate a table in the model’s internal database with the data from an external Excel file, using any of the available code fields, for example, a function body or agent action.

To do that:

1. To access the model’s database, create an instance of the ModelDatabase object:
   ModelDatabase modelDB = getEngine().getModelDatabase();
2. Use the Database class constructor to specify the location of the Excel file you need, for example:
   Database myFile = new Database(this, "A DB from Excel", "D:\Files\myDBFile.xls")
   In the Database constructor, the first argument (this) specifies the database owner (an agent or experiment), the second ("A DB from Excel") is the flavor text (an arbitrary name of the database for your convenience), and the last one is the file name.
   You don’t have to specify the absolute path all the time — the relative path will do, too. Moreover, you can use various UI elements to allow users to specify different files. Consider using File Chooser for that purpose.
3. Finally, call the importFromExternalDB() function:
   modelDB.importFromExternalDB(myFile.getConnection(), "Sheet 1", "Internal DB - Excel Sheet 1", true, false);
   This function uses a database Connection, which is retrieved by the getConnection() method call. The second argument ("Sheet 1") specifies the name of the Excel file sheet you want to export, while the third ("Internal DB - Excel Sheet 1") specifies the name of the target table within the internal model database. true in the 4th argument commands AnyLogic to wipe the existing contents from the target table before executing the call, and false identifies that the copy action would not be considered a proper database transaction (which may cause performance issues in some cases).
   If you need to retrieve data from multiple sheets, repeat the function call and specify a different source sheet in the same file.
4. That’s it — upon the execution of the code described above, the specified table within the internal model’s database will be populated with values from the Excel sheet.
   You can use regular querying methods to interact with these values as you see fit.

To import data from MS SQL Server database

1. In the Projects view, right-click the model’s  Database item, and choose Import database tables... from the context menu.

   

2. You will see the Import database tables... dialog box. First, choose the database in the left pane, Setup connection settings.
3. In the Type drop-down list, choose Microsoft SQL Server.
4. Specify the host computer in the Host field and the name of the database you want to access in the Database name field.
5. Enter Login and Password if they are required by the database.
6. When finished, establish the connection with the selected database. Click the Show list of tables button. AnyLogic will try to connect to the specified database, and show the list of external database tables in the right pane of the dialog.
7. In the Select table(s) you want to import table, select the checkboxes for the database tables you want to import into your model’s database. Here you can modify the auto-generated names of the tables that will be created in AnyLogic database (in the Name in AnyLogic DB column).
8. If you expect data in external database to be updated, and want to always have the up-to-date data in your model, select the checkbox Update data on the model startup. Having set this option, you tell AnyLogic to update data from the external database when the running model addresses the database for the first time. You can switch the auto-update off in the database properties.
9. If tables with the same names already exist in AnyLogic database, you will see the warning as shown at the bottom of the figure below. You can choose whether you want to replace the existing tables with the tables from the external database, or keep the existing tables, and use different names for the imported tables.

   

10. Click OK button. In the Projects view, expand the  Database item branch, and you will see the list of tables added into the AnyLogic database.

    

If data in the external database have been changed, you can update data in the built-in database.

To update data in the imported AnyLogic database

1. In the Projects view, click the model’s  Database item.
2. In the Properties view, open the Import section and click the Update tables data link.

You can also set your built-in database to update automatically on model startup.

To make AnyLogic database update on model startup

1. In the Projects view, click the model’s  Database item.
2. In the Properties view, open the Import section and select the Autoupdate imported tables on model startup checkbox.

How can we improve this article?

Send