Sometimes, the data in your source file isn't in the exact structure or format that your forms require. Data Transformation is an optional but powerful step in the import process that allows you to clean, restructure, and manipulate your source data before it gets imported.
This feature is essential for complex imports and is required when using JSON or XML files. It gives you complete control to reshape your data to meet any requirement.
You'll build your transformation in the Import Processing Editor. This tool is designed to make writing the necessary XSLT 1.0 code much easier by visualizing your source data and providing shortcuts to generate code.
The editor shows your source data structure on the right, allowing you to click buttons to automatically insert loops, values, and conditional logic into the code editor on the left.
The transformation process uses a standard web technology called XSLT (Extensible Stylesheet Language Transformations). Even if you're not familiar with XSLT, the workflow is straightforward:
- Initial Conversion to XML: The system first takes your source file—whether it's a CSV, JSON, or XML file—and converts it into a standardized, intermediate XML format.
- Your Transformation is Applied: The system then applies your custom XSLT stylesheet to this intermediate XML. Your stylesheet contains a set of rules that tell the system how to change the XML structure.
- Final XML is Produced: The output of your transformation is a final XML file structured in the exact format the Import Module requires for processing.
This final, clean XML is then used for caching, matching, and importing into your forms.
Data transformations can solve a wide range of common data problems:
- Restructure Data: Change the layout of your source data, such as converting nested JSON or XML into the flat structure needed for import.
- Clean Data: Correct inconsistencies, such as reformatting dates, trimming extra spaces, or changing text to uppercase.
- Combine Fields: Concatenate multiple source columns into a single target column (e.g., combining {FirstName} and {LastName} into {FullName}).
- Split a Field: Split a single source column into multiple target columns (e.g., splitting {FullName} into {FirstName} and {LastName}).
- Combine Rows: Consolidate multiple source rows into a single row in the final output.
- Split a Row: Split a single source row into multiple rows in the final output.
- Use Conditional Logic: Apply if/then logic to route data to different target columns based on the value of another field (e.g., if {ContactType} is "Home", map a number to the {HomePhone} column).
- Create Repeater Data: Loop through nested data to create the <instance> nodes required for repeater imports.
Next Steps
Before you can write a transformation, you need to know what the intermediate and final XML structures look like. The next article provides a detailed guide with clear examples of these formats.