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

Upload monthly log files to MySQL

Other discussion about creating web sites for Cumulus that doesn't have a specific subforum

Moderator: daj

User avatar
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

Post by PaulMy »

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 :oops:

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

Image
Mapantz
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

Post by Mapantz »

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?
Image
User avatar
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

Post by PaulMy »

Thank you for your sample. Unfortunately that didn't work:

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:
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
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

Image
User avatar
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

Post by PaulMy »

I can't seem to get it to work, and get

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:
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
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

Image
User avatar
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

Post by PaulMy »

I solved the issue :o :clap: .
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

Image
User avatar
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

Post by mcrossley »

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...

Code: Select all

ALTER TABLE `Monthly` ADD `LogDateTime` DATE NOT NULL FIRST;
Populate it...

Code: Select all

UPDATE `Monthly` SET LogDateTime = TIMESTAMP(LogDate, LogTime);
Create a unique index on the new column...

Code: Select all

ALTER TABLE `Monthly` ADD PRIMARY KEY(LogDateTime);
You can then drop the existing separate date and time columns...

Code: Select all

ALTER TABLE `Monthly` DROP LogDate, LogTime;
User avatar
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

Post by PaulMy »

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
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

Image
User avatar
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

Post by mcrossley »

Ah, the adding of the new table column is wrong, it should be...

Code: Select all

ALTER TABLE `Monthly` ADD `LogDateTime` DATETIME NOT NULL FIRST;
The rest of it depends on your existing table structure, you may have to do...

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);
You can PM me with details if you have issues.
User avatar
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

Post by PaulMy »

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 8-)

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 :roll: as not enough room in these old grey cells anymore :cry:

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

Image
weatherist34
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

Post by weatherist34 »

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 8-)

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 :roll: as not enough room in these old grey cells anymore :cry:

Enjoying,
Paul
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.
jlmr731
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

Post by jlmr731 »

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.
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.
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.
User avatar
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

Post by PaulMy »

And it would be nice to see what Paul has for any differences.
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.

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

Image
weatherist34
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

Post by weatherist34 »

jlmr731 wrote:
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.
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.
Thanks for all your help.
All 3 tables are within the dump file
good morning

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
User avatar
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

Post by mcrossley »

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.
weatherist34
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

Post by weatherist34 »

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.
so are you saying monthly and daily append identical data in each ?

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 ..
Post Reply