Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
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