Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Dynamic selection of text file; open and edit; save elsewhere...

Archaeopteryx
10 - Fireball

Hi,

 

I am tasked with creating a workflow that will ::

1) Select text ( .txt ) files whose names are in a certain format

2) Insert a date ( from the file name ) into a specific position in row two ( between two empty commas )

3) Rewrite the edited txt file to a new directory.

 

A directory, which is local for now, contains .txt files whose file names have a specific format. I must use Alteryx to find those files whose names have "BB" in the 6th and 7th position of the file name.

 

Each file name has the date in YYYYMMDD format. 

 

I must capture that date, insert it in between two empty commas in line 2 of the content in MMDDYYYY format. 

 

My largest challenge at the moment is to have Alteryx look in a local directory, pick the correct .txt file, do what I've described above, then go to the next .txt file that meets the criteria mentioned. Do this until the last file has been edited and rewritten.

 

Any assistance with getting started would be appreciated. 

 

Chris

 

 

8 REPLIES 8
JoeS
Alteryx
Alteryx

Hi @Archaeopteryx

 

It sounds like you may want to investigate the Directory Tool and you'd then need to create a batch macro for each file to do the replace on line 2 for each file.

 

I am happy to build a workflow to get you started, but didn't want to in case you wanted some pointers and to then try yourself. If you do want me to build it out, if you could upload a couple sample files that'd be great.

Archaeopteryx
10 - Fireball

HI Joe,

 

Thank you and I appreciate your willingness to help. I think if you don't mind helping me get started with part 1, Reading text files where "BB" is in the sixth and seventh position of the file name. That would be a big help. I have provided three files as examples of input. 

 

Thank you,

Chris

JoeS
Alteryx
Alteryx

Hi Chris,

 

I have built out a workflow and macro to achieve what you were looking for.

 

The first workflow looks to filter the data in a specified folder that meets your criteria:

Workflow.png

 

The Directory tool looks in the folder for .txt, the filter tool then checks the two characters equal "BB", the formula tool then creates a field with the desired output location (you will need to change this to meet your requirements)

 

The macro is then where the replace comes in:

Macro.png

 

The input tool read the data in with no delimiter or header (so not to lose any formatting) and outputting the file name as a field. 

Add a record id to then split it into 3 chunks. 

Row 1, Row 2 and the rest.

For Row 2 I then use a formula and replace statement to replace the ,, with the date from the file name.

Replace([Field_1], ",,", ","+Substring([FileName],7,8)+",")

I then remove the recordID and file name and output the data.

 

Control Parameters update the input and output and that should get you what you need. I have included everything in a zip here too.

 

Archaeopteryx
10 - Fireball

Hi,

I'm running version 11.8 in Designer. I won't be able to upgrade to 2018.2 until our Server upgrades in October. The macro referenced uses features that 11.8 Designer does not have. So, I'm not certain whether 11.8 Designer can perform the actions I need it to. 

 

Anyway, I'll study the diagram and see if I can somehow replicate the macro's activities in 11.8. 

 

Thank you,

Chris

JoeS
Alteryx
Alteryx

Hi Chris,

 

I have attached the workflows changed to work with your version.

 

The version is stored at the top of the XML. You can reverse it to earlier versions and it will work so long as the workflow is not using any new features (which I don't think it was)

 

Details how here: https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Adjusting-Alteryx-Files-for-Different-Versio...

 

Thanks

 

Joe

Archaeopteryx
10 - Fireball

Hi Joe

 

Thank you for hanging in there with me. I have attached the screen shot below. I'm uncertain what to select for this error message about mapping the control parameter. I have gone in to this workflow and provided the correct path for the input directory, but this one for the macro, which I placed in the Alteryx Designer macro folder, has me stumped. 

 

controlParameterMustBeMapped.png

Archaeopteryx
10 - Fireball

Joe,

 

I did some experimenting with the macro's configuration window and entered the following: 

InputFilePath: FullPath; and OutputfilePath: OutputPath.

 

So I saved this and ran the workflow and, you know, it seems to be working. I'll keep running this workflow with my source text files and then analyze the output files. 

 

One question I have about the BatchReadWrite Macro's Output.csv << -- why do I have this? 

 

controlParameterMightBe_Mapped.png

 

Thanks again, Joe. I would not have got this far without your help. Greatly appreciated.

Chris

JoeS
Alteryx
Alteryx

Hi Chris,

 

I am glad you managed to find out the right set up.

I am more than happy to help!

Labels