Pulling Year Information From Row

[smoskowitz]

Hey all --

I have a scenario I have been trying to work out and want to see how you might approach it. I solved the issue, but wanted and alternative.

Each file starts off like the one below. #usd 12-<Year>. I have about 10 of these that I want to merge together, I want to associate the file to its year. So in the first column for each row -- I want to have the year repeated for each data element by year.  So when 2013 comes in I will have 2013 in each row.

I solved it by bringing in the file name a parsing from that, but I want to see how to pull the field from the file itself, relate it to the data, and then remove the row #usd-12-<year.> Thanks...seth

[adm510 · Accepted solution]

Add a record id so you can filter row 1 to extract the year, then use a dynamic rename tool to rename the rest of the data. Take a look at the example I attached

EDIT: I just realized you attached some sample data. I modified the workflow to work with that file so the screenshot looks slightly different

[mborriero]

Hi @smoskowitz

I would have used something like that.

[smoskowitz]

So they theory above works for me for one file, but if I try to bring in multiple .csv files -- this process breaks. How can I bring in 10 files and have this process run for each file? Right now I am just using a wildcard in the directory where all the files are and it looks like it opens all files first -- then runs through the process.

Thanks, for your help.

Seth

[adm510]

Place the workflow above in a batch macro, then use a directory tool as the input for the macro.

[smoskowitz]

Thank you for the reply. I tried to do what you said and can't seem to get it right. It still seems to bring everything in, but only appends 1 particular year. 

I'm sure I am not creating the macro correctly (maybe a little advanced for my skill level. Any suggestions and or insight you can provide would be helpful.

[smoskowitz]

I seem to be very close, it brings in each file but assigns a single year to it. The year is attributed to the template I have in the macro. Not sure how to change this yet.

Thanks,

Seth

[adm510]

It worked perfectly for me, are you sure you were passing in the full path when you called the macro?  Also, in the action tool, you don't need to check the option to replace a specific string, if you going to replace the whole string... but either way works

[smoskowitz]

I got it to work. I changed the input tool in the macro to a *.csv and it worked perfectly. I am not sure why, but it did.

Thanks,

Seth