How to ignore the blank values from json response

Help Studio
, ,
1

Hi,

I am facing an error while converting the JSON data into datatable. For each blank key from JSON which is not having any value is giving me an error while converting it into datatable.
for e.g.
“Vendor Name”: “ABC Suppliers”,
"Invoice Date ": “”,
"Invoice Amount ": “”,

Here, I want to ignore the blank values (Invoice Date & Invoice Amount) while converting the JSON into Datatable. I am attaching the screenshot of the error. Please help me to solve this issue.
image

2

Have a look at the following series for a defensive access
grafik

what was done / how was modelled it in detail?

3

please try to inject this code after reading json file
For Each item As JObject In jsonArray
Dim values As Object() = item.Properties().Select(Function(p) p.Value).ToArray()
If Not values.Any(Function(value) value Is Nothing OrElse String.IsNullOrEmpty(value.ToString())) Then
dataTable.Rows.Add(values)
End If
Next

4

Hi @ppr,

I am following the further steps.

image
image751×570 27.6 KB

5

from the screenshots we cannot derive the needed info. Better you share with us a few JSON samples along with the description of the expected output. Thanks

6

Hi @ppr ,

New Input - Copy.json (638 Bytes)
My requirement is, From the given JSON, The first portion of JSON is OK. In the second portion, Some of the field values are coming as blank. I want to convert the JSON into the excel. For the blank values I want to keep column name as it is & its values as blank cell.

7

Hi @Hamza_Mesfar Thanks for the reply, It is giving me some errors & I am not able to covert the JSON response into datatable.

8

Assumption a Jarray like:
grafik

which we deserialized into a JArray - myJArray

Getting all column names:
grafik

So we would prepare an empty datatable (e.g. Build DataTable, or dynamic) - dtData

  • For each Activity | Item in myJArray.Values(Of JObject)
    • constructing a data row by defensive value access as shown above
    • add Data Row Activity | using the above constructed datarow
9

as an alternate we can also conver the JArray into a List Of Dictionary(Of String, Object)
grafik

And can use Dictionary related actions for the datatable construction as well

Kindly note:
grafik

is not working, due the failing data column datatype calculation using the first item for it
Crosscheck:
grafik

10

Hi @ppr

What should I take inside the body of For each?

image
image535×587 10.9 KB

11

depending on you other modellings the defensive extraction part e.g.

Assign Activity
myAmount | DataType: Double =

IF(String.IsNullOrEmpty(currentJToken("Invoice Amount").toString),0,currentJToken("Invoice Amount").Value(Of Double))

… do this also for the other fields, then use a Add DataRow activity

12

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.