Skip to content

Database Import (ODBC & XLSX)

The File|Database menu enables to import tabulated data from different data sources via ODBC (Open Database Connectivity) of WINDOWS. Alternatively, table data can be imported directly from MS Excel folders (file format * .xlsx).

The types of objects and their attributes are listed on the table „Object Type“ on the dialog File|Database|Definition. For point objects (point source, receiver) also the geometry (xyz coordinates) can be imported.

ODBC Import

In order to access to the ODBC database interface suitable ODBC drivers must be installed on your PC. The ODBC drivers on your system are listed in the WINDOWS Control Panel. Otherwise, suitable ODBC drivers must be installed. It is not sufficient having the drivers just stored on your system. In addition, a suitable database table used for import must exist (by assigning the ODBC driver to a type of database). Only in this case, the selected data source appears in the list.

Data for Import

At present, data for the following object types can be imported using the Database dialog:

  • data for all CadnaR objects (see Chapter 5 - CadnaR-Objects), except for level frame, text frame, section and symbol.,
  • data for library objects in the following libraries:
    • text blocks
    • sound power spectra
    • sound insulation spectra
    • absorption spectra
    • scattering coefficient spectra
    • product library

Selecting the ODBC data source

This list can later be accessed in CadnaR. However, you may call this data connection also in CadnaR. In this case, no change to the system level is required. Select the Define command from the File|Database menu.

Note

The data sources shown - here on a German Windows system - depend on the installed ODBC driver versions.

In the Select Data Source dialog, the data source is selected first. Click on the file selector symbol and select the appropriate type of database and close the dialog with OK.

Selecting the data range

Prior to the import from MS-Excel the data range must be named (e.g. by „Data“). This data range of the table is addressed via the list box „Table“ on the dialog Database.

Selecting object type and table

Select first the object type by clicking with the mouse. Then enable the option „Import Object Type“ and select from the list box „Table“ the appropriate data range.

Assigning attributes

The column mapping table defines which CadnaR attribute (left column "Attribute") is to be written with which value from the database (right column "Table Column"). In the case of an Excel file, the database attribute is defined by a column header. By default, the column mapping table is empty. Entries can be added as follows:

  • Click "Predefined" and select "Default Attributes" to add a list of the most relevant attributes (not all).
  • Right-click on the table and select "Insert before/after".

Values for individual rows can be entered either directly in the table or via the dialog that opens when double-clicking a row:

The object‘s ID is used for synchronization between the object parameters and object‘s geometry. Finally, after assigning all attributes, close the dialog Database with OK.

Importing the data

Proceed by selecting the command Import from the File|Database menu.

Select on the Import Database dialog one or both options:

  • Update existing Objects: Just the existing objects will get updated without appending non-existent objects.
  • Append non-existing Objects: The non-existing objects will be appended without updating the existing ones.

With both options checked, the existing objects are updated and non-existing are appended.

Buttons „Save/Load“

These two buttons on the Database dialog offer to save and load the selected options and the list of assigned attributes to an external database configuration file (*.cndb).

Note

The settings made on the Database dialog remain while CadnaR is running, but will be lost when closing the software. This option enables to save the settings anyway.

Requirements and Limitations for ODBC-Import

Please consider that the data import via ODBC is, in principal, a text import and that - depending on the driver used - the following requirements and limitations regarding the file path, the filename, and the text format may apply:

  • The accepted length of the file path may differ among different ODBC drivers. In case of import problems copy the file to be imported to a shorter file path (e.g. to C:\...)
  • The filename accepted by some ODBC drivers are limited to 8+3 characters (as was enforced by Windows 3.11).
  • Column headings must start with text (i.e. with letters). Numbers as leading character in column headings cause import errors.
  • In column heading no special characters or blanks are permitted (except for the underscore "_").
  • If a column lacks data in the first data lines, the ODBC driver assumes that no data are existing also in the subsequent lines. The import then does not provide the desired data. Fill the void cells in these cases in the import file with permissible values (e.g. zero) and repeat the import procedure.

Importing Objects with ID from ObjectTree

When importing objects the ID part of which results from the ObjectTree this part will be ignored when synchronizing data. This default setting can be changed via file CADNAR.INI in section [Main] by OdbcUseObjtreePart=1. In this case (with OdbcUseObjtreePart=1), the ID-part resulting from the ObjectTree will be respected.

Resetting the ODBC-Connection

In case error messages by the Windows-system appear during ODBC-import, this can result from a ODBC-connection not having been reset during a previous import procedure. To reset a failing ODBC-connection hold the CTRL key pressed while selected the command Database|Definition (File menu). In case attributes have been addressed already, those will be kept when resetting.

XSLX Import

Importing data from an MS Excel folder (* .xlsx file format) does not require a database driver. The data can be imported directly. The procedure in the Database dialog is analogous to that for ODBC data sources and is therefore only described here in a nutshell:

  • Select the „Excel Sheet“ option from the Database dialog.
  • Select the XLSX file containing the import data via the activated file selection icon .

Note

In the XLSX file, the data range must be named. These named range of the table are accessed via the list box „Table“ on the Database dialog. The first row of the XLSX table should contain the column headings. Empty data lines should be avoided.

  • Select the object type to be imported from the “Object Type“ table using the mouse. Just a single object type can be selected at a time.

  • Activate the option „Import Object Type“.

  • In the „Table“ list box, select the named range containing the data.

The table „Assign Columns“ is used to define the assignment of object attributes of the selected object type.

  • Double-click in a row of the „Table Column“ column to display and select the appropriate data column from the MS Excel spreadsheet.

Note

Alternatively, the column‘s name - if known - can be entered using the keyboard.

  • Close the Database dialog with OK.
  • Open the dialog File|Database|Import.
  • Activate one or both available options and click OK to import the data.

Buttons „Save/Load“

see above

Example

importing the geometry import of receivers