How to upload Multiple Tabbed Excel file For Reading from Analytic App

[bsharbo]

Hello everyone. I apologize for what might be another simple question but I am stuck with this one! 

I have a request that a user be able to upload a multi tabbed Excel file to an analytic app on the gallery.  If it was a csv file or a basic text file I would simply connect an update tool to the Input Data tool, however given that is is an multi tabbed Excel file I am not really sure how to handle this one...

First Idea

my first thought was to simply upload the file location, (path location + file name) and then write some code to have the server go grab the file (pull the file and "copy" it on the C drive of the server to then do whatever you need with it), however users will be uploading files from aliased directories.   AKA they will upload a file from the drive of "I:\Projects" instead of the full proper UNC path of \\cgi.company.com\projects.

Unfortunately since every user can map drives to whatever name they want I can't rely on a drive mapping, so I don't think this idea has legs.

Second Idea

is there any way to write a tool that simply takes the file that the user uploaded to the server and copies it to a specific path.  Aka take the entire file and simply Save AS it to the location of c:\user files  or something along those lines?

Has anyone run into this requirement / problem before?

[MarqueeCrew]

How about a run command to copy it?

[bsharbo]

Hello marquee. This sounds amazing... Do you have any idea how to do this?

my biggest concern is I don't know how to run a copy command against a network path that isn't mapped on my server (the server has rights to the network path but needs to use the UNC path).

AkA the user interacts with the app and uploads a file named I:\projects\Brandon\myfile.xlsx.

In reality this is located at \\cgi.Company.com\projects\Brandon and the server won't have this UNC mapped as the "I" drive (won't have any network paths mapped).

If this doesn't matter and you still think that is possible would you be able to post a few simple example code?  Honestly I don't understand the file browse tool very well so am still a little iffy on what it can actually do.

[bsharbo]

Hello again Marquee!  I just realized that another way to ask my question would be.

Question: 

How do you get the location of a file uploaded through the File Browse of an app used on the analytic Gallery?

Reason why needed:

Lets say you need to upload a file to the application gallery, and you have a requirement to store a copy of the pure file that was uploaded to an archive location on the server for up to 3 years (for audit purposes / etc..).

So we would need a command line to copy the file from wherever the users upload it from (could be their desktops, mapped network drives, etc..) to a server network drive of C:\Audit Purposes\Files

In theory if I can figure out how to the copy an uploaded file to a given location, I can then work with that file and figure out basically everything else I need :-)

As always thanks for your help!

[MarqueeCrew · Accepted solution]

@bsharbo,

From your application, I would update the location of the file (from) and have a fixed directory for the location of the target.   When I use the command prompt for something like this, I have a dummy.csv file that contains a single row of "Dummy".  

If I want to make sure that the copy has taken place before the next step, I can use an APPEND FIELDS tool and append the output of the copy to the rest of the data (but don't actually append the data).  This will ensure that the copy is complete before the data moves past this point.  A typical use case would be:  Copy an Excel Template from a source directory to my target directory and then populate data into the raw data tab.

Hopefully this will help you.

[bsharbo]

Hey  MarqueeCrew.  I see what you are doing in the code, howver I won't know the location of the base file.

Unless I am being thick, in your example you are pulling from a hardcoded csv file located at \\Mac\home\downloads\alteryx Challenge\dummy.csv...

In my very specific case I need to replace that hard-coded location with whereever the file uploaded using a browse tool is put when run an an analytic app gallery.

So if I put a file upload use interface on an analytic app, and a user uploaded a file from their C drive of "C:\bsharbo\desktop"  to the server, I need to figure out where that file is being put on the server so I can copy it.

I can't run "C:\bsharbo\desktop"  because the server doesn't have this file location.   

So in short I don't know how to update the "source" or "from" of the copy command, because I have no idea where the heck the file is going!  That is my problem really. 

Is my question making sense?

[bsharbo]

MarqueeCrew was able to hop on a phone call with me and walk me through a solution using his run command.  I am going to mark his post as correct, and then I will post a detailed explanation of the steps that he walked me through on my server to get this to work.

MarqueeCrew thank you again! You really went above and beyond to help me with this problem, and without your help I would have remained stuck for a long period of time! 

[bsharbo]

Steps to Grab file uploaded through Analytic App to Gallery: Explained to me by marquee Crew

Hello everyone. Below are the steps that Marquee Crew walked me through in order to grab a file that is uploaded to an analytic application.  This will allow end users to store the files that are uploaded into an archive location for audit purposes / file manipulation / etc!

Attached is a sample workflow Marquee helped me create over the phone to test this functionality!

#1) Connect your file upload tool to a Run command using an Update Action!

It turns out that if you use a file upload tool and connect it to certain tools through an update action (text input, run command, etc..) it actually provides the location of the file that was uploaded.  When run through an analytic app, the folder location looks like a long GUID string (something like In_5756e52fb1bf795355d79cba\Yourfilename.xls|yourtabName).  

When AlterYX runs an analytic app from the gallery, it makes a temporary folder location on the C drive of your server at the location of C:\ProgramData\Alteryx\Service\Staging (by default, this can be changed in your system settings however!).

When you use the file upload tool, it will provide you the name of the tempoary folder that is created.  This is where the file you uploaded is temporarily stored! This means that if you use a run command to copy the file, and you start it with this location, the alterYX gallery will be smart enough to go to that temporary folder and grab the file!

#2) Set up your run command to look the way mine does in the attached workflow, and then also your Update Tool command.

In our command argument we are copying the data file from "source" to a hard-coded file location of "C:\AlterYX Workflows\BoA File Upload\Archive".  Please note: You can make the output location a variable as well, but I did not since it is not required for this demonstration purpose.

Our command line arguement starts as...

/c copy "Source" "C:\AlterYX Workflows\BoA File Upload\Archive" /y

#3) Change your update vaule to "update with formula" and plug in the following formula.

replace([Destination],"Source",REGEX_Replace([#1], "(.*)(\|.*)","$1"))

This code is replacing the word "source" in the command line arguement with the results of the file you uploaded.  

HOWEVER: Some special options had to be put in place for Excel files.   When a user uploads an excel file, they have to choose a tab to upload.  In doing so the syntax of the file uploaded becomes In_5756e52fb1bf795355d79cba\Yourfilename.xls|yourtabName.     This is a problem since the command line for copy doesn't recognize the |YourTabName  portion of the file extension.   This is where the REGEX_replace comes into play.  It removes all of the strings after the pipe character is found, so your resulting string will just be the name of the Excel file and not the file name + the tab.

Your string will now look like In_5756e52fb1bf795355d79cba\Yourfilename.xls

#4) Create a temporary "fake" csv file named dummycsv.txt  and point your read results of the Run command to it.  

a) This file won't be used in any way, but seems to be necessary in order for the run command to properly work.

That's it!  This problem can now be put on your server gallery, and any file that you upload to that server gallery will now be copied to the location of C:\AlterYX Workflows\BoA File Upload\Archive  at run time!

I hope that helps users in the future who have a similar question!