https://robdy.io/component---src-templates-blog-post-js-content-fi…cel-to-shp-list-mdx-53f01b7eadc0ad0477c0.js

1"use strict";(self.webpackChunkrobdy_io=self.webpackChunkrobdy_io||[]).push([[2192],{12732:function(e,t,n){n.r(t),n.d(t,{Head:function(){return y},default:function(){return w}});var r=n(20249),l=n.n(r),o=n(28453),a=n(96540);function i(e){const t=Object.assign({p:"p",code:"code",a:"a",blockquote:"blockquote",strong:"strong",ul:"ul",li:"li",h2:"h2",img:"img",pre:"pre"},(0,o.R)(),e.components);return a.createElement(a.Fragment,null,a.createElement(t.p,null,"SharePoint doesn't provide the out-of-the-box option to import data from Excel file to existing list. There is, however, an interesting workaround which is based on PowerAutomate."),"\n",a.createElement(t.p,null,"If you want to put some data from Excel to new list using ",a.createElement(t.code,null,"Import Spreadsheet")," app, but it's not very flexible and sometimes problematic. For example, you have to use browser that supports ActiveX controls (",a.createElement(t.a,{href:"https://www.techwalla.com/articles/what-browsers-support-activex"},"=Internet Explorer"),")."),"\n",a.createElement(t.p,null,"There is, however, an interesting workaround which is based on PowerAutomate (formerly called MS Flow)."),"\n",a.createElement(t.blockquote,null,"\n",a.createElement(t.p,null,a.createElement(t.strong,null,"NOTE"),": There's also ",a.createElement(t.a,{href:"https://appsource.microsoft.com/en-us/product/office/WA104380432?tab=Overview"},a.createElement(t.code,null,"Import Excel or CSV to List")," addon")," which you can use. I learned about it after I finished this article, but I'm leaving it here anyway as it might be useful in some cases."),"\n"),"\n",a.createElement(t.p,null,"Let's say that we have an existing list which we'd like to use for our leave request system. It contains 3 fields:"),"\n",a.createElement(t.ul,null,"\n",a.createElement(t.li,null,"title (we put user email here, string type)"),"\n",a.createElement(t.li,null,"quota (number type)"),"\n",a.createElement(t.li,null,"approver (we choose who will approve requests fot that user, field type is user/group)"),"\n"),"\n",a.createElement(t.h2,null,"Preparing the spreadsheet"),"\n",a.createElement(t.p,null,"If you want to import the spreadsheet, you need to remember that Excel connector for PowerAutomate only supports data from the table. If you're not sure if your data is in the table, click on any cell and you should see additional tab ",a.createElement(t.code,null,"Design")," in the ribbon:"),"\n",a.createElement(t.img,{src:"../src/img/posts/import-excel-to-shp/new-tab-ribbon.png",alt:"new-tab-ribbon.png"}),"\n",a.createElement(t.p,null,"If you're not seeing it, click Ctrl+t to create new table:"),"\n",a.createElement(t.img,{src:"../src/img/posts/import-excel-to-shp/we-have-a-table.png",alt:"we-have-a-table.png"}),"\n",a.createElement(t.p,null,"Now put the file in your OneDrive folder (you can also use your MS Group library or SharePoint) and you can start creating a flow."),"\n",a.createElement(t.h2,null,"Creating a flow"),"\n",a.createElement(t.p,null,"We'll be using instant (manually trigered) flow:"),"\n",a.createElement(t.img,{src:"../src/img/posts/import-excel-to-shp/create-flow.png",alt:"create-flow.png"}),"\n",a.createElement(t.p,null,"We name it and choose ",a.createElement(t.code,null,"Manually trigger a flow"),":"),"\n",a.createElement(t.img,{src:"../src/img/posts/import-excel-to-shp/choose-a-trigger.png",alt:"choose-a-trigger.png"}),"\n",a.createElement(t.p,null,"Next action is ",a.createElement(t.code,null,"List rows present in a table")," from Excel Online (Business) connector."),"\n",a.createElement(t.p,null,"We choose the file and proper table."),"\n",a.createElement(t.p,null,"Then we add ",a.createElement(t.code,null,"Apply to each")," block and use the value from previous step:"),"\n",a.createElement(t.img,{src:"../src/img/posts/import-excel-to-shp/apply-to-each-input.png",alt:"apply-to-each-input.png"}),"\n",a.createElement(t.p,null,"Inside the block we use ",a.createElement(t.code,null,"Create item")," from ",a.createElement(t.code,null,"SharePoint")," connector."),"\n",a.createElement(t.p,null,"For some of the fields, values might not be chooseable. This usually happens for types other than string (for ",a.createElement(t.code,null,"User/Group")," type we specify ",a.createElement(t.code,null,"Claims")," value which is also string) like numbers."),"\n",a.createElement(t.p,null,"We can workaround this using the expression:"),"\n",a.createElement(t.pre,null,a.createElement(t.code,{className:"language-powershell"},"item()?['PropertyName']\n")),"\n",a.createElement(t.img,{src:"../src/img/posts/import-excel-to-shp/create-item-inside-loop.png",alt:"create-item-inside-loop.png"}),"\n",a.createElement(t.p,null,"If we save and refresh we'll see that expression converted to the value similar to what we chosen for string-type fields:"),"\n",a.createElement(t.img,{src:"../src/img/posts/import-excel-to-shp/create-item-converted.png",alt:"create-item-converted.png"}),"\n",a.createElement(t.p,null,"Now we can 'test' the flow (be careful as it will create items on the list, so it's more 'test in production')"),"\n",a.createElement(t.img,{src:"../src/img/posts/import-excel-to-shp/test-in-production.png",alt:"test-in-production.png"}),"\n",a.createElement("sub",null,"Source: ",a.createElement(t.a,{href:"https://community.spiceworks.com/topic/2026463-testing-upgrades-without-a-test-environment"},"Spiceworks")),"\n",a.createElement(t.p,null,"In top-right corner we choose 'Test' link and specify to perform trigger action manually:"),"\n",a.createElement(t.img,{src:"../src/img/posts/import-excel-to-shp/test-flow.png",alt:"test-flow.png"}),"\n",a.createElement(t.p,null,"We create manually-triggered flow so the wizard allows us to trigger it straight from the website. After we confirm the connectors being used:"),"\n",a.createElement(t.img,{src:"../src/img/posts/import-excel-to-shp/run-flow-2.png",alt:"run-flow-2.png"}),"\n",a.createElement(t.p,null,"We can click 'Continue':"),"\n",a.createElement(t.img,{src:"../src/img/posts/import-excel-to-shp/run-flow-3.png",alt:"run-flow-3.png"}),"\n",a.createElement(t.p,null,"Aaaaand it runs:"),"\n",a.createElement(t.img,{src:"../src/img/posts/import-excel-to-shp/flow-started.png",alt:"flow-started.png"}))}var c=function(e){void 0===e&&(e={});const t=Object.assign({},(0,o.R)(),e.components).wrapper;return t?a.createElement(t,e,a.createElement(i,e)):i(e)},s=n(64810),m=n(56099),u=n(24799),g=n(48733),p=n(70552),M=n(38383),d=n(95238);const h=e=>{let t=e.children,n=e.date,r=(e.description,e.modificationTime),o=e.tags,i=e.title,c=e.relativePath;const m=n.toLocaleDateString("en-us",{month:"short",day:"2-digit",year:"numeric"}),p=n.toISOString(),M=r.toLocaleDateString("en-us",{month:"short",day:"2-digit",year:"numeric"}),h=r.toISOString();return a.createElement("section",{className:"section"},a.createElement("div",{className:"container content"},a.createElement("div",{className:"header-container"},a.createElement(g.A),a.createElement("h1",{className:"post-title"},i),a.createElement("p",{className:"post-subheader"},"By"," ",a.createElement(s.N_,{className:"post-subheader-link",to:"/about"},"Robert Dyjas")," on ",a.createElement("time",{datetime:p},m),". Last edit: ",a.createElement("time",{datetime:h},M)," • ",a.createElement("a",{href:"https://github.com/robdy/robdy.io/edit/src/blog/"+c,className:"post-subheader-link",target:"_blank",rel:"nofollow noopener noreferrer"},"Edit this post")),o&&o.length?a.createElement("div",{className:"taglist-container"},a.createElement("ul",{className:"taglist"},o.map(e=>a.createElement(s.N_,{to:"/tags/"+l()(e)+"/"},a.createElement("li",{key:e+"tag"},e))))):null),a.createElement(d.A),a.createElement("div",{id:"blog-post-content"},t),a.createElement(u.A)))},E=e=>{const t=e.data.mdx;return a.createElement(m.A,null,a.createElement(h,{children:e.children,date:new Date(t.frontmatter.date),description:t.frontmatter.description,modificationTime:new Date(t.fields.modificationTime),relativePath:t.parent.relativePath,tags:t.frontmatter.tags,title:t.frontmatter.title}))};function w(e){return a.createElement(E,e,a.createElement(c,e))}const y=e=>{let t=e.data.mdx,n=e.location.pathname;const r=(0,M.A)().title,l=t.frontmatter.title+" | "+r;return a.createElement(p.O,{pathname:n},a.createElement("title",{id:"title"},l),a.createElement("meta",{id:"description",name:"description",content:""+t.frontmatter.description}),a.createElement("meta",{id:"og:description",property:"og:description",content:""+t.frontmatter.description}),a.createElement("meta",{id:"og:title",property:"og:title",content:l}),a.createElement("meta",{property:"article:published_time",content:""+t.frontmatter.date}),a.createElement("meta",{property:"og:modification_time",content:""+t.fields.modificationTime}))}},24799:function(e,t,n){n.d(t,{A:function(){return o}});var r=n(25540),l=n(96540);let o=function(e){function t(t){var n;return(n=e.call(this,t)||this).commentBox=l.createRef(),n}(0,r.A)(t,e);var n=t.prototype;return n.componentDidMount=function(){let e=document.createElement("script");e.setAttribute("src","https://utteranc.es/client.js"),e.setAttribute("crossorigin","anonymous"),e.setAttribute("async",!0),e.setAttribute("repo","robdy/blog-comments"),e.setAttribute("issue-term","pathname"),e.setAttribute("theme","github-light"),this.commentBox.current.appendChild(e)},n.render=function(){return l.createElement("div",null,l.createElement("div",{ref:this.commentBox,className:"comment-box"}))},t}(l.Component)},48733:function(e,t,n){n.d(t,{A:function(){return o}});var r=n(96540),l=n(64810);var o=()=>{const e="undefined"!=typeof window?window.location.href:"";return r.createElement("div",{className:"container post-nav flex"},r.createElement("ul",{className:"flex navbar-links"},r.createElement("li",null," ",r.createElement(l.N_,{className:"",to:"/"},r.createElement("img",{src:"data:image/svg+xml;

1base64,PHN2ZyBhcmlhLWhpZGRlbj0idHJ1ZSIgZm9jdXNhYmxlPSJmYWxzZSIgZGF0YS1wcmVmaXg9ImZhcyIgZGF0YS1pY29uPSJob21lIiBjbGFzcz0ic3ZnLWlubGluZS0tZmEgZmEtaG9tZSBmYS13LTE4IiByb2xlPSJpbWciIHhtbG5zPSJodHRwOi8vd3d3LnczLm9yZy8yMDAwL3N2ZyIgdmlld0JveD0iMCAwIDU3NiA1MTIiPjxwYXRoIGQ9Ik0yODAuMzcgMTQ4LjI2TDk2IDMwMC4xMVY0NjRhMTYgMTYgMCAwIDAgMTYgMTZsMTEyLjA2LS4yOWExNiAxNiAwIDAgMCAxNS45Mi0xNlYzNjhhMTYgMTYgMCAwIDEgMTYtMTZoNjRhMTYgMTYgMCAwIDEgMTYgMTZ2OTUuNjRhMTYgMTYgMCAwIDAgMTYgMTYuMDVMNDY0IDQ4MGExNiAxNiAwIDAgMCAxNi0xNlYzMDBMMjk1LjY3IDE0OC4yNmExMi4xOSAxMi4xOSAwIDAgMC0xNS4zIDB6TTU3MS42IDI1MS40N0w0ODggMTgyLjU2VjQ0LjA1YTEyIDEyIDAgMCAwLTEyLTEyaC01NmExMiAxMiAwIDAgMC0xMiAxMnY3Mi42MUwzMTguNDcgNDNhNDggNDggMCAwIDAtNjEgMEw0LjM0IDI1MS40N2ExMiAxMiAwIDAgMC0xLjYgMTYuOWwyNS41IDMxQTEyIDEyIDAgMCAwIDQ1LjE1IDMwMWwyMzUuMjItMTkzLjc0YTEyLjE5IDEyLjE5IDAgMCAxIDE1LjMgMEw1MzAuOSAzMDFhMTIgMTIgMCAwIDAgMTYuOS0xLjZsMjUuNS0zMWExMiAxMiAwIDAgMC0xLjctMTYuOTN6Ij48L3BhdGg+PC9zdmc+",alt:"List icon",className:"navbar-icon"})," Home")),r.createElement("li",null," ",r.createElement(l.N_,{className:e.endsWith("all")?"disabled":"",to:"/all"},r.createElement("img",{src:"data:image/svg+xml;base64,PHN2ZyBhcmlhLWhpZGRlbj0idHJ1ZSIgZm9jdXNhYmxlPSJmYWxzZSIgZGF0YS1wcmVmaXg9ImZhcyIgZGF0YS1pY29uPSJ0aC1saXN0IiBjbGFzcz0ic3ZnLWlubGluZS0tZmEgZmEtdGgtbGlzdCBmYS13LTE2IiByb2xlPSJpbWciIHhtbG5zPSJodHRwOi8vd3d3LnczLm9yZy8yMDAwL3N2ZyIgdmlld0JveD0iMCAwIDUxMiA1MTIiPjxwYXRoIGZpbGw9ImN1cnJlbnRDb2xvciIgZD0iTTE0OS4zMzMgMjE2djgwYzAgMTMuMjU1LTEwLjc0NSAyNC0yNCAyNEgyNGMtMTMuMjU1IDAtMjQtMTAuNzQ1LTI0LTI0di04MGMwLTEzLjI1NSAxMC43NDUtMjQgMjQtMjRoMTAxLjMzM2MxMy4yNTUgMCAyNCAxMC43NDUgMjQgMjR6TTAgMzc2djgwYzAgMTMuMjU1IDEwLjc0NSAyNCAyNCAyNGgxMDEuMzMzYzEzLjI1NSAwIDI0LTEwLjc0NSAyNC0yNHYtODBjMC0xMy4yNTUtMTAuNzQ1LTI0LTI0LTI0SDI0Yy0xMy4yNTUgMC0yNCAxMC43NDUtMjQgMjR6TTEyNS4zMzMgMzJIMjRDMTAuNzQ1IDMyIDAgNDIuNzQ1IDAgNTZ2ODBjMCAxMy4yNTUgMTAuNzQ1IDI0IDI0IDI0aDEwMS4zMzNjMTMuMjU1IDAgMjQtMTAuNzQ1IDI0LTI0VjU2YzAtMTMuMjU1LTEwLjc0NS0yNC0yNC0yNHptODAgNDQ4SDQ4OGMxMy4yNTUgMCAyNC0xMC43NDUgMjQtMjR2LTgwYzAtMTMuMjU1LTEwLjc0NS0yNC0yNC0yNEgyMDUuMzMzYy0xMy4yNTUgMC0yNCAxMC43NDUtMjQgMjR2ODBjMCAxMy4yNTUgMTAuNzQ1IDI0IDI0IDI0em0tMjQtNDI0djgwYzAgMTMuMjU1IDEwLjc0NSAyNCAyNCAyNEg0ODhjMTMuMjU1IDAgMjQtMTAuNzQ1IDI0LTI0VjU2YzAtMTMuMjU1LTEwLjc0NS0yNC0yNC0yNEgyMDUuMzMzYy0xMy4yNTUgMC0yNCAxMC43NDUtMjQgMjR6bTI0IDI2NEg0ODhjMTMuMjU1IDAgMjQtMTAuNzQ1IDI0LTI0di04MGMwLTEzLjI1NS0xMC43NDUtMjQtMjQtMjRIMjA1LjMzM2MtMTMuMjU1IDAtMjQgMTAuNzQ1LTI0IDI0djgwYzAgMTMuMjU1IDEwLjc0NSAyNCAyNCAyNHoiPjwvcGF0aD48L3N2Zz4=",alt:"List icon",className:"navbar-icon"})," All posts"))))}},95238:function(e,t,n){var r=n(96540);t.A=()=>{const e=(0,r.useState)(0),t=e[0],n=e[1],l=()=>{const e=document.getElementById("blog-post-content");if(!e)return;const t=window.scrollY;if(0===t)return void n(0);const r=e.clientHeight,l=e.offsetTop,o=window.innerHeight;n((t-l+o)/r*100)};(0,r.useEffect)(()=>{if(document.getElementById("blog-post-content"))return window.addEventListener("scroll",l),()=>{window.removeEventListener("scroll",l)}},[]);const o={width:t+"%"};return r.createElement("div",{className:"reading-progress-bar"},r.createElement("div",{className:"reading-progress",style:o}))}}}]);
2//# sourceMappingURL=component---src-templates-blog-post-js-content-file-path-blog-2020-02-14-import-excel-to-shp-list-mdx-53f01b7eadc0ad0477c0.js.map