Skip to main content

Preparation for targeting CSV files using the ODBC connector

Bravura Security Fabric provides two methods for targeting CSV files: the CSV connector (agtcsv) and the ODBC connector (agtodbcscript). Determining which method to use depends on:

  • The flexibility of the CSV format. Can it be modified or not?

  • The need to write back out to the CSV files. Are the CSV files read-only or do they also need to be updated by Bravura Security Fabric .

  • Do columns within the CSV need to be combined to create unique or meaningful data for Bravura Security Fabric ? For example are two columns required to make a unique key?

  • Do the CSV files exist on a remote share or are they local to the Bravura Security Fabric server?

  • Is object data and attributes spread across multiple CSV files or is it self-contained in a single CSV file?

Choose the CSV connector (agtcsv) if:

  • You have a requirement to write data back out to the CSV files from Bravura Security Fabric .

  • Your CSV files exist on a remote server and a persistent drive mapping cannot be established.

  • Your CSV file format is flexible and columns can be added in order to satisfy the requirements of the CSV connector’s configuration.

Choose the ODBC connector (agtodbcscript) if:

  • You do not require the ability to write data back out to the CSV files (read only).

  • Your CSV files exist locally on the Bravura Security Fabric servers, or can be accessed remotely via a persistent drive mapping.

  • Your CSV file format is unchangeable, and the connector must be able to join, merge, and manipulate columns of the CSV file to extract the appropriate data.

Attribute and object data is spread across multiple CSV files.

The rest of this section shows you how to target CSV files using the ODBC connector. To learn how to target CSV files using the CSV connector, see FLAT FILE SYSTEMS > CSV file .

Defining a data source for CSV file systems

The ODBC connector can perform operations with CSV files by leveraging the "Microsoft Text Driver (*.txt; *.csv)" ODBC driver. This driver is available from Microsoft in both 32-bit and 64-bit versions. Depending on the version of the driver you have installed, use either the 64-bit or the 32-bit ODBC connector.

To target CSV files via the ODBC connector you must first configure an ODBC source using the "Microsoft Text Driver (*.txt; *.csv)" ODBC driver:

  1. Start the ODBC data source administrator tool (odbcad32.exe).

    Depending on your system, this is located at:

    • c:\Windows\System32\odbcad32.exe (64-bit)

    • c:\Windows\SysWow64\odbcad32.exe (32-bit)

  2. Click the System DSN tab, then Add...

  3. Select the "Microsoft Text Driver (*.txt; *.csv)", and click Finish.

  4. Give your data source a name and description.

  5. Deselect Use Current Directory and select the folder location of your CSV files. If a network drive is required, it must be a persistent drive mapping with the same drive letter.

  6. Click Options > Define format... to configure the schema for each CSV file. From here you can define:

    • Whether the CSV contains column headers or not

    • The delimiter (if not a comma)

    • The character set (see UNICODE CSV files for UNICODE support)

    • The columns, data types and lengths for each CSV file

    The Guess button generally gets the columns correct; however if the CSV file includes a byte order mark (BOM) then you need to manually override the columns.

  7. Click OK to close the ODBC data source administrator tool.

The define format process above creates a schema.ini file along side the CSV files. This is a plain text file that can be edited afterwards for additional configuration. The format of the schema.ini file is:

[fileone.csv]
ColNameHeader=True|False
Format=CSVDelimited|Delimited(<char>)
MaxScanRows=25
CharacterSet=ANSI|OEM|<CodePage>
Col1=COLUMN_NAME <DataType> Width <FieldLength>
Col2=COLUMN_NAME <DataType> Width <FieldLength>
...
ColN=COLUMN_NAME <DataType> Width <FieldLength>
[filetwo.csv]
....

UNICODE CSV files

The ODBC data source administrator tool has a couple of limitations when it comes to supporting UNICODE. The editor does not properly detect/handle the byte order mark (BOM) that is present in many UNICODE files. Nor is UNICODE an option for the character set in the editor.

To support UNICODE CSV files, you need to manually override the settings in the schema.ini file.

For UTF-8 files you must:

  1. Manually change the CharacterSet= key-value in the schema.ini to:

    CharacterSet=65001

  2. Manually define the Columns' names and data types since the Guess option does not properly handle the BOM.

CSV sample files

A sample set of CSV files, schema.ini file and ODBC configuration is provided with the ODBC connector. This can be used directly or as a reference for defining your own CSV ODBC data source. The included fies are:

  • The CSV files which include groups, group memberships, computers, users, and subscribers samples.

    • agtodbcscript_sample_groups.csv

    • agtodbcscript_sample_members.csv

    • agtodbcscript_sample_servers.csv

    • agtodbcscript_sample_subscribers.csv

    • agtodbcscript_sample_users.csv

  • Schema definition file for the ODBC data source for each of the above CSV files:

    agtodbcscript_sample_schema.ini

  • Configuration file for the ODBC connector which targets the above CSV files:

    agtodbcscript-csv.cfg

In this section: