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

Macro to open files, fix a field and write over the file.

Highlighted
Asteroid

I have a folder with about 100 .csv files - some of the files, but not 100%, have a field called 'Comments'.

I need to open each file, go to the comments field and take out the double quotes. (Replace([Comments], '"", ' '))

THEN reoutput the file back to the directory.

 

I've done a few macros before but I'm having trouble with this one.... 

I think this would be a batch macro.

I'm unsure how to get it to write out file with the 'fixed' Comments field over the existing file.

 

I welcome some direction on how to approach this effort!

 

 

Quasar

I've not tested this thoroughly, but here's the rough idea:

 

You'll need a set of macros to actually accomplish the task here.

 

1) Your workflow starts with a directory read of the folder and then routes to a macro which reads the fields in each file. This is necessary because as you've mentioned, the schema for the files differs.

 

Macro:

field info macro.PNG

This will loop through the files and get the fields in each file, those with comments and those without.

 

2) from there, filter out for only those files with the "Comments" field available

workflow.png

(The formula tool is to just modify the output of the field info tool to get the filename needed for the second macro.)

 

3) The second macro then uses a formula tool to remove the double quotes in your files, and outputs them to their original location, leaving all other fields intact.

macro 2.PNG

 

There may be a simpler way to go about this, but that was the best idea I had at the time!

 

Good luck!

Inactive User
Not applicable

Macro creation: input tool pointing to generic location, control parameter over this replacing path. Then ensure csv has output full path option as a field. Then use your formula. Output tool and then write using the full path from the input and deselect it as a field.

 

Then, new workflow, directory tool pointing to location, pull full paths. Lay that over the batch macro. Done.

Labels