Ninox script to work with csv files

John Halls 

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 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!

Hello Adrian Schibler ,

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 that's great news! - I'm very pleased my post helped move you forward. Nice one!

Kind regards, Andrew

Rafael ,

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