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