How to convert below from a CSV file to JSON using powershell?

Question

How can I convert below from Xlsx to JSON? I want to the JSON file to pick up data like this:

{"POV":["DC01","DC05",...,"Input"],
"Years": ["FY19", "FY19",..., "FY19"],
"Columns":[["Jan", "Feb",...,"Apr"]],
"Rows":[{"Account":["Meals"],"Data":["5000","6000","5000","7000"]}

Really struggling with PowerShell (Example):

$Excel = New-Object -Com Excel.Application
$wb = $Excel.Workbooks.Open("C:\Users\Blam\OneDrive - Huron Consulting Group\Desktop\Projects\Bio_Rad\EPBCS - Copy\EPBCS - Copy\Test.xlsx")

$WorkBook = $objExcel.Workbooks.Open($strPath)
$Worksheet = $Worksheet.sheet.item("Test")
$intRowMax = ($worksheet.UsedRange.Rows).count

HariHaran · Accepted Answer · 2019-08-14 04:57:45Z

1

Import-Csv .\Test.csv | ConvertTo-Json | Add-Content -Path "result.json"

My Excel:

Here's the JSON Result

[
    {
        "Jan":  "6000",
        "Feb":  "2342",
        "Mar":  "5424"
    },
    {
        "Jan":  "6001",
        "Feb":  "2343",
        "Mar":  "5425"
    },
    {
        "Jan":  "6002",
        "Feb":  "2344",
        "Mar":  "5426"
    },
    {
        "Jan":  "6003",
        "Feb":  "2345",
        "Mar":  "5427"
    },
    {
        "Jan":  "6004",
        "Feb":  "2346",
        "Mar":  "5428"
    },
    {
        "Jan":  "6005",
        "Feb":  "2347",
        "Mar":  "5429"
    },
    {
        "Jan":  "6006",
        "Feb":  "2348",
        "Mar":  "5430"
    },
    {
        "Jan":  "6007",
        "Feb":  "2349",
        "Mar":  "5431"
    }
]

answered Aug 14, 2019 at 4:57

HariHaran

4,4592 gold badges26 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to convert below from a CSV file to JSON using powershell?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related