Very Urgent /Extracting EXCEL nested data

[#1]

Hello friends,

we have one scenario that needs to fetch from excel file in the format which mentioned in below pic.

Target structure also listed in the below pic.

So please give me your valuable tips to sort out this requirement ASAP.

Thanks & Regards

Vaishaali

[Former Member]

This is not a one step process. When you have such kind of file you need to follow the basic rules

1) Get the basic data as it is in to the excel file format. remember to uncheck the option in the excel file format editor (use First row values as column names). If you uncheck this option then you will see Field Names like F1, F2, F3....

2) Categorize the data based on Filters in query transform and create column data using Case statements. For e.g. Scheme name can be categorized as follows

Select SCHEME_NAME= case when SCHEME_NAME like '%UTI-Fixed%' then 'Fixed Maturity Plan' else 'Others' from Master_data

Suppose if you filter all the unwanted rows from your master excel file it will look like this.

Then you create additional columns and categorize the data.You may also need to use the reverse pivot and Pivot transforms to rotate the data as per the axis values.

Although I tried to import the excel schema in to my Designer I couldn't do that as I need to install some drivers on my Linux job server in order to make it work.

I hope you will achieve this. Although it is complex but it is achievable by applying some tricks.

Regards

Arun Sasi

[Former Member]

Hello Sasi,

Thank you for your valuable input. I have been trying this a lot! but couldn't achieve it. If you don't mind can u plz share the .atl once you achive it.  Consider it as my humble request.

Thanks in Advance

Vaishaali

[Former Member]

Will try to work on it.

But it might delay as I dont have full control of the system.

Will keep you posted. Till then keep trying

Regards

Arun Sasi

[former_member187605]

I have once posted a solution based on flat file input, see . Maybe you can tweak it to deal with an Excel file, too.

Alternatively, you can save your Excel as a csv and then directly apply my approach.

In fact, my solution will definitely work for you. It's exactly the same situation, you've got data records of different nature and you'll have to combine data from header records (schema category / name) with your details (the numbers). You need one additional step only: once you've brought everything together use 2 Pivot transforms to map your data from columns into rows.

Message was edited by: Dirk Venken