Friday, March 31, 2017

Convert an excelfile to json with PowerShell

In my blog I've been showing you how easily you can import excel files.
Of course, not everyone works with PowerShell.  Some might rather work with a language where excel is less easy or JSON would be a better fit.  And everyone probably has a windows machine running somewhere, right ? Well, with this script, you can convert your "simple" excel files to JSON. Once you have your JSON-file, the sky should be the limit. 


To read the excel file, you will need the PSExcel module.


This is meant for simple excel files.  Single Table of data in each worksheet.

The code

(ps : save this as Convert-ExcelToJson.ps1)

   Converts excel files to json files
   Converts excel files to json files
   The json file is created in the same path as the excel file
   NOTE : This is meant for simple excel files, each sheet, a simple table of data
   .\convert-exceltojson.ps1 -Name "c:\temp\myexcel.xlsx"
   dir *.xlsx | .\convert-exceltojson.ps1

    # Name of the file, can be absolute path or relative

    Import-Module PSExcel
    function getExcelData($path){
        $excel = New-Excel -Path $path
        $worksheets = $excel | Get-Worksheet | %{$_.Name}
        foreach($w in $worksheets){
            $data = Import-XLSX -Path $path -Sheet $w
            $ws = @{
                worksheet = $w;
                data = $data
            write-output (New-Object -TypeName PSObject -Property $ws)
    foreach($n in $Name){
        $path = (resolve-path $n).Path
        Write-Verbose "Converting $path"
        getExcelData -path $path | ConvertTo-Json -Depth 5 | Out-File ($path -replace ".xlsx",".json") -Force

No comments :

Post a Comment