0

Environment

I created a Custom List via New > App > Custom List.

I created columns and added entries via the form associated with the list.

Some of the columns are of type Choice and configured to allow multiple selections.

For example:

Service Type:  

 - Groceries - Apples
 - Groceries - Oranges   
 - Groceries - General

Desired Behaviour

Copy a SharePoint list into a CSV file via a recurring Microsoft Flow based on this template.

I have only chosen the CSV format for the backup because I thought it would be easier to set up the related flow - if an Excel spreadsheet backup could be created, that would be ideal.

Current Behaviour

When I open the CSV in Excel, a single Choice column has turned into 4 columns:

ServiceType
ServiceType@odata.type

ServiceType#Id  

ServiceType#Id@odata.type

with the corresponding values of:

[{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference","Id":2,"Value":"Groceries - General"}]

#Collection(Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference)

[2] 

#Collection(Int64)

Question

How can I ensure that:

  • Only a single column is displayed for each Choice column

  • Only the values of the columns are displayed, rather than all the Microsoft 'markup' text

Flow:

enter image description here enter image description here

0

For your requirement, i will suggest directly export sharepoint list to excel

You can always update the file to the latest list via refresh all, no need to update it everyday.

2
  • Desired behaviour is to copy a SharePoint list into a CSV file via a recurring Microsoft Flow based on this template from Microsoft. An automated process is required, not a manual Export to Excel. – user1063287 Apr 2 '20 at 2:04
  • @user1063287 this template uses the action create csv table, it will not meet your requirement. – Jerry_MSFT Apr 2 '20 at 6:20
0

Short Answer

Use this:

xpath(xml(json(concat('{"root":{"choices":', item()?['MyChoiceColumnInternalName'] , '}}'))), '/root/choices/Value/text()')

or this:

join(xpath(xml(json(concat('{"root":{"choices":', item()?['MyChoiceColumnInternalName'] , '}}'))), '/root/choices/Value/text()'), ';#')

Long Answer

I thought it would be helpful to:

  • create a list that contained all column types

  • add an entry

  • export the list to CSV table

  • see what the exported values were

These are the columns:

Edit: Things I learned after posting this:

  • Column names have a 32 characters limit 'internally', longer names are truncated (src)
  • Changing the column name does not change the 'internal' column name (src)
+--------------------------------------+------------------------+
|                Column                |          Type          |
+--------------------------------------+------------------------+
| Title                                | Single line of text    |
| MySingleLineOfTextColumn             | Single line of text    |
| MyMultipleLinesOfTextColumn          | Multiple lines of text |
| MyNumberColumn                       | Number                 |
| MyYesNoColumn                        | Yes/No                 |
| MyPersonColumn                       | Person or Group        |
| MyDateColumn                         | Date and Time          |
| MyChoiceColumnMultiWithSelections    | Choice                 |
| MyChoiceColumnMultiWithoutSelections | Choice                 |
| MyChoiceColumnSingleWithSelection    | Choice                 |
| MyChoiceColumnSingleWithoutSelection | Choice                 |
| MyHyperlinkColumn                    | Hyperlink or Picture   |
| MyPictureColumn                      | Hyperlink or Picture   |
| MyCurrencyColumn                     | Currency               |
| MyLocationColumn                     | Location               |
| Modified                             | Date and Time          |
| Created                              | Date and Time          |
| Created By                           | Person or Group        |
| Modified By                          | Person or Group        |
+--------------------------------------+------------------------+

So first, I switched to the Custom option in the Create CSV Table section.

The stars below indicate dynamic content variables that had 'extended' versions available because their value is an object, not a string, ie:

MyLocationColumn: Name
MyPersonColumn DisplayName
MyChoiceColumnSingleWithSelection Value 
MyChoiceColumnSingleWithoutSelection Value
Modified By DisplayName 
Created By DisplayName

enter image description here

When I tested the flow, these were the results in the Create CSV Table step:

enter image description here

JSON (the INPUTS > From section):

The Get items step produces an array of objects, where each object is an item in the list.

You can see below which columns have a string, object or array as a value.

The column type of Choice (multiple selections allowed) has the latter - ie an array of objects.

[
  {
    "@odata.etag": "\"6\"",
    "ItemInternalId": "2",
    "ID": 2,
    "Title": "Test",
    "MySingleLineOfTextColumn": "Here is a single line of text",
    "MyMultipleLinesOfTextColumn": "Here\nAre\nMultiple\nLines\nOf\nText",
    "MyNumberColumn": 5,
    "MyYesNoColumn": true,
    "MyPersonColumn": {
      "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
      "Claims": "i:0#.f|membership|info@mydomain.onmicrosoft.com",
      "DisplayName": "John Smith",
      "Email": "info@mydomain.onmicrosoft.com",
      "Picture": "https://mydomain.sharepoint.com/sites/mydomain/_layouts/15/UserPhoto.aspx?Size=L&AccountName=info@mydomain.onmicrosoft.com",
      "Department": null,
      "JobTitle": null
    },
    "MyPersonColumn#Claims": "i:0#.f|membership|info@mydomain.onmicrosoft.com",
    "MyDateColumn": "2020-04-30",
    "MyChoiceColumnMultiWithSelection": [
      {
        "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
        "Id": 0,
        "Value": "Choice 1"
      },
      {
        "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
        "Id": 1,
        "Value": "Choice 2"
      },
      {
        "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
        "Id": 2,
        "Value": "Choice 3"
      }
    ],
    "MyChoiceColumnMultiWithSelection@odata.type": "#Collection(Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference)",
    "MyChoiceColumnMultiWithSelection#Id": [
      0,
      1,
      2
    ],
    "MyChoiceColumnMultiWithSelection#Id@odata.type": "#Collection(Int64)",
    "MyChoiceColumnMultiWithoutSelect": [],
    "MyChoiceColumnMultiWithoutSelect@odata.type": "#Collection(Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference)",
    "MyChoiceColumnMultiWithoutSelect#Id": [],
    "MyChoiceColumnMultiWithoutSelect#Id@odata.type": "#Collection(Int64)",
    "MyChoiceColumnSingleWithSelectio": {
      "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
      "Id": 0,
      "Value": "Choice 1"
    },
    "MyChoiceColumnSingleWithSelectio#Id": 0,
    "MyHyperlinkColumn": "https://www.google.com",
    "MyPictureColumn": "https://i.picsum.photos/id/237/200/300.jpg",
    "MyCurrencyColumn": 20,
    "CountryOrRegion": "Australia",
    "State": "NSW",
    "City": "Sydney",
    "PostalCode": "2000",
    "Street": "100 Test St",
    "DispName": "100 Test St, Sydney, NSW 2000",
    "Modified": "2020-04-30T08:42:27Z",
    "Created": "2020-04-30T07:17:22Z",
    "Author": {
      "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
      "Claims": "i:0#.f|membership|info@mydomain.onmicrosoft.com",
      "DisplayName": "John Smith",
      "Email": "info@mydomain.onmicrosoft.com",
      "Picture": "https://mydomain.sharepoint.com/sites/mydomain/_layouts/15/UserPhoto.aspx?Size=L&AccountName=info@mydomain.onmicrosoft.com",
      "Department": null,
      "JobTitle": null
    },
    "Author#Claims": "i:0#.f|membership|info@mydomain.onmicrosoft.com",
    "Editor": {
      "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
      "Claims": "i:0#.f|membership|info@mydomain.onmicrosoft.com",
      "DisplayName": "John Smith",
      "Email": "info@mydomain.onmicrosoft.com",
      "Picture": "https://mydomain.sharepoint.com/sites/mydomain/_layouts/15/UserPhoto.aspx?Size=L&AccountName=info@mydomain.onmicrosoft.com",
      "Department": null,
      "JobTitle": null
    },
    "Editor#Claims": "i:0#.f|membership|info@mydomain.onmicrosoft.com",
    "{Identifier}": "Lists%252fMy%2bAll%2bColumn%2bTypes%2bList%252f2_.000",
    "{IsFolder}": false,
    "{Thumbnail}": {
      "Large": null,
      "Medium": null,
      "Small": null
    },
    "{Link}": "https://mydomain.sharepoint.com/sites/mydomain/_layouts/15/listform.aspx?PageType=4&ListId=5aaeae22-ccf3-48ba-a88b-4130b4293d9f&ID=2&ContentTypeID=0x0100C770D8569664E542BC4A1154C9A4D48E",
    "{Name}": "Test",
    "{FilenameWithExtension}": "Test",
    "{Path}": "Lists/My All Column Types List/",
    "{FullPath}": "Lists/My All Column Types List/2_.000",
    "{HasAttachments}": false,
    "{VersionNumber}": "1.0"
  }
]

Text (the OUTPUTS > Body section):

Title,MySingleLineOfTextColumn,MyMultipleLinesOfTextColumn,MyNumberColumn,MyYesNoColumn,MyPersonColumn,MyDateColumn,MyChoiceColumnMultiWithSelections,MyChoiceColumnMultiWithoutSelections,MyChoiceColumnSingleWithSelection,MyChoiceColumnSingleWithoutSelection,MyHyperlinkColumn,MyPictureColumn,MyCurrencyColumn,MyLocationColumn,Modified,Modified By,Created,Created By
Test,Here is a single line of text,"Here
Are
Multiple
Lines
Of
Text",5,True,John Smith,2020-04-30,"[{""@odata.type"":""#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference"",""Id"":0,""Value"":""Choice 1""},{""@odata.type"":""#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference"",""Id"":1,""Value"":""Choice 2""},{""@odata.type"":""#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference"",""Id"":2,""Value"":""Choice 3""}]",[],Choice 1,,https://www.google.com,https://i.picsum.photos/id/237/200/300.jpg,20,"100 Test St, Sydney, NSW 2000",2020-04-30T08:42:27Z,John Smith,2020-04-30T07:17:22Z,John Smith

Final output displayed in CSV file is therefore:

+--------------------------------------+------------------------+------------------+
|                Column                |          Type          |      Output      |
+--------------------------------------+------------------------+------------------+
| Title                                | Single line of text    | Text             |
| MySingleLineOfTextColumn             | Single line of text    | Text             |
| MyMultipleLinesOfTextColumn          | Multiple lines of text | Text             |
| MyNumberColumn                       | Number                 | Text             |
| MyYesNoColumn                        | Yes/No                 | Text             |
| MyPersonColumn                       | Person or Group        | Text***          |
| MyDateColumn                         | Date and Time          | Text             |
| MyChoiceColumnMultiWithSelections    | Choice                 | Array of Objects |
| MyChoiceColumnMultiWithoutSelections | Choice                 | Array (Empty)    |
| MyChoiceColumnSingleWithSelection    | Choice                 | Text***          |
| MyChoiceColumnSingleWithoutSelection | Choice                 | Text***(Blank)   |
| MyHyperlinkColumn                    | Hyperlink or Picture   | Text             |
| MyPictureColumn                      | Hyperlink or Picture   | Text             |
| MyCurrencyColumn                     | Currency               | Text             |
| MyLocationColumn                     | Location               | Text***          |
| Modified                             | Date and Time          | Text             |
| Created                              | Date and Time          | Text             |
| Created By                           | Person or Group        | Text***          |
| Modified By                          | Person or Group        | Text***          |
+--------------------------------------+------------------------+------------------+

*** indicates an 'extended' dynamic variable was used.

Conclusion

You can get a text output for most column types, however Choice (multiselection) is different as its value is an array of objects:

[{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference","Id":0,"Value":"Choice 1"},{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference","Id":1,"Value":"Choice 2"},{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference","Id":2,"Value":"Choice 3"}]

Choice columns (without multiple selections allowed) have the dynamic variable ColumnName Value available because their value is an object.

Solution

Choice column (with multiple selections allowed) can be accessed using an expression like this:

xpath(xml(json(concat('{"root":{"choices":', item()?['MyChoiceColumnInternalName'] , '}}'))), '/root/choices/Value/text()')

which will produce an array like this (if selections have been made):

["Choice 1","Choice 2"]  

or an empty array if no selections have been made:

[]  

This approach is mentioned in an answer to this post:

https://powerusers.microsoft.com/t5/Building-Flows/Flow-get-multiple-values-from-sharepoint-column-for-CSV-table/td-p/126853

Explanation of solution

In case it helps anyone visualising what the xpath expression does in the solution provided:

var concatenated_string = '{"root":{"choices":' + '[{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference","Id":0,"Value":"Choice 1"},{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference","Id":1,"Value":"Choice 2"},{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference","Id":2,"Value":"Choice 3"}]' + '}}'; 

/*

produces a string:

{"root":{"choices":[{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference","Id":0,"Value":"Choice 1"},{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference","Id":1,"Value":"Choice 2"},{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference","Id":2,"Value":"Choice 3"}]}}

*/

var json_concatenated_string = JSON.parse(concatenated_string);  

/*

produces an object:

{
  "root": {
    "choices": [
      {
        "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
        "Id": 0,
        "Value": "Choice 1"
      },
      {
        "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
        "Id": 1,
        "Value": "Choice 2"
      },
      {
        "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
        "Id": 2,
        "Value": "Choice 3"
      }
    ]
  }
}

*/

var my_xml = json2xml(json_concatenated_string); 

/*

produces xml:  

<?xml version="1.0" encoding="UTF-8"?>
<root>
   <choices odata.type="#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference">
      <Id>0</Id>
      <Value>Choice 1</Value>
   </choices>
   <choices odata.type="#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference">
      <Id>1</Id>
      <Value>Choice 2</Value>
   </choices>
   <choices odata.type="#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference">
      <Id>2</Id>
      <Value>Choice 3</Value>
   </choices>
</root>
*/


/* 

xpath(xml, '/root/choices/Value/text()')  

get's each Choice's 'Value' property and returns an array of matches, eg:

["Choice 1","Choice 2","Choice 3"] 

see:  https://docs.microsoft.com/en-gb/azure/logic-apps/workflow-definition-language-functions-reference#xpath  

*/


// function to convert JSON to XML
/*  This work is licensed under Creative Commons GNU LGPL License.

    License: http://creativecommons.org/licenses/LGPL/2.1/
   Version: 0.9
    Author:  Stefan Goessner/2006
    Web:     http://goessner.net/ 
*/
function json2xml(o, tab) {
   var toXml = function(v, name, ind) {
      var xml = "";
      if (v instanceof Array) {
         for (var i=0, n=v.length; i<n; i++)
            xml += ind + toXml(v[i], name, ind+"\t") + "\n";
      }
      else if (typeof(v) == "object") {
         var hasChild = false;
         xml += ind + "<" + name;
         for (var m in v) {
            if (m.charAt(0) == "@")
               xml += " " + m.substr(1) + "=\"" + v[m].toString() + "\"";
            else
               hasChild = true;
         }
         xml += hasChild ? ">" : "/>";
         if (hasChild) {
            for (var m in v) {
               if (m == "#text")
                  xml += v[m];
               else if (m == "#cdata")
                  xml += "<![CDATA[" + v[m] + "]]>";
               else if (m.charAt(0) != "@")
                  xml += toXml(v[m], m, ind+"\t");
            }
            xml += (xml.charAt(xml.length-1)=="\n"?ind:"") + "</" + name + ">";
         }
      }
      else {
         xml += ind + "<" + name + ">" + v.toString() +  "</" + name + ">";
      }
      return xml;
   }, xml="";
   for (var m in o)
      xml += toXml(o[m], m, "");
   return tab ? xml.replace(/\t/g, tab) : xml.replace(/\t|\n/g, "");
}

Bonus

If you want to output this (a value that can be imported back into a SharePoint list as a Choice (multiple selections allowed) value):

Choice 1;#Choice 2;#Choice 3

Wrap the xpath in a join:

join(xpath(xml(json(concat('{"root":{"choices":', item()?['MyChoiceColumnInternalName'] , '}}'))), '/root/choices/Value/text()'), ';#')  

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.