CODEWITHSUNDEEP
powershell
Alex
Alex

Reputation: 65

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

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

Upvotes: 0

Views: 45

Answers (1)

HariHaran
HariHaran

Reputation: 4099

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

My Excel:

enter image description here

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"
    }
]

Upvotes: 1

Related Questions