cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kky1
Helper I
Helper I

Help exporting data to sharepoint list from json array - either get error or no data

I am fairly new to Power Automate - this is my first post - but I would so appreciate any help - I have read more articles and posts of solutions than I can count. I have been working through one error after another for a week, and I'm finally on the last step of the flow and I can't figure out what is wrong. 

 

I have a flow that gets a txt file of real estate county data and extracts text into an array with the split function, and then uses filter array and compose actions to pull out the text. I formatted the array in a compose action, and composed its outputs to append the selected text to the array which I had initalized at the start of my flow. I then parsed the array into JSON (following http://johnliu.net/blog/2018/6/a-thesis-on-the-parse-json-action-in-microsoft-flow article) 

 

I now have tried two versions of the flow:

(1) with the Parse JSON and Apply to each/Create item within an Apply to each loop which runs through the array - result is an error on the Create Item action.

(2) with Parse JSON and Apply to each/Create item outside of the Apply to each Loop - flow runs successfully but there is no data captured in the SharePoint list. 

 

Side note: I wasn't able to get rid of the carriage return \r so I'm wondering if that is the issue... (I've tried every post to remove but can't although I was able to get rid of the \n by setting the enter key as a variable and replacing. 

 

The schema for my json is:

{
    "type""array",
    "items": {
        "type""object",
        "properties": {
            "AutoNumber": {},
            "NumRecord": {},
            "Owner": {},
            "Address": {},
            "Parcel": {},
            "Parcel2": {},
            "Parcel3": {},
            "MarketValue1": {},
            "MarketValue2": {},
            "AssessedValue": {},
            "County": {},
            "PropType": {},
            "PropType2": {},
            "AssessmtYr": {},
            "NumBeds": {},
            "NumBaths": {},
            "SqFt": {},
            "SqFt2": {},
            "Acres": {},
            "Acres2": {},
            "Acres3": {},
            "LastSoldDate": {},
            "LastSoldAmt": {}
        },
        "required": []
    }
}

Note: I removed the "type": in the schema bc of errors in data types and null values.

 

Here is a snippet of the compose action to create Json format, a second Compose for its output, and then appending to the output to an initialized array which is the value for the parse JSON action:

kky1_0-1651091804474.png

 

Here is the raw output of the parse JSON action:

kky1_1-1651092100335.png

 

Flow with error Parse JSON and Apply to each/Create Item within an Apply to each loop:

kky1_2-1651092211854.png

 

Overview look at flow - this is the flow that run successful but with no data:

kky1_3-1651092628164.png

 

Another side note: I have also tried removing the parse JSON and doing Create CSV table from the array and then create csv file; however it only exports the first row of data and then I get an error message that the file already exists. 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Pstork1
Dual Super User
Dual Super User

I think your problem is in Compose 2.  Using that gives you an array of objects with multiple properties.  What you want is a single level array where each item is a set of values.  If you look at your schema you will see that you have an array of "items" with "Properties".  So to process that you would need a loop for each item and then use the properties of each item to populate the SharePoint list.  But you should be able to just append the set of properties to the array and skip Compose 2.  You would still need the loop, but the properties would be at the top level of the array instead of embedded in an item.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

Pstork1
Dual Super User
Dual Super User

Make sure you use a sample from the variable array to re-do the JSON schema after removing the Compose 2 action.  Removing that will change the schema of the array.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

10 REPLIES 10
Pstork1
Dual Super User
Dual Super User

You can't just delete the Type values from the schema.  They are the whole point of the schema.  Without them the JSON can't be parsed and that is one of the reasons you aren't getting any data.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Thank you Pstork1 for the quick reply!! I did this because of its suggestion on the web page referenced above. I originally had ["string","null"] as the type for all my fields, but was getting errors and became concerned the \r carriage return was causing issues. Here is the instructions I follow as to why I took out the type:

kky1_0-1651093407806.png

 

kky1
Helper I
Helper I

So I changed my type in the schema back the way I had it and it ran again without error (which is good news since it wasn't the carriage return before causing issues - thank you PStork1 for having to point out the obvious to me), but I'm still not getting data in the SP list even though the flow is successful.

 

Is it because the actions need to be inside the loop where the array exists (even if it is set as a variable)? 

 

Here is the new schema:

{
  "type""array",
  "items": {
    "type""object",
    "properties": {
      "AutoNumber": {
        "type": [
          "integer",
          "null"
        ]
      },
      "NumRecord": {
        "type": [
          "string",
          "null"
        ]
      },
      "Owner": {
        "type": [
          "string",
          "null"
        ]
      },
      "Address": {
        "type": [
          "string",
          "null"
        ]
      },
      "Parcel": {
        "type": [
          "string",
          "null"
        ]
      },
      "Parcel2": {
        "type": [
          "string",
          "null"
        ]
      },
      "Parcel3": {
        "type": [
          "string",
          "null"
        ]
      },
      "MarketValue1": {
        "type": [
          "string",
          "null"
        ]
      },
      "MarketValue2": {
        "type": [
          "string",
          "null"
        ]
      },
      "AssessedValue": {
        "type": [
          "string",
          "null"
        ]
      },
      "County": {
        "type": [
          "string",
          "null"
        ]
      },
      "PropType": {
        "type": [
          "string",
          "null"
        ]
      },
      "PropType2": {
        "type": [
          "string",
          "null"
        ]
      },
      "AssessmtYr": {
        "type": [
          "string",
          "null"
        ]
      },
      "NumBeds": {
        "type": [
          "string",
          "null"
        ]
      },
      "NumBaths": {
        "type": [
          "string",
          "null"
        ]
      },
      "SqFt": {
        "type": [
          "string",
          "null"
        ]
      },
      "SqFt2": {
        "type": [
          "string",
          "null"
        ]
      },
      "Acres": {
        "type": [
          "string",
          "null"
        ]
      },
      "Acres2": {
        "type": [
          "string",
          "null"
        ]
      },
      "Acres3": {
        "type": [
          "string",
          "null"
        ]
      },
      "LastSoldDate": {
        "type": [
          "string",
          "null"
        ]
      },
      "LastSoldAmt": {
        "type": [
          "string",
          "null"
        ]
      }
    }
  }
}
 New version of flow - no data:
kky1_0-1651095952925.png

 

Pstork1
Dual Super User
Dual Super User

What are you processing your loops on?  It looks like it should be on Properties.  That would give you access to the individual values, if they aren't null.  The other problem I see is that you are Parsing the JSON in one loop but trying to create items in another loop.  The loop to process properties array needs to be inside the other loop right after you parse the JSON.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Hi PStork: I'm honestly not sure how to confirm the loop is running on properties, so thought I would send you the detail of the flow, hoping I've probably made an obvious error that will jump out. 

 

First steps:

Trigger flow and initialize variables

filecontent/filecontent2 as string 

Enter as string - manual enter on keyboard

delimiter as string - ^

AutoNum as integer - set 0

Increment variable of 1

split as string - specific text to split the records in txt file

none - as string (not currently used in flow)

array as array

kky1_0-1651148864341.png

 

Second steps - get and "clean data"

Get file content - is a txt.file

set filecontent2 variable as body of txt file

Compose: C-URIcomp - decodeUriComponent(variables('filecontent2'))

Compose: Replace - replace(outputs('C-URIcomp'), variables('Enter'),'^')

Compose: Replacen - replace(outputs('Replace'),variables('Delimiter'), '')

 Compose: splitecords - split(outputs('Replacen'), variables('r'))

Compose: RemoveLastRow - take(outputs('splitrecords'),add(length(outputs('splitrecords')),-1))

kky1_1-1651149204674.png

Step 3: Apply to each - based on outputs of RemoveLastRow

split2 is my 2nd attempt to remove the \r ghost return carriage (didn't work) - \n was removed in above steps

split3: split(item(), ':')

Compose: NumRec is variable AutoNum for a running count of the records

(After NumRec-v is a series of filter array and compose actions to hone in on line of text containing values I want in the array.)

kky1_2-1651149456430.png

 

Step 4: create array with values (not sure if these steps are necessary....)

Compose: action titled JsonArray to create format from the outputs of the filtered values above

Compose2: outputs of Json Array compose action for format

kky1_3-1651149739610.png

 

Step5: Append outputs to array and parse JSON to create Sharepoint list

Appended outputs from Compose2 above to an array variable

New Apply to each to Parse Json from array variable 

Nested Apply to each below (which  I fixed based on your helpful response!) from the body of the Parse JSON and dynamic values pulled into the Create Item section.

kky1_4-1651150038306.pngkky1_5-1651150102552.png

Bird's eye view (in case it is helpful):

kky1_6-1651150237523.png

 

 

 

 

Pstork1
Dual Super User
Dual Super User

I think your problem is in Compose 2.  Using that gives you an array of objects with multiple properties.  What you want is a single level array where each item is a set of values.  If you look at your schema you will see that you have an array of "items" with "Properties".  So to process that you would need a loop for each item and then use the properties of each item to populate the SharePoint list.  But you should be able to just append the set of properties to the array and skip Compose 2.  You would still need the loop, but the properties would be at the top level of the array instead of embedded in an item.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
kky1
Helper I
Helper I

Thank you PStork1 to help me even understand where to start troubleshooting! I deleted the Compose2 action and based the value for the varArray variable on the outputs of the JsonArray compose action I used to pull in values, so the flow is now this:

kky1_0-1651152240554.png

I am seeing a Parse JSON error which describes exactly what you suspected - that the values are still in an array! So where in my flow do I need another Apply to each loop to get to the properties of each item before the Parse JSON action. 

kky1_1-1651152417429.png

 

Also, thank you for your patience helping me! I am truly grateful for your help and obvious knowledge!

Pstork1
Dual Super User
Dual Super User

Make sure you use a sample from the variable array to re-do the JSON schema after removing the Compose 2 action.  Removing that will change the schema of the array.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
kky1
Helper I
Helper I

Thank you for the tip - as you rightly guessed, I hadn't updated that. After testing and then pasting the run history value of the varArray variable, I now see that all of the data types came up as an array. (I composed varArray to look through all the results, but do not see duplicate values for any of the fields so am concerned if the \r return is causing issues after all.) You've been so patient with me so I feel badly asking one more question, but is there a function that can be used on an array which would enable me to combine what appears to be duplicate values (like a union or concat function for a string?) 

I guess I'm just looking for a way to get the data values at the top level of the JSON schema.

 

Thank you again for all the guidance in helping me understand JSON schema. 

 

Here is the new schema:

{
    "type""object",
    "properties": {
        "NumRecord": {
            "type""array",
            "items": {
                "type""string"
            }
        },
        "Owner": {
            "type""array",
            "items": {
                "type""string"
            }
        },
        "Address": {
            "type""array",
            "items": {
                "type""string"
            }
        },
        "Parcel": {
            "type""array",
            "items": {
                "type""string"
            }
        },
        "Parcel2": {
            "type""array"
        },
        "Parcel3": {
            "type""array"
        },
        "MarketValue1": {
            "type""array",
            "items": {
                "type""string"
            }
        },
        "MarketValue2": {
            "type""array"
        },
        "AssessedValue": {
            "type""array",
            "items": {
                "type""string"
            }
        },
        "County": {
            "type""array",
            "items": {
                "type""string"
            }
        },
        "PropType": {
            "type""array",
            "items": {
                "type""string"
            }
        },
        "PropType2": {
            "type""array",
            "items": {
                "type""string"
            }
        },
        "AssessmtYr": {
            "type""array",
            "items": {
                "type""string"
            }
        },
        "NumBeds": {
            "type""array"
        },
        "NumBaths": {
            "type""array"
        },
        "SqFt": {
            "type""array",
            "items": {
                "type""string"
            }
        },
        "SqFt2": {
            "type""array"
        },
        "Acres": {
            "type""array",
            "items": {
                "type""string"
            }
        },
        "Acres2": {
            "type""array"
        },
        "Acres3": {
            "type""array",
            "items": {
                "type""string"
            }
        },
        "LastSoldDate": {
            "type""array"
        },
        "LastSoldAmt": {
            "type""array"
        }
    }
}
 
kky1_0-1651182908524.png

 

kky1
Helper I
Helper I

 Thank you again PStork1! You advice was the ticket and enabled me to troubleshoot the real issue, which was the data needing more refinement! I so appreciate your help and hope someday I might become good enough at this to pay it forward! Take care and thanks again!

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

MPA Licensing.jpg

Ask your licensing questions at the Power Automate AMA!

Join Priya Kodukula and the licensing team, super users and MVPs to find answers to your questions on Power Automate licensing.

Users online (3,959)