CSV data shows conflict from the Configurable List format

When accessing the Configurable List output in a CSV file, it appears to format the output thusly:
"Key1: Value1, Key2: Value2, Key3: Value3"
The problem is that some of my keys have commas in them, and the free-form answers may have commas in them as well. I can theoretically finagle something to capture any text between a colon and the last comma before another colon, but if my question has commas in it I'll be capturing part of the question as well (not good).
Is it possible to specify a delimiting character(s) for this widget? Let's say I chose '|' as my delimiter. The CSV output for this widget could then be:
"Key1:Value1|Key2:Value2|Key3:Value3"
and parsing these values would be trivial.
Alternatively, you could just format the text inside the string as minimized JSON, which would be a billion times better.

Hello Evan,

Thanks for reaching Jotform. Can you share with us a screenshot of the issue you are having with both the Configurable list and the CSV file? If there is sensitive information, we suggest blurring them or cutting the important details that we need to see before uploading here in the support forum channel. Check out this link for your guide on posting screenshots in the support forum.

We would also like to clone the form involved but we noticed that you are not logged into your Jotform account when you reach us your account was not auto verified here. Please make sure that you are logged in to your Jotform account before you respond to this support forum.

We can move forward with a solution as soon as we receive a response from you.

Hey this is Evan on another account.

So I downloaded the records as CSV. I'm going to drop a single value from a Configurable List (with its bookended commas)

,"Start: 2022-08-03, End: 2022-08-12, Employer: Acme, Phone: 1234567890,222, Address: 1832 West Calif St, East, Job Title: Honorary Marxman, Esq, Immediate Supervisor/Title: Al Gore Ithm, May we contact for reference?: Yes, Summarize nature of work performed and job responsibilities: I worked a lot, yes I did. Indeed, work is what I did do. ;:\|+=-_?~!@#$%^&*fasdrfa, Compensation Start: 12345, Compensation Final: 123456, Number of Hours Worked per Week: 1, Did you supervise employees: No, Number of employees supervised: , Reason for leaving: Didn't like it.

Start: 2022-08-26, End: 2022-08-10, Employer: dfasdrf|drsdfr, Phone: asdfasdf, Address: asfasdf, Job Title: asfasdf, Immediate Supervisor/Title: asdfas, May we contact for reference?: Yes, Summarize nature of work performed and job responsibilities: fsfasdf, Compensation Start: 1234, Compensation Final: 1234, Number of Hours Worked per Week: , Did you supervise employees: Yes, Number of employees supervised: -1, Reason for leaving: complaints

",

So my Python script reads this value as a string. I now want to read through it, creating a dict object with keys and values.

{

"Start": "2022-08-03",

"End": "2022-08-12",

  "Employer": "Acme",

"Phone": "1234567890",

"222{computer breaking sounds}

I can write some regex which gets pretty close.

This captures all the keys (except the first): https://regex101.com/r/b8eTen/1 (?<=, )[^:]*

And this captures all the values (except the last): https://regex101.com/r/ObN0br/1 (?<=: )[^:]*(?=,[^:]*:)

All in all this is a deeply terrible workaround. Apparently this tool used to export as JSON, but that was changed for... reasons. https://www.jotform.com/answers/2702908-excel-csv-download-export-configurable-list-data-in-json-format

Hi Evan,

Thanks for getting back to us and for providing additional information. Allow me to have time to check on this, I will also ask our higher support in regards to the issue to expedite the assistance. I will get back to you as soon as possible.

Thank you for your patience and understanding.

Hello Evan,

Thank you for waiting, I appreciate it. After thorough testing and with the help of my support, here is the solution that comes up. As you've already noticed yourself, the CSV export is not very code-friendly. And we were unable to find a good way of converting the value given for the Configurable List in CSV to something usable in Python.

I would like to suggest using our API(https://api.jotform.com/docs/) instead. It gives all the information in a more easy-to-use format for coding. I've included a small example below from my colleague which only requires two imports, json and requests. And if you don't have an API key already, you can create one here (https://www.jotform.com/help/253-how-to-create-a-jotform-api-key/)

The code below is very simple, just to show that the data is usable from the Configurable List. It fetches all the submissions on the given form and converts it to a dict. Then it gets the value from the Configurable List field on the first submission, which is a string, but differently formatted when fetched through the API. And then converts it into a dict as well. Then the output is just to show that the Configurable List can be used as expected for a dict in the end.

import json

import requests



def extractConfList(formID,apiKey):

    formsUrl = f"https://api.jotform.com/form/{formID}/submissions?apikey= {apiKey}&limit=500&orderby=created_at"

resp = requests.get(formsUrl)

submissionDict = json.loads(resp.text)

#'101' is the ID for the field in my test form, so this might differ

confListStr = submissionDict['content'][0]['answers']['101']['answer']

confList = json.loads(confListStr)

return confList



test = extractConfList("FORMID","APIKEY")



print(f"Worked at {test[0]['Employer']} from {test[0]['Start']} to {test[0]['End']}")

print(f"Worked at {test[1]['Employer']} from {test[1]['Start']} to {test[1]['End']}")



Output:

Worked at Test from 1958-06-09 to 1957-06-06

Worked at Test from 1965-06-06 to 1966-11-07

The following guides can help create API keys, Link 1 and link 2. Give it a try and let us know if you have any other questions. 

Thank you! I'll give this a try.