Overview of Creating Form Mappings for a CSV Source Migration Project

Connection and Configuration

Projects that use CSV as a source are slightly different to other projects in they do not have a server connection nor do they require authentication to be defined to access the data.

Instead, the user identifies which CSV Files will be used for source data in the project and creates a CSV File Definition for each. Each CSV File defined will become a 'Form' available for mapping in the project. The Identifier defined in the CSV File Definition will become the Form Name. Each field/column defined within the CSV File Definition will become a selectable Field in the Mapping Project. The field's identifier will become the field name and the field will have the type defined in the CSV File Definition.

Tip: Configure all the CSV Files with correct field types and identifiers BEFORE starting to build mappings.

You can only make changes to CSV Definitions during opening the project, and if identifiers of Files and Fields are changed here, any references to them in the project will be broken and the re-named field/form will need to be reselected before the project is executed.

Creating a Form Mapping

This works in the same way as for any other server, the list of source forms available will match the set of CSV File Definitions that have been created for the project. More information can be found in section Creating a New Form Mapping.

If you change the identifier for a CSV File, you will need to update the source form for each form mapping that uses it. The section Changing the Mapping Source / Target explains how to do this.

Source Filtering

The source filtering is configured using a query structured the same way as for any other Source. You will see that an additional field ROW_INDEX is available for filtering on. This is the zero based row number of the record in the file. Therefore you could restrict the number of rows retrieved using something like this:  [ROW_INDEX]<=10. This would retrieve the first 10 data rows, assuming the first row (0) is a header row and the first data row has an index of 1.

An empty value in a csv record is interpreted as NULL. All data types support having a NULL value and NULL values can be searched for using [Field]=NULL. Likewise, non-empty values can be found using [Field]!=NULL. Care should be taken with Boolean values particularly since [Boolean Field]!=TRUE will include rows with either an empty value or FALSE, which may not be what is expected.

Further information on source filtering can be found in section Creating a Source Filter Query

Inclusion filtering is also possible, where a column in the CSV File holds values that can be used as a key to records in a previously migrated CSV. More information on Inclusion Filtering can be found in section Filtering By Inclusion Using a Previous Migration Set

Key Mappings

Key Mappings work the same as for any other server, with the exception that the option to use Server Defined ID fields is not supported. Further information on Key mappings can be found in section Introduction to Key Mappings

Defining Field Mappings

The table below outlines the field mapping options that are available for CSV Source Projects:

Mapping Type	Notes
Simple	See section Creating a Simple Field Mapping
Assignment	Note that Assignment mapping expressions are often sensitive to field data types, so it is important to ensure that your CSV Definition uses the correct field types when they are to be used in an expression. See section Introduction to Assignment Functions
Value	Boolean fields have an explicit values that can be mapped (True and False) all other field types require the source values to be entered as free text. See section Creating a Value Match Field Mapping
Reference	This mapping type is NOT supported
Lookup	See notes below on creating Source Lookups Also see section Creating a Lookup Field Mapping

Defining Source Lookups

Source Lookups that search a csv file for rows that satisfy the search condition are supported. Note however that when searching very large csv files (>100,000 rows) this feature can be slow due to the need to scan all the rows in the file to search for a match.

You can use values, keywords, fields from both the search form and current form in your queries. See section Creating a Source Lookup Query

It should be noted that if a particular row cannot be evaluated when a query is run against it (usually due to the data format for the values in the row not matching that defined in the CSV definition) the row will not be matched. An error is recorded in the log if this occurs for troubleshooting purposes.

Execution

Execution operates in exactly the same way as any other project. Validation is performed first - this will include checking that the files and columns defined in the csv definition exist. If a file or column is missing, the execution will be aborted during the loading stage.

Troubleshooting

On selecting a file for a CSV definition, the application hangs or an error is shown indicating that the default field definitions could not be created

• Confirm that you have permissions to read this file
• Check the encoding type of the file. If it does not match that of the file this can cause unpredictable results in retrieving csv data from the file. If the encoding of the file is found to be different, uncheck the 'Use Default' option and select the correct encoding method before selecting the file
• If the file is very large (>1m rows), it can take Precision Bridge some time to scan it to find the number of columns and rows it has.

Some values in my file are failing to be migrated, the target value is being set to NULL, or some other unexpected value.

• Check that the value in the row where the issue is matches its type and, for date/time values matches the format defined for this field in the CSV File Definition. The format must match the data exactly. Precision Bridge does not support multiple date time formats being used in the same column.
• For assignment field mappings change the On Error action to 'Log Error' in an attempt to see if there is an issue in calculating the expression.
• For lookup field mappings, change the No Match action to 'Log Error' in an attempt to see if there are actually any matches found or not
• Service Now Target Only: If import sets are being used to migrate the data, check the import set record value. If a value is set here, but not on the target, the issue is likely to be either:
  • A business rule is changing the value - try with business rules disabled
  • ACL's exist that are preventing this value being updated by the migration user
  • The field is calculated and being overwritten.