Custom Mappings for Source ODBC and CSV connectionsCreated: 2015-03-12 14:58:50
Modified: 2019-07-31 17:06:58
Tags: CSV ODBC Troubleshooting UnitySync
As of UnitySync v2.4, handling of ODBC/CSV Source headers has been enhanced.
This enhancement allows the column names exported from ODBC/CSV to be LDIF compliant, making the headers easier to handle at Sync time. Now, UnitySync converts any non-alphanumeric character to a dash, including spaces and underscores.
Previously, underscores and spaces were commonly used in ODBC column headers, and UnitySync, at the time, converted all to underscores. These non-alphanumeric values must be changed when upgrading to the latest UnitySync.
Specifically, underscores and other non-alpha numeric values are no longer allowed:
- in Field Definitions on the Source tab, or
- in the Join Queries on the Destination tab (if applicable), or
- in custom Object and List map files on the right side of the equal sign in an ODBC or CSV source connection
The sole exception is the dash (-) character.
IMPORTANT NOTE: You do not need to change any of the column headers in your ODBC/CSV Source. Instead, review and make the following adjustments.
To determine if this applies to your connections, and to fix the affected connections, follow the steps below:
- Proceed with your upgrade as detailed in the Release Notes - Install Guide, including copying over all \connections, \map\custom, etc.
- Determine if you have any ODBC connections. Use our knowledge base article on using findstr to help you identify connections. Search for “ODBC-” and “csv-” as detailed in the instructions. This will tell you which connections use an ODBC or CSV source.
- Once you have determined which connections are effected, go to the UnitySync User Interface (UI) and look at each connection in turn.
On the Source tab:
- If column headers are disabled, no change needs to be made to this connection.
- If column headers are enabled, review the Source Data column headers. You may need to scroll right to view them all.
- Take note of any header that includes a dash character (-).
- If none of your column headers contain a dash, no change needs to be made to this connection.
- In the Field Definitions, For any column header that contains a dash, you must ensure that exact column header name, with the dash, is shown in the Field Definitions (if used).
- For any column header that contains a dash, you must also ensure your custom map file shows this exact column header. Make note of any affected column headers and see next steps.
On the Destination tab:
- If you are using Join functionality - that is, if you’ve selected either Join or Both for the Sync/Join Mode - you may need to update your join queries.
- Review your join query entries, located in the Join With Existing Objects box.
- For any column header name that is included in a join query, you must ensure the exact column header name as shown in the Field Definitions on the Source tab is included.
On the Custom tab:
- Open the custom Object map file for this connection.
- Review all values on the right side of the mapping syntax - anything to the right of the = sign.
- Update any mapping that show the affected columns headers.
- Once finished updating the mappings, click Save to close the map file.
For example, if your source column header in the ODBC or CSV Source actually contains a space, like “email address”, prior versions converted the space to an underscore, like “email_address”. Therefore, your mapping would have looked like this:
You should have seen on the Source tab that this column now shows as “email-address”. Therefore, you must change the mapping to match:
Make sure to review all column names on the right side of the mapping syntax. If any contain a space, underscore or non-alphanumeric value other than a dash, you must update the value to match the value noted while you were on the Source tab (which should include a dash).
If you require any assistance with these connection modifications, please contact Support.