โ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.