Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

[Urgent] Macro that gives back the data from any filepath

dvdfrck
7 - Meteor

Hi,

 

I am trying to create a macro, which takes a 1x1 table with any filepath of any .xlsm file as an input and gives me the data form that file as an output.

 

Later I will have multiple Folders which all contain 3 .xlsm sheets. the workflow will be saved in every folder an is going to be used only once in every Folder. Thats why I want a really dynamic implementation without adjustments for every Folder. With the directory tool (.\ and *.xlsm*) I get all the file paths and with that I want to get the data behind the paths. I mean it must be possible somhow to get the data from the file with "only" the filepath. But I dont know how and I am running out of approaches.

 

Thanks for your help in advance!

 

Greetings, David

12 REPLIES 12
paulfound
11 - Bolide

Hi @dvdfrck 

 

Think it's not possible as different files have different outputs. I Completed an old weekly challenge recently #180. 

 

https://community.alteryx.com/t5/Weekly-Challenge/Challenge-180-Thousand-File-Challenge/td-p/461356 

 

This is the whole challenge, I would check it out for some answers, or ideas.

 

Thanks

dvdfrck
7 - Meteor

Hi PaulFound,

 

That is not a Problem. The Files are pretty similar and I already have a Filter which transforms all possible data into the same structure. I just dont get the data importet.

 

Greetings David

 

 

paulfound
11 - Bolide

Maybe it would help if you shared an example? 

 

Maybe share what you have already?

dvdfrck
7 - Meteor

Hi PaulFound,

 

saidy the data is all confidential. I really just need something that takes the 1x1 table containing the filepath and puts out the data from that file. 

 

for example:

table 1x1: C:\user\david\a1.xlsm -> "Something" -> data from a1.xlsm

or

table 1x1: D:\user\alex\abxdfg.xlsm -> "Something" -> data from abxdfg.xlsm

 

The "Something" is that what I need and it should work for all possible filepaths without the need for a manual interaction each time. 

Does that help you?

 

Thanks for your time!

 

Grettings David

paulfound
11 - Bolide

Sounds like you want the Dynamic Input Tool.

 

PaulFound_0-1598447908402.png

https://help.alteryx.com/2020.2/DynamicInput.htm

 

This takes a filepath as a parameter and returns the data.

dvdfrck
7 - Meteor

I tried it put it always want another path first otherwise i get an error

paulfound
11 - Bolide

@dvdfrck I think I know the issue.

 

Its a bit of a messy fix, but it should work.

 

1. Drop in a dynamic input. 

2. Type into the template source to %temp%test.csv and click ok. 

 

Notice that this is set to a CSV file:

PaulFound_1-1598449095684.png

 

3. Make sure your able to view xml properties:

PaulFound_2-1598449188614.png

If you cant' go to user settings and enable it.

PaulFound_4-1598449265719.png

4. Once enabled click on the dynamic tool and view xml

5. You should see this:

PaulFound_5-1598449345983.png

or something similar.

 

6. In the top right click edit and change the FileFormat from "0" to "-1"

PaulFound_6-1598449440754.png

if you click back onto the dynamic input and click edit you'll see that format is blank:

PaulFound_7-1598449502686.png

 

And that's it. as long as the outputs have the same number of columns it will work.

 

dvdfrck
7 - Meteor

Thanks PaulFound,

 

I followed your instruction and another error has occured when I tried to run it:

 

Anmerkung 2020-08-27 125949.png

 

I manualy add the name of the Sheet with a formular tool and the syntax : "|||'Datenbasis$'". Is that maybe the mistake?

paulfound
11 - Bolide

Hi @dvdfrck 

 

filename|||Datenbasis

 

should be enough, there as long as the excel has a sheet called Datenbasis.

Labels