There is a wonderful script on GitHub that helps to transform a full Excel sheet to JSON format utilizing PowerShell. The script expects the desk to be in the beginning of the sheet; that’s, to have the primary header within the A1 cell.
I had a bit completely different requirement. I needed to convert a selected desk amongst varied tables obtainable inside a sheet in an Excel file as proven in picture beneath.
Our requirement is to learn Class 6 pupil’s knowledge. Within the above screenshot, there are a number of sheets inside the Excel workbook. There are a number of tables like Class 1, Class 2, and so forth contained in the Science sheet.
As our requirement is to solely learn Class 6 pupil’s knowledge from Science sheet, let’s look carefully at how the information is obtainable within the Excel sheet.
- The identify of the category is at row 44.
- The column headers are on row 45.
- The information begins from row 46.
Observe
The tables may be at any location (any column and any row) inside the sheet. The one mounted identifier is ClassName which is Class 6 on this instance.
Steps to comply with
Observe these steps to see how one can learn Class 6 knowledge from Science sheet:
Deal with enter parameters.
The script accepts 3 parameters:
- $InputFileFullPath – That is path of the enter Excel file.
- $SubjectName – That is identify of the sheet contained in the Excel file.
- $ClassName – That is identify of the desk inside the Excel sheet.
$InputFileFullPath="C:DataABCDSchool.xlsx"
$SubjectName="Science"
$ClassName="Class 6"Open the Excel file and skim the Science sheet.
$excelApplication = New-Object -ComObject Excel.Utility
$excelApplication.DisplayAlerts = $false
$Workbook = $excelApplication.Workbooks.Open($InputFileFullPath)
$sheet = $Workbook.Sheets | The place-Object { $_.Title -eq $SubjectName }
if (-not $sheet) { throw "Couldn't discover topic '$SubjectName' within the workbook" }Seize the Class 6 desk inside the Science sheet to work with.
# Discover the cell the place Class identify is talked about
$discovered = $sheet.Cells.Discover($ClassName) #discover the cell the place Class identify is talked about
$beginAddress = $Discovered.Deal with(0,0,1,1).Cut up("!")[1]
$beginRowAddress = $beginAddress.Substring(1,2)
$startHeaderRowNumber = [int]$beginRowAddress + 1 #header row begins 1 row after the category identify
$startDataRowNumber = $startHeaderRowNumber + 1 #pupil knowledge row begins 1 rows after header row
$beginColumnAddress = $beginAddress.Substring(0,1)
$startColumnHeaderNumber = [BYTE][CHAR]$beginColumnAddress - 65 + 1 #ASCII variety of columnExtract the header column names (Logical Seat Location, Precise Seat Location, LAN Port #, Monitor Cable Port, Scholar Title, Scholar#, and Room Kind)
$Headers = @{}
$numberOfColumns = 0
$foundHeaderValue = $true
whereas ($foundHeaderValue -eq $true) {
$headerCellValue = $sheet.Cells.Merchandise($startHeaderRowNumber, $numberOfColumns+$startColumnHeaderNumber).Textual content
if ($headerCellValue.Trim().Size -eq 0) {
$foundHeaderValue = $false
} else {
$numberOfColumns++
if($Headers.ContainsValue($headerCellValue))
{
#don't add any duplicate column once more.
}
else
{
$Headers.$numberOfColumns = $headerCellValue
}
}
}$rowNumber = $startDataRowNumber
$end = $false
whereas($end -eq $false)
{
if ($rowNumber -gt 1) {
$end result = @{}
foreach ($columnNumber in $Headers.GetEnumerator()) {
$columnName = $columnNumber.Worth
$cellValue = $sheet.Cells.Merchandise($rowNumber, $columnNumber.Title+($startColumnHeaderNumber-1)).Value2 # pupil knowledge row, pupil knowledge column quantity
if($cellValue -eq $null)
{
$end = $true
break;
}
$end result.Add($columnName.Trim(),$cellValue.Trim())
}
if($end -eq $false)
{
$end result.Add("RowNumber",$rowNumber) #including excel sheet row quantity for validation
$outcomes += $end result
$rowNumber++
}
}
}Create the JSON file and shut the Excel file.
$inputFileName = Cut up-Path $InputFileFullPath -leaf
$jsonOutputFileName = "$($inputFileName.Cut up(".")[0])-$SubjectName-$ClassName.json"
$jsonOutputFileFullPath = [System.IO.Path]::GetFullPath($jsonOutputFileName) #Output file identify will probably be "ABCDSchool-Science-Class 6.json"
Write-Host "Changing sheet '$SubjectName' to '$jsonOutputFileFullPath'"
$ignoreOutput = $outcomes | ConvertTo-Json | Out-File -Encoding ASCII -FilePath $jsonOutputFileFullPath
$ignoreOutput = $excelApplication.Workbooks.Shut()
$ignoreOutput = [void][System.Runtime.InteropServices.Marshal]::ReleaseComObject($excelApplication)Placing all of it collectively
The total code goes like this:
param (
# Excel identify
[Parameter(Mandatory=$true)]
[string]$InputFileFullPath,
# Sheet identify
[Parameter(Mandatory=$true)]
[string]$SubjectName,
# Identifier for the desk
[Parameter(Mandatory=$true)]
[string]$ClassName
)
#area Open Excel file
$excelApplication = New-Object -ComObject Excel.Utility
$excelApplication.DisplayAlerts = $false
$Workbook = $excelApplication.Workbooks.Open($InputFileFullPath)
# Discover sheet
$sheet = $Workbook.Sheets | The place-Object { $_.Title -eq $SubjectName }
if (-not $sheet) {
throw "Couldn't discover topic '$SubjectName' within the workbook"
}
#endregion Open Excel file
#area Seize the desk inside sheet to work with
# Discover the cell the place Class identify is talked about
$discovered = $sheet.Cells.Discover($ClassName)
$beginAddress = $Discovered.Deal with(0, 0, 1, 1).Cut up('!')[1]
$beginRowAddress = $beginAddress.Substring(1, 2)
# Header row begins 1 row after the category identify
$startHeaderRowNumber = [int]$beginRowAddress + 2
# Scholar knowledge row begins 1 row after header row
$startDataRowNumber = $startHeaderRowNumber + 1
$beginColumnAddress = $beginAddress.Substring(0,1)
# ASCII variety of column
$startColumnHeaderNumber = [BYTE][CHAR]$beginColumnAddress - 65 + 1
#endregion Seize the desk inside sheet to work with
#area Extract Header Columns Title
$Headers = @{}
$numberOfColumns = 0
$foundHeaderValue = $true
whereas ($foundHeaderValue -eq $true) {
$headerCellValue = $sheet.Cells.Merchandise(
$startHeaderRowNumber,
($numberOfColumns + $startColumnHeaderNumber)
).Textual content
if ($headerCellValue.Trim().Size -eq 0) {
$foundHeaderValue = $false
} else {
$numberOfColumns++
if ($Headers.ContainsValue($headerCellValue)) {
# Don't add any duplicate column once more.
} else {
$Headers.$numberOfColumns = $headerCellValue
}
}
}
#endregion Extract Header Columns Title
#area Extract Scholar Data Rows
$outcomes = @()
$rowNumber = $startDataRowNumber
$end = $false
whereas ($end -eq $false) {
if ($rowNumber -gt 1) {
$end result = @{}
foreach ($columnNumber in $Headers.GetEnumerator()) {
$columnName = $columnNumber.Worth
# Scholar knowledge row, pupil knowledge column quantity
$cellValue = $sheet.Cells.Merchandise(
$rowNumber,
($columnNumber.Title + ($startColumnHeaderNumber - 1))
).Value2
if ($cellValue -eq $null) {
$end = $true
break
}
$end result.Add($columnName.Trim(),$cellValue.Trim())
}
if ($end -eq $false) {
# Including Excel sheet row quantity for validation
$end result.Add("RowNumber",$rowNumber)
$outcomes += $end result
$rowNumber++
}
}
}
#endregion Extract Scholar Data Rows
#area Create JSON file and shut Excel file
$inputFileName = Cut up-Path $InputFileFullPath -leaf
$inputFileName = $inputFileName.Cut up('.')[0]
# Output file identify will probably be "ABCDSchool-Science-Class 6.json"
$jsonOutputFileName = "$inputFileName-$SubjectName-$ClassName.json"
$jsonOutputFileFullPath = [System.IO.Path]::GetFullPath($jsonOutputFileName)
Write-Host "Changing sheet '$SubjectName' to '$jsonOutputFileFullPath'"
$null = $outcomes |
ConvertTo-Json |
Out-File -Encoding ASCII -FilePath $jsonOutputFileFullPath
$null = $excelApplication.Workbooks.Shut()
$null = [System.Runtime.InteropServices.Marshal]::ReleaseComObject(
$excelApplication
)
#endregion Create JSON file and shut Excel fileThe output JSON file will seem like beneath:
[
{
"Room Type": "Standard",
"RowNumber": 46,
"Student Name": "Alex",
"Student#": "RL45",
"LAN Port #": "LAN Port 7-8",
"Logical Seat Location": "SL 11",
"Actual Seat Location": "Seat43",
"Monitor Cable Port": "C-D"
},
{
"Room Type": "Standard",
"RowNumber": 47,
"Student Name": "Alex",
"Student#": "RL45",
"LAN Port #": "LAN Port 5-6",
"Logical Seat Location": "SL 11",
"Actual Seat Location": "Seat43",
"Monitor Cable Port": "A-B"
},
{
"Room Type": "Standard",
"RowNumber": 48,
"Student Name": "John",
"Student#": "RL47",
"LAN Port #": "LAN Port 3-4",
"Logical Seat Location": "SL 11",
"Actual Seat Location": "Seat43",
"Monitor Cable Port": "C-D"
},
{
"Room Type": "Standard",
"RowNumber": 49,
"Student Name": "John",
"Student#": "RL47",
"LAN Port #": "LAN Port 1-2",
"Logical Seat Location": "SL 11",
"Actual Seat Location": "Seat43",
"Monitor Cable Port": "A-B"
},
{
"Room Type": "Standard",
"RowNumber": 50,
"Student Name": "Victor",
"Student#": "RL35",
"LAN Port #": "LAN Port 7-8",
"Logical Seat Location": "SL 10",
"Actual Seat Location": "Seat33",
"Monitor Cable Port": "C-D"
},
{
"Room Type": "Standard",
"RowNumber": 51,
"Student Name": "Victor",
"Student#": "RL35",
"LAN Port #": "LAN Port 5-6",
"Logical Seat Location": "SL 10",
"Actual Seat Location": "Seat33",
"Monitor Cable Port": "A-B"
},
{
"Room Type": "Standard",
"RowNumber": 52,
"Student Name": "Honey",
"Student#": "RL42",
"LAN Port #": "LAN Port 3-4",
"Logical Seat Location": "SL 10",
"Actual Seat Location": "Seat33",
"Monitor Cable Port": "C-D"
}
]Be happy to drop your suggestions and inputs on this web page. Until then, Pleased Scripting!!!
Learn extra about PowerShell-Neighborhood
