Mapping data from Excel to XML - how to handle optional fields
Question asked by Gianluca Rattacaso - 2/16/2023 at 2:32 PM
Hi there, 

I'm new with Liquid Studio. 

I'm trying to map rows from an Excel file into an XML with multiple occurrences.
The issue I'm running into is the following, when a field is optional in the xsd and the matching field in the Excel is empty. Instead of just skipping the element in the XML, the mapper throws an error. (The error occurs when the mapper tries to convert an empty 'date' field into DateTime).

Example ;


field A (required)field B (required)field C(optional)Field D (required)
row 1value_1value_2value_3value_4
row 2value_5value_6

I'm hoping to get an XML as follows ;


Sorry for the rather simple question but I wonder if such a behavior is feasible. 

Thank you,

2 Replies

Reply to Thread
Liquid Support Replied
Employee Post
The following transform should do what you want, 
However if you have the following source data
05/09/1999value 145345

Value 2345

and you set Col1 up as a Date, then when it reads the empty value it tries to cast it to a Date - which fails.

Having reviewed this functionality we think it should return a 'null' value when no data is present in a cell, which would see the above transform generating the data 

        <MyString>value 1</MyString>
        <MyString>Value 2</MyString>
We will make the changes in the next point release.

In the meantime you can do this to work around the issue

Where Col1 is defined as containing a "string".

Liquid Support Replied
Employee Post Marked As Answer

A fix for this issue is now available in v20.2.5:

Empty fields now create empty sequences unless the data type is a string, in which case an empty string is returned.

Reply to Thread