Encoding issue when calling API via powershell

, ,

Recently we need to fetch a big dataset from an API via powershell, then import to Azure Data Explorer (ADX).

Problem

1
2
#Used Measure-Command for measuring performance
Measure-Command {curl 'THE_API_END_POINT' | select -expand Content > data.json}

The data.json file looks perfectly fine, but during import to ADX, it reported error “invalid json format”.

Troubleshooting

  1. Using online validation tool such as https://jsonlint.com/, copy & paste the content from data.json. The json objects are valid.

  2. Using local tool jsonlint, reports error. It shows the data.json file has encoding issue.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    PS C:\Users\lufeng\Desktop> jsonlint .\data.json
    Error: Parse error on line 1:
    ��[ { " _ i d " : {
    ^
    Expecting 'STRING', 'NUMBER', 'NULL', 'TRUE', 'FALSE', '{', '[', got 'undefined'
        at Object.parseError (C:\Users\lufeng\AppData\Roaming\npm\node_modules\jsonlint\lib\jsonlint.js:55:11)  
        at Object.parse (C:\Users\lufeng\AppData\Roaming\npm\node_modules\jsonlint\lib\jsonlint.js:132:22)      
        at parse (C:\Users\lufeng\AppData\Roaming\npm\node_modules\jsonlint\lib\cli.js:82:14)
        at main (C:\Users\lufeng\AppData\Roaming\npm\node_modules\jsonlint\lib\cli.js:135:14)
        at Object.<anonymous> (C:\Users\lufeng\AppData\Roaming\npm\node_modules\jsonlint\lib\cli.js:179:1)      
        at Module._compile (internal/modules/cjs/loader.js:955:30)
        at Object.Module._extensions..js (internal/modules/cjs/loader.js:991:10)
        at Module.load (internal/modules/cjs/loader.js:811:32)
        at Function.Module._load (internal/modules/cjs/loader.js:723:14)
        at Function.Module.runMain (internal/modules/cjs/loader.js:1043:10)

    Solution

    Switch to a different powershell command solved the problem

    1
    2
    Invoke-WebRequest -Uri 'THE_API_END_POINT' -OutFile data.json

EOF

Share Comments