cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Lasse_
New Member

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_
New Member

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_
New Member

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

v-xida-msft
Community Support
Community Support

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.

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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Users online (2,185)