4

Ninox script to work with csv files

Ninox has a convenient wizard for importing (.csv) data which is well featured and easy to use.

If you regularly work with data contained in csv files, you may be keen to to avoid retracing your steps in the import wizard for each import.

You can use Ninox Script to read, act upon and create records based on the content of .csv files.

I recommend you create a table designed to manage your imported (attached) files. My table looks like this:

 In my example, I have a reporting task to complete every fortnight based on data contained in .csv files obtained from a number of sources. This table is useful for a number of reasons, giving me a place to store similarly named .csv files for each period (my data sources), allowing me to pass the period or dates as columns to the script to differentiate data by batch (case I need to roll back) and to allow for the calculation of year-to-date analyses.

I simply select a record, then attach the files I need to process, like so:

 So far, so good.

You may have noticed I added a button called Process Data on the form above. In my actual database I use a single script to:

  1. to check if the process has already been run (completed) for the period;
  2. to process the files;
  3. then mark the process as complete in the table, so the process is not run in error more than once.

You'll have your own unique process so I've left steps 1 and 3 out of my example script - I've also made it easier to understand by dealing with a single file, and I've not added a reference to the period (because that might differ from your planned workflow).

So without further ado, here is a script to process data from the uploaded csv file, creating records for each line of the file in a table in Ninox. I've documented the script heavily because it helps new users. You can largely ignore the debug parts other than they help when troubleshooting new files...

 

---
ninox cloud (for use with ninox public or private cloud)
mass table operations are most efficiently performed on the server
---;
do as server
    ---
    let tDebugOutput := "";
    ---;
    ---
    create a reference (or link) to a .csv file attached to the current record
    ---;
    let tShareURL := shareFile(this, "cos_final.csv");
    ---
    create an array of lines from the file using split by urlencoded line endings
    ---;
    let tLines := split(urlEncode(concat(http("GET", tShareURL).result)), "%0A");
    ---
    count the number of rows in the array containing the contents of the .csv file
    the count is used when looping through the lines of the file below
    ---;
    let tCount := count(tLines);
    ---
    let tFirstDataLine := item(tLines, 1);
    tDebugOutput := tDebugOutput + " | " + tFirstDataLine + " | ";
    ---;
    ---
    initiate counter i
    let i := 0; if header row is not present in the .csv file.
    let i := 1; if header row is present in the .csv file.
    ---;
    let i := 1;
    ---
    while the value of the counter is less than the number of lines in the file continue to process the lines of the file
    ---;
    while i < tCount do
        ---
        assign the data from line i of the .csv file to variable tLine
        ---;
        let tLine := item(tLines, i);
        ---
        create an array of columns from a line in the file using split by urlencoded comma
        ---;
        let tColumns := split(tLine, "%2C");
        ---
        assign values each column to variables that match columns in table cos
        ---;
        let tNdisNumber := urlDecode(item(tColumns, 0));
        ---
        tDebugOutput := tDebugOutput + i + " | " + tNdisNumber + " | ";
        ---;
        let tNoteDate := urlDecode(item(tColumns, 1));
        ---
        tDebugOutput := tDebugOutput + i + " | " + tNoteDate + " | ";
        ---;
        let tNoteSupportItem := urlDecode(item(tColumns, 2));
        ---
        tDebugOutput := tDebugOutput + i + " | " + tNoteSupportItem + " | ";
        ---;
        let tManagementStyle := urlDecode(item(tColumns, 3));
        ---
        tDebugOutput := tDebugOutput + i + " | " + tManagementStyle + " | ";
        ---;
        let tNoteDurationalDecimal := urlDecode(item(tColumns, 4));
        ---
        tDebugOutput := tDebugOutput + i + " | " + tNoteDurationalDecimal + " | ";
        ---;
        let tNoteRate := urlDecode(item(tColumns, 5));
        ---
        tDebugOutput := tDebugOutput + i + " | " + tNoteRate + " | ";
        ---;
        let tNoteFee := urlDecode(item(tColumns, 6));
        ---
        tDebugOutput := tDebugOutput + i + " | " + tNoteFee + " | ";
        ---;
        let tClient := urlDecode(item(tColumns, 7));
        ---
        tDebugOutput := tDebugOutput + i + " | " + tClient + " | ";
        ---;
        let tCos := urlDecode(item(tColumns, 8));
        ---
        tDebugOutput := tDebugOutput + i + " | " + tCos + " | ";
        ---;
        let tCosRate := urlDecode(item(tColumns, 9));
        ---
        tDebugOutput := tDebugOutput + i + " | " + tCosRate + " | ";
        ---;
        let tCosFee := urlDecode(item(tColumns, 10));
        ---
        tDebugOutput := tDebugOutput + i + " | " + tCosFee + " | ";
        ---;
        ---
        create a new record in table cos for the current row from the .csv source file
        ---;
        (create cos).(
            ndisnumber := tNdisNumber;
            notedate := tNoteDate;
            notesupportitem := tNoteSupportItem;
            managementstyle := tManagementStyle;
            notedurationdecimal := tNoteDurationalDecimal;
            noterate := tNoteRate;
            notefee := tNoteFee;
            client := tClient;
            cos := tCos;
            cosrate := tCosRate;
            cosfee := tCosFee
        );
        ---
        increment counter i + 1
        ---;
        i := i + 1
    end
;
    ---
    remove the reference (or link) to the .csv file attached to the current record
    ---;
    unshareFile(this, "cos_final.csv")
end;
---
alert(tDebugOutput);
---;
---
display an alert to the user - process complete
---;
alert("Process complete.")

 

Don't forget to include 'do as server'. If you don't your scripts may run for hours.

Processing of the data now takes seconds. Hooray!

I'd also recommend using a dedicated .csv viewer (like Modern CSV or similar) when working with .csv files, as Microsoft Excel has a tendency to re-format raw .csv data in unexpected ways (especially date and time formats).

One more thing...if you wish to transform your .csv data using SQL before importing to Ninox check out https://hub.steampipe.io/plugins/turbot/csv . It's a command line utility that allows you to query .csv files as if they are postgre database tables using standard SQL. You can even create new .csv files from query output.

Enjoy!

Kind regards, Andrew

19 replies

null
    • Daniel_Berrow
    • 9 mths ago
    • Reported - view

    Very Interesting! 

    Didn't know you could comment using --- ---;

    • Mel_Charles
    • 9 mths ago
    • Reported - view

    First Class - Useful and Practical. Nice one 👋

    • John_Halls
    • 9 mths ago
    • Reported - view

     Hi Andrew. I learned so much from your post here. I had to look a few things up especially shareFile and unshareFile which I hadn't heard of before. Call you tell me, what would happen if you didn't include the unshareFile command?

      • Andrew_Dickey
      • 9 mths ago
      • Reported - view

       

      Hello John,  (The quick answer) I believe the reference (URL) remains usable within your application until such time as you perform unShareFile on the file. The reference (URL) is persistent (remains) between Ninox sessions.

      The long(er) answer.

      The Ninox documentation does not provide detail of the scope of shareFile / unshareFile.

      I have discovered:

      1. The shareFile reference (URL) remains the same for a file no matter how many times you call shareFile on the file, nor does it unset between user sessions.

      I created a file containing the names of the Beatles ("beatles.csv"). It is a .csv but could have been any type of file.

      I set the following code in the 'On click' event of a button:

      It will output the same reference (URL), every time I click the button. Therefore, the shareFile reference (URL) remains the same for a file once set.

      2. Calling unshareFile destroys the reference (URL) to the file.

      3. After destroying the reference (URL) with unshareFile, when you next call shareFile for the same file you will receive a new reference (URL).

      4. The reference (URL) created by shareFile is accessible in Ninox only, and not publicly.

      I believe that is an accurate summary, however someone from team Ninox may be able to provide further insight.

      Kind regards, Andrew

    • ROBERT.5
    • 6 mths ago
    • Reported - view

    Hi, I could use a push in the correct direction here. I do have a .CSV file attached to the current record. When I create the array I return only one (1) line and it appears to be metadata type html and not the body of the attached file itself. I do know that the attached file contains many records. Thanks in advance.

      • Andrew_Dickey
      • 6 mths ago
      • Reported - view

       I'm happy to help where I can. Not all .csv files are created equal. Some use differing field delimiters (tab, comma etc) and/or differing text qualifiers (none, single, or double quotes) and/or different line endings (usually based on the operating system the file was created on). I often view my .csv files in a dedicated csv editor (such as Modern Csv) to spot and resolve any weirdness with the file itself. Once you know the delimiters and qualifiers used, adapt the lines in my code for the line endings and delimiter.  As a last resort, if the data is not sensitive (or you can alter the data)  you could PM me a link file and I would be happy to troubleshoot. If you are getting meta data, the file may have started out other than a .csv. Good luck!

      • ROBERT.5
      • 6 mths ago
      • Reported - view

       Thank you for replying. I am going to check out what you said. The file definitely started on a Windows based system. If I just double click the attachment in Ninox, it  is opened by Numbers, and all the data shows, but, of course, I want the data to be visible to my own code. Thanks again, I appreciate it.

    • Andrew_Dickey
    • 6 mths ago
    • Reported - view

    Hello Robert, from what you shared, my guess is that you were running that script on the Ninox Mac App and not in the Public or Private cloud. Is that correct?

      • ROBERT.5
      • 6 mths ago
      • Reported - view

       Yes, that is true. So that changes how the code functions?

    • Andrew_Dickey
    • 6 mths ago
    • Reported - view

    Hello Robert, apologies for the delay in replying. I believe shareFile is a Ninox Cloud (Public/Private) only function as it creates a URL reference to the file on the Ninox Cloud with prefix "https://share.ninox.com/". In the comments of my original post, I noted:

     said:
    for use with ninox public or private cloud

    I will download the latest Ninox Mac app, and double-check what is possible later today.

    Kind regards, Andrew  

    • Adrian_Schibler
    • 6 mths ago
    • Reported - view

    really nice work here, very useful.

    i have a csv file which contains text including linebreaks. so this kinda messes up the import. any way to maybe replace the linebreaks in the entire column before running the import? has to be before

     said:
    let tLines := split(urlEncode(concat(http("GET", tShareURL).result)), "%0A");

     i guess? 

      • Andrew_Dickey
      • 6 mths ago
      • Reported - view

      Hello  ,

      Apologies for the delay in replying (I do understand how frustrating it can be wanting on an answer). 

      This is a common complication that in hindsight I probably should have addressed in my original post.

      Generally, when any of the columns in your .csv file themselves contain line breaks, then the line endings used for the rows in your file should be different than the line endings used in the cells containing line breaks.

      Now, the common line endings as html entities are "%0A" for line feed and "%OD%0A" carriage return - line feed. Other common html entities used in the code are of course "%2C" for comma and "%22" for quote. The reason I mention quotes at the end, is that you will generally need to strip quotes for strings containing their own line endings.

      I visually review the .csv files I'm working with in a dedicated .csv editor rather than in Excel, Numbers or Google Sheets before importing them.

      If I notice multi-line data in individual cells, I simply (and instantly) change the row line endings before saving the file and uploading it to Ninox.

      I then use a "slightly" modified version of the code I originally posted.

      Let me run you through an example.

      As mentioned in my original post, I use the free version of Modern CSV to view and occasionally edit my .csv files, regardless of where they originated from.

      I created a sample file...

      The address column has multi-line data.

      To differentiate the line endings, I simple change the Line Ending in the Save Parameters from LF to CRLF then save the file. It's a 5 second task.

      I then import the file into Ninox.

       

      I've added a button and some code to process the file and output the results to a "log" (a multiline text field).

      Let's review the code revised for this example:

      'Data Log' := null;
      do as server
          ---
          let tDebugOutput := "";
          ---;
          ---
          create a reference (or link) to a .csv file attached to the current record
          ---;
          let tShareURL := shareFile(this, "in_cell_line_feeds.csv");
          ---
          create an array of lines from the file using split by urlencoded line endings
          ---;
          let tLines := split(urlEncode(concat(http("GET", tShareURL).result)), "%0D%0A");
          ---
          count the number of rows in the array containing the contents of the .csv file
          the count is used when looping through the lines of the file below
          ---;
          let tCount := count(tLines);
          ---
          let tFirstDataLine := item(tLines, 1);
          'Data Log' := 'Data Log' + " | " + tFirstDataLine +
              " |
      ";
          ---;
          ---
          initiate counter i
          let i := 0; if header row is not present in the .csv file.
          let i := 1; if header row is present in the .csv file.
          ---;
          let i := 1;
          ---
          while the value of the counter is less than the number of lines in the file continue
          to process the lines of the file
          ---;
          while i < tCount do
              ---
              assign the data from line i of the .csv file to variable tLine
              ---;
              let tLine := item(tLines, i);
              ---
              create an array of columns from a line in the file using split by urlencoded comma
              ---;
              let tColumns := split(tLine, "%2C");
              ---
              assign values each column to variables that match columns in table cos
              ---;
              let tFirst := urlDecode(item(tColumns, 0));
              'Data Log' := 'Data Log' + "First = " + tFirst +
                  "
      ";
              let tLast := urlDecode(item(tColumns, 1));
              'Data Log' := 'Data Log' + "Last = " + tLast +
                  "
      ";
              let tAddress := split(replace(item(tColumns, 2), "%22", ""), "%0A");
              'Data Log' := 'Data Log' + "Address Line 1 = " + urlDecode(item(tAddress, 0)) +
                  "
      ";
              'Data Log' := 'Data Log' + "Address Line 2 = " + urlDecode(item(tAddress, 1)) +
                  "
      ";
              'Data Log' := 'Data Log' + "Address Line 3 = " + urlDecode(item(tAddress, 2)) +
                  "  ";
              ---
              increment counter i + 1
              ---;
              i := i + 1
          end
      ;
          ---
          remove the reference (or link) to the .csv file attached to the current record
          ---;
          unshareFile(this, "in_cell_line_feeds.csv")
      end;
      ---
      display an alert to the user - process complete
      ---;
      alert("Process complete.")

      Adrian, highlights from the new code:

      • we are now using "%0D%0A" carriage return - line feed as our row delimiter
      • we are still using "%2C" comma as our column delimiter
      • we are now using "%0A" line feed as the delimiter for column "address"
      • we replace "%22" quote/s in the address field with an empty string

      All other mechanisms established in the original code remain intact.

      The results of the log appear as follows:

       

      The results are now correctly parsed for you to use in your workflow.

      I hope that helps.

      Happy to answer any further questions you might have.

      Kind regards, Andrew

    • Adrian_Schibler
    • 6 mths ago
    • Reported - view

    Hi   , wow i’m  impressed, thank you so much for the explanation. Got this to work now :) amazing job. thank you for your time andrew

      • Andrew_Dickey
      • 6 mths ago
      • Reported - view

       that's great news! - I'm very pleased my post helped move you forward. Nice one!

      Kind regards, Andrew

      • Rafael Sanchis
      • Rafael_Sanchis
      • 6 mths ago
      • Reported - view

       

      hi Andres.

      Can you share this example if is possible, I try to recreate it but don't work.

      Appreciate.

      • Rafael Sanchis
      • Rafael_Sanchis
      • 6 mths ago
      • Reported - view

       I have this Problem. The csv have 2 records.

    • Rafael Sanchis
    • Rafael_Sanchis
    • 5 mths ago
    • Reported - view

    Hi all,

    Has Anyone had positive result in Clound version ?

      • Andrew_Dickey
      • 5 mths ago
      • Reported - view

       ,

      Has Anyone had positive result in Clound version ?

      Rafael, I prefer patience to trolling negatively for attention. We are all busy.

      Rafael, these solutions work as documented with the Private and Public Cloud versions of Ninox. We use them to process many different .csv files on a daily basis. As I'm sure you have read, Adrian Schibler reported success in using this technic only a few days ago. 

      Rafael, there are only two moving parts to the solution, the script examples which I have shared and your .csv files. I suspect the issue you are experiencing derives from the format of data within your .csv file, or a mod you may have done to the script.

      I'm happy to troubleshoot for you if you are able to share your .csv file and modified script via private message.

      Kind regards, Andrew

      • Rafael Sanchis
      • Rafael_Sanchis
      • 5 mths ago
      • Reported - view

       

      Not trolling negatively anything sorry and thanks, go about your busines. 👍

Content aside

  • 4 Likes
  • 5 mths agoLast active
  • 19Replies
  • 556Views
  • 7 Following