JSON to CSV.

[Phil23]

Hi all,

I've been searching for a way of converting the JSON graph files into CSV for the past week & not coming up with a solution.

Closest thing I can find is the mention of Excels "Get & Transform", but that only seems to be present in Office 365.
Not available in any of my installs here, ranging from 2007 thru to 2016.

Surely I'm not completely trying to reinvent the wheel.
I'd assume someone's done this before.

Any suggestions?

Thanks

Phil.

[ExperiMentor]

If you post a sample of the JSON file, I could take a look

[ExperiMentor]

Have you tried any of the online conversion tools?

Conversion might not be straightforward (or even impossible) if the JSON uses a very complicated structure, but I guess that will not be the case here.

https://jsontoexcel.com/ will let you upload a JSON up to 3 MB and download the result as a CSV file

Or Google "Excel JSON" for dozens of others

[Phil23]

Have you tried any of the online conversion tools?

Or Google "Excel JSON" for dozens of others

Tried both of the above...

Both pasting & uploading Tempdata.json as a test & not finding any way of pulling it apart.

Frustrating from the point of view that I can see & mentally interpret the data just by looking at it.

[mcrossley]

Argh! <Deleted> Forget that, it needs to iterate over each time entry as well.

[mcrossley]

I'm not quite sure how you'd represent the JSON data in a CSV file? It being multi-property with multi-values for each property it doesn't lend itself to being represented in a one dimensional array, hence the use of JSON to begin with.

[Phil23]

I'm not quite sure how you'd represent the JSON data in a CSV file?

For the sake of the initial exercise I'd be happy with it being flattened into a single array.

In the case of Tempdata.json these headings would apply to the columns

Date - Time - Intemp - Date - Time - Dew - Date - Time - Apptemp - Date - Time - Heatindex….. etc.

That would be a sequential read of the file creating 3 columns at a time until it hits the next element.

Obviously the time & date columns would be duplicated for each element, but the stamps appear identical, rounded on the 10 minute mark.

This could be ignored in the raw data by additional pages in Excel doing the Analysis.

Dunno; Just thinking out loud as usual & making the assumption that each file has the same number or records per element.

Cheers.

[HansR]

Sorry to interfere because I actually have no idea what is going on and why, but is using the Json files for the graphs turning into data (in excel) not kind of wagging the dog by the tail? Would it not be easier to read the CMX data files straight into excel and work from there? Data is already csv (or semicolon) separated...

[Phil23]

Yes,

I've looked at that.

Difference is the JSON's will have a 7 day Span for some & a 30 Day span for others in my data.

Other thing with the raw data files is that there is no one data file.

Assume you've guessed what I'm thinking; Creating a 3, 6, 12 or 24 hour moving average window of certain readings.

Both files would of course have 10 minute readings.

Code: Select all

 Volume in drive C has no label.
 Volume Serial Number is 1ED5-907F

 Directory of C:\CumulusMX\data

31/07/2011  11:50 PM           286,596 Jul11log.txt
31/08/2011  11:50 PM           495,358 Aug11log.txt
30/09/2011  11:50 PM           510,536 Sep11log.txt
31/10/2011  10:50 PM           521,039 Oct11log.txt
30/11/2011  10:50 PM           609,190 Nov11log.txt
31/12/2011  10:50 PM           625,666 Dec11log.txt
31/01/2012  10:50 PM           589,483 Jan12log.txt
03/03/2012  08:23 AM           602,108 Feb12log.txt
31/03/2012  10:50 PM           755,001 Mar12log.txt
30/04/2012  11:50 PM           531,070 Apr12log.txt
31/05/2012  11:50 PM           623,157 May12log.txt
30/06/2012  11:50 PM           619,112 Jun12log.txt
31/07/2012  11:50 PM           590,746 Jul12log.txt
03/09/2012  10:03 PM           631,897 Aug12log.txt
30/09/2012  11:50 PM           584,998 Sep12log.txt
31/10/2012  10:50 PM           760,346 Oct12log.txt
01/12/2012  04:54 AM           635,090 Nov12log.txt
31/12/2012  10:50 PM           594,540 Dec12log.txt
31/01/2013  10:50 PM           613,792 Jan13log.txt
28/02/2013  10:50 PM           530,131 Feb13log.txt
31/03/2013  10:50 PM           541,312 Mar13log.txt
30/04/2013  11:50 PM           633,883 Apr13log.txt
31/05/2013  11:50 PM           570,250 May13log.txt
01/07/2013  08:50 AM           523,926 Jun13log.txt
01/08/2013  08:50 AM           537,830 Jul13log.txt
01/09/2013  08:50 AM           558,292 Aug13log.txt
01/10/2013  08:50 AM           507,556 Sep13log.txt
01/11/2013  07:50 AM           627,698 Oct13log.txt
01/12/2013  07:50 AM           514,852 Nov13log.txt
01/01/2014  07:50 AM           593,216 Dec13log.txt
01/02/2014  07:50 AM           587,242 Jan14log.txt
01/03/2014  07:50 AM           487,617 Feb14log.txt
01/04/2014  07:50 AM           582,644 Mar14log.txt
02/05/2014  08:12 AM           594,589 Apr14log.txt
01/06/2014  08:50 AM           588,245 May14log.txt
01/07/2014  08:50 AM           569,981 Jun14log.txt
01/08/2014  08:50 AM           588,262 Jul14log.txt
01/09/2014  08:50 AM           656,586 Aug14log.txt
01/10/2014  08:50 AM           699,762 Sep14log.txt
01/11/2014  07:50 AM           601,002 Oct14log.txt
01/12/2014  07:50 AM           525,291 Nov14log.txt
01/01/2015  07:50 AM           548,679 Dec14log.txt
01/02/2015  07:50 AM           549,533 Jan15log.txt
01/03/2015  07:50 AM           488,783 Feb15log.txt
05/04/2015  10:56 AM           660,000 Mar15log.txt
10/05/2015  06:52 AM           647,216 Apr15log.txt
02/06/2015  08:52 AM           837,527 May15log.txt
01/07/2015  08:50 AM           759,408 Jun15log.txt
01/08/2015  08:50 AM           541,808 Jul15log.txt
01/09/2015  08:50 AM           611,079 Aug15log.txt
01/10/2015  08:50 AM           760,363 Sep15log.txt
01/11/2015  07:50 AM           798,727 Oct15log.txt
01/12/2015  07:50 AM           614,652 Nov15log.txt
01/01/2016  08:05 AM           775,623 Dec15log.txt
01/02/2016  07:50 AM           736,795 Jan16log.txt
04/03/2016  08:02 PM           744,549 Feb16log.txt
01/04/2016  08:34 PM           702,006 Mar16log.txt
03/05/2016  08:58 AM           643,237 Apr16log.txt
01/06/2016  08:50 AM           666,279 May16log.txt
01/07/2016  08:50 AM           725,667 Jun16log.txt
01/08/2016  08:50 AM           773,408 Jul16log.txt
01/09/2016  08:50 AM           779,330 Aug16log.txt
01/10/2016  08:50 AM            29,924 Sep16log.txt
01/11/2016  07:50 AM           545,737 Oct16log.txt
01/12/2016  07:50 AM           541,989 Nov16log.txt
01/01/2017  07:50 AM           572,295 Dec16log.txt
01/02/2017  07:50 AM           567,207 Jan17log.txt
01/03/2017  07:50 AM           516,568 Feb17log.txt
01/04/2017  07:50 AM           567,277 Mar17log.txt
01/05/2017  08:50 AM           547,214 Apr17log.txt
01/06/2017  08:50 AM           558,982 May17log.txt
01/07/2017  08:50 AM           540,532 Jun17log.txt
01/08/2017  08:50 AM           558,353 Jul17log.txt
01/09/2017  08:50 AM           556,837 Aug17log.txt
01/10/2017  07:50 AM           545,397 Sep17log.txt
01/11/2017  07:50 AM           569,740 Oct17log.txt
01/12/2017  07:50 AM           554,559 Nov17log.txt
01/01/2018  07:50 AM           574,860 Dec17log.txt
01/02/2018  07:50 AM           563,336 Jan18log.txt
02/03/2018  06:01 PM           512,557 Feb18log.txt
01/04/2018  08:50 AM           570,527 Mar18log.txt
01/05/2018  08:50 AM           547,749 Apr18log.txt
01/06/2018  08:50 AM           558,180 May18log.txt
01/07/2018  08:50 AM           539,441 Jun18log.txt
01/08/2018  08:50 AM           558,523 Jul18log.txt
01/09/2018  08:50 AM           561,123 Aug18log.txt
01/10/2018  08:50 AM           543,085 Sep18log.txt
01/11/2018  07:50 AM           569,838 Oct18log.txt
01/12/2018  07:50 AM           556,912 Nov18log.txt
01/01/2019  07:50 AM           576,952 Dec18log.txt
01/02/2019  07:50 AM           540,523 Jan19log.txt
01/03/2019  07:50 AM           512,400 Feb19log.txt
01/04/2019  07:50 AM           564,873 Mar19log.txt
01/05/2019  08:50 AM           545,449 Apr19log.txt
01/06/2019  08:50 AM           545,349 May19log.txt
01/07/2019  08:50 AM           537,130 Jun19log.txt
01/08/2019  08:50 AM           556,731 Jul19log.txt
01/09/2019  08:50 AM           557,546 Aug19log.txt
22/09/2019  09:00 AM           520,910 alltimelog.txt
26/09/2019  09:00 AM           268,650 dayfile.txt
26/09/2019  04:00 PM           459,370 Sep19log.txt
01/10/2019  03:33 PM                 0 dir.txt
             104 File(s)     59,275,049 bytes
               0 Dir(s)  264,043,782,144 bytes free

[HansR]

Yes,

I've looked at that.

OK.

Difference is the JSON's will have a 7 day Span for some & a 30 Day span for others in my data.

Other thing with the raw data files is that there is no one data file.

Assume you've guessed what I'm thinking; Creating a 3, 6, 12 or 24 hour moving average window of certain readings.

Both files would of course have 10 minute readings.

Actually, no I hadn't guessed it OK, can't help you here, but I probably would go back to the datafiles and do some condensing calculations while importing these in Excel to reduce the nr of datapoints. I don't assume the datafiles have a 10 minute average (mine have 1 minute datapoints). And if they do, life gets only easier.

[mcrossley]

I think tempdata.json is the exception, the other json data object have "reading1 name", "array of [time,value] values" ,"reading2 name", "array of [time,value] values", etc Where each array of values can be quite large.

For instance, using PowerShell on tempdata might give something possibly usable...

Code: Select all

Invoke-WebRequest -Uri http://localhost:8998/api/graphdata/tempdata.json | `
    ConvertFrom-Json | `
    ForEach-Object {$_.PSObject.Properties} | `
    ForEach-Object `
        -Begin {$line = ""} `
        -Process {$line += "$($_.Name),"
            foreach ($i in $_.Value) {
                $line += "$($i[0]),$($i[1]),"
            }
        } `
        -End {Out-File -encoding ASCII -FilePath C:\Test.csv -InputObject $line.Substring(0, $($line.Length - 1))}

But using it on daily temps is less so...

Code: Select all

Invoke-WebRequest -Uri http://localhost:8998/api/graphdata/dailytemp.json | `
    ConvertFrom-Json | `
    ForEach-Object {$_.PSObject.Properties} | `
    ForEach-Object `
        -Begin {$line = ""} `
        -Process {$line += "$($_.Name),"
            foreach ($i in $_.Value) {
                $line += "$($i[0]),$($i[1]),"
            }
        } `
        -End {Out-File -encoding ASCII -FilePath C:\Test.csv -InputObject $line.Substring(0, $($line.Length - 1))}

[HansR]

I just play along because I never used powershell scripting like this [never too old to learn]

Three remarks:

1) I needed -UseBasicParsing after the URL (IE is not installed on my machine)
2) the path C:\ for the output file was refused, so I used .\
3) every character gets a space added because of Unicode encoding, may have to use: Out-File -encoding ASCII -FilePath etc...

All probably because I use Win10
Nice tricks...

[Phil23]

Trying to run it here on my Win10 to access the data on the Win8 PC, but returning these errors.
Just changed localhost to the appropriate IP.

Firewall? Or what was mentioned above by Hans.

Code: Select all

PS C:\> Invoke-WebRequest -Uri http://192.168.XXX.XX:8998/api/graphdata/tempdata.json | `
>>     ConvertFrom-Json | `
>>     ForEach-Object {$_.PSObject.Properties} | `
>>     ForEach-Object `
>>         -Begin {$line = ""} `
>>         -Process {$line += "$($_.Name),"
>>             foreach ($i in $_.Value) {
>>                 $line += "$($i[0]),$($i[1]),"
>>             }
>>         } `
>>         -End {Out-File -encoding ASCII -FilePath C:\Test.csv -InputObject $line.Substring(0, $($line.Length - 1))}
Invoke-WebRequest : Unable to connect to the remote server
At line:1 char:1
+ Invoke-WebRequest -Uri http://192.168.XXX.XX:8998/api/graphdata/tempda ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-WebRequest], WebExc
   eption
    + FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeWebRequestCommand

PS C:\>

[mcrossley]

Can you get the data of you open that URL in a browser on the same box that you get the error? I assume this is all on your private network.

[HansR]

Try to add the -UseBasicParsing qualifier after the URI specification:

Code: Select all

Invoke-WebRequest -Uri http://192.168.XXX.XX:8998/api/graphdata/tempdata.json -UseBasicParsing | `