{"id":921,"date":"2023-04-11T13:35:44","date_gmt":"2023-04-11T20:35:44","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/powershell-community\/?p=921"},"modified":"2023-04-15T07:49:47","modified_gmt":"2023-04-15T14:49:47","slug":"convert-specific-table-of-excel-sheet-to-json","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/powershell-community\/convert-specific-table-of-excel-sheet-to-json\/","title":{"rendered":"Convert specific table of excel sheet to JSON using PowerShell"},"content":{"rendered":"<p>There is an excellent <a href=\"https:\/\/github.com\/chrisbrownie\/Convert-ExcelSheetToJson\/blob\/master\/Convert-ExcelSheetToJson.ps1\">script on GitHub<\/a> that helps to convert a full Excel sheet to <a href=\"https:\/\/www.w3schools.com\/whatis\/whatis_json.asp\">JSON<\/a> format using PowerShell. The script expects the table to be at the start of the sheet; that is, to have the first header in the <code>A1<\/code> cell.<\/p>\n<p>I had a little different requirement. I had to convert a specific table among various tables available within a sheet in an Excel file as shown in image below.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/devblogs.microsoft.com\/powershell-community\/wp-content\/uploads\/sites\/69\/2023\/04\/Image-MultipleTablesInOneSheet.png\" alt=\"Screenshot of an Excel sheet showing a table in the middle of a sheet instead of at the start\" \/><\/p>\n<p>Our requirement is to read <code>Class 6<\/code> student&#8217;s data. In the above screenshot, there are multiple sheets within the Excel workbook. There are multiple tables like <code>Class 1<\/code>, <code>Class 2<\/code>, and so on inside the <strong>Science<\/strong> sheet.<\/p>\n<p>As our requirement is to only read <code>Class 6<\/code> student&#8217;s data from <strong>Science<\/strong> sheet, let&#8217;s look closely at how the data is available in the Excel sheet.<\/p>\n<ul>\n<li>The name of the class is at row 44.<\/li>\n<li>The column headers are on row 45.<\/li>\n<li>The data starts from row 46.<\/li>\n<\/ul>\n<p><div class=\"alert alert-primary\"><p class=\"alert-divider\"><i class=\"fabric-icon fabric-icon--Info\"><\/i><strong>Note<\/strong><\/p> The tables can be at any location (any column and any row) within the sheet. The only fixed identifier is <strong>ClassName<\/strong> which is <code>Class 6<\/code> in this example. <\/div><\/p>\n<h2>Steps to follow<\/h2>\n<p>Follow these steps to see how you can read <code>Class 6<\/code> data from <strong>Science<\/strong> sheet:<\/p>\n<h3>Handle input parameters.<\/h3>\n<p>The script accepts 3 parameters:<\/p>\n<ul>\n<li>$InputFileFullPath &#8211; This is path of the input Excel file. <\/li>\n<li>$SubjectName &#8211; This is name of the sheet inside the Excel file. <\/li>\n<li>$ClassName &#8211; This is name of the table within the Excel sheet.<\/li>\n<\/ul>\n<pre><code class=\"language-powershell\">$InputFileFullPath = 'C:\\Data\\ABCDSchool.xlsx' \n$SubjectName = 'Science' \n$ClassName = 'Class 6'<\/code><\/pre>\n<h3>Open the Excel file and read the <strong>Science<\/strong> sheet.<\/h3>\n<pre><code class=\"powershell\">$excelApplication = New-Object -ComObject Excel.Application\n$excelApplication.DisplayAlerts = $false\n$Workbook = $excelApplication.Workbooks.Open($InputFileFullPath)\n$sheet = $Workbook.Sheets | Where-Object { $_.Name -eq $SubjectName }\nif (-not $sheet) { throw \"Could not find subject '$SubjectName' in the workbook\" }<\/code><\/pre>\n<h3>Grab the <code>Class 6<\/code> table within the <strong>Science<\/strong> sheet to work with.<\/h3>\n<pre><code class=\"powershell\"># Find the cell where Class name is mentioned\n$found = $sheet.Cells.Find($ClassName) #find the cell where Class name is mentioned\n$beginAddress = $Found.Address(0,0,1,1).Split(\"!\")[1]\n$beginRowAddress = $beginAddress.Substring(1,2)\n$startHeaderRowNumber = [int]$beginRowAddress + 1 #header row starts 1 row after the class name \n$startDataRowNumber = $startHeaderRowNumber + 1 #student data row starts 1 rows after header row\n$beginColumnAddress = $beginAddress.Substring(0,1)\n$startColumnHeaderNumber = [BYTE][CHAR]$beginColumnAddress - 65 + 1 #ASCII number of column<\/code><\/pre>\n<h3>Extract the header column names (<strong>Logical Seat Location<\/strong>, <strong>Actual Seat Location<\/strong>, <strong>LAN Port #<\/strong>, <strong>Monitor Cable Port<\/strong>, <strong>Student Name<\/strong>, <strong>Student#<\/strong>, and <strong>Room Type<\/strong>)<\/h3>\n<pre><code class=\"language-powershell\">$Headers          = @{}\n$numberOfColumns  = 0\n$foundHeaderValue = $true\nwhile ($foundHeaderValue -eq $true) {\n    $headerCellValue = $sheet.Cells.Item($startHeaderRowNumber, $numberOfColumns+$startColumnHeaderNumber).Text \n    if ($headerCellValue.Trim().Length -eq 0) {\n        $foundHeaderValue = $false\n    } else {\n        $numberOfColumns++\n        if($Headers.ContainsValue($headerCellValue))\n        {\n            #do not add any duplicate column again.\n        }\n        else\n        {            \n            $Headers.$numberOfColumns = $headerCellValue\n        }\n    }\n}<\/code><\/pre>\n<h3>Extract the data (<code>Class 6<\/code> student information rows).<\/h3>\n<pre><code class=\"language-powershell\">$rowNumber = $startDataRowNumber\n$finish = $false\nwhile($finish -eq $false)\n{\n    if ($rowNumber -gt 1) {\n        $result = @{}        \n        foreach ($columnNumber in $Headers.GetEnumerator()) {\n            $columnName = $columnNumber.Value\n            $cellValue = $sheet.Cells.Item($rowNumber, $columnNumber.Name+($startColumnHeaderNumber-1)).Value2 # student data row, student data column number\n            if($cellValue -eq $null)\n            {\n                $finish = $true\n                break;\n            }\n            $result.Add($columnName.Trim(),$cellValue.Trim())\n        }\n        if($finish -eq $false)\n        {\n            $result.Add(\"RowNumber\",$rowNumber) #adding excel sheet row number for validation        \n            $results += $result\n            $rowNumber++\n        }\n    }\n}<\/code><\/pre>\n<h3>Create the JSON file and close the Excel file.<\/h3>\n<pre><code class=\"language-powershell\">$inputFileName = Split-Path $InputFileFullPath -leaf\n$jsonOutputFileName = \"$($inputFileName.Split(\".\")[0])-$SubjectName-$ClassName.json\"\n$jsonOutputFileFullPath = [System.IO.Path]::GetFullPath($jsonOutputFileName) #Output file name will be \"ABCDSchool-Science-Class 6.json\" \nWrite-Host \"Converting sheet '$SubjectName' to '$jsonOutputFileFullPath'\"\n$ignoreOutput = $results | ConvertTo-Json | Out-File -Encoding ASCII -FilePath $jsonOutputFileFullPath\n$ignoreOutput = $excelApplication.Workbooks.Close()\n$ignoreOutput = [void][System.Runtime.InteropServices.Marshal]::ReleaseComObject($excelApplication)<\/code><\/pre>\n<h2>Putting it all together<\/h2>\n<p>The full <a href=\"https:\/\/gist.github.com\/Sonam12345\/af457456e0df6c5290c54f8bb5bf4fda\">code<\/a> goes like this:<\/p>\n<pre><code class=\"language-powershell\">param (\n    # Excel name\n    [Parameter(Mandatory=$true)]\n    [string]$InputFileFullPath,\n    # Sheet name\n    [Parameter(Mandatory=$true)]\n    [string]$SubjectName,\n    # Identifier for the table\n    [Parameter(Mandatory=$true)]\n    [string]$ClassName\n)\n\n#region Open Excel file\n$excelApplication = New-Object -ComObject Excel.Application\n$excelApplication.DisplayAlerts = $false\n$Workbook = $excelApplication.Workbooks.Open($InputFileFullPath)\n\n# Find sheet\n$sheet = $Workbook.Sheets | Where-Object { $_.Name -eq $SubjectName }\n\nif (-not $sheet) {\n    throw \"Could not find subject '$SubjectName' in the workbook\"\n}\n#endregion Open Excel file\n\n#region Grab the table within sheet to work with\n# Find the cell where Class name is mentioned\n$found           = $sheet.Cells.Find($ClassName)\n$beginAddress    = $Found.Address(0, 0, 1, 1).Split('!')[1]\n$beginRowAddress = $beginAddress.Substring(1, 2)\n# Header row starts 1 row after the class name\n$startHeaderRowNumber = [int]$beginRowAddress + 2\n# Student data row starts 1 row after header row\n$startDataRowNumber = $startHeaderRowNumber + 1\n$beginColumnAddress = $beginAddress.Substring(0,1)\n# ASCII number of column\n$startColumnHeaderNumber = [BYTE][CHAR]$beginColumnAddress - 65 + 1\n#endregion Grab the table within sheet to work with\n\n#region Extract Header Columns Name\n$Headers          = @{}\n$numberOfColumns  = 0\n$foundHeaderValue = $true\n\nwhile ($foundHeaderValue -eq $true) {\n    $headerCellValue = $sheet.Cells.Item(\n        $startHeaderRowNumber,\n        ($numberOfColumns + $startColumnHeaderNumber)\n    ).Text\n\n    if ($headerCellValue.Trim().Length -eq 0) {\n        $foundHeaderValue = $false\n    } else {\n        $numberOfColumns++\n        if ($Headers.ContainsValue($headerCellValue)) {\n            # Do not add any duplicate column again.\n        } else {\n            $Headers.$numberOfColumns = $headerCellValue\n        }\n    }\n}\n#endregion Extract Header Columns Name\n\n#region Extract Student Information Rows\n$results   = @()\n$rowNumber = $startDataRowNumber\n$finish    = $false\n\nwhile ($finish -eq $false) {\n    if ($rowNumber -gt 1) {\n        $result = @{}\n\n        foreach ($columnNumber in $Headers.GetEnumerator()) {\n            $columnName = $columnNumber.Value\n            # Student data row, student data column number\n            $cellValue = $sheet.Cells.Item(\n                $rowNumber,\n                ($columnNumber.Name + ($startColumnHeaderNumber - 1))\n            ).Value2\n\n            if ($cellValue -eq $null) {\n                $finish = $true\n                break\n            }\n\n            $result.Add($columnName.Trim(),$cellValue.Trim())\n        }\n\n        if ($finish -eq $false) {\n            # Adding Excel sheet row number for validation\n            $result.Add(\"RowNumber\",$rowNumber)\n            $results += $result\n            $rowNumber++\n        }\n    }\n}\n#endregion Extract Student Information Rows\n\n#region Create JSON file and close Excel file\n$inputFileName = Split-Path $InputFileFullPath -leaf\n$inputFileName = $inputFileName.Split('.')[0]\n# Output file name will be \"ABCDSchool-Science-Class 6.json\"\n$jsonOutputFileName     = \"$inputFileName-$SubjectName-$ClassName.json\"\n$jsonOutputFileFullPath = [System.IO.Path]::GetFullPath($jsonOutputFileName)\n\nWrite-Host \"Converting sheet '$SubjectName' to '$jsonOutputFileFullPath'\"\n\n$null = $results |\n    ConvertTo-Json |\n    Out-File -Encoding ASCII -FilePath $jsonOutputFileFullPath\n$null = $excelApplication.Workbooks.Close()\n$null = [System.Runtime.InteropServices.Marshal]::ReleaseComObject(\n    $excelApplication\n)\n#endregion Create JSON file and close Excel file<\/code><\/pre>\n<p>The output JSON file will look like below:<\/p>\n<pre><code class=\"language-json\">[\n    {\n        \"Room Type\":  \"Standard\",\n        \"RowNumber\":  46,\n        \"Student Name\":  \"Alex\",\n        \"Student#\":  \"RL45\",\n        \"LAN Port #\":  \"LAN Port 7-8\",\n        \"Logical Seat Location\":  \"SL 11\",\n        \"Actual Seat Location\":  \"Seat43\",\n        \"Monitor Cable Port\":  \"C-D\"\n    },\n    {\n        \"Room Type\":  \"Standard\",\n        \"RowNumber\":  47,\n        \"Student Name\":  \"Alex\",\n        \"Student#\":  \"RL45\",\n        \"LAN Port #\":  \"LAN Port 5-6\",\n        \"Logical Seat Location\":  \"SL 11\",\n        \"Actual Seat Location\":  \"Seat43\",\n        \"Monitor Cable Port\":  \"A-B\"\n    },\n    {\n        \"Room Type\":  \"Standard\",\n        \"RowNumber\":  48,\n        \"Student Name\":  \"John\",\n        \"Student#\":  \"RL47\",\n        \"LAN Port #\":  \"LAN Port 3-4\",\n        \"Logical Seat Location\":  \"SL 11\",\n        \"Actual Seat Location\":  \"Seat43\",\n        \"Monitor Cable Port\":  \"C-D\"\n    },\n    {\n        \"Room Type\":  \"Standard\",\n        \"RowNumber\":  49,\n        \"Student Name\":  \"John\",\n        \"Student#\":  \"RL47\",\n        \"LAN Port #\":  \"LAN Port 1-2\",\n        \"Logical Seat Location\":  \"SL 11\",\n        \"Actual Seat Location\":  \"Seat43\",\n        \"Monitor Cable Port\":  \"A-B\"\n    },\n    {\n        \"Room Type\":  \"Standard\",\n        \"RowNumber\":  50,\n        \"Student Name\":  \"Victor\",\n        \"Student#\":  \"RL35\",\n        \"LAN Port #\":  \"LAN Port 7-8\",\n        \"Logical Seat Location\":  \"SL 10\",\n        \"Actual Seat Location\":  \"Seat33\",\n        \"Monitor Cable Port\":  \"C-D\"\n    },\n    {\n        \"Room Type\":  \"Standard\",\n        \"RowNumber\":  51,\n        \"Student Name\":  \"Victor\",\n        \"Student#\":  \"RL35\",\n        \"LAN Port #\":  \"LAN Port 5-6\",\n        \"Logical Seat Location\":  \"SL 10\",\n        \"Actual Seat Location\":  \"Seat33\",\n        \"Monitor Cable Port\":  \"A-B\"\n    },\n    {\n        \"Room Type\":  \"Standard\",\n        \"RowNumber\":  52,\n        \"Student Name\":  \"Honey\",\n        \"Student#\":  \"RL42\",\n        \"LAN Port #\":  \"LAN Port 3-4\",\n        \"Logical Seat Location\":  \"SL 10\",\n        \"Actual Seat Location\":  \"Seat33\",\n        \"Monitor Cable Port\":  \"C-D\"\n    }\n]<\/code><\/pre>\n<p>Feel free to drop your feedback and inputs on this page. Till then, Happy Scripting!!!<\/p>\n<p>Read more about <a href=\"https:\/\/devblogs.microsoft.com\/powershell-community\/\">PowerShell-Community<\/a><\/p>\n<p><!-- Link Reference Definitions --><\/p>\n","protected":false},"excerpt":{"rendered":"<p>This posts explains how to Convert Specific Table of Excel Sheet to JSON<\/p>\n","protected":false},"author":115450,"featured_media":77,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[13],"tags":[82,52,3],"class_list":["post-921","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-powershell","tag-excel","tag-json","tag-powershell"],"acf":[],"blog_post_summary":"<p>This posts explains how to Convert Specific Table of Excel Sheet to JSON<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/powershell-community\/wp-json\/wp\/v2\/posts\/921","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/powershell-community\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/powershell-community\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/powershell-community\/wp-json\/wp\/v2\/users\/115450"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/powershell-community\/wp-json\/wp\/v2\/comments?post=921"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/powershell-community\/wp-json\/wp\/v2\/posts\/921\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/powershell-community\/wp-json\/wp\/v2\/media\/77"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/powershell-community\/wp-json\/wp\/v2\/media?parent=921"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/powershell-community\/wp-json\/wp\/v2\/categories?post=921"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/powershell-community\/wp-json\/wp\/v2\/tags?post=921"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}