{"id":13,"date":"2018-05-11T11:00:00","date_gmt":"2018-05-11T11:00:00","guid":{"rendered":"http:\/\/nkascocom.nk\/wordpress\/index.php\/2018\/05\/11\/2018-5-9-manipulating-excel-with-windows-powershell\/"},"modified":"2018-05-11T11:00:00","modified_gmt":"2018-05-11T11:00:00","slug":"2018-5-9-manipulating-excel-with-windows-powershell","status":"publish","type":"post","link":"https:\/\/blog.nkasco.com\/wordpress\/index.php\/2018\/05\/11\/2018-5-9-manipulating-excel-with-windows-powershell\/","title":{"rendered":"Manipulating Excel with Windows PowerShell"},"content":{"rendered":"
\n

The need to read, modify, or output to an Excel document seems to be something that commonly occurs in the Powershell world. Whether it’s needing to simply append logs or maybe something more complex where there is data on a number of different sheets within a single workbook. Maybe an external client sends you an Excel sheet and you don’t even have Excel installed. Let’s go over 2 potential ways to manipulate Excel data with Windows PowerShell:<\/p>\n

    \n
  1. Leveraging the ImportExcel Module<\/li>\n
  2. Using a COM Object<\/li>\n<\/ol>\n

    Leveraging the ImportExcel Module:<\/strong><\/p>\n

    First, Doug Finke<\/a> has already created an awesome module to manipulate Excel data that isn’t even dependent on Excel being installed. To get it installed simply open an elevated console and run:<\/p>\n

    Install-Module -Name ImportExcel<\/pre>\n
    Now that you have the module installed you can start leveraging it’s cmdlets. To get started go ahead and run:<\/p>\n
    Get-Command -Module ImportExcel<\/pre>\n<\/div>\n<\/p>\n<\/p>\n
                
    \n              View fullsize<\/span><\/p>\n<\/p>\n
    <\/p>\n
                      \"cmdlets.png\"<\/p><\/div>\n<\/p><\/div>\n
                <\/button><\/p>\n<\/figure><\/div>\n
    \n
    Feel free to check it out for yourself at: https:\/\/github.com\/dfinke\/ImportExcel<\/a>.<\/p>\n
    Using a COM Object:<\/strong><\/p>\n
    If you work in an environment where security is a bit higher and it can be more difficult than it’s worth to get the module installed. If you already have Excel installed you can simply create a COM object and have direct access to do whatever might be needed. Let’s take a look at an example where we simply need to import our data into Powershell:<\/p>\n
    $WorkbookPath = \"C:\\Users\\Nate\\Desktop\\Book1.xlsx\"\n\n#Create COM object & open our workbook\n$ExcelObj = New-Object -ComObject Excel.Application\n$ExcelWorkbook = $ExcelObj.Workbooks.Open($WorkbookPath)\n\n#Select the first sheet\n$ExcelWorksheet = $ExcelWorkbook.Sheets.Item(1)\n\n#Get the used range\n$UsedRange = $ExcelWorksheet.UsedRange\n\n#Create a PS Custom Object to store data in\n$ExcelData = [PSCustomObject]@{}\n\n$Column = 1\nwhile($Column -le 3){\n    $Data = $UsedRange.Cells.Item(1,$Column).EntireColumn.value2 | Select-Object -Skip 1\n    $Header = $UsedRange.Cells.Item(1,$Column).EntireColumn.value2 | Select-Object -First 1\n\n    $ExcelData | Add-Member -type NoteProperty -Name $Header -Value $Data\n\n    $Column++\n}\n\n#Close Excel\n$excelObj.Quit()<\/pre>\n
    Now we have all of our data imported into Powershell as $ExcelData to do whatever we need to do. I encourage you to leverage Get-Member<\/em> on $ExcelWorksheet and don’t forget to run it on sub-members as well to identify all possible ways to manipulate data in the way that is needed. Take a look at the output below compared to our actual Excel document:<\/p>\n<\/div>\n<\/p>\n<\/p>\n
                
    \n              View fullsize<\/span><\/p>\n<\/p>\n
    <\/p>\n
                      \"ExcelDoc.png\"<\/p><\/div>\n<\/p><\/div>\n
                <\/button><\/p>\n<\/figure><\/div>\n<\/p>\n<\/p>\n
                
    \n              View fullsize<\/span><\/p>\n<\/p>\n
    <\/p>\n
                      \"Output.png\"<\/p><\/div>\n<\/p><\/div>\n
                <\/button><\/p>\n<\/figure><\/div>\n","protected":false},"excerpt":{"rendered":"
    The need to read, modify, or output to an Excel document seems to be something that commonly occurs in the Powershell world. Whether it’s needing to simply append logs or maybe something more complex where there is data on a number of different sheets within a single workbook. Maybe an external client sends you an Excel sheet and you don’t even have Excel installed. Let’s go over 2 potential ways to manipulate Excel data with Windows PowerShell  <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-13","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"yoast_head":"\nManipulating Excel with Windows PowerShell - Nathan Kasco - Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/blog.nkasco.com\/wordpress\/index.php\/2018\/05\/11\/2018-5-9-manipulating-excel-with-windows-powershell\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Manipulating Excel with Windows PowerShell - Nathan Kasco - Blog\" \/>\n<meta property=\"og:description\" content=\"The need to read, modify, or output to an Excel document seems to be something that commonly occurs in the Powershell world. Whether it's needing to simply append logs or maybe something more complex where there is data on a number of different sheets within a single workbook. Maybe an external client sends you an Excel sheet and you don't even have Excel installed. Let's go over 2 potential ways to manipulate Excel data with Windows PowerShell\" \/>\n<meta property=\"og:url\" content=\"https:\/\/blog.nkasco.com\/wordpress\/index.php\/2018\/05\/11\/2018-5-9-manipulating-excel-with-windows-powershell\/\" \/>\n<meta property=\"og:site_name\" content=\"Nathan Kasco - Blog\" \/>\n<meta property=\"article:published_time\" content=\"2018-05-11T11:00:00+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/images.squarespace-cdn.com\/content\/v1\/5ad54fd4cef37247634a91ca\/1525911377285-H2OW1PH10SFVMX9H9UAJ\/cmdlets.png\" \/>\n<meta name=\"author\" content=\"Nate Kasco\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@Bu5yGiraffe\" \/>\n<meta name=\"twitter:site\" content=\"@Bu5yGiraffe\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Nate Kasco\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/blog.nkasco.com\/wordpress\/index.php\/2018\/05\/11\/2018-5-9-manipulating-excel-with-windows-powershell\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/blog.nkasco.com\/wordpress\/index.php\/2018\/05\/11\/2018-5-9-manipulating-excel-with-windows-powershell\/\"},\"author\":{\"name\":\"Nate Kasco\",\"@id\":\"https:\/\/blog.nkasco.com\/wordpress\/#\/schema\/person\/1dfd694a2ed094a43a37dc6882c65eb3\"},\"headline\":\"Manipulating Excel with Windows PowerShell\",\"datePublished\":\"2018-05-11T11:00:00+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/blog.nkasco.com\/wordpress\/index.php\/2018\/05\/11\/2018-5-9-manipulating-excel-with-windows-powershell\/\"},\"wordCount\":309,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/blog.nkasco.com\/wordpress\/#\/schema\/person\/1dfd694a2ed094a43a37dc6882c65eb3\"},\"image\":{\"@id\":\"https:\/\/blog.nkasco.com\/wordpress\/index.php\/2018\/05\/11\/2018-5-9-manipulating-excel-with-windows-powershell\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/images.squarespace-cdn.com\/content\/v1\/5ad54fd4cef37247634a91ca\/1525911377285-H2OW1PH10SFVMX9H9UAJ\/cmdlets.png\",\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/blog.nkasco.com\/wordpress\/index.php\/2018\/05\/11\/2018-5-9-manipulating-excel-with-windows-powershell\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/blog.nkasco.com\/wordpress\/index.php\/2018\/05\/11\/2018-5-9-manipulating-excel-with-windows-powershell\/\",\"url\":\"https:\/\/blog.nkasco.com\/wordpress\/index.php\/2018\/05\/11\/2018-5-9-manipulating-excel-with-windows-powershell\/\",\"name\":\"Manipulating Excel with Windows PowerShell - Nathan Kasco - Blog\",\"isPartOf\":{\"@id\":\"https:\/\/blog.nkasco.com\/wordpress\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/blog.nkasco.com\/wordpress\/index.php\/2018\/05\/11\/2018-5-9-manipulating-excel-with-windows-powershell\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/blog.nkasco.com\/wordpress\/index.php\/2018\/05\/11\/2018-5-9-manipulating-excel-with-windows-powershell\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/images.squarespace-cdn.com\/content\/v1\/5ad54fd4cef37247634a91ca\/1525911377285-H2OW1PH10SFVMX9H9UAJ\/cmdlets.png\",\"datePublished\":\"2018-05-11T11:00:00+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/blog.nkasco.com\/wordpress\/index.php\/2018\/05\/11\/2018-5-9-manipulating-excel-with-windows-powershell\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/blog.nkasco.com\/wordpress\/index.php\/2018\/05\/11\/2018-5-9-manipulating-excel-with-windows-powershell\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/blog.nkasco.com\/wordpress\/index.php\/2018\/05\/11\/2018-5-9-manipulating-excel-with-windows-powershell\/#primaryimage\",\"url\":\"https:\/\/images.squarespace-cdn.com\/content\/v1\/5ad54fd4cef37247634a91ca\/1525911377285-H2OW1PH10SFVMX9H9UAJ\/cmdlets.png\",\"contentUrl\":\"https:\/\/images.squarespace-cdn.com\/content\/v1\/5ad54fd4cef37247634a91ca\/1525911377285-H2OW1PH10SFVMX9H9UAJ\/cmdlets.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/blog.nkasco.com\/wordpress\/index.php\/2018\/05\/11\/2018-5-9-manipulating-excel-with-windows-powershell\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/blog.nkasco.com\/wordpress\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Manipulating Excel with Windows PowerShell\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/blog.nkasco.com\/wordpress\/#website\",\"url\":\"https:\/\/blog.nkasco.com\/wordpress\/\",\"name\":\"Nathan Kasco - Blog\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/blog.nkasco.com\/wordpress\/#\/schema\/person\/1dfd694a2ed094a43a37dc6882c65eb3\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/blog.nkasco.com\/wordpress\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/blog.nkasco.com\/wordpress\/#\/schema\/person\/1dfd694a2ed094a43a37dc6882c65eb3\",\"name\":\"Nate Kasco\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/blog.nkasco.com\/wordpress\/wp-content\/uploads\/2024\/03\/cropped-logo.png\",\"url\":\"https:\/\/blog.nkasco.com\/wordpress\/wp-content\/uploads\/2024\/03\/cropped-logo.png\",\"contentUrl\":\"https:\/\/blog.nkasco.com\/wordpress\/wp-content\/uploads\/2024\/03\/cropped-logo.png\",\"width\":200,\"height\":200,\"caption\":\"Nate Kasco\"},\"logo\":{\"@id\":\"https:\/\/blog.nkasco.com\/wordpress\/wp-content\/uploads\/2024\/03\/cropped-logo.png\"},\"sameAs\":[\"http:\/\/nkascocom.nk\/wordpress\",\"https:\/\/x.com\/Bu5yGiraffe\"],\"url\":\"https:\/\/blog.nkasco.com\/wordpress\/index.php\/author\/nate\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Manipulating Excel with Windows PowerShell - Nathan Kasco - Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/blog.nkasco.com\/wordpress\/index.php\/2018\/05\/11\/2018-5-9-manipulating-excel-with-windows-powershell\/","og_locale":"en_US","og_type":"article","og_title":"Manipulating Excel with Windows PowerShell - Nathan Kasco - Blog","og_description":"The need to read, modify, or output to an Excel document seems to be something that commonly occurs in the Powershell world. Whether it's needing to simply append logs or maybe something more complex where there is data on a number of different sheets within a single workbook. Maybe an external client sends you an Excel sheet and you don't even have Excel installed. Let's go over 2 potential ways to manipulate Excel data with Windows PowerShell","og_url":"https:\/\/blog.nkasco.com\/wordpress\/index.php\/2018\/05\/11\/2018-5-9-manipulating-excel-with-windows-powershell\/","og_site_name":"Nathan Kasco - Blog","article_published_time":"2018-05-11T11:00:00+00:00","og_image":[{"url":"https:\/\/images.squarespace-cdn.com\/content\/v1\/5ad54fd4cef37247634a91ca\/1525911377285-H2OW1PH10SFVMX9H9UAJ\/cmdlets.png","type":"","width":"","height":""}],"author":"Nate Kasco","twitter_card":"summary_large_image","twitter_creator":"@Bu5yGiraffe","twitter_site":"@Bu5yGiraffe","twitter_misc":{"Written by":"Nate Kasco","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/blog.nkasco.com\/wordpress\/index.php\/2018\/05\/11\/2018-5-9-manipulating-excel-with-windows-powershell\/#article","isPartOf":{"@id":"https:\/\/blog.nkasco.com\/wordpress\/index.php\/2018\/05\/11\/2018-5-9-manipulating-excel-with-windows-powershell\/"},"author":{"name":"Nate Kasco","@id":"https:\/\/blog.nkasco.com\/wordpress\/#\/schema\/person\/1dfd694a2ed094a43a37dc6882c65eb3"},"headline":"Manipulating Excel with Windows PowerShell","datePublished":"2018-05-11T11:00:00+00:00","mainEntityOfPage":{"@id":"https:\/\/blog.nkasco.com\/wordpress\/index.php\/2018\/05\/11\/2018-5-9-manipulating-excel-with-windows-powershell\/"},"wordCount":309,"commentCount":0,"publisher":{"@id":"https:\/\/blog.nkasco.com\/wordpress\/#\/schema\/person\/1dfd694a2ed094a43a37dc6882c65eb3"},"image":{"@id":"https:\/\/blog.nkasco.com\/wordpress\/index.php\/2018\/05\/11\/2018-5-9-manipulating-excel-with-windows-powershell\/#primaryimage"},"thumbnailUrl":"https:\/\/images.squarespace-cdn.com\/content\/v1\/5ad54fd4cef37247634a91ca\/1525911377285-H2OW1PH10SFVMX9H9UAJ\/cmdlets.png","inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/blog.nkasco.com\/wordpress\/index.php\/2018\/05\/11\/2018-5-9-manipulating-excel-with-windows-powershell\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/blog.nkasco.com\/wordpress\/index.php\/2018\/05\/11\/2018-5-9-manipulating-excel-with-windows-powershell\/","url":"https:\/\/blog.nkasco.com\/wordpress\/index.php\/2018\/05\/11\/2018-5-9-manipulating-excel-with-windows-powershell\/","name":"Manipulating Excel with Windows PowerShell - Nathan Kasco - Blog","isPartOf":{"@id":"https:\/\/blog.nkasco.com\/wordpress\/#website"},"primaryImageOfPage":{"@id":"https:\/\/blog.nkasco.com\/wordpress\/index.php\/2018\/05\/11\/2018-5-9-manipulating-excel-with-windows-powershell\/#primaryimage"},"image":{"@id":"https:\/\/blog.nkasco.com\/wordpress\/index.php\/2018\/05\/11\/2018-5-9-manipulating-excel-with-windows-powershell\/#primaryimage"},"thumbnailUrl":"https:\/\/images.squarespace-cdn.com\/content\/v1\/5ad54fd4cef37247634a91ca\/1525911377285-H2OW1PH10SFVMX9H9UAJ\/cmdlets.png","datePublished":"2018-05-11T11:00:00+00:00","breadcrumb":{"@id":"https:\/\/blog.nkasco.com\/wordpress\/index.php\/2018\/05\/11\/2018-5-9-manipulating-excel-with-windows-powershell\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/blog.nkasco.com\/wordpress\/index.php\/2018\/05\/11\/2018-5-9-manipulating-excel-with-windows-powershell\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/blog.nkasco.com\/wordpress\/index.php\/2018\/05\/11\/2018-5-9-manipulating-excel-with-windows-powershell\/#primaryimage","url":"https:\/\/images.squarespace-cdn.com\/content\/v1\/5ad54fd4cef37247634a91ca\/1525911377285-H2OW1PH10SFVMX9H9UAJ\/cmdlets.png","contentUrl":"https:\/\/images.squarespace-cdn.com\/content\/v1\/5ad54fd4cef37247634a91ca\/1525911377285-H2OW1PH10SFVMX9H9UAJ\/cmdlets.png"},{"@type":"BreadcrumbList","@id":"https:\/\/blog.nkasco.com\/wordpress\/index.php\/2018\/05\/11\/2018-5-9-manipulating-excel-with-windows-powershell\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/blog.nkasco.com\/wordpress\/"},{"@type":"ListItem","position":2,"name":"Manipulating Excel with Windows PowerShell"}]},{"@type":"WebSite","@id":"https:\/\/blog.nkasco.com\/wordpress\/#website","url":"https:\/\/blog.nkasco.com\/wordpress\/","name":"Nathan Kasco - Blog","description":"","publisher":{"@id":"https:\/\/blog.nkasco.com\/wordpress\/#\/schema\/person\/1dfd694a2ed094a43a37dc6882c65eb3"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/blog.nkasco.com\/wordpress\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/blog.nkasco.com\/wordpress\/#\/schema\/person\/1dfd694a2ed094a43a37dc6882c65eb3","name":"Nate Kasco","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/blog.nkasco.com\/wordpress\/wp-content\/uploads\/2024\/03\/cropped-logo.png","url":"https:\/\/blog.nkasco.com\/wordpress\/wp-content\/uploads\/2024\/03\/cropped-logo.png","contentUrl":"https:\/\/blog.nkasco.com\/wordpress\/wp-content\/uploads\/2024\/03\/cropped-logo.png","width":200,"height":200,"caption":"Nate Kasco"},"logo":{"@id":"https:\/\/blog.nkasco.com\/wordpress\/wp-content\/uploads\/2024\/03\/cropped-logo.png"},"sameAs":["http:\/\/nkascocom.nk\/wordpress","https:\/\/x.com\/Bu5yGiraffe"],"url":"https:\/\/blog.nkasco.com\/wordpress\/index.php\/author\/nate\/"}]}},"_links":{"self":[{"href":"https:\/\/blog.nkasco.com\/wordpress\/index.php\/wp-json\/wp\/v2\/posts\/13","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.nkasco.com\/wordpress\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.nkasco.com\/wordpress\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.nkasco.com\/wordpress\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.nkasco.com\/wordpress\/index.php\/wp-json\/wp\/v2\/comments?post=13"}],"version-history":[{"count":0,"href":"https:\/\/blog.nkasco.com\/wordpress\/index.php\/wp-json\/wp\/v2\/posts\/13\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.nkasco.com\/wordpress\/index.php\/wp-json\/wp\/v2\/media?parent=13"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.nkasco.com\/wordpress\/index.php\/wp-json\/wp\/v2\/categories?post=13"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.nkasco.com\/wordpress\/index.php\/wp-json\/wp\/v2\/tags?post=13"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}