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

[brendafos]

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!

[neilgallen · Accepted solution]

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:

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

(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.

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]

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.