I am currently uploading my solar inverter data to PVOutput using a Linux machine and a scratch built Perl script. I want to add the meter data from my enrgy supplier. I have an 87,000 row CSV file downloaded from my energy supplier that contains the following (sample) data:
NMI,Meter Serial Number,Stream,Register Number,Register Description,UOM,ReadingDate,Units,Read Type<br> 700555,7006444,B,B1,Solar energy,kWh,23-09-21 23:45,0, Actual 700555,7006444,E,E1,Offpeak, kWh,23-09-21 23:45,0.093, Actual 700555,7006444,B,B1,Solar energy,kWh,23-09-21 23:30,0, Actual 700555,7006444,E,E1,Offpeak, kWh,23-09-21 23:30,0.085, Actual 700555,7006444,B,B1,Solar energy,kWh,23-09-21 23:15,0, Actual 700555,7006444,E,E1,Offpeak, kWh,23-09-21 23:15,0.085, Actual
By creating a virtual column (MyDate) and using the following select:
select MyDate, "Register Description", sum(Units) from MeterData group by MyDate, "Register Description"
I have been able to massage the data in SQLite to give me:
20210920|Offpeak|3.821 20210920|Peak|4.954 20210920|Shoulder|5.695 20210920|Solar energy|2.958 20210921|Offpeak|3.831 20210921|Peak|4.41 20210921|Shoulder|4.428 20210921|Solar energy|4.354 20210922|Offpeak|4.355 20210922|Peak|6.572 20210922|Shoulder|1.961 20210922|Solar energy|7.217
But for upload to PVOutput, the format I'm looking for is:
20210919|Offpeak|3.514|Peak|8.453|Shoulder|7.445|Solar energy|2.792 20210920|Offpeak|3.821|Peak|4.954|Shoulder|5.695|Solar energy|2.958 20210921|Offpeak|3.831|Peak|4.410|Shoulder|4.428|Solar energy|4.354 20210922|Offpeak|4.355|Peak|6.572|Shoulder|1.961|Solar energy|7.217
As you can see, my SQL skills are weak, and any help would be most appreciated.