read multiple files and bring data across different rows into tabular format

Good Day.

I have 100s of files where data sits across horizontal rows as seen in the two templates. I need the output as seen in Sheet1 in one of the files.

Example is only for the asset PR2177213. The data from other file should continue at the bottom. Can Alteryx process it?

As is Data in tab: PR Checklist

Output needed in tab: Sheet1

Thanks & Regards,

Chinmaya.

Workflow

Transformation

Accepted answers

AlteryxUserFL

Please see the attached workflow. It works for both of your attached files. There could be some situations that were not taken into account, but it should be simple to modify. Let me know if it works as expected 🙂

You can look into the directory tool and dynamic input tool to setup the workflow to run automaticity through all your files at once if desired.

Example Workflow.yxmd

All comments

atcodedog05

Hi @chinu267,

This article might be helpful to you.

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/The-Ultimate-Input-Data-Flowchart/ta-p/20480

AlteryxUserFL

This can be done in Alteryx. I can show you an example, but I have two question first. If there are multiple asset types in the first table, how do you know which line items in the second table correspond to the different assets. Also, can you ever have more then 5 line items?

chinu267

The first table(TYPE OF ASSET) in the excel file is the type of asset, which in this case is "System, Automation". The asset might have single/multiple sub items(can be more than 5). The sub items in the 2nd table(PO LINE ITEM / DESCRIPTION) belongs to asset "System, Automation".

There will be individual forms for every type of asset. Normally Multiple assets will not be in the same form, rather they will be in different forms. There might be exception to this at times. Sometimes there might be multiple assets in the first table. In this case they will be linked to main item using Activity CODE PCAM. Refer screen below

The third table(CASH FLOW PROJECTIONS*) has additional information about the line item descriptions that is related to payment terms/expected invoice dates. Some more files are attached as examples

AlteryxUserFL

You can look into the directory tool and dynamic input tool to setup the workflow to run automaticity through all your files at once if desired.

Example Workflow.yxmd

chinu267

@AlteryxUserFL I love your approach of attaching it by 3 different tables. Thanks for investing your valuable time to look into the problem and providing a possible solution.

Now issue is for every file the table locations/ranges are not the same. I need to either set named ranges in all 100+ files in some way or have to think of attacking the problem in a different way where we look for scanning all the lines at specific intervals and stop just when the next interval appears. Had it been a text file, I could have done it using program.

I will work on some other approach and see how it goes. If I have a better solution than yours I will post it here.

AlteryxUserFL

The set ranges can be helpful if there is some consistency. I was working under the assumption that the column names and header would be the same for each workbook. Then the other tools can narrow down the data as required based on setting the range as large as possible. Can you post a handful more files for testing and optimization of the flow.

chinu267

Column names and header are same across workbooks but issue is they start in different rows in the files. Have to find a way of picking the tables in each files dynamically which will help me to process multiple files quickly. Thank You @AlteryxUserFL

apathetichell

correct me if i'm wrong - there are three header types and three header categories in each record? The number doesn't really matter but the identification of a master list of types of record grouping is probably good.

You'll need to figure out a way to dynamically divide the records into different structures (hi multi-row formula - test to see if a previous row is null() and the new row contains something you define as characteristic of your header identifier) and allocate different records to different types of processes. Next you use a filter to assign them to the proper workflow. you use dynamic rename to get the columns in the right position.

You batch this process so that it repeats for each file. This is probably a nested batch process and may be a fairly substantial workflow. I do not know if you will be able to get a community sourced workflow as it's a fairly laborious process to build something like this.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703