Importing Data from a CSV File into OpenAir

Integration Manager lets you import information from a comma separated values (CSV) file into OpenAir. You can use this functionality to import information from a third-party software application in two steps, exporting information from that third-party application to a CSV file first, and then importing information from that CSV into OpenAir.

You can use the Integration Manager import functionality to create new records or update existing records in OpenAir. Integration Manager creates a new record if it determines that the record does not exist in OpenAir. The rules determining if a record already exists in OpenAir and if the record should be created or updated depend on the record type and whether the import is configured to lookup any existing OpenAir record with matching external ID. For more information, see Record Creation or Update Rules on Import.

You can select the columns in the CSV file containing the information you want to import into OpenAir and map CSV columns to OpenAir fields, include information you want to store in the custom fields specific to your OpenAir account, look up related OpenAir records by external ID, combine values from several CSV columns into one OpenAir field, split values from a CSV column into several OpenAir fields, import specific information only when the import creates a new record in OpenAir. You can specify the format of date and time columns in the CSV file you import information from, and build in some simple logic to change a target field value in OpenAir based on a test field value in your source CSV file. If you use Integration Manager to import user information for your employees, you can import user settings, including user preferences and user privileges that are stored in the switch table, in addition to the employee information stored in the user table.

Important:

Review the following guidelines:

• Always use caution when importing information from a CSV file into OpenAir. You should not use Integration Manager unless you have received the relevant training. You must have a good understanding of the OpenAir application and how its database is structured, as well as knowledge of the Integration Manager application.

• Integration Manager 6.6 or later version supports Unicode (UTF-8) characters — You can choose between ANSI (codepage 1252, Western Europe) or UTF-8 encoding. Previous versions of Integration Manager use ANSI (codepage 1252, Western Europe) encoding exclusively. When using ANSI encoding, for imports, only Western European characters are supported. Be sure that the source CSV file containing the information you import into OpenAir uses the character encoding scheme selected in the Integration Manager application or shortcut bundle instance you are using to import the information.

• Use only a decimal point to separate the integral part and decimal part of decimal number values imported to OpenAir. Other decimal separators and thousands separators are not supported in integral numbers.

• Most decimal numbers in OpenAir have two-digit precision. To avoid rounding inconsistencies in OpenAir, you should use the same precision in your source CSV file.

• Conditional overrides may be used to modify the format of values following a regular expression pattern. For more information, see Setting Up Conditional Overrides and Regular Expressions Use Case Examples.

• To verify the format and decimal precision of an OpenAir field, refer to the OpenAir Data Dictionary. See OpenAir Data Dictionary.

To import data from a CSV file into OpenAir:

1. Launch Integration Manager. To do so, do one of the following:

   • Double-click the shortcut icon on your desktop (if you opted to add the shortcut during installation).

     

   • Enter "OpenAir Integration Manager" in the search box on the Windows task bar, then double-click OpenAir Integration Manager.

2. Enter your OpenAir account and login details. See Connecting Integration Manager with your OpenAir Account.

3. Select the character encoding scheme for CSV imports and exports. See CSV Character Encoding.

4. In Integration Manager, identify the type of record you want to import and configure the import. To do so:

   1. Map OpenAir fields to CSV columns. See Mapping OpenAir Fields to CSV Columns.

      The field mapping functionality lets you select the CSV columns you want to import into OpenAir and map each CSV column to the corresponding OpenAir field. You can also:

      • Import information into the custom fields specific to your OpenAir account. See Making OpenAir Custom Fields Available for Mapping.

      • Include additional information not already available in your source CSV file and import it into an OpenAir field. See Making Additional Information Available for Mapping (Calculated Fields).

      • Combine values under several CSV columns and import them into one OpenAir field, or split values under one CSV column and import them into separate OpenAir fields. See Combining and Splitting Information.

      • When importing user information for your employees, import user settings, including user preferences and user privileges that are stored in the switch table, in addition to the employee information stored in the user table. See Making User Settings Available for Mapping (User Imports Only).

      • Look up related OpenAir records by external ID. See OpenAir Field Value Lookup (Export) and Record Lookup (Import).

      Important:

      Integration Manager uses an import key field or a combination of import field key fields to determine if a matching record already exists in OpenAir. If the record exists, Integration Manager updates the OpenAir record with the corresponding information in the CSV file. If the record does not exist, Integration Manager adds a new record in OpenAir.

      Integration Manager determines which field to use as import key based on the field mapping configuration. For more information about rules determining record creation or update, see Record Creation or Update Rules on Import.

   2. Set formatting options. See Formatting Information for Export and Import.

      The formatting options let you:

      • Specify the format of values containing both date and time parts in your CSV file. See Setting the Date and Time Format Used in the CSV File.

      • Build in some simple logic using conditional overrides to change a target field value in OpenAir based on a test field value in your source CSV file. See Setting Up Conditional Overrides.

5. Create an Integration Manager shortcut if you want to import records of this type on a regular basis manually without launching the Integration Manager application, or to schedule the import to run automatically. This may be useful to support an on-going integration, for example. The Integration Manager shortcut you create captures the field mapping, format and other settings you configured for this import. See Working with Export and Import Shortcuts and Working with Export and Import Shortcuts.

6. On the main Integration Manager window, select the row corresponding to the record type you want to import to a CSV file, then click Start to launch the import process.

   The Select Import Options window appears.

   

7. Select the CSV file you want to import information from. To do so, click the Select file icon , select the CSV file, then click Open.

   The Select Import Options window shows the file path.

8. Select the delimiter used in the CSV file from the File delimiter dropdown options.

9. Check the Use first row to get field names box if the selected CSV file includes column headers (field names) in the first row. If the CSV file does not contain column headers on the first row, enter column headers in the bottom box. You can edit existing column headers if required.

   Important:

   The name and sequence of column headers in the CSV file must be the same as in the CSV file you used to configure the import.

10. Click OK.

    The Date/Time Format window appears.

11. Specify the format of values containing both date and time parts in your CSV file. See Setting the Date and Time Format Used in the CSV File.

12. Click OK.

    A confirmation message appears

13. 

14. Click Yes to continue with the import.

    A progress status appears and shows the detailed progress of your export from start to completion.

    
    Note:

    To keep the import progress status window open after the import completes, clear the Close this dialog box when complete box. This may be useful if you want to check the status of your import on completion without viewing the log file.

    To open the imported CSV file in the default application associated with CSV files on your computer, check Open file when complete.