Link to external files in ACP3

Rob,

• ACP on Lumina's server allow files with extensions *.ana, *.xlsx, *.xls, *.txt and *.csv.  To upload a file to your ACP3 directory, use the Upload button in the User interface. In the windows open file dialog, switch to 'all files' in the file types and select the data file to upload.

• For security reasons, Lumina's ACP server does not support the Analytica spreadsheet functions for use with excel.
  • https://docs.analytica.com/index.php/ACP3#Features_excluded_from_ACP3

• Lumina's ACP server does support reading spreadsheets and csv files using Libxl {experimental, but functional}:
  • https://docs.analytica.com/index.php/LibXL

• I'll have to get back with you on connecting to Google sheets or Google directories.
• Currently we have not implemented functionality for subdirectories within an Acp account - although there are projects within ACP Group accounts.
• For those with an Acp server license, Spreadsheet functions can be enabled on their server.

The Docs you are referring to probably applied to ACP1. There are still some links to those old pages on the wiki which need to be updated.

When you evaluate the Read functions (like ReadTextFile function, ReadBinaryFile function, etc.) in desktop Analytica, the basic behavior if you don't change it via optional parameters is to read the file with the specified name if it can (i.e., if it exists), but display a file chooser dialog if the file cannot be found or cannot be read. With the file chooser you can find the file and continue.  Or, you can set the «showDialog» parameter to true to force the file chooser.

On ACP, it won't show you a file chooser for the server file system -- that would have some obvious undesirable security issues. So when the file chooser appears, you'll see your own file system (on the browser computer). When you select a file, it reads it in just as the function would have done in desktop Analytica, but of course it is reading it from your computer, which involves a file upload.  What this means is your existing Analytica code that lets a user select a file works the same and usually does not require any modification.

If the requested file is in your folder on the server (because you uploaded it from the ACP portal), then it will find it there and load it without the dialog (unless you specify the «showDialog» parameter to be true). 

These Read functions read the file into memory, so the file won't persist on the server file system. Causing it to persist takes a little extra code.

First, you may want to receive the filename that the user selected, not just the content. You can do that using this construct:

Local ( content, filename ) := ReadTextFile( "" );

Next, your code can write the content to your project folder on the server using

WriteTextFile( filename, content )

You can only read or write to folders that are within your own ACP account. The user portal does not expose the presence of any subfolders. Projects in a group account are folder-like, but it doesn't expose subfolders within projects. However, subfolders could exist on the server, and when they do, your model CAN access those subfolders since they are within your account folder. You can create a subfolder (like from a button script) using the function FileSystemNewFolder. You would want to do this only once, after which it could potentially write files to that folder.

If you are using these techniques to upload and save binary files with ReadBinaryFile and WriteBinaryFile, you'll want to set the «typeFlags» parameter to 7 in both, which reads the full file into memory as a binary blob (rather than splitting it into an array of bytes or ints). Something like an excel spreadsheet would be done in this fashion. (Keeping in mind that we don't put Excel on our main ACP server for security reasons, and because it tends to throw up UI dialogs to the console that nobody can see on a server, making it appear to hang).

I don't know how to interface with Google Doc files, but it is most likely possible using the ReadFromURL function to make REST queries to Google's server. But since I don't know about that, you'd have to research what REST API's Google exposes, or perhaps another reader of this Forum might know something about that.

Posted by: @drice

↑

On ACP, it won't show you a file chooser for the server file system -- that would have some obvious undesirable security issues. So when the file chooser appears, you'll see your own file system (on the browser computer). When you select a file, it reads it in just as the function would have done in desktop Analytica, but of course it is reading it from your computer, which involves a file upload.  What this means is your existing Analytica code that lets a user select a file works the same and usually does not require any modification.

Using the following to open an Excel spreadsheet...

SpreadsheetOpen('', showDialog:True )

a file chooser window appears that allows me to point to a file on my Google drive (after I set the format to "All Files"), that my desktop version easily reads, that I can select. However, the node that contains the above definition doesn't return «ExcelWorkbook» as if the connection is made and loaded. If I close the model, reopen it, and try to run the same model, I never get the file chooser window again. The Analytica cube just appears and spins interminably until I Cancel. I am using Google Chrome.

If I Restart ACP, it takes me through the same sequence again.

You can't use Spreadsheet functions on our ACP server because we don't install Excel on that server for security reasons. We recommend you have people upload *.csv files, not *.xlsx files. 

If you purchase ACP and install it on your own server, you would of course have the option to install Excel on that server. You might trust all people who have access, so wouldn't have to worry about people uploading virus-containing excel files, etc. Excel is still a pain on a server, though. It has a tendency to throw up dialogs on the console with questions for the user, which you can't see when using it from a browser. Earlier versions of Excel (pre Excel 2016) don't have this problem and actually do work fairly well on a server, so that is a reasonable option (on your own server). 

There is one other option that we use internally for our applications that allow people to upload *.xlsx files, which is to use LibXL (instead of the spreadsheet functions). This enables access to the values in a spreadsheet, but does not have any of the Excel calculation engine running, so you can't use the usual spreadsheet functions to set and recalculate Excel cells. I won't go into further details on that option here since I'm recommending that you have your users upload CSV files.