Dayfile upload to MySQL

[PaulMy]

Doing an update to my Top10 page http://www.komokaweather.com/mysql/top10.php Thanks Mark for your continued work on that and now using the latest v.1.3.

Then found that I had some bad data in my Cumulus\\data\dayfile.txt so edited that with Cumulus. After midnight I have Cumulus Toolbox upload the dayfile.txt to my webserver (overwrite), then Toolbox external command to run ImportCumulusFile.php to update MySQL database Dayfile table. I presume this update appends the latest dayfile.txt addition to the MySQL and not the whole file.

I also have some other incorrect or incomplete data in the DB and rather than editing each of those can I update the complete Dayfile DB table by a new ImportCumulusFile upload of my current dayfile.txt, and if so what would be the command line? Or is there another way to have MySQL updated with my edited/corrected dayfile.txt?

Paul

[mcrossley]

Paul, I don't have time to post a replay at the moment, but if you don't find a solution quickly, reply this this thread and I'll pick it up when I have the time - the forum will not let me mark threads as 'unread' so once I read them they get lost if I forget about them.

[PaulMy]

There was a suggestion posted a few days ago and how to import the full file but seems to be deleted.

I have done some edit in my monthly log file to remove the incorrect solar data and renamed my dayfile.txt and had Cumulus create a new dayfile.txt. This dayfile.txt now looks much better.

What changes would I need to make to the latest ImportCumulusFile.php to import the full new dayfile to MySQL database. I already have a Dayfile table with all the dayfile data as of yesterday. I have a few scripts that use this Dayfile table so would like to keep the same name. Should I backup the existing Dayfile table and then use the ImportCumulusFile.php (edited to import the whole file) to overwrite or purge the existing Dayfile and have the import do a new table? Likely much easier than what I am making it, but I do get nervous getting into some of what I am not very comfortable with.

Thanks,
Paul

[jlmr731]

If your new dayfile is the same format as the old one, as in same number of columns and same position's for the data, i would assume it would import just fine.
Sounds as if you just changed data to be correct, if so just make backups and give her a go and see what happens, this way you will have backups to save ya

[PaulMy]

Sorry to drag this on, but just realized my original MySQL Dayfile table has 48 fields with field 47 being HWindGBearSym and field 48 DomWindDirSym and both have data including up to yesterday.
However my dayfile.txt has only 46 fields and not those last 2 Wind Symbol fields. This is in both the original and the new created dayfile.txt in my Cumulus data folder. I have run Create Missing in Cumulus but that didn't add anything. How are these created in the MySQL table?

I have not yet imported the new dayfile.txt yet for fear of loosing something.

Paul

[jlmr731]

I dont use that script but I believe some of the wind data is calculated within the import script to convert degree's to n,s,e,w type direction.
this is in the change log of his script
2.0 - 13 March 2013,
Added 'extra' columns to record wind directions as compass points where they are only provided as bearings:

Dayfile: Added the following columns:- HWindGBearSym, DomWindDirSym
Monthly: Added the following columns:- WindbearingSym, CurrWindBearingSym
I know it is no help to you now but you also said in your first post about bad data being in the database and i dont think it will go back and change any of that data to the dayfile table, It uses the date as a primary field so those old records will not be overwritten.
My guess is you will have to manually edit the database of the incorrect or incomplete data or rename your dayfile table to save as backup and use the script to create the data as a new dayfile table with all the correct info.

[PaulMy]

Thank you, now that I have re-read the ImportCumulusFile.php comments I do recall that extra fields from a few years ago

I have run the v2.8 of the script to import to a new table Dayfile2 and I think it was successful. I can't access my host to see the actual table so will have to recheck tomorrow (I mean later today) and if all is good I can then remove the old Dayfile table and re-Import a new Dayfile. This is the result from the Import:
06/01/17 - 22:44:04
Importing to table: Dayfile2 ...
Processing dayfile: dayfile.txt
Table does not exist, creating it...
Inserting data...
Inserted 2998 rows
done.
End of script 06/01/17 - 22:44:06 - execution time: 2.851s

Actually easier than I am making it

Thanks again,
Paul

[mcrossley]

Hi Paul, yes - this should be easy!

Either, import the data to a new table as you have done, check it looks OK. If ok drop your original table and rename the new one to the original table name. Then all your scripts should work with the new data.

Or, do a test import to a new table as you have done. Once happy the data is OK drop the new and old tables and import again to the original table name.

[mcrossley]

And, yep those last two columns are calculated from the source data.

[PaulMy]

Looking at Dayfile2 table on server and the Import worked great, and learned a few things along the way. I was able to rename the MySQL Dayfile and Dayfile2 tables to use for my Top10 script to see the results.

HOWEVER... the saga goes on....
I forgot that when I had Cumulus create a new dayfile.txt that the value and times could be different when getting it from the monthly log files. So now all my times are at the log interval :10, ten minute interval which isn't too bad but many of the actual high/low are different in comparing to the original dayfile.txt as captured by Cumulus on an ongoing basis.

The new dayfile.txt created by Cumulus using the monthly log files has a lot of the data that were not in the original dayfile,txt as that data was not captured at the time. This looks much better. So now what should I do?
a) go back to the original dayfile.txt which has all correct data and times but several fields of no data from before all the existing fields were provided by Cumulus
b) use the newly created dayfile.txt which has data for all the fields from October 2008 but at the :10 interval so not precise
c) add those missing fields' data from the new dayfile.txt to the original dayfile.txt using Excel or another program so all data is available and correct

I would prefer doing c) but would Excel distort the data? or what program would be best to do that?

I took precaution so that I have the original dayfile still being active by Cumulus and had that updated to MySQL overnight, and have backups, just in case!!!

Paul

[jlmr731]

The dayfile.txt is only updated once a day So are you talking about the monthly file's (Jan17log.txt these name type) at 10 min intervals?

to edit them i think it has been suggested notepad plus or something like that, it should be in the FAQ or wiki.

Glad you took the plunge to recreate the dayfile like that

[PaulMy]

Thanks for all the comments and suggestions. I have finally mastered this one

I edited the original dayfile.txt by copying in the additional fields that were created by the Cumulus Create Missing. I used Excel but made sure it was saved as csv and saved as txt type. Cumulus recognized the new dayfile.txt so then FTP to webserver and imported as Dayfile table and my results are much better than what I had before:
http://www.komokaweather.com/mysql/top10.php
http://www.komokaweather.ca/wxTop_10.php

and for my other scripts using dayfile.txt
http://www.komokaweather.com/mysql/monthly-temp.php
http://www.komokaweather.com/mysql/monthly-data.php
http://www.komokaweather.ca/wxmaxmin.php
http://www.komokaweather.ca/wxthisdate.php
and I can remove the "Blank cells due to such data not being calculated at the time."
and even my http://www.komokaweather.com/weather/be ... maryCU.php looks better with all fields now populated.

Again thanks and credit due to those providing scripts and the basics to make all of this work.

Enjoying,
Paul