Planning Analytics

 View Only

  • 1.  Upload a flat file from Excel to PA SaaS

    Posted Thu January 02, 2025 05:08 PM
    Reply

    We're looking for a creative way to get a file (CSV) from Excel to the PA SaaS server to then be consumed by a TI process. 

    We initially tried to just use DBSW formulas to upload the data but it is too slow and also seeing some odd behavior on the server side with this. 

    It would be amazing if you could upload a file from Excel to the upload directory (model_upload) and I will be putting an RFE out there for that. 

    I suspect in the v11 server (PA on Cloud) you could leverage the Externals directory, but it doesn't seem like that directory is accessible via TI in V12. 



    ------------------------------
    Matthew Berson
    ------------------------------


  • 2.  RE: Upload a flat file from Excel to PA SaaS

    Posted Thu January 02, 2025 05:36 PM
    Reply

    Replying to my own post ... seems that we may be able to use the "Publish" button (or the publish function from the PAFE API). I didn't realize that the }Applications folder is now .applications within File Manager and the }Externals folder is nested under that as .externals. 



    ------------------------------
    Matthew Berson
    ------------------------------

    Original Message


  • 3.  RE: Upload a flat file from Excel to PA SaaS

    Posted Fri January 03, 2025 02:33 AM
    Reply

    HI Matthew,

    Would using a button on a PAW book with the "Allow file upload" box check work for you?

    Assuming a user clicks the button and uploads a CSV/TXT file, the file will end up in the model_data folder.

    Pass the filename from the button with the Control Type=File.

    From there, the TI can use Location and the filename to process it as a data source name for server... 



    ------------------------------
    George Tonkin
    Business Partner
    MCI Consultants
    Johannesburg
    ------------------------------

    Original Message


  • 4.  RE: Upload a flat file from Excel to PA SaaS

    Posted Fri January 03, 2025 09:18 AM
    Reply

    George, thank you.

    That was the first thing we presented to the users. I'll spare you the details, but they are insistent on not leaving Excel to do this. I would much prefer to do this in PAW. 



    ------------------------------
    Matthew Berson
    ------------------------------

    Original Message


  • 5.  RE: Upload a flat file from Excel to PA SaaS

    Posted Mon January 06, 2025 03:41 AM
    Reply

    I found this solution from one of my clients: 

    1. User prepare data in excel format
    2. There is a VB macro coding to convert the excel data into text file
    3. Load data from excel by reading the new text file using TI process

    Their problem is the VB Macro takes too long. Maybe you can use python to do it instead of Macro



    ------------------------------
    Veronika Gultom
    ------------------------------

    Original Message


  • 6.  RE: Upload a flat file from Excel to PA SaaS

    Posted 12 days ago
    Reply

    The problem specifically is getting the data to the IBM Cloud which requires either SFTP, API or going out to PAW to use File Manger / Action button. 



    ------------------------------
    Matthew Berson
    ------------------------------

    Original Message


  • 7.  RE: Upload a flat file from Excel to PA SaaS

    Posted 12 days ago
    Reply

    We have found a solution to this and bundled it all to a single macro that massages an Excel file to a TI friendly format, saves it as a CSV, uploads it to the "applications" structure (different storage method between V11 and V12) , and then kicks off a TI process to load it. You can read the details here in our blog post. 

    There is also sample code for VBA and TI  in our GitHub repository. 

    Lastly, I have made an RFE to allow file upload a possibility for  PAFE action buttons similar to PAW here - Please vote if you like the idea!



    ------------------------------
    Matthew Berson
    ------------------------------

    Original Message