how to pick columns based on a sample file and picking it from different excel files ?

i have multiple excel files which has many sheets inside it. So i need to get an output of certain fields based on the sample file attached. The excel formats are same for all input files (the input files can be two files or hundreds of files). i have attached the input file format and sample Output data i need to extract from the files i upload. if anyone of you can help me, would be appreciated

output file format.xlsx

Sample input file.xlsx

Workflow

Datasets

Output

Custom Tools

Accepted answers

caltang

Then you can get it from the file name if you can change it to full path from the input, you should get the sheet name also.

If the above solved your need, kindly like & mark as accepted solution so that you may help others find the solution more quickly + to close the thread as is. Thanks!

Best regards,
Calvin Tang
https://www.linkedin.com/in/calvintangkw/

Emmanuel_G

@Binu456m

As we discussed, the main steps are :

1 - Get a macro to read and concatenate all the files in directory

2 - then filter on the labels you want in the output template. AT this step, I have selected the fields that contains labels and the corresponding values (BDO, F3, F5, F17)

3 - finally change the layout by using either crosstabl tool. It allows us to have one line for each filename as you said there are more than 100 files.

Do not hesitate to mark answer as solution to help others.

All comments

caltang

You'll need to group and cleanse the values to get those values into a proper data frame like your output set.

This is a complete build and will take some effort. Who are you building this for?

Binu456m

This is building for my project. Since i have 100+ files as input file, it is impossible to cleanse the data and run right? all 100 files has same format data. so is there anyway to get the output?

caltang

Can you provide three sample files? Just to see if the dataframe is consistent amongst all the files.

In theory it is doable, does take some time.

Binu456m

attached two input files for your reference. the expecting output file format, i have attached earlier. any help would be appreciated

2023 BCTO Engagement Budget Template - Assurance.xlsx

2023 Piada Engagement Budget Template.xlsx

caltang

@Binu456m your two attached files have problems. I can't open them. Can you check?

caltang

Also, from your sample file:

You hid some rows - and they're still referencing your SharePoint man.... I can't use these files.

Binu456m

I just need data from the highlighted columns and other can be ignored... i need column name and column value based on each file (all file has same template)

cjaneczko

Start by using a directory tool to grab all the files. Then you can use a union tool and have it "Auto Config by Name" and "Output Common Subset of Fields". Use the Template file as the first join, then join the directory tool to it.