Endpoint CSV text processing

Nextworld Platform
1

I have a particular problem with a specific endpoint call which returns a text string containing column headers and data lines/rows. The format uses a \n for line breaks.

I need to have a method of mapping this to a table of columns/data items with similar names but with a prefix nsStx in front of the name, and no spaces in the name

I have done some of this using the logic block text manipulation, but that is not ideal and for large volumes this is rather slow

I can get anything up to a few thousand lines in a response

{
  "itemreturned": {
    "csvFile": "Item number;Description;Site name;Lot number;Supplier lot;Transaction type;Quantity;Transaction UM;Date\n18849020;item 1;Site 1;PN2503659;PN2503659;IE;104000;EA;2025-06-02;16:56:17\n18849020;item 1;Site 1;PN2504734;PN2504734;IT;-104000;EA;2025-06-04;11:07:55\n18849020;item 1;Site 1;PN2504734;PN2504734;IE;104000;EA;2025-06-06;17:51:06\n18849020;item 1;Site 1;PN2503659;PN2503659;IT;-104000;EA;2025-06-10;13:04:47\n18849020;item 1;Site 1;PN2504734;PN2504734;IT;-104000;EA;2025-06-11;12:45:44\n18849020;item 1;Site 1;PN2504734;PN2504734;IE;104000;EA;2025-06-11;17:07:12\n18849020;item 1;Site 1;PN2503659;PN2503659;IT;-104000;EA;2025-06-13;00:30:41\n18849020;item 1;Site 1;PN2503659;PN2503659;IT;-104000;EA;2025-06-13;09:52:38\n18849020;item 1;Site 1;PN2504734;PN2504734;IE;208000;EA;2025-06-13;16:57:24\n18849020;item 1;Site 1;PN2504734;PN2504734;IT;-104000;EA;2025-06-16;12:35:14\n18849020;item 1;Site 1;PN2504734;PN2504734;IE;104000;EA;2025-06-16;16:56:49\n18849020;item 1;Site 1;PN2504734;PN2504734;IE;104000;EA;2025-06-18;17:23:33\n18849020;item 1;Site 1;PN2504734;PN2504734;IT;-104000;EA;2025-06-20;13:48:06\n18849020;item 1;Site 1;PN2504734;PN2504734;IT;-104000;EA;2025-06-25;08:30:17\n18849020;item 1;Site 1;PN2504734;PN2504734;IT;-104000;EA;2025-06-25;12:34:01\n18849020;item 1;Site 1;PN2504734;PN2504734;IT;-104000;EA;2025-06-25;15:33:37\n18849020;item 1;Site 1;PN2504734;PN2504734;IE;104000;EA;2025-06-25;17:34:44\n18849020;item 1;Site 1;PN2504734;PN2504734;IT;-104000;EA;2025-06-27;11:37:55\n18849020;item 1;Site 1;PN2504734;PN2504734;IE;312000;EA;2025-06-27;17:14:20\n18849020;item 1;Site 1;PN2504734;PN2504734;IT;-104000;EA;2025-06-27;22:50:11\n18849020;item 1;Site 1;PN2504734;PN2504734;IT;-104000;EA;2025-06-27;22:55:32\n18849020;item 1;Site 1;PN2506137;PN2506137;IE;312000;EA;2025-06-30;18:37:48\n23313254;item 2;Site 1;PN2411207;PN2411207;IT;-1000;EA;2025-06-10;10:10:03\n23316003;item 3;Site 1;PN2403714;PN2403714;IT;-500;EA;2025-06-28;06:12:39\n23320327;item 4;Site 1;PN2501688;PN2501688;IT;-800;EA;2025-06-28;00:14:30\n23320788;item 5;Site 1;PN2502786;PN2502786;IT;-1000;EA;2025-06-03;08:43:00\n23320788;item 5;Site 1;PN2503465-1;PN2503465-1;IT;-500;EA;2025-06-03;14:12:15\n23320788;item 5;Site 1;PN2502786;PN2502786;IT;-500;EA;2025-06-03;16:38:50\n23320788;item 5;Site 1;PN2502786;PN2502786;IT;-500;EA;2025-06-04;00:17:22\n23320788;item 5;Site 1;PN2502786;PN2502786;IT;-500;EA;2025-06-04;06:22:56\n23320788;item 5;Site 1;PN2504825-1;PN2504825-1;IE;3000;EA;2025-06-04;14:07:08\n23320788;item 5;Site 1;PN2503465-1;PN2503465-1;IT;-500;EA;2025-06-04;14:21:32\n23320788;item 5;Site 1;PN2503465-1;PN2503465-1;IT;-500;EA;2025-06-04;22:13:34\n23320788;item 5;Site 1;PN2503465-1;PN2503465-1;IT;-1000;EA;2025-06-05;02:10:49\n23320788;item 5;Site 1;PN2504825-1;PN2504825-1;IT;-500;EA;2025-06-05;14:16:52\n23320788;item 5;Site 1;PN2504825-1;PN2504825-1;IT;-500;EA;2025-06-05;21:58:22\n23320788;item 5;Site 1;PN2504825-1;PN2504825-1;IE;3000;EA;2025-06-06;13:37:59\n23320788;item 5;Site 1;PN2504825-1;PN2504825-1;IT;-500;EA;2025-06-11;14:28:46\n23320788;item 5;Site 1;PN2504825-1;PN2504825-1;IT;-500;EA;2025-06-11;22:21:28\n23320788;item 5;Site 1;PN2504825-1;PN2504825-1;IT;-500;EA;2025-06-12;06:25:33\n23320788;item 5;Site 1;PN2504825-1;PN2504825-1;IT;-500;EA;2025-06-12;14:27:34\n23320788;item 5;Site 1;PN2504825-1;PN2504825-1;IT;-500;EA;2025-06-12;22:16:10\n23320788;item 5;Site 1;PN2504825-1;PN2504825-1;IT;-500;EA;2025-06-12;23:51:30\n23320788;item 5;Site 1;PN2504825-1;PN2504825-1;IT;-500;EA;2025-06-13;06:36:16\n23320788;item 5;Site 1;PN2504825-1;PN2504825-1;IT;-500;EA;2025-06-13;22:26:23\n23320788;item 5;Site 1;PN2411705;PN2411705;IT;-500;EA;2025-06-14;06:42:27\n23320788;item 5;Site 1;PN2504995;PN2504995;IE;3000;EA;2025-06-14;07:33:02\n23320788;item 5;Site 1;PN2504825-1;PN2504825-1;IT;-500;EA;2025-06-14;14:01:48\n23320788;item 5;Site 1;PN2504825-1;PN2504825-1;IT;-500;EA;2025-06-16;14:19:02\n23320788;item 5;Site 1;PN2504995;PN2504995;IE;2000;EA;2025-06-17;07:12:28\n23320788;item 5;Site 1;PN2504995;PN2504995;IT;-1000;EA;2025-06-18;07:58:30\n23320788;item 5;Site 1;PN2504995;PN2504995;IT;-500;EA;2025-06-18;22:45:45\n23320788;item 5;Site 1;PN2504995;PN2504995;IE;500;EA;2025-06-19;06:49:07\n23320788;item 5;Site 1;PN2504995;PN2504995;IE;1500;EA;2025-06-21;07:28:56\n23320788;item 5;Site 1;PN2504995;PN2504995;IT;-500;EA;2025-06-24;06:49:10\n23320788;item 5;Site 1;PN2504995;PN2504995;IT;-500;EA;2025-06-24;15:59:57\n23320788;item 5;Site 1;PN2504995;PN2504995;IE;1000;EA;2025-06-26;07:18:21"
  }
}
2

Brandt,

I think LLMs are pretty good at this type of thing. I just built a prototype based your data and it seems to work pretty well. Here’s the general concept with tools that are currently available:

  1. Store the returned JSON in a JSONObject data item type inside the logic block that makes the endpoint call
  2. Create a Prompt Definition (Models and Prompt Definitions app) whose input definition is a JSONObject data item type and output definition is a JSONArray data item type
  3. For the System Prompt, use something like
    You convert CSV data that is inside a JSON object into new rows in a table
  4. For the User Prompt, use something like below (maybe be more specific with what you want all the fields to be named)
    Take the CSV data that is inside the JSON below and return an array of JSON objects for each row of CSV Data.
    JSON: ${nsXXXJSONObject}
  5. In the logic block , use a Run Model Definition statement to invoke the prompt and pass theJSONObject that was returned by the endpoint over to it
  6. In the Process Output (Loop) of the Run Model Definition, use Loop Array to loop the returned JSONArray (Data Type for the array = Json Object)
  7. Use Set Values statements to map each of the keys of the individual JSON Object to specific variables (this is where forcing the LLM to use specific field names in a longer User Prompt probably pays off in case the LLM decides to use unexpected field names)
  8. Insert data to wherever you want, using the variables defind at 7. for the field mappings

Here’s the JSON Array I got back using the PLATFORM_FAST model (Claude Haiku) and the above prompt:

[
  {
    "Item number": "18849020",
    "Description": "item 1",
    "Site name": "Site 1",
    "Lot number": "PN2503659",
    "Supplier lot": "PN2503659",
    "Transaction type": "IE",
    "Quantity": "104000",
    "Transaction UM": "EA",
    "Date": "2025-06-02",
    "Time": "16:56:17"
  },
  {
    "Item number": "18849020",
    "Description": "item 1",
    "Site name": "Site 1",
    "Lot number": "PN2504734",
    "Supplier lot": "PN2504734",
    "Transaction type": "IT",
    "Quantity": "-104000",
    "Transaction UM": "EA",
    "Date": "2025-06-04",
    "Time": "11:07:55"
  },
  {
    "Item number": "18849020",
    "Description": "item 1",
    "Site name": "Site 1",
    "Lot number": "PN2504734",
    "Supplier lot": "PN2504734",
    "Transaction type": "IE",
    "Quantity": "104000",
    "Transaction UM": "EA",
    "Date": "2025-06-06",
    "Time": "17:51:06"
  }
  etc
3

Example of this working. For me with the above prompt, the Quantity is returned as a Text instead of a number (being more explicit in the prompt should overcome this), and some finagling is required in the logic block to convert the Date and Time fields from Text, but you get the idea.

image
image3822×1753 197 KB

4

Thanks Ian. This sounds like a plan..

5

One tweak I thought of overnight - instead of instantiating a bunch of variables, you can instantiate a DSN (variable type = New Data Source Name) of the target record and then set values directly against its fields, and then reference that DSN when doing the insert. Might be slightly cleaner.