71 views

Extract column and value separated by Delimiter

Today, I will discuss about the scenario where data is present in a format column:value . See the below sample data for the same.

EmpId :1
Emp_First_Name:Ram
Emp_Last_Name:Kumar
Address:GachiBowli ,Hyderabad
Phone_Number:1234567890
Pin_code:122011

Such kind of data you might get in colleges,schools where admin team ask each student to fill in these details and in backend , it is getting stored in the format mentioned above.
See the below ETL code where we fetch employee details from the above data.

STEP1: Read Emp data using the “CSV file Input”. See the below SS.

Here, You might be thinking that I could have used “:” as delimiter ,but here is the point , we are not getting data in text file.
This data you might get on the fly using API /HTTP call which you might need to handle without any input file component. Hence , I have not mentioned the correct delimiter intentionally.
STEP2: Now, we need to convert the column data into row format using “Row Flattener”. See the below SS for the same.

STEP3: Now, we need to split the data using delimiter “:” using “Modified JavaScript Value” component with the help of array and split functions. See the below SS for the same.

Now, Data is available in row format. Hence it can be dumped into a file.
See the logs below.

2020/05/09 13:46:49 – Write to log.0 – ————> Linenr 1——————————
2020/05/09 13:46:49 – Write to log.0 – actual_Emp = 1
2020/05/09 13:46:49 – Write to log.0 – actual_FName = Ram
2020/05/09 13:46:49 – Write to log.0 – actual_LName = Kumar
2020/05/09 13:46:49 – Write to log.0 – actual_add = GachiBowli
2020/05/09 13:46:49 – Write to log.0 – actual_PNo = 1234567890
2020/05/09 13:46:49 – Write to log.0 –
2020/05/09 13:46:49 – Write to log.0 – ====================


Related posts