cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Lasse_
Level: Powered On

JSON Parser missing data points

Hi,

 

I am using a Parse JSON data operation to parse through an email body that consists of fairly simple JSON. The problem is that JSON Parser is not capturing all the data points in the email body, but leaves randomly ~1/4 of them blank. Any ideas?

 

As said, JSON Is simple: {"Case ID":"12","Sales Manager":"Test","Customer":"NYC","Country":"United States","Currency":"Dollars","Year":"2017","Price":"1234"}  (Shortened here to 7 data pairs, the actual file has ~30 data pairs). All json data fields are strings because of the source, i.e on purpose. I can take care of that problem when data is in database where i am planning to put it. And naturally I convert the email html to txt before parsing.

 

When troubleshooting this I see that output of the parser is e.g.:

 

Case ID : 12

Sales Manager : Test

Customer : (empty)

Country : United States

Currency : Dollars

Year : 2017

Price : (empty)

 

Places where the empty data appears change from time to time, but are always the same when same data is sent (i.e. if I don't change the input, they always appear in the same places).

 

JSON sample I use works fine in "Parse JSON" when creating the flow step, and in the next step I can connect the data points to respective db fields without any problems. 

1 ACCEPTED SOLUTION

Accepted Solutions
Lasse_
Level: Powered On

Re: JSON Parser missing data points

After another day of testing I noticed the problem was that JSON parser changed some of the empty spaces to \n and hence the database update missed them. I.e. "Sales Manager" became "Sales\nManager". Replacing spaces with underscores fixed the problem.

View solution in original post

3 REPLIES 3
Lasse_
Level: Powered On

Re: JSON Parser missing data points

After another day of testing I noticed the problem was that JSON parser changed some of the empty spaces to \n and hence the database update missed them. I.e. "Sales Manager" became "Sales\nManager". Replacing spaces with underscores fixed the problem.

View solution in original post

Community Support Team
Community Support Team

Re: JSON Parser missing data points

Hi @Lasse_,

 

Could you please share a screenshot of your flow's configuration?

 

The json data within my email Body as below:

20.JPG

 

I have made a test on my side and don't have the issue that you mentioned. My flow's configuration as below:17.JPG

Witin Schema field of "Parse JSON" action, type the following formula:

{
    "type": "object",
    "properties": {
        "Case ID": {
            "type": "string"
        },
        "Sales Manager": {
            "type": "string"
        },
        "Customer": {
            "type": "string"
        },
        "Country": {
            "type": "string"
        },
        "Currency": {
            "type": "string"
        },
        "Year": {
            "type": "string"
        },
        "Price": {
            "type": "string"
        },
        "Case ID1": {
            "type": "string"
        },
        "Sales Manager1": {
            "type": "string"
        },
        "Customer1": {
            "type": "string"
        },
        "Country1": {
            "type": "string"
        },
        "Currency1": {
            "type": "string"
        },
        "Year1": {
            "type": "string"
        },
        "Price1": {
            "type": "string"
        },
        "Case ID2": {
            "type": "string"
        },
        "Sales Manager2": {
            "type": "string"
        },
        "Customer2": {
            "type": "string"
        },
        "Country2": {
            "type": "string"
        },
        "Currency2": {
            "type": "string"
        },
        "Year2": {
            "type": "string"
        },
        "Price2": {
            "type": "string"
        },
        "Case ID3": {
            "type": "string"
        },
        "Sales Manager3": {
            "type": "string"
        },
        "Customer3": {
            "type": "string"
        },
        "Country3": {
            "type": "string"
        },
        "Currency3": {
            "type": "string"
        },
        "Year3": {
            "type": "string"
        },
        "Price3": {
            "type": "string"
        },
        "Case ID4": {
            "type": "string"
        },
        "Sales Manager4": {
            "type": "string"
        },
        "Country4": {
            "type": "string"
        },
        "Currency4": {
            "type": "string"
        },
        "Year4": {
            "type": "string"
        },
        "Price4": {
            "type": "string"
        },
        "Customer4": {
            "type": "string"
        }
    }
}

The flow works successfully as below:18.JPG19.JPG

 

Please check if you have filled proper json schema witin Schema field of "Parse JSON" action. In addition, please consider take a try to clear cahe of your browser and re-create your flow to check if the issue is solved.

 

Also please check if you have filled proper json data within email body on your side.

 

Best regards,

Kris

 

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Lasse_
Level: Powered On

Re: JSON Parser missing data points

Hi Kris,

 

thanks for testing this and sorry that I didn't mark it solved before. As I wrote above, the problem was that the parser (or html-to-text before that) changes some of the spaces to \n: 

Flow_problem.JPG

Like you see above, space in Pricing manager is changed, space in "Case ID" is not. After this Sharepoint upload failed on those data fields where space was changed. Replacing a simple space with underscore fixed the problem.

 

Flow itself is exacty as you also made it:

 

Flow.JPG

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors (Last 30 Days)
Users online (4,834)