Skip to content

ODBC-Import

CadnaA enables to import data from various data sources via its ODBC interface (Open Database Connectivity) into a project file (menu File| Database).

In order to establish access using the database interface, an appropriate data set is required from which the parameters are imported (e.g. MS-Excel or MS-Access). Furthermore, a suitable ODBC-driver must be installed on the PC. To check your system for available ODBC driver go to Settings|Control Panel|Administrative Tools|Data Sources (ODBC) from the Windows start menu.

Examples for the data import via ODBC:

  1. sound level spectra, sound reduction spectra etc.

  2. object attributes (name, emission data etc., text attributes for the Memo-Window)

  3. object coordinates of point objects (e.g. receiver points, point sources, bitmaps)

Example 1: Importing Sound Power Level Spectra

Example

Path: Import/ODBC/spectra.zip

The following spectra of sound power levels shall be imported via ODBC from an MS-Excel-worksheet.

Table with sound power level spectra, named range selected

The tables consist of the following columns:

  • Name,
  • ID,
  • spectrum type:type=2 means a PWL-spectrum, see attribute LW_TYP on the Windows-help or in CadnaA-manual „Attributes, Variables and Keywords“)
  • weighting (here: A-weighted),
  • octave band data from 31 to 8000 Hz.

The data range (including the column designation) to be imported is selected in the "Named Range" of MS-Excel by the name „data“. To this data range access is provided on ODBC-import.

Proceed as follows to import the data into CadnaA:

  • Open the dialog Database|Definition on the File menu.

  • Select via the file selector symbol from the ODBC data sources available on your PC the driver for MS-Excel-files (about tabs "File Data Sources" or "Machine Data Sources").

  • On the following dialog select the file spectra.xls and click OK.

  • Scroll in the table „Object Type“ on dialog Database down to the bottom and select the object type „Sound Levels“.
  • Now, activate the option „Import Object Type“. The selected object type is highlighted.
  • Select the named range of cells from „Table“ (here: „data“).

Configuring data import via ODBC

Now, the columns of the Excel table must be assigned to the corresponding object attributes in CadnaA. To do this, first select "Default Attributes" from the "Defaults" menu to load a list of relevant attributes (additional entries can be added using "Insert before/after"). The assignment can then be done as follows:

  • Double-click into the line "Bez" of the table „Assign Columns“.
  • On dialog Assign Columns select the table column „Name“ to assign its content to the attribute BEZ.

Assigning column „Name“ to the attribute BEZ

  • Proceed with the attribute ID and assign this to the table column „ID“.
  • The attribute BEWERT (i.e. weighting type) gets assigned to the table column „Weight“.
  • Furthermore, the attribute STYPI is assigned to column „Type“.

  • Finally, assign the octave band data to the respective attributes. To facilitate the procedure you may use the arrow buttons to switch to the next attribute:

    • attribute SRAW@STF31 to column „f_31“,
    • attribute SRAW@STF63 to column „f_63“ etc. till
    • attribute SRAW@STF8000 to column „f_8000“.

  • So, the configuration of the ODBC interface is completed. Close the dialog Database with OK.

  • To start the import, select the command Database|Import from the File menu.
  • Activate on the subsequent dialog the option „Append non-existing Objects“ and click OK.

Now, the data is imported. Alternatively, you may also update existing objects or select the combination of both options. The imported sound level spectra can be displayed from the Tables menu, tables Libraries (local)|Sound Levels..

Example 2: Import of Road Data

Example

Path: Import/ODBC/road_data.zip

Now, road data shall be imported for 9 roads via ODBC.

The MS-Excel file has the following columns:

  • Name: road’s name,
  • ID: relevant characteristic for synchronization,
  • MDTD: mean daily traffic data,
  • Type: type of road,
  • speed_car: speed limit for cars (km/h),
  • speed_truck: speed limit for trucks (km/h).
  • rd_width: distance between the outer lane’s axis in m.

First, enter into a new CadnaA-file nine roads.

  • Select the object „Road“ from the toolbox.
  • Enter an arbitrary road network consisting of nine separate roads (i.e. nine road objects).

Road network consisting of nine roads

  • Open the Road table from the Tables|Sources menu.
  • Click with the right mouse button in the column "ID" and select the command „Change Column“.
  • Enter in section „Replace Strings, Replace with“: rd_ #
  • After OK all roads receive the ID "rd_" plus a single-digit number.
  • Save the file under a new name.
  • Open the dialog Database|Definition on the File menu.
  • Select via the file selector symbol from the ODBC data sources available on your PC the driver for MS-Excel-files.
  • Select the file road_data.xls in the following dialog and click OK.
  • On dialog Database select the object type „Road“ from the table of object types.
  • Activate the option „Import Object Type“. The selected object type is marked.
  • Select the named range in the MS-Excel-file via „Table“ (here: „data).
  • Assign the following attributes the respective table column as listed in the following table per double-click:
Attribute Table Column
BEZ Name
ID ID
FBABST dist
DTV MDTD
STRGATT Type
VPKW sp_car
VLKW sp_truck
  • Close the dialog Database with OK.
  • Select the command Import on menu File|Database to import the data.
  • Activate on the subsequent dialog the option „Update existing Objects“ and click OK.

The data is imported and the road data is updated. The imported data can be displayed via the menu Tables|Sources|Roads..

Table Road showing the imported road data