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 the data in the model by querying the AnyLogic database tables.

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

If data has changed in the external database, you can update the data in the imported database. If you expect data to change constantly, you can set your database to update automatically each time you start the model.

To import data from a Microsoft Excel or Microsoft 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, such as a function body or agent action.

To do this:

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 an 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 filename.
   You don’t have to specify the absolute path all the time — the relative path will do. In addition, you can use various UI elements to allow users to specify different files. Consider using File Chooser for this 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 obtained by the getConnection() function 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 of the target table before executing the call, and false indicates 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 another source sheet in the same file.
4. That’s it — when the code described above is executed, the specified table in the internal model’s database is populated with values from the Excel sheet.
   You can use regular query functions to interact with these values as you see fit.

To import data from a Microsoft SQL Server database:

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

   

2. The Import database tables… dialog box appears. Select the database in the left pane, Setup connection settings.
3. Select Microsoft SQL Server from the Type drop-down list.
4. Type the address or the name of 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. Click the Show list of tables button. AnyLogic will attempt to connect to the specified database and display 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 the data in the external database to be updated, and you want to always have the most recent data in your model, select the checkbox Update data on the model startup. By selecting this option, you are telling AnyLogic to update data from the external database the first time the running model accesses the database. You can disable auto-update in the database properties.
9. If tables with the same name already exist in the 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. In the Projects view, expand the  Database item branch, and you will see the list of tables added to the AnyLogic database.

    

To import data from a PostgreSQL database:

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

   

2. The Import database tables… dialog box appears. Select the database in the left pane, Setup connection settings.
3. Select PostgreSQL from the Type drop-down list.
4. Type the address or the name of 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. Click the Show list of tables button. AnyLogic will attempt to connect to the specified database and display 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 the data in the external database to be updated, and you want to always have the most recent data in your model, select the checkbox Update data on the model startup. By selecting this option, you are telling AnyLogic to update data from the external database the first time the running model accesses the database. You can disable auto-update in the database properties.
9. If tables with the same name already exist in the 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. In the Projects view, expand the  Database item branch, and you will see the list of tables added to the AnyLogic database.

    

To import data from a Oracle database:

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

   

2. The Import database tables… dialog box appears. Select the database in the left pane, Setup connection settings.
3. Select Oracle from the Type drop-down list.
4. Type the address or the name of 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. Click the Show list of tables button. AnyLogic will attempt to connect to the specified database and display 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 the data in the external database to be updated, and you want to always have the most recent data in your model, select the checkbox Update data on the model startup. By selecting this option, you are telling AnyLogic to update data from the external database the first time the running model accesses the database. You can disable auto-update in the database properties.
9. If tables with the same name already exist in the 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.

   

   In Oracle databases, if the column name is not enclosed in quotes, the comparison is in uppercase: my_column, MY_COLUMN, mY_cOlUmN are the same name. If it is quoted, the comparison is case-sensitive: my_column and "my_column" are the same name.
10. Click OK. In the Projects view, expand the  Database item branch, and you will see the list of tables added to the AnyLogic database.

    

To import data from a MySQL\MariaDB database:

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

   

2. The Import database tables… dialog box appears. Select the database in the left pane, Setup connection settings.
3. Select MySQL\MariaDB from the Type drop-down list.
4. Type the address or the name of 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. Click the Show list of tables button. AnyLogic will attempt to connect to the specified database and display 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 the data in the external database to be updated, and you want to always have the most recent data in your model, select the checkbox Update data on the model startup. By selecting this option, you are telling AnyLogic to update data from the external database the first time the running model accesses the database. You can disable auto-update in the database properties.
9. If tables with the same name already exist in the 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. In the Projects view, expand the  Database item branch, and you will see the list of tables added to 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