3.20.0 question

[Mapantz]

Hi Mark

I've updated to the latest version.

I used the "Update database table" in CMX, to add the extra columns.

I also used the creatmissing to make a new dayfile.txt so that all the new values have been added - that was all successful, with no errors.

Question; How do I now update my SQL dayfile table with the missing values? Would I just empty the table and run the import dayfile script?

Nice release btw!

[mcrossley]

I haven't updated my table yet, but I'll probably just select the date and extra three values from the dayfile using a grid editor or Excel, then wrap the data in the boilerplate UPDATE dayfile SET ... Etc to create a SQL file

[Mapantz]

I thought I'd try the import script.. not sure why, but it is extremely slow! It's taking like 5 or 6 seconds to add one row.

I've also just realised, the import script doesn't have the chillhours or 24 hour rain data. This could be a long day.

Edit: Script stops after 3 months of data

Tried using CMD: ExportMySql.exe dayfile, that isn't updated to add the new data.

Edit: I've given up with that idea. It's painfully slow at adding rows in. I'm not sure why?

I've used the import script dozens of times to import lots of data in to the monthly table, it's normally very fast. This is taking 10 minutes to add 3 months.
As the scripts need updating, i've just uploaded the table back to how it was.

[mcrossley]

I imported the dayfile into Excel, skiped all the columns except LogDate, ChillHours, HighRain24h, and THighRain24h

Moved the LogDate column to the end - leaving a gap, added extra columns for the boilerplate, reformatted the logdate to yyyy/mm/dd
and ended up with this...

Capture.jpg

Saved that as a TSV file, opened the TSV in a text editor and removed all the tabs and you end up with this...

Code: Select all

UPDATE Dayfile SET ChillHours=24,HighRain24h=0.3,THighRain24h='14:40' WHERE LogDate='2010/01/13';
UPDATE Dayfile SET ChillHours=48,HighRain24h=0.6,THighRain24h='13:20' WHERE LogDate='2010/01/14';

Saved as a .sql file, then used phpMyAdmin to Import that SQL file to the table.

[Mapantz]

I don't have excel or any apps like that.

I don't want to buy office 365.

[mcrossley]

There are plenty of free alternatives, I installed OpenOffice and my wife's laptop, does virtually everything Excel does.

[Mapantz]

I think I'll wait until ExportMySql.exe is updated, too much hassle for me.

[Mapantz]

I added in the missing fields to ImportCumulusFile.php
after TMaxHumidex
and before HWindGBearSym

Code: Select all

            array('ChillHours',       'decimal(5,1) NOT NULL'),                        // 54
            array('HighRain24h',    "decimal(6,$rainDec) NOT NULL"),            // 55
            array('THighRain24h',   'varchar(5)'),                                          // 56

Seems to work as it should

[water01]

You beat me to it Mapantz. I like ImportCumulusFile so I updated it as well, although I added my fields after, found my error then fixed it. I also added the $windBearField to the Wind Bearing symbol fields and they are now updating correctly with the compass points.

I must admit it is going slow, need to investigate that!!

PHP code attached if anybody wants it.

[sfws]

I think I'll wait until ExportMySql.exe is updated, too much hassle for me.

The 3.20.0 download zip includes an updated ExportToMySQL.exe, and the release announcement says that includes the new columns. Presumably, any column that already has value is ignored during any run?

Writing a PHP script that reads dayfile.txt and creates SQL exactly as in Mark's update example, but within a loop would not be hard.

But Libre Office is free, available for all operating systems, and it's spreadsheet functionality would do as Mark says, plus it is easy to use, probably easier than Excel. It even has database functionality, although that might be harder to learn.

[Mapantz]

I think I'll wait until ExportMySql.exe is updated, too much hassle for me.

The 3.20.0 download zip includes an updated ExportToMySQL.exe, and the release announcement says that includes the new columns.

I tried it, it didn't populate the last 3 columns.

[Mapantz]

I must admit it is going slow, need to investigate that!!

Yep - I'm still going. Mine automatically stops after 3 months of entries, so I have to keep hitting refresh after it stops. I'm not even half way through my data yet.

[The PIT]

Looks like I will need to hold off this release until the speed humps get fixed.
I guess you have to back your mysql database just in case lunch occurs.

Mapantz I wonder if it's the size of the database that's killing things?

I used to use toad to look at the database but as the database grew Toad would burp at the size of the file and grind to a halt. Pity as it was a nice tool to look at your database data.

[The PIT]

Can't you use mysql workbench to add the columns?

[mcrossley]

Using my script the dayfile updated - all days since Jan 2010 - in about 20 seconds, and that is running on a Raspberry Pi.

ExportToMySQL adds missing rows, it does not add missing data to existing rows.