10-23-2020 08:54 AM
I have a quite large excel file (>150 columns) and need to sort the mapped fields precisely for target destination. I use File Reader > Excel Parser > Mapper…
At the moment the fields run A, B,C…BX,BY…GZ. However after dragging the Input Schema fields onto the Mapping Table they are re-ordered A, AA, AB …GY,GZ… X,Y,Z. I need the target schema to be ordered exactly how they are done in the original input. How can this be done?
I tried Sort Snap and Sort Path as $ , $A , just various combinations to no success
Any suggestions on how to avoid ‘manually’ having to set the Fields in the default order. Assume that the default order is mandatory.
Solved! Go to Solution.
10-25-2020 09:24 AM
@Raph, I assume you want to do some transformation to the data, or otherwise, I don’t think you need the mapper at all; just push the data as-is to the target, without the mapper.
If it is the case that you need the mapper and want to maintain the key name order in the output data (and don’t necessarily care about the schema view), you may get by with just checking the checkbox in your mapper. It won’t re-order the schema view in the UI, but you can still manipulate some or all of the data and the output to the target should return to original order.
Otherwise, if you absolutely need the schema view in the UI to represent the original order, attached below is a sample pipeline with an optional hack to help.
The Pad Key Names mapper prefixes the key names with spaces, keeping them all the same fixed length, based on the length of the rightmost column key name. Since spaces are first in the schema sort order, and all your key names should be upper case, the Transform Values mapper schema should keep the same key order in its input schema, while allowing you to manipulate the data for the target. The Trim Key Names mapper reverts the key names back to their original names, without spaces, while maintaining the key order.
Community.8623_2020_10_25.slp (8.1 KB)
10-24-2020 02:28 PM
Hi @Raph,
It’s very simple. You just need to not map the columns manually, i.e use from a Mapper this expression
$.mapValues((value,key) => value) into $ as target and that’s all.
Attached is a pipeline
Original order for columns_2020_10_24.slp (3.4 KB)
/Igor
10-27-2020 05:27 AM
I tried this suggestion. I am sorry I don’t understand what you meant. The mapper generates an error. The snaps used for test were as follows: Filreader > Parser > Mapper (.mapValues((value,key)=>value) > Excel Formatter > File Writer.
The columns run from A…GM
Can you clarify a little please?
10-27-2020 05:47 AM
Hi @Raph,
$
.mapValues((value,key)=>value)). I don’t know why i can’t see the sign $
. in front of mapValues. Did you maybe use .mapValues((value,key)=>value)) without $
? You can take a look at the attached pipeline “Original order for columns_2020_10_24.slp” above and execute it. Then just make similarly in your pipeline.
You can also share your pipeline.
/Igor
10-27-2020 06:05 AM
Hi @igormicev . Apologies. I did leave out the $ in error. This is also an effective solution to the problem.
Now if there is a table structure or .csv file with many many columns I can perform transformations, mappings etc with the order maintained if necessary.
Thank you.