Input more than one Excel file in one macro

[asteryx]

I have a simple workflow that reads an area from an Excel sheet into a database.

But when I try to turn it into a macro that runs through a directory full of Excel files with the same structure and imports all the areas from each file, it never works.

I've looked at all the examples of importing many files/many sheets, and I cannot get mine to work.

I use a directory tool to feed the full path to my macro.

In the macro, the Control parameter takes the full path and feeds it to the Action tool with a "replace string" option that equals the full path of the Data Input tool.

The Data Input tool has a SQL query to target the cells (Select * From '<sheetname>$c6:c72').

The whole process works, but only for the first file in the directory. I do not need to import more than one sheet per file and only need the specific range above for all of them.

(Note - the attached uses an example directory and filename, so ignore the error there.)

Thanks for any suggestions and help!

[JordanB]

Hi @asteryx

Can you try adding the sheetname and cell range directly to each full path (Formula tool) [Full Path]+"|<sheetname>$c6:c72' before feeding these into the macro. You will need to update action tool in the macro to replace the full string not just the file name. 

I suspect that when batching through full paths it may not keep the 'Table or Query' section so your select* statement may not be there on the second run, hence you not getting an output. I believe adding the sheet name and replacing the full path plus sheet name inside the macro will read through all the files. 

Let me know if you have any questions. 

Best,

Jordan Barker

Solutions Consultant 

[MikeB]

Hi @asteryx

One thing I see as I open your example is when I click on the Action tool, the "Value or Attribute to Update" doesn't appear to be on the right item.

What I see is the parent at the top "Input Data (1)" is selected.

If you see the same thing, try selecting the "File - value" item as shown here in my screenshot:

To see this same config panel, select the "Action" tool in between your Control Parameter and the Input tool

[asteryx]

Hi - sorry for not getting back here sooner and thanks for your feedback.

I don't follow your comment, MikeB.

My Action tool links downstream to an Input tool that Alteryx has named "Input Data (1)"

The file - value field is pointing to a directory called "sample1" in the file I attached here just so I could anonymize my submission. It's not the same in my actual workflow. (But both tools have the same "sample" text, so I don't think you're refering to that.)

The string it replaces in the Input Data tool already has the full path plus filename PLUS a pipe followed by a select statement. That is the latest version of my efforts to get the right configuration.

The Action Tool has "Replace a specific string" checked with the entry as:

D:\ignorethissamplefoldername\sampleworkbook-06June16.xlsx|SELECT * FROM `5. Export $c6:c72`

I've also tried this setting with the worksheet name only (removing the pipe and everything after it), and I get exactly the same behavior.

The Input Tool has the select statement in it's options setting under Table or Query, too, in both cases.

I've tried setting the Output Data Tool to 'Append Existing' and it also has no effect.

Can you explain a bit more what you're seeing?

Thanks.

[asteryx]

Hi - sorry for not getting back to this sooner.

Thanks for your input!

I think I implemented your suggestion...

Added a formula before calling the macro: [FullPath]+"|5. Export $c6:c72"

I already had the full path in the Action Tool.

The Input Tool requires something in the Table or Query option setting, though.

So, right now, I've got "Replace a specific string" as:

D:\fullpath\workbook-06June16.xlsx|5. Export $

And the Input Tool is connecting to

D:\fullpath\workbook-06June16.xlsx

with a the Table name "5. Export $"

I've tried the same configuration with the table name plus the cell range, too.

Unfortunately, I don't see a way to capture the string that the macro actually uses, which would help in debugging.

Appreciate any more ideas you may have.

-Laurence

[MikeB]

Ok, just ignore my comment.

I wanted to make sure you had the Action Tool pointing to the right XML element to udpate and it sounds like you do.

When I opened the sample workflow attached, the Action Tool was not set to the right item. So that's why I asked.

[patrick_digan]

@asteryx For what it's worth, see the attached working example. I'm guessing your workflow is very close to working, and it could be a number of small things. Since you asked about help with debugging, I figured I would show you what I would do:

Delete the action tool and rename the control parameter to Input under the annotation tab as shown below:

Change your input tool like so. You would obviously change your table or query back to what you need it to be, but I've used this setup for my example. I like using the undocumented %Question.<toolname>% method instead of action tools. It makes it easier to debug in my mind: 

Now on the workflow tab, you can feed in test data like so. After adding in my test file (.\testing.xlsx), I can then run the macro and make sure everything looks like it's working.

Finally I've added some tools to help us debug. The text input is just capturing the input from the control parameter, I append it back to the data I'm reading in, and then added an output so my workflow could capture it.

Now when I run my sample workflow, I get these results:

F1	Field1
Field1	C:\Users\216005535\Downloads\testing.xlsx
1	C:\Users\216005535\Downloads\testing.xlsx
Field1	C:\Users\216005535\Downloads\testing2.xlsx
5	C:\Users\216005535\Downloads\testing2.xlsx

Hope that helps! Let me know if anything is unclear.

[asteryx]

Thanks, Patrick. This looks very useful.

I've got some things that aren't working still:

- When I put %Question.ControlParam.Input% into the Input Tool and add my select statement to the configuration, I get an error:

WFileBrowser::FindFilesInFolder cannot process null search WString

- I can't edit the WorkflowDirectory in the Workflow Configuration.

Am I taking the wrong approach?

[patrick_digan]

You would want to add a default value in the macro. In the picture below, you would want to put a default value in lieu of the .\testing.xlsx below. It wouldn't be a best practice, but the macro would still run without a default value (since at runtime it's picking up the values from your workflow). 

[JordanB · Accepted solution]

@asteryx and I just ran through the workflow and the following amendments were made:

- Made sure the sheet syntax was correct after the full path: {Full Path]:"|`Sheet1$A1:L120`"

- Made sure were replacing the full string and not a specific string, because were were feeding in the full path plus sheet name into the macro. 

- We then configured the output tool to append to existing table, to make sure each file path was unioned together correctly. 

Workflow is now working. 

Thanks everyone for their posts and insight!

Best,

Jordan Barker

Solutions Consultant