How can I use filenames as variables in Input Data

[franc1s]

I have a semi-unstructured data file. The unstructured part is a header section which is somewhat "free-style". 

At the moment I am using the input data tool 3 times to pull 1) 2 items from a header section and 2) pull delimited data from from the structured part of the file. So, i all cases, I am pulling data from (each) a file with 3 input data tools.

For 1) I am using the "start row", "row numbers to read" and special delimiters to read the rows from the unstructured data that I am interested in.

As such, the 3 data input tools have a special configuration each, although they are linked to the same file.

The problem arises when I want to loop over a number of files. How can I keep the input file names in sync across the 3 input tools?

Is there a better way to do this altogether?

Thanks very much in advance.

[atcodedog05]

Hi @franc1s 

You can set the option output filename as filed as filename only like below

Hope this helps : )

[franc1s]

Thanks. Now, how can I take the data in the filename column as input for the file name in two other Input Data tools?

[Luke_C]

Hi @franc1s 

Are all 3 of those input tools using the same file? If so you just need one input tool and you can connect it to multiple different tools.

[franc1s]

Due to the formatting of the input file i believe i need the 3 input tools to retrieve structured and unstructured data from the same file. I am not sure how to do this differently at the moment. I want to pull the currency and the reporting period from the "header", after that. its all "|" delimited.

[Luke_C · Accepted solution]

@franc1s 

You can definitely read all the data in and parse it within the workflow. For example, if you need to skip rows you can use a sample tool to skip the first x rows and ignore the header.

Maybe you can provide some sample data?

[atcodedog05]

Hi @franc1s 

You can use join tool to join on filename and get only the same file data across.

Hope this helps : )

[franc1s]

Thank you everyone. You have all given me good ideas. I will share how I will end up dealing with this.

[franc1s]

I first separated the formatted data from the free formatted data, and for the latter used the sample tool (as suggested above) to work on the free formatted data separately. I will still experiment to see if i can make this more efficient by using the text to columns tool on the free format data instead of the formula/select and filter tools. Thanks everyone!

Then it was just a matter of combining the data as I was doing before.

I'm sure 

[atcodedog05 · Accepted solution]

Hi @franc1s 

Here you can use the join tool instead of the append tool. Join on filename since filename will be same it be many to many match similar to append tool. Also, this way only same filenames are matched.

Hope this helps : )