Input more than one Excel file in one macro

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!

Import PAT Workbook Scores v3.yxmc

Input

Macros

Accepted answers

JordanB

@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

All comments

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:

\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:

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

And the Input Tool is connecting to

\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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703