Last week, Fabio Vettore published this blog post about loading a CSV file into SAP Build Apps and further to a Visual Cloud Functions backend. This intrigued me as I was planning on doing a CSV import to an app I was working on, but I thought to do it in a way that did not include file uploads, converting files and custom JavaScript. I finished yesterday and wanted to showcase my simplistic solution.
As context, I am creating a FAQ app which has two data entities: Questions and Tags. Each Question can have several Tags, and my logic checks if the Tag(s) exist and creates new ones for the Question if they don’t. This type of nested or relational data causes some of my logic to be more complex than it would be in a case where Tags would only be plain text instead of Objects with the option to add synonyms for a Tag later on for improved search functionality.
To start, here is what happens as a user comes onto this page (see video😞
The simplest realisation here is what CSV is: comma-separated values. Thus, I can easily split it using the SPLIT formula into lists of texts, which I can then further convert to suit my needs.
When the user presses the “Convert CSV to JSON” button, the following logic happens:
Since I need to have the headers or column names for later functionality, I split them into their own page variable.
Here you can see the SPLIT formula already in action, as I first split off the first line or row of the CSV by splitting it from the line break (\n). I only need the first row, so I select it with [0]. Then I split it again to get a neat list of texts, where each text is the name of the column.
The next step could be skipped, but I do a similar split, removing the first row with the headers and save this into the rows page variable for clarity. Note that I don’t split the rows into lists of texts at this point, but rather I do that in the next step.
Then the magic happens: the conversion of the CSV into JSON format.
I love the MAP formula. It’s basically a for-each loop and I use it all the time.
Here I am using it to loop the rows, and for each row I use REDUCE_INIT to convert the text the row has into an Object. The REDUCE_INIT formula loops the headers I split from the rows before, and building on an empty Object (the accumulator), it sets key-value pairs with the header’s value as the key and the row’s value as the value.
And that’s it. The data is in JSON format now!
This should/could be also a step where the app checks whether the data looks correct, but this is the quicker and dirtier way, having the user check the data themselves.
The quickest way to look at JSON data is to simply take the whole variable it is stored in and use ENCODE_JSON formula. I do this as I develop to quickly debug the app all the time.
This, however, is not easy to read nor user-friendly, which is why I display the data like this:
This could be made to show all of the data in the CSV, but since CSVs can have hundreds of rows, I’m just showing the first data row to check that the headers and values are lining up correctly.
Thus, to show key and value pairs in the UI, I made a formula that took the keys of the first Object in the list and then got the value of that key for that first Object.
I’m not going to go into as much details in this step, as due to the context I’m doing this in I have a more complex nested create loop going on.
However, whenever you want to batch operations like create, you will need to do looping logic. My recommendation is to have a variable you set to be true when the operation is ongoing (and use this variable to show a spinner and disable buttons) and another page variable for the index to loop your logic with and increment with every loop. Once again, much like a for-each loop.
Since I have nested data, I have two loops within each other.
There you have it, a way to convert CSV into JSON and then upload it to your backend of choice. Leave a comment below with your thoughts, questions or improvement ideas, and for other relevant topics, check out the SAP Builders group!
As context, I am creating a FAQ app which has two data entities: Questions and Tags. Each Question can have several Tags, and my logic checks if the Tag(s) exist and creates new ones for the Question if they don’t. This type of nested or relational data causes some of my logic to be more complex than it would be in a case where Tags would only be plain text instead of Objects with the option to add synonyms for a Tag later on for improved search functionality.
To start, here is what happens as a user comes onto this page (see video😞
- User copy pastes CSV into input field
- User converts CSV to JSON by pressing a button
- User checks that the format looks correct
- User uploads the data into the backend
Step 1: Converting CSV to JSON
The simplest realisation here is what CSV is: comma-separated values. Thus, I can easily split it using the SPLIT formula into lists of texts, which I can then further convert to suit my needs.
When the user presses the “Convert CSV to JSON” button, the following logic happens:
Screenshot from my logic attached to the button
Since I need to have the headers or column names for later functionality, I split them into their own page variable.
Here you can see the SPLIT formula already in action, as I first split off the first line or row of the CSV by splitting it from the line break (\n). I only need the first row, so I select it with [0]. Then I split it again to get a neat list of texts, where each text is the name of the column.
SPLIT(SPLIT(pageVars.input, "\n")[0], ";")The next step could be skipped, but I do a similar split, removing the first row with the headers and save this into the rows page variable for clarity. Note that I don’t split the rows into lists of texts at this point, but rather I do that in the next step.
MAP(REMOVE_ITEMS_AT(SPLIT(pageVars.input, "\n"),0), item)Then the magic happens: the conversion of the CSV into JSON format.
I love the MAP formula. It’s basically a for-each loop and I use it all the time.
Here I am using it to loop the rows, and for each row I use REDUCE_INIT to convert the text the row has into an Object. The REDUCE_INIT formula loops the headers I split from the rows before, and building on an empty Object (the accumulator), it sets key-value pairs with the header’s value as the key and the row’s value as the value.
MAP<row, i>(pageVars.rows, REDUCE_INIT<accumulator, header, index>(pageVars.headers, {}, SET_KEY(accumulator, header, PICK_ITEM(SPLIT(row, ";"), index))))And that’s it. The data is in JSON format now!
Step 2: Displaying arbitary JSON data (optional)
This should/could be also a step where the app checks whether the data looks correct, but this is the quicker and dirtier way, having the user check the data themselves.
The quickest way to look at JSON data is to simply take the whole variable it is stored in and use ENCODE_JSON formula. I do this as I develop to quickly debug the app all the time.
ENCODE_JSON(pageVars.csv)This, however, is not easy to read nor user-friendly, which is why I display the data like this:
Screenshot from the app I made
This could be made to show all of the data in the CSV, but since CSVs can have hundreds of rows, I’m just showing the first data row to check that the headers and values are lining up correctly.
Thus, to show key and value pairs in the UI, I made a formula that took the keys of the first Object in the list and then got the value of that key for that first Object.
MAP(KEYS(pageVars.csv[0]), {key: item, value: LOOKUP(pageVars.csv[0], item)})Step 3: Batch create from JSON to VCF (nested)
I’m not going to go into as much details in this step, as due to the context I’m doing this in I have a more complex nested create loop going on.
However, whenever you want to batch operations like create, you will need to do looping logic. My recommendation is to have a variable you set to be true when the operation is ongoing (and use this variable to show a spinner and disable buttons) and another page variable for the index to loop your logic with and increment with every loop. Once again, much like a for-each loop.
Since I have nested data, I have two loops within each other.
Screenshot of the complex logic I have made for my relational data creation to VCF
Done!
There you have it, a way to convert CSV into JSON and then upload it to your backend of choice. Leave a comment below with your thoughts, questions or improvement ideas, and for other relevant topics, check out the SAP Builders group!
- SAP Managed Tags:
7 Comments
