In Big-data era, the data are stored in flat files which is supporting wider range of technology and systems. Json is serialized data to feeds into multiple development language and automation scripts in devOps system. Export Json to Csv would common question whoever doing data across platform, here are the simple script to claim your times.
Despite data needs to insert into MSSQL in Azure DB so converting to Csv.
Example 01
If the Json has a header and value that required to -Join. Below Servers json data file has columns and values.
|
1 2 3 4 5 6 7 8 9 |
{ “servers”: { “columns”: [“ID”, “V_NAME”, “DATE”, “TIME”], “values”: [ [“SRVIIS01, “server1“, “city123“, “02\/14\/2020“, “15:34:15“], [“SRVSP01“, “server2“, “cit3“, “02\/14\/2020“, “12:58:32“] ] } } |
PowerShell to ConvertFrom-Csv
|
1 2 3 4 5 6 |
$file = “C:\Users\winadmin\Downloads\custom_data\server_data.json” $json = ConvertFrom–JSON (Get–Content $file –Raw) $json.servers.values | ForEach–Object { # If json has header and content then used below -join to ConvertFrom-CSV with -Header values $_ –join ‘,’ | ConvertFrom–Csv –Header $json.servers.columns | Export–Csv –path C:\Users\winadmin\Downloads\custom_data\server_data.csv –NoTypeInformation –Append } |
Example 02
Play with Export-Csv to save the file
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
{ “servers”: [ { “ID”: “SRVIIS01”, “V_NAME”: “”, “DATE”: “02\/14\/2020”, “TIME”: “15:34:15”, }, { “ID”: “SRVSP01”, “V_NAME”: “”, “DATE”: “02\/14\/2020”, “TIME”: “20:34:15”, } ] } |
PowerShell – Export list value directly to csv with out join comment.
|
1 2 3 4 5 6 |
$file = “C:\Users\winadmin\Downloads\custom_data\server_data.json” $json = ConvertFrom–JSON (Get–Content $file –Raw) $json.servers.values | ForEach–Object { # If json has header and content then used below -join to ConvertFrom-CSV with -Header values $_ | Export–Csv <span class=“hljs-parameter”>–UseQuotes</span> AsNeeded –path C:\Users\winadmin\Downloads\custom_data\server_data.csv –NoTypeInformation –Append } |
Export-Csv -UseQuotes AsNeeded work in PowerShell v6 and higher. So download and install latest version from MS git release.
Leave a Reply