โ11-22-2022 07:47 PM
Hoping someone might be able to find a solution to a challenge I have. I have a headerless csv file. Iโm also reading in a header array that I wish to match up with the data in the headerless csv. I donโt want to add the header values to the CSV Parser snap so that I can avoid creating a custom parser for a multitude of headerless files that I might receive. Basically, hereโs my situation.
โheadersโ: [โemployeeIdโ, โfirstNameโ, โlastNameโ]
The csv file looks like this after going through the CSV Parser.
โfield1โ: โ1001โ, โfield2โ: โSylvesterโ, โfield3โ: โStalloneโ
โfield1โ: โ1005โ, โfield2โ: โJohnโ, โfield3โ: โWickโ
โfield1โ: โ1010โ, โfield2โ: โArnoldโ, โfield3โ: โSchwarzeneggerโ
The results I desire should look like this:
โemployeeIdโ: โ1001โ, โfirstNameโ: โSylvesterโ, โlastNameโ: โStalloneโ
โemployeeIdโ: โ1005โ, โfirstNameโ: โJohnโ, โlastNameโ: โWickโ
โemployeeIdโ: โ1010โ, โfirstNameโ: โArnoldโ, โlastNameโ: โSchwarzeneggerโ
Iโve been playing around with sl.zipObject, but I canโt seem to make it work correctly. Hints, tips, and tricks would be greatly appreciated.
Thanks, Alex
Solved! Go to Solution.
โ11-23-2022 03:22 PM
I got my solution! @Spiro_Taleski, thank you so much for your wisdom! This is what Spiro showed me.
I applied his solution to the bigger challenge I was having and this sample pipeline does exactly what I needed it to do.
sample_ConfiguredHeadersToData_2022_11_23.slp (8.0 KB)
Thank you all!
Alex
โ11-22-2022 10:04 PM
Below is my pipeline that simulates your situation. Could it be that after the csv parser you map field1, field2, and field3 to your corresponding field names?
community_pipeline_V1.0_2022_11_23.slp (6.5 KB)
Let me know if this works for you
Regards
Jens
โ11-23-2022 08:25 AM
Thank you, Jens, for your suggestion. Itโs certainly a valid solution, however, based on the needs of our application architecture, it wonโt really fit in with our design pattern. We are moving away from using customized mappings, and having everything configurable instead. For this reason, I was focused on the sl.zipObject method to automatically align our data with configured headers. Hereโs an example.
Say we have 2 customers that send us employee headerless data (we actually have more, which makes it even more important to develop a non-customized strategy that works for every customer). We donโt enforce any strict schemas or column ordering on our customers, so they can send the data elements in any order.
For customer ABC, we have a header configuration of โ[employeeId, firstName, lastName, hireDate].โ ABC sends their data like this, with the CSV parser results afterwards:
โ1001โ, โSylvesterโ, โStalloneโ, โ2001-08-21โ
โ1010โ, โNicoleโ, โKidmanโ, โ1997-05-18โโfield001โ: โ1001โ, โfield002โ: โSylvesterโ, โfield003โ: โStalloneโ, โfield004โ: โ2001-08-21โ
โfield001โ: โ1010โ, โfield002โ: โNicoleโ, โfield003โ: โKidmanโ, โfield004โ: โ1997-05-18โ
For customer XYZ, we have a header configuration of โ[DOB_Date, First_Name, ID, Last_Name].โ XYZ sends their data like this, with the CSV parser results afterwards:
โ1960-07-04โ, โBobbyโ, โ99999โ, โFischerโ
โ2005-04-27โ, โBillie Jeanโ, โ88888โ, โKingโโfield001โ: โ1960-07-04โ, โfield002โ: โBobbyโ, โfield003โ: โ99999โ, โfield004โ: โFischerโ
โfield001โ: โ2005-04-27โ, โfield002โ: โBillie Jeanโ, โfield003โ: โ88888โ, โfield004โ: โKingโ
Without having a create a separate parser with embedded header names for each customer, and without having to use a custom mapper for each customer, this is what I want to achieve for each respective customer.
ABC:
โemployeeIdโ: โ1001โ, โfirstNameโ: โSylvesterโ, โlastNameโ: โStalloneโ, โhireDateโ: โ2001-08-21โ
โemployeeIdโ: โ1010โ, โfirstNameโ: โNicoleโ, โlastNameโ: โKidmanโ, โhireDateโ: โ1997-05-18โ
XYZ:
โDOB_Dateโ: โ1960-07-04โ, โFirst_Nameโ: โBobbyโ, โIDโ: โ99999โ, โLast_Nameโ: โFischerโ
โDOB_Dateโ: โ2005-04-27โ, โFirst_Nameโ: โBillie Jeanโ, โIDโ": โ88888โ, โLast_Nameโ: โKingโ
Iโve had partial success using ideas derived from Transforming JSON Data and JSON returns Column Names separately from the Rows - #5 by Garrett and using the sl.zipObject method, however, after going through the parser, my data has the field001, field002, etc. key tags on them, which is where Iโm running into a wall. I feel if I could create an array of values for each row of my data, without key tags on them, then this might be the solution Iโm looking for.
If I could make my post parser data transform from this:
โfield001โ: โ1960-07-04โ, โfield002โ: โBobbyโ, โfield003โ: โ99999โ, โfield004โ: โFischerโ
to this, I feel like I could achieve my goal, because once I have the data array, the sl.zipObject method should work exactly as it does in the links I referenced above.
[โ1960-07-04โ, โBobbyโ, โ99999โ, โFischerโ]
Anyways, thanks again Jens. Even though yours wasnโt the exact solution I was looking for, I truly do appreciate your time and generosity in responding to my plea for help. ๐
Alex
โ11-23-2022 03:22 PM
I got my solution! @Spiro_Taleski, thank you so much for your wisdom! This is what Spiro showed me.
I applied his solution to the bigger challenge I was having and this sample pipeline does exactly what I needed it to do.
sample_ConfiguredHeadersToData_2022_11_23.slp (8.0 KB)
Thank you all!
Alex