Welcome to the Cumulus Support forum.
Latest Cumulus MX V3 release 3.28.6 (build 3283) - 21 March 2024
Cumulus MX V4 beta test release 4.0.0 (build 4018) - 28 March 2024
Legacy Cumulus 1 release v1.9.4 (build 1099) - 28 November 2014 (a patch is available for 1.9.4 build 1099 that extends the date range of drop-down menus to 2030)
Download the Software (Cumulus MX / Cumulus 1 and other related items) from the Wiki
Latest Cumulus MX V3 release 3.28.6 (build 3283) - 21 March 2024
Cumulus MX V4 beta test release 4.0.0 (build 4018) - 28 March 2024
Legacy Cumulus 1 release v1.9.4 (build 1099) - 28 November 2014 (a patch is available for 1.9.4 build 1099 that extends the date range of drop-down menus to 2030)
Download the Software (Cumulus MX / Cumulus 1 and other related items) from the Wiki
Upload monthly log files to MySQL
Moderator: daj
- PaulMy
- Posts: 3777
- Joined: Sun 28 Sep 2008 11:54 pm
- Weather Station: Davis VP2 Plus 24-Hour FARS
- Operating System: Windows8 and Windows10
- Location: Komoka, ON Canada
- Contact:
Upload monthly log files to MySQL
I had successfully uploaded my monthly log files to a 'Monthly' table on my webhost server MySQL database up to September 2013. I now want to append that MySQL table with the monthly log files since that time. But I have forgotten the exact URL to do this and haven't been able to figure it out
The Wiki says: http://www.myserver.com/ImportCumulusFi ... n10log.txt
I presume my url would be like: http://www.komokaweather.com/mysql/Impo ... t13log.txt
The ImportCumulusFile.php has an include ('dbconfig.php.php'); and this dbconfig.php has the database access information and password, etc.
All the files including dbconfig.php and the monthly log files I will upload are in the same folder /www.komokaweather.com/mysql/
I have ImportCumulusFile.php version 2.3
What would I substitute for "?type=dayfile" and for "&key=letmein" ? or should it be different than what I presume?
Thanks,
Paul
The Wiki says: http://www.myserver.com/ImportCumulusFi ... n10log.txt
I presume my url would be like: http://www.komokaweather.com/mysql/Impo ... t13log.txt
The ImportCumulusFile.php has an include ('dbconfig.php.php'); and this dbconfig.php has the database access information and password, etc.
All the files including dbconfig.php and the monthly log files I will upload are in the same folder /www.komokaweather.com/mysql/
I have ImportCumulusFile.php version 2.3
What would I substitute for "?type=dayfile" and for "&key=letmein" ? or should it be different than what I presume?
Thanks,
Paul
Davis Vantage Pro2+
C1 www.komokaweather.com/komokaweather-ca
MX www.komokaweather.com/cumulusmx/index.htm /index.html /index.php
MX www.komokaweather.com/cumulusmxwll/index.htm /index.html /index.php
MX www. komokaweather.com/cumulusmx4/index.htm
C1 www.komokaweather.com/komokaweather-ca
MX www.komokaweather.com/cumulusmx/index.htm /index.html /index.php
MX www.komokaweather.com/cumulusmxwll/index.htm /index.html /index.php
MX www. komokaweather.com/cumulusmx4/index.htm
-
- Posts: 1775
- Joined: Sat 17 Dec 2011 11:55 am
- Weather Station: Davis Vantage Pro2
- Operating System: Windows 11 x64
- Location: Dorset - UK
- Contact:
Re: Upload monthly log files to MySQL
Hi Paul
This is what I would use:
http://www.warehamwx.co.uk/cumulus/Impo ... t16log.txt
I presume you have set $key with a key in the importCumulusFile.php?
This is what I would use:
http://www.warehamwx.co.uk/cumulus/Impo ... t16log.txt
I presume you have set $key with a key in the importCumulusFile.php?
- PaulMy
- Posts: 3777
- Joined: Sun 28 Sep 2008 11:54 pm
- Weather Station: Davis VP2 Plus 24-Hour FARS
- Operating System: Windows8 and Windows10
- Location: Komoka, ON Canada
- Contact:
Re: Upload monthly log files to MySQL
Thank you for your sample. Unfortunately that didn't work:
I think I have too many dbconfig files and may have to double check the one I am using in the mysql folder. Will have to do another attempt tomorrow evening.
Paul
Code: Select all
18/10/16 - 20:52:29
Importing to table: Monthly ...
Processing monthfile: Nov13log.txt
Inserting data... Error: Failed to insert data:
INSERT IGNORE INTO Monthly (LogDateTime,Temp,Humidity,Dewpoint,Windspeed,Windgust,Windbearing,RainRate,TodayRainSoFar,Pressure,Raincounter,InsideTemp,InsideHumidity,LatestWindGust,WindChill,HeatIndex,UVindex,SolarRad,Evapotrans,AnnualEvapTran,ApparentTemp,MaxSolarRad,HrsSunShine,CurrWindBearing,RG11rain,WindbearingSym,CurrWindBearingSym) Values('13-11-01 00:00','15.6','87','14.3','21.4','64.2','200','1.1','0.0','989.44','795.2','25.3','39','23.5','13.6','15.6','0.0','0','0.00','0.00','12.5','0','0.0','238','0.0','SSW','WSW')
Error: Failed to insert data:
INSERT IGNORE INTO Monthly (LogDateTime,Temp,Humidity,Dewpoint,Windspeed,Windgust,Windbearing,RainRate,TodayRainSoFar,Pressure,Raincounter,InsideTemp,InsideHumidity,LatestWindGust,WindChill,HeatIndex,UVindex,SolarRad,Evapotrans,AnnualEvapTran,ApparentTemp,MaxSolarRad,HrsSunShine,CurrWindBearing,RG11rain,WindbearingSym,CurrWindBearingSym) Values('13-11-01 00:10','15.6','87','14.3','17.2','38.6','205','3.3','0.4','989.54','795.6','25.3','39','23.5','13.6','15.6','0.0','0','0.00','0.00','13.3','0','0.0','192','0.0','SSW','SSW')
Error: Failed to insert data:
Paul
Davis Vantage Pro2+
C1 www.komokaweather.com/komokaweather-ca
MX www.komokaweather.com/cumulusmx/index.htm /index.html /index.php
MX www.komokaweather.com/cumulusmxwll/index.htm /index.html /index.php
MX www. komokaweather.com/cumulusmx4/index.htm
C1 www.komokaweather.com/komokaweather-ca
MX www.komokaweather.com/cumulusmx/index.htm /index.html /index.php
MX www.komokaweather.com/cumulusmxwll/index.htm /index.html /index.php
MX www. komokaweather.com/cumulusmx4/index.htm
- PaulMy
- Posts: 3777
- Joined: Sun 28 Sep 2008 11:54 pm
- Weather Station: Davis VP2 Plus 24-Hour FARS
- Operating System: Windows8 and Windows10
- Location: Komoka, ON Canada
- Contact:
Re: Upload monthly log files to MySQL
I can't seem to get it to work, and get
I am using the same ImportCumulusFile.php version 2.3 with which I am successfully using a cron job to upload my realtime.txt to the MySQL "realtime" table once a minute.
The successful URL is http://www.komokaweather.com/weather/Im ... altime.txt.
And for monthly log is http://www.komokaweather.com/mysql/Impo ... t13log.txt which fails.
The ImportCumulusFile.php and the Oct13log.txt are in the same /mysql folder
The "Monthly" table exists in the MySQL database which has the previously uploaded monthly log files data from Oct08 to Sep13 that I imported in 2013, and I am trying to add the monthly files from Oct13 to current.
Could there be an issue with the Monthly table that stops appending to it now?
Paul
Code: Select all
19/10/16 - 17:02:13
Importing to table: Monthly ...
Processing monthfile: Oct13log.txt
Inserting data... Error: Failed to insert data:
INSERT IGNORE INTO Monthly (LogDateTime,Temp,Humidity,Dewpoint,Windspeed,Windgust,Windbearing,RainRate,TodayRainSoFar,Pressure,Raincounter,InsideTemp,InsideHumidity,LatestWindGust,WindChill,HeatIndex,UVindex,SolarRad,Evapotrans,AnnualEvapTran,ApparentTemp,MaxSolarRad,HrsSunShine,CurrWindBearing,RG11rain,WindbearingSym,CurrWindBearingSym) Values('13-10-01 00:00','11.5','87','10.2','0.0','0.0','0','0.0','0.0','1015.65','686.0','26.3','48','0.0','11.5','11.5','0.0','0','0.00','0.00','11.4','0','0.0','0','0.0','N','N')
Error: Failed to insert data:
INSERT IGNORE INTO Monthly (LogDateTime,Temp,Humidity,Dewpoint,Windspeed,Windgust,Windbearing,RainRate,TodayRainSoFar,Pressure,Raincounter,InsideTemp,InsideHumidity,LatestWindGust,WindChill,HeatIndex,UVindex,SolarRad,Evapotrans,AnnualEvapTran,ApparentTemp,MaxSolarRad,HrsSunShine,CurrWindBearing,RG11rain,WindbearingSym,CurrWindBearingSym) Values('13-10-01 00:10','11.4','87','10.1','0.0','0.0','0','0.0','0.0','1015.58','686.0','26.3','48','0.0','11.4','11.4','0.0','0','0.00','0.00','11.3','0','0.0','0','0.0','N','N')
Error: Failed to insert data:
INSERT IGNORE INTO Monthly (LogDateTime,Temp,Humidity,Dewpoint,Windspeed,Windgust,Windbearing,RainRate,TodayRainSoFar,Pressure,Raincounter,InsideTemp,InsideHumidity,LatestWindGust,WindChill,HeatIndex,UVindex,SolarRad,Evapotrans,AnnualEvapTran,ApparentTemp,MaxSolarRad,HrsSunShine,CurrWindBearing,RG11rain,WindbearingSym,CurrWindBearingSym) Values('13-10-01 00:20','11.3','87','10.0','0.0','0.0','0','0.0','0.0','1015.72','686.0','26.3','48','0.0','11.3','11.3','0.0','0','0.00','0.00','11.1','0','0.0','0','0.0','N','N')
Error: Failed to insert data:
The successful URL is http://www.komokaweather.com/weather/Im ... altime.txt.
And for monthly log is http://www.komokaweather.com/mysql/Impo ... t13log.txt which fails.
The ImportCumulusFile.php and the Oct13log.txt are in the same /mysql folder
The "Monthly" table exists in the MySQL database which has the previously uploaded monthly log files data from Oct08 to Sep13 that I imported in 2013, and I am trying to add the monthly files from Oct13 to current.
Could there be an issue with the Monthly table that stops appending to it now?
Paul
Davis Vantage Pro2+
C1 www.komokaweather.com/komokaweather-ca
MX www.komokaweather.com/cumulusmx/index.htm /index.html /index.php
MX www.komokaweather.com/cumulusmxwll/index.htm /index.html /index.php
MX www. komokaweather.com/cumulusmx4/index.htm
C1 www.komokaweather.com/komokaweather-ca
MX www.komokaweather.com/cumulusmx/index.htm /index.html /index.php
MX www.komokaweather.com/cumulusmxwll/index.htm /index.html /index.php
MX www. komokaweather.com/cumulusmx4/index.htm
- PaulMy
- Posts: 3777
- Joined: Sun 28 Sep 2008 11:54 pm
- Weather Station: Davis VP2 Plus 24-Hour FARS
- Operating System: Windows8 and Windows10
- Location: Komoka, ON Canada
- Contact:
Re: Upload monthly log files to MySQL
I solved the issue .
Looks like I used ver 1.4a of the ImportCumulusFile.php in my initial import and one of the changes to ver 2.3 is that the date and time fields were separate in ver 1.4a and with the newer 2.3 version these are combined. I replaced my current ImportCumulusFile.php to ver 1.4a and got all the monthly files added.
So my realtime table has date and time combined as I was using ver 2.3 of the script when I srated importing this a couple months ago, and my Monthly has date and time separate because I started 3 years ago with ver 1.4a. Will have to remember this when I import the next months in the future.
Another lesson learned.
Paul
Looks like I used ver 1.4a of the ImportCumulusFile.php in my initial import and one of the changes to ver 2.3 is that the date and time fields were separate in ver 1.4a and with the newer 2.3 version these are combined. I replaced my current ImportCumulusFile.php to ver 1.4a and got all the monthly files added.
So my realtime table has date and time combined as I was using ver 2.3 of the script when I srated importing this a couple months ago, and my Monthly has date and time separate because I started 3 years ago with ver 1.4a. Will have to remember this when I import the next months in the future.
Another lesson learned.
Paul
Davis Vantage Pro2+
C1 www.komokaweather.com/komokaweather-ca
MX www.komokaweather.com/cumulusmx/index.htm /index.html /index.php
MX www.komokaweather.com/cumulusmxwll/index.htm /index.html /index.php
MX www. komokaweather.com/cumulusmx4/index.htm
C1 www.komokaweather.com/komokaweather-ca
MX www.komokaweather.com/cumulusmx/index.htm /index.html /index.php
MX www.komokaweather.com/cumulusmxwll/index.htm /index.html /index.php
MX www. komokaweather.com/cumulusmx4/index.htm
- mcrossley
- Posts: 12694
- Joined: Thu 07 Jan 2010 9:44 pm
- Weather Station: Davis VP2/WLL
- Operating System: Bullseye Lite rPi
- Location: Wilmslow, Cheshire, UK
- Contact:
Re: Upload monthly log files to MySQL
Paul, you should be able to update your table to use a combined date/time column (I would advise you do - most of the scripts around assume you have a combined date/time).
Make sure you have a backup of the database before starting!
Create the new column...
Populate it...
Create a unique index on the new column...
You can then drop the existing separate date and time columns...
Make sure you have a backup of the database before starting!
Create the new column...
Code: Select all
ALTER TABLE `Monthly` ADD `LogDateTime` DATE NOT NULL FIRST;
Code: Select all
UPDATE `Monthly` SET LogDateTime = TIMESTAMP(LogDate, LogTime);
Code: Select all
ALTER TABLE `Monthly` ADD PRIMARY KEY(LogDateTime);
Code: Select all
ALTER TABLE `Monthly` DROP LogDate, LogTime;
- PaulMy
- Posts: 3777
- Joined: Sun 28 Sep 2008 11:54 pm
- Weather Station: Davis VP2 Plus 24-Hour FARS
- Operating System: Windows8 and Windows10
- Location: Komoka, ON Canada
- Contact:
Re: Upload monthly log files to MySQL
Thanks Mark,
I finally felt brave and tried the change .
I didn't/don't really understand how SQL or MySQL works so have been very cautious and afraid to tinker with it. However after I talked to my webhost GoDaddy I found it quite easy once you get over the fear of loosing or screwing up everything.
1. Backed up successfully.
2. Created a new field LogDateTime.
3. However with the UPDATE `Monthly` SET LogDateTime = TIMESTAMP(LogDate, LogTime); only the dates were added in the new field, not the times.
4. And ALTER TABLE `Monthly` ADD PRIMARY KEY(LogDateTime); gave an error - didn't jot it down but something like duplicate key and possibly caused by not having date/time in the new field.
I have now DROP the incomplete LogDateTime so back as it was originally.
Any suggestion what I could try to get both date and time in LogDateTime?
Paul
I finally felt brave and tried the change .
I didn't/don't really understand how SQL or MySQL works so have been very cautious and afraid to tinker with it. However after I talked to my webhost GoDaddy I found it quite easy once you get over the fear of loosing or screwing up everything.
1. Backed up successfully.
2. Created a new field LogDateTime.
3. However with the UPDATE `Monthly` SET LogDateTime = TIMESTAMP(LogDate, LogTime); only the dates were added in the new field, not the times.
4. And ALTER TABLE `Monthly` ADD PRIMARY KEY(LogDateTime); gave an error - didn't jot it down but something like duplicate key and possibly caused by not having date/time in the new field.
I have now DROP the incomplete LogDateTime so back as it was originally.
Any suggestion what I could try to get both date and time in LogDateTime?
Paul
Davis Vantage Pro2+
C1 www.komokaweather.com/komokaweather-ca
MX www.komokaweather.com/cumulusmx/index.htm /index.html /index.php
MX www.komokaweather.com/cumulusmxwll/index.htm /index.html /index.php
MX www. komokaweather.com/cumulusmx4/index.htm
C1 www.komokaweather.com/komokaweather-ca
MX www.komokaweather.com/cumulusmx/index.htm /index.html /index.php
MX www.komokaweather.com/cumulusmxwll/index.htm /index.html /index.php
MX www. komokaweather.com/cumulusmx4/index.htm
- mcrossley
- Posts: 12694
- Joined: Thu 07 Jan 2010 9:44 pm
- Weather Station: Davis VP2/WLL
- Operating System: Bullseye Lite rPi
- Location: Wilmslow, Cheshire, UK
- Contact:
Re: Upload monthly log files to MySQL
Ah, the adding of the new table column is wrong, it should be...
The rest of it depends on your existing table structure, you may have to do...
You can PM me with details if you have issues.
Code: Select all
ALTER TABLE `Monthly` ADD `LogDateTime` DATETIME NOT NULL FIRST;
Code: Select all
ALTER TABLE `Monthly` DROP PRIMARY KEY;
ALTER TABLE `Monthly` DROP LogDate;
ALTER TABLE `Monthly` DROP LogTime;
ALTER TABLE `Monthly` ADD PRIMARY KEY(LogDateTime);
- PaulMy
- Posts: 3777
- Joined: Sun 28 Sep 2008 11:54 pm
- Weather Station: Davis VP2 Plus 24-Hour FARS
- Operating System: Windows8 and Windows10
- Location: Komoka, ON Canada
- Contact:
Re: Upload monthly log files to MySQL
Thank you again Mark and very much appreciated, that resolved the upload and all 306,613 data lines look fine. I'll add Oct16log in a few days.
Now what to do with all the data in these tables - Monthly, Dayfile, realtime and indoor, in addition to the various tables for Meteotemplate
A while back our daughter/son-in/law dropped of boxes of books including probably 20+ SQL training textbooks (Microsoft SQL 2008, marketing, etc. as my son-in-law works for MS) and we put them out for Goodwill collection. Maybe I should have kept some of them - then maybe not as I wasn't going to try to read and study those 1.1/2" thick manuals as not enough room in these old grey cells anymore
Enjoying,
Paul
Now what to do with all the data in these tables - Monthly, Dayfile, realtime and indoor, in addition to the various tables for Meteotemplate
A while back our daughter/son-in/law dropped of boxes of books including probably 20+ SQL training textbooks (Microsoft SQL 2008, marketing, etc. as my son-in-law works for MS) and we put them out for Goodwill collection. Maybe I should have kept some of them - then maybe not as I wasn't going to try to read and study those 1.1/2" thick manuals as not enough room in these old grey cells anymore
Enjoying,
Paul
Davis Vantage Pro2+
C1 www.komokaweather.com/komokaweather-ca
MX www.komokaweather.com/cumulusmx/index.htm /index.html /index.php
MX www.komokaweather.com/cumulusmxwll/index.htm /index.html /index.php
MX www. komokaweather.com/cumulusmx4/index.htm
C1 www.komokaweather.com/komokaweather-ca
MX www.komokaweather.com/cumulusmx/index.htm /index.html /index.php
MX www.komokaweather.com/cumulusmxwll/index.htm /index.html /index.php
MX www. komokaweather.com/cumulusmx4/index.htm
-
- Posts: 51
- Joined: Wed 13 Apr 2016 11:09 am
- Weather Station: Davis Vantage Pro2 Plus
- Operating System: OSX High Sierra
- Contact:
Re: Upload monthly log files to MySQL
send me a copy of an exported sql preferably a monthly or daily sql (*Monthly, Dayfile, realtime)and we can make use of them ,dont send me a 306,613 lined one tho .just need to see your structure.PaulMy wrote:Thank you again Mark and very much appreciated, that resolved the upload and all 306,613 data lines look fine. I'll add Oct16log in a few days.
Now what to do with all the data in these tables - Monthly, Dayfile, realtime and indoor, in addition to the various tables for Meteotemplate
A while back our daughter/son-in/law dropped of boxes of books including probably 20+ SQL training textbooks (Microsoft SQL 2008, marketing, etc. as my son-in-law works for MS) and we put them out for Goodwill collection. Maybe I should have kept some of them - then maybe not as I wasn't going to try to read and study those 1.1/2" thick manuals as not enough room in these old grey cells anymore
Enjoying,
Paul
-
- Posts: 225
- Joined: Sat 27 Aug 2016 12:11 am
- Weather Station: Davis vantage pro 2
- Operating System: Debian
- Location: Wickliffe, Ohio
- Contact:
Re: Upload monthly log files to MySQL
Not to step on his post But here is sql dump for what Steve programed into CumulusMX to upload to database. And it would be nice to see what Paul has for any differences. I only have a few months of data so far seeing i just got into this. At some point I would like to be able to do more with it.weatherist34 wrote: send me a copy of an exported sql preferably a monthly or daily sql (*Monthly, Dayfile, realtime)and we can make use of them ,dont send me a 306,613 lined one tho .just need to see your structure.
Thanks for all your help.
All 3 tables are within the dump file
You do not have the required permissions to view the files attached to this post.
Jeff
My Site http://wickliffeweather.com/
WeatherUnderground https://www.wunderground.com/personal-w ... KOHYOUNG21
My Site http://wickliffeweather.com/
WeatherUnderground https://www.wunderground.com/personal-w ... KOHYOUNG21
- PaulMy
- Posts: 3777
- Joined: Sun 28 Sep 2008 11:54 pm
- Weather Station: Davis VP2 Plus 24-Hour FARS
- Operating System: Windows8 and Windows10
- Location: Komoka, ON Canada
- Contact:
Re: Upload monthly log files to MySQL
This http://wiki.sandaysoft.com/a/ImportCumulusFile is what I have been using for realtime, dayfile and monthly logs. I will likely need to update to latest version. Still using Cumulus 1.And it would be nice to see what Paul has for any differences.
Paul
Davis Vantage Pro2+
C1 www.komokaweather.com/komokaweather-ca
MX www.komokaweather.com/cumulusmx/index.htm /index.html /index.php
MX www.komokaweather.com/cumulusmxwll/index.htm /index.html /index.php
MX www. komokaweather.com/cumulusmx4/index.htm
C1 www.komokaweather.com/komokaweather-ca
MX www.komokaweather.com/cumulusmx/index.htm /index.html /index.php
MX www.komokaweather.com/cumulusmxwll/index.htm /index.html /index.php
MX www. komokaweather.com/cumulusmx4/index.htm
-
- Posts: 51
- Joined: Wed 13 Apr 2016 11:09 am
- Weather Station: Davis Vantage Pro2 Plus
- Operating System: OSX High Sierra
- Contact:
Re: Upload monthly log files to MySQL
good morningjlmr731 wrote:Not to step on his post But here is sql dump for what Steve programed into CumulusMX to upload to database. And it would be nice to see what Paul has for any differences. I only have a few months of data so far seeing i just got into this. At some point I would like to be able to do more with it.weatherist34 wrote: send me a copy of an exported sql preferably a monthly or daily sql (*Monthly, Dayfile, realtime)and we can make use of them ,dont send me a 306,613 lined one tho .just need to see your structure.
Thanks for all your help.
All 3 tables are within the dump file
thats ideal if everyone has this same structure i can easily use this data structure but I have a gut feeling not everyone is using same structure
so far i have seen 5 different structures but this is far the cleanest but i need to understand if these are continuously appended to, as there was no actual data in your zip
just tell me do any of these use truncate at any period ?
example
monthly
end of month last day truncate
dayfile
end of day 23:59 truncate
realtime
??
brian
- mcrossley
- Posts: 12694
- Joined: Thu 07 Jan 2010 9:44 pm
- Weather Station: Davis VP2/WLL
- Operating System: Bullseye Lite rPi
- Location: Wilmslow, Cheshire, UK
- Contact:
Re: Upload monthly log files to MySQL
The monthly and daily tables continuously grow. The realtime table is variable, each user selects a retention period - I use 10 days for instance.
The tables have had odd columns added over the years, but they have been stable for some time.
The tables have had odd columns added over the years, but they have been stable for some time.
-
- Posts: 51
- Joined: Wed 13 Apr 2016 11:09 am
- Weather Station: Davis Vantage Pro2 Plus
- Operating System: OSX High Sierra
- Contact:
Re: Upload monthly log files to MySQL
so are you saying monthly and daily append identical data in each ?mcrossley wrote:The monthly and daily tables continuously grow. The realtime table is variable, each user selects a retention period - I use 10 days for instance.
The tables have had odd columns added over the years, but they have been stable for some time.
can the user for realtime select 1 day period ?
sorry the ones forwarded on don't contain any data just the structures apart from one went from 2016 to 2008 i think ..