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 . 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"), ";")
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.
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
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!