Delimiter Issue
AnsweredHi
I'm trying to insert data from a API JSON response into a SQL table. Certain string properties in the JSON response contains either a comma, pipe or tab delimiter, which breaks in the SQL insert.
I've tried removing the pipe delimiter from the JSON response before an Insert Records command, using the "Find and replace" command. However, this is the response I'm receiving:
At least one line in your file is longer than the acceptable limit of 65536 bytes. Please reduce the length of the lines in your file and try again.
How can I get around this issue?
-
This happens when the API response returns a single line JSON string. If you know the structure of the JSON string you can get around it by using Handlebars to rebuild the JSON.
If you have a JSON array like this:
[{"id":1,"name":"Apple","price":1.25,"category":"Fruit","details":{"color":"Red","origin":"USA","organic":true},"reviews":[{"user":"Alice","rating":5,"comment":"Delicious!"},{"user":"Bob","rating":4,"comment":"Good, but a bit tart."}]},{"id":2,"name":"Banana","price":0.75,"category":"Fruit","details":{"color":"Yellow","origin":"Ecuador","organic":false},"reviews":[{"user":"Charlie","rating":3,"comment":"Okay, a little mushy."},{"user":"David","rating":5,"comment":"Perfect for smoothies!"}]},{"id":3,"name":"Carrot","price":0.50,"category":"Vegetable","details":{"color":"Orange","origin":"California","organic":true},"reviews":[{"user":"Eve","rating":4,"comment":"Fresh and crunchy."},{"user":"Frank","rating":5,"comment":"Great for snacks!"}]}]You could use a template like this:
[
{{#each data}}
{
"id": {{this.id}},
"name": "{{this.name}}",
"price": {{this.price}},
"category": "{{this.category}}",
"details":
{
"color": "{{this.details.color}}",
"origin": "{{this.details.origin}}",
"organic": {{this.details.organic}}
},
"reviews":
[
{{#each this.reviews}}
{
"user": "{{this.user}}",
"rating": {{this.rating}},
"comment": "{{this.comment}}"
}{{#unless @last}},{{/unless}}
{{/each}}
]
}{{#unless @last}},{{/unless}}
{{/each}}
]and end up with JSON like this:
[
{
"id": 1,
"name": "Apple",
"price": 1.25,
"category": "Fruit",
"details":
{
"color": "Red",
"origin": "",
"organic": true
},
"reviews":
[
{
"user": "Alice",
"rating": 5,
"comment": "Delicious!"
},
{
"user": "Bob",
"rating": 4,
"comment": "Good, but a bit tart."
}
]
},
{
"id": 2,
"name": "Banana",
"price": 0.75,
"category": "Fruit",
"details":
{
"color": "Yellow",
"origin": "Ecuador",
"organic": false
},
"reviews":
[
{
"user": "Charlie",
"rating": 3,
"comment": "Okay, a little mushy."
},
{
"user": "David",
"rating": 5,
"comment": "Perfect for smoothies!"
}
]
},
{
"id": 3,
"name": "Carrot",
"price": 0.5,
"category": "Vegetable",
"details":
{
"color": "Orange",
"origin": "",
"organic": true
},
"reviews":
[
{
"user": "Eve",
"rating": 4,
"comment": "Fresh and crunchy."
},
{
"user": "Frank",
"rating": 5,
"comment": "Great for snacks!"
}
]
}
]The output of the Handlebars - Render File Template can then be passed to the File Utils - Find and Replace to remove the pipe symbols.
Wade
--
Wade Hays - https://towerturn.com/services/dedicated-expert 0
Comments
2 comments