Importing Or Exporting Lookup Tables

TIBCO Cloud™ Integration - Connect Lookup Table values can be exported to a file in a CSV format or appended to an existing Lookup Table from a CSV file. If you have more than one TIBCO Cloud™ Integration - Connect Organization, you can export Lookup Table values from one Organization and add them to an existing Lookup Table in another Organization. The process of creating Lookup Tables can be sped up significantly by entering the data in an Excel spreadsheet and then adding the data to an existing Lookup Table.

Requirements For Appending Lookup Table Values

CSV files used to add Lookup Table values to an existing Lookup Table have the following requirements and restrictions:

File must contain a header row with these two headings exactly as shown:
- Value1
- Value2
There cannot be any empty rows between rows of data.
Only the first two columns in the file are imported. If you have more than two columns, column three and higher are ignored.
Values for each Lookup Table must be contained in a single CSV file. See Sample CSV Files for an example.
The Value1 field should contain one of the two parameters being looked up, such as, an ID number that corresponds to a named item. For example, if Value1 contains ID 100, Value2 might contain a product name such as Chocolate Candy.
The Value2 field should contain the second of the two parameters being looked up, such as a product name.
Leading or trailing white space in Value fields is trimmed.
Maximum number of records in the CSV file is 5000.

Note: If you have exported Lookup Table values to CSV and plan to edit the file, then append them to an existing Lookup Table, do not edit the CSV file in NotePad. The CSV file is exported with newline characters (/n) that do not display correctly in NotePad. Edit the CSV files in Excel.

Editing CSV Files

Best practice is to edit the CSV file in Excel. Excel handles the comma field delimiter and the required carriage return at the end of a record for you. If there are commas or double quotes within a field, when you save the CSV file, Excel surrounds those fields with the appropriate double quotes required by the import process.

Each field must be separated by a comma and each row by a carriage return.
If a field contains a comma, that field must be enclosed in double quotes.
If a field contains one or more double quotes, each double quote must be repeated, and the field itself must be surrounded in double quotes. See Sample CSV Files for examples of working with commas and double-quotes.

Sample CSV Files

CSV File In Excel

CSV File In NotePad

In the sample CSV files shown above, note how the items that contain commas and double-quotes are treated. It is important to use double quotes correctly or the import may fail.

Append Lookup Table Values From A CSV File

From the menu, select Environment & Tools.
Under Environment Settings, select Lookup Tables.
From the Lookup Tables page, select New Lookup Table to create a new Lookup Table. TIBCO Cloud™ Integration - Connect creates an empty Lookup Table and opens the Lookup Table dialog.
Modify the default name for this Lookup Table, and optionally, enter a description. Only alphanumeric characters, underscore, and dash are supported in these two fields.
Note: Consider using the Description field to call out the entities and fields you are using for this Lookup Table.
Select Append.
On the Append Lookup Table Values dialog, make sure the Column Headers In First Row option is enabled.
Select Choose file to browse to the location of your CSV file, select the file and select Open.
Select Complete to append the table values or use the Remove link to remove the file and choose a different one.
Note: CSV files are appended immediately. There is no confirmation dialog. If the table contains duplicate values, all values are included. All values are appended to the existing list of values.
When the process is complete the Append Lookup Table Values dialog displays the following message: Lookup table values successfully appended.
Select Close on the Lookup Tables dialog.

Note: Changes to Lookup Tables are saved immediately and cannot be canceled.

Export Lookup Table Values

Note: Each export contains the values for a single Lookup Table. Multiple tables cannot be exported at the same time or to the same export file.