The Data Import component has three tabs:
- Source - where you define where the data is coming from, and any requests needed to obtain it
- Destination - where you define the tables and fields where the data will go
- Options
Source
Specify the file type: CSV or XML
Your choice will affect the display of the remainder of the Data Import component.
For CSV filetype only:
- Ignore the first - lets you specify if the first 1 or more lines in the CSV file should be ignored. This is used if the file contains a heading or field titles at the start of the file.
- Field separator - fields are normally separated by a comma, but some locales use a semi-colon.
- Text delimiter - a single or double quote mark is used to surround some text fields in CSV files.
For all filetypes:
Specify the Request (optional)
This section is used when the file import will be triggered via a request from a server. If the file will be uploaded to the site this should be left blank.
Request type - GET (the http request type)
Request URLs
- Add the URL where the file will be requested from
If the you add more than one URL, a request will be made in the displayed sequence, until one responds. This is to provide a failover capability if the first URL is offline.
Authentication
If the feed requires you to use Basic Authentication, enter the Username and Password, otherwise leave these fields blank.
Note: If you need to authenticate by giving your credentials as part of the querystring, add them to the Request Parameters.
Request Parameters
- Add parameters to the request url. The parameters will be added in order specified
There are four types of parameter you may add:
- Text
- Table Field
- Timestamp
- Signature
Text parameter
For the parameter, enter the:
Example: if you want the request url to contain color=yellow, set Name to 'color' and Value to 'yellow'
Table Field parameter
This is used when a value will be passed in from a query request, with the value being linked to a table field
Enter the:
- Name - the parameter name
- Field - the field in the destination table
- Minimum length & Maximum length - (optional)
Example: if you have a Query by Example view set up on a Query that used the Data Import component, if the user enters a value to search for in the Query by Example, that value will be used here.
Timestamp parameter
Used in conjunction with signatures to prevent replay attacks
Enter the:
- Name - the parameter name
- Format - ISO 8601
Signature parameter
Used to sign the request. If used this is usually the last parameter.
Enter the:
- Name - the parameter name
- Type - HMAC SHA256
- Key - as supplied by the provider site
Test Request
This allows you to test the request and run an import without configuring a Query and Query by Example.
If there are any Request parameters configured of type Table Field, they will be listed here so you can enter the values for them. Fill these in, and press the 'Make test request' button.
The test will run, and a dialog will show the outcome,along with the raw file returned to be imported.
Destination
Table - specify the Table that will receive the imported data.
If the data will be imported into several tables, specify the main table here, and the related tables will be specified later in the configuration.
For CSV filetypes only:
Field Mappings
For each field in the import file that you wish to import, Add an entry. This shows the
Field Mapping dialog
This contains the following settings:
Field index in CSV file
Identify the field.
- Use a number, 1 for the first column, 2 for the second etc, or
- Use a letter, A for the first column, B for the second etc. After Z, use BA, BB etc, following the naming convention Excel uses for spreadsheets.
Leave this blank if later in this dialog the Table Field will be of datatype Record Link or Multiple Record Link and the Against Multiple Fields option is selected, since the field(s) in the CSV file will be specified in the subsidiary dialog.
Table
The Table specified earlier in the configuration is confirmed here.
Click 'Edit...' if you need to modify the Table.
Table Field
The field within the Table where the value will be stored.
If you choose fields of type Record Link or Multiple Record Link, additional settings will appear.
Response value prefix & Response value suffix
Any values entered here will be used to modify the imported value before it is stored.
Include in cache control
Cache control determines whether the target table is deemed to already contain the relevant data (ie it is cached in the site), so the record need not be imported, or not, in which case the record should be imported.
Additional settings for fields of type Record Link and Multiple Record Link:
Against
Select whether the value should be matched against
- the Record Identifier of the linked table
- Multiple fields in the linked table
Create linked record if not found
Check this to create a new record in the linked table if an existing match is not found. The new record will have the imported value(s) in the relevant fields (except for Record Identifiers when they are made of a combination of fields).
If checked the following option becomes available:
Create linked record if all field values are empty
Check this to create a new record in the linked table even if all the values relating to the linked table are empty. Leave this unchecked to avoid creating such a blank record in the linked table.
Linked Table Field Mappings dialog
If the Against settings is set to Multiple Fields, click the adjacent button to open the Linked Table Field Mappings dialog. This contains the following settings:
Table
The table the Record Link or Multiple Record Link is linked to is confirmed here.
Click 'Edit...' if you need to modify the Table.
Field
Add one or more fields from this table.
This shows a Field Mapping Dialog.
Per record sequence index in CSV file
This is shown only if the dialog was called from a Multiple Record Link field. This allows the multiple records that are being added in the linked table to be sequenced.
The field should be identified using either a number or letters.
The field identified should contain an integer value.
Cache control at this level matches
Cache control determines whether the target table is deemed to already contain the relevant data (ie it is cached in the site), so the record need not be imported, or not, in which case the record should be imported.
When examining records in this linked table to find a match, it can either look for a match over all records in the table, or restrict the search to records linked to by the record at the lower level.
Select either:
- All records in this table
- Records joined to the previous level
