community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Add File Creation Date as Column From Directory Input.

Highlighted
Atom

Hi All,

 

I am trying to build an archive of data files in several sub-folders of in one master folder. The files are a total of 70+ files each with average rows of 20k and most of the files have the same name as they were generated by the same cloud system. It is a database of customer lead scores over a period of time so the most of the file entries are duplicates. The only thing we can use to differentiate them are the dates the files where created/generated from the cloud. I have so far been able to merge the all the files from all sub-folders in into a single database file using a batch macro. But in other to differentiate them each entry int the mater file, I need to add the file creation date, which is not a column in each file. Any ideas on how i can do this? 

 

Thank you.

Bolide

Hi @abdulib 

 

Where are you reading these files from? If it's from a local/network drive you can just use a Directory input at the start, which will give you the info you need downstream:

Capture2.PNG

 

...after which, you could use the dynamic input tool to bring in your files along with the [CreationTime] field as part of your batch process.

Or are you reading from a database? 

 

Andy 

 

 

Atom

Thank you for your response @andyuttley.

 

I am reading the files in from a local/network drive. 

 

Can you explain further on how to use the dynamic input tool to bring in your files along with the [CreationTime] field as part of your batch process?

 

This was the original batch macro I used to merge the files.

Batch Macro.PNG

Bolide

No problem. 

 

Assuming your control parameter is just feeding a list of file paths, you could instead retrieve these paths from the Directory tool, as this will give you the extra info you need, such as [Creation Time] (and others).

You can then maintain this information once you've brought the actual data in (example below I'm using a Dynamic Input): 

Capture3.PNG

 

Note that I've just used a select records tool (of 1 row) to replicate what would be your batch macro. 

 

There's also a great demo provided in your Alteryx Designer if you want to see some additional examples, just go to: Help -> Sample Workflows -> Learn One Tool at a Time -> Developer -> Dynamic Input 

 

Hope that helps

Andy 

Atom

Thank very much @andyuttely

 

The solution works but it only imports and appends the creation time to the first file in the directory. 

 

Apologies if my questions seems very basic. I only started using Alteryx last week.

Bolide

Hey @abdulib 

Great, pleased it works. Yes, that'll work just as well in a batch macro, which will run for all paths/files... 

 

I've included a full example here, including batch macro, which I'm hoping you can just point at your directory and plug straight in. 

 

Capture.PNG

 

....all I've done here is take the workflow from our example above and embed it in the macro so that it loops through all records (not just one)

 

Hope that helps! 

Andy 

Atom

Hi @andyuttley,

 

I can't seem to get it working for some reason.Can you explain how the filter works with control parameters and its configuration in the action tool? 

Bolide

hi @abdulib 

 

Did you download my attached yxzp file above and run it? Hopefully you can see everything I’m referring to here.

 

The control parameter doesn’t actually need any configuration; this tool is what makes the macro a batch macro, and will say ‘run it for every record you have feeding this’. The upside-down '?' you see feeding the batch macro is what's going into our control parameter. 

 

The macro input itself (shown below) will contain many rows, but we only want to run one row at a time. If your data is structured slightly differently you’ll find that the dynamic input tool will start to skip files because they don’t match the source input. This is why we’re batching it; basically saying run it independently as many times as there are rows, and then just union them together.

p1.PNGnote that there are 56 rows here, but the dynamic input tool can struggle sometimes if the files are different structures

 

 

The filter is only in place to ensure we’re just taking one row at a time. The way it’s configured is arbitrary, because it is the control parameter that will update this (via the action tool) for every single run:

p2.PNGHere it's taking the top row, but this filter will eventually match every record that's being fed in by the control parameter (we're only seeing 'one run' here)

 

 

The action tool is needed for the control parameter to update the filter so it matches the path of row 1 on run 1, row 2 of run 2 etc. It’s fairly simple to configure: just leave it as ‘update value’ and choose the static value in your filter (hence why it was an arbitrary value), which it will update each time. As is shown below:

p3.PNGHere we're telling the action tool (on each run) to replace the value in [FullPath]=[Value] every time it runs. Hence the macro being run as many times as there are rows going into your control parameter (the upside-down '?')

 

 

Hopefully that answers your question? If yours still isn't working feel free to package it and I can take a look.  

Andy  

Labels