WXforum.net

Weather Software => Weather Display => Topic started by: weatheroz on December 29, 2010, 08:01:07 AM

Title: extracting old WD data from logfiles for import in to mysql
Post by: weatheroz on December 29, 2010, 08:01:07 AM
I've asked this question a few times on the weatherwatch forum, but no one there seems to have an answer for me. :(


What I am looking for is a way to get all the data out of my logfiles to import in to a mysql database. The current wdmysql only gets a small amount of data out of the logfiles, and completely misses out on import items such as UV, Solar, and the like.


I'm hoping that someone here has come across this problem, and rolled their own script to extract data from all the monthly logfiles (the UV, Solar and some other data that isn't being returned from the main logfile).


Preferably with a selectable timeframe, i.e. every 10 minutes, rather than records for every minute.


I'm currently kicking myself for not storing all my data in to a mysql db from way back on day one. :oops:


Thanks in advance.
Title: Re: extracting old WD data from logfiles for import in to mysql
Post by: weatheroz on October 16, 2011, 07:11:41 AM
Just bumping this topic. ;)

Still wondering if anyone has worked out a way to import all of their old data from their WD logfiles in to mysql ?

as mentioned earlier, the current import old data option in wdmysql does not import quite a lot of the old data, such as vp2 UV and Solar data.
Title: Re: extracting old WD data from logfiles for import in to mysql
Post by: xykotik on October 16, 2011, 12:26:04 PM
I have seen this question several times.  If you can script it yourself, you will be a hero to all those who follow.

Depending on what your hosting company (or maybe it's your own server?) there should be an SQL manager or two installed that you can use to import from a flat-file (comma, tab, pipe delimited) into an SQL table.  Here are a few examples of SQL managers I find in some of my site control panels.  You will have to find the one on your host, or upload your own.

phpPgAdmin (http://phppgadmin.sourceforge.net/doku.php?id=start)
phpMyAdmin (http://www.phpmyadmin.net/home_page/index.php)
MySQL (http://dev.mysql.com/doc/administrator/en/)
PostGreSQL (http://www.postgresql.org/)

I also found some other methods that may be a step in the right direction, but they may require specific modules, like ASP or MSSQLServer, and at least some desire to get up to your armpits in code.  I'm not a coder, these are just seeds for you.  That's about all I can offer for a question that was cold for so long.

Import Text Files (http://www.nigelrivett.net/ImportTextFiles.html)
Bulk Insert (http://www.sqlteam.com/article/using-bulk-insert-to-load-a-text-file)
SQL Queries on Text Files (http://synametrics.com/SynametricsWebApp/WPTextFiles.jsp)
Using SSIS (http://beyondrelational.com/blogs/ashish/archive/2010/04/25/loading-data-from-text-files-to-sql-server-tables-csv-files-tab-separated-files-or-fixed-column-length-files.aspx)
Title: Re: extracting old WD data from logfiles for import in to mysql
Post by: Weather Display on October 16, 2011, 02:10:57 PM
its possible I could get to also include solar and UV data
just was not a high priority on the lists of things to do
Title: Re: extracting old WD data from logfiles for import in to mysql
Post by: weatheroz on October 16, 2011, 06:55:55 PM
I have seen this question several times.  If you can script it yourself, you will be a hero to all those who follow.

Depending on what your hosting company (or maybe it's your own server?) there should be an SQL manager or two installed that you can use to import from a flat-file (comma, tab, pipe delimited) into an SQL table.  Here are a few examples of SQL managers I find in some of my site control panels.  You will have to find the one on your host, or upload your own.

phpMyAdmin (http://www.phpmyadmin.net/home_page/index.php)


I also found some other methods that may be a step in the right direction, but they may require specific modules, like ASP or MSSQLServer, and at least some desire to get up to your armpits in code.  I'm not a coder, these are just seeds for you.  That's about all I can offer for a question that was cold for so long.

Import Text Files (http://www.nigelrivett.net/ImportTextFiles.html)
Bulk Insert (http://www.sqlteam.com/article/using-bulk-insert-to-load-a-text-file)
SQL Queries on Text Files (http://synametrics.com/SynametricsWebApp/WPTextFiles.jsp)
Using SSIS (http://beyondrelational.com/blogs/ashish/archive/2010/04/25/loading-data-from-text-files-to-sql-server-tables-csv-files-tab-separated-files-or-fixed-column-length-files.aspx)

I'll look at some of those links, but I think it is all beyond my capabilities to do such a thing, hence my question. ;)

The biggest problem I can see is that the logfiles are recorded every minute, but for data that's so old, I'd sooner only add it in to the mysql db from every 10 or maybe 20 minute intervals. Otherwise I can see that the mysql db is going to be absolutely huge in size. :(

Title: Re: extracting old WD data from logfiles for import in to mysql
Post by: weatheroz on October 16, 2011, 07:09:38 PM
its possible I could get to also include solar and UV data
just was not a high priority on the lists of things to do

There were some other items the wdmysql wouldn't import, but just can't remember what they were at the moment, but definately things l ike UV and Solar, plus things like the data from extra sensors that some might have.


I really think one of the reasons behind the limited number of people doing mysql db stuff with WD data is because they start to export their data to mysql quite a long time after they set up WD, and then get stuck in the same rut I am in, where importing their old data loses huge amounts of data they wish to display, i.e. UV/Solar.


I am certainly there will be plenty of very happy WD users if you can write a separate export to mysql db tool that allows them to have real data rather than zeros as it currently inserts in to mysql. Better yet if the user can select the fields they want to be exported to mysql. :)

Title: Re: extracting old WD data from logfiles for import in to mysql
Post by: Weather Display on October 17, 2011, 02:37:14 AM
Quote
Better yet if the user can select the fields they want to be exported to mysql.
you can .....via the custom setup....
and you can set what data is added now to the normal setup as well
Title: Re: extracting old WD data from logfiles for import in to mysql
Post by: weatheroz on October 17, 2011, 07:07:42 PM
Quote
Better yet if the user can select the fields they want to be exported to mysql.
you can .....via the custom setup....
and you can set what data is added now to the normal setup as well

I think I haven't explained myself properly..... what I was saying was in respect to getting the old logfile data exported to a mysql db. :)

I was aware that you can do a custom setup to export current data to mysql of the fields that you want, but you can't grab the old data.


Perhaps I might need to download a new WD update.... you do a huge amount of work all the time to WD, and there might be something there to surprise me again. :)
Title: Re: extracting old WD data from logfiles for import in to mysql
Post by: Weather Display on October 17, 2011, 07:10:23 PM
there is alot of improvements to the latest wdmysql.exe
Title: Re: extracting old WD data from logfiles for import in to mysql
Post by: weatheroz on October 17, 2011, 09:36:35 PM
there is alot of improvements to the latest wdmysql.exe

Just got the latest version, and it doesn't do the old solar/uv stuff properly.

I'm getting -100 in to the solar field in mysql db. :(
Title: Re: extracting old WD data from logfiles for import in to mysql
Post by: Weather Display on October 17, 2011, 11:13:55 PM
for live data?
I have not seen any other reports of a problem with that
which version of WD itself are you using?
Title: Re: extracting old WD data from logfiles for import in to mysql
Post by: weatheroz on October 18, 2011, 12:22:14 AM
for live data?
I have not seen any other reports of a problem with that
which version of WD itself are you using?

Live is fine.

archived data is adding it in as -100.

Also, I just discovered that it had stopped doing the live updates from when I tried to import the old data.


Edit: latest build 83 I downloaded today, i.e. the full install.
Title: Re: extracting old WD data from logfiles for import in to mysql
Post by: Weather Display on October 20, 2011, 05:10:05 AM
but I have not as yet made it possible for solar and UV to be added via the load past data

I noted above its possible I could add it...just need to find the spare time
Title: Re: extracting old WD data from logfiles for import in to mysql
Post by: weatheroz on October 20, 2011, 05:56:53 AM
but I have not as yet made it possible for solar and UV to be added via the load past data

I noted above its possible I could add it...just need to find the spare time

No worries, looking forward to when you get a chance to do it. ;)
Title: Re: extracting old WD data from logfiles for import in to mysql
Post by: Weather Display on October 24, 2011, 06:51:46 PM
actually it should be working already with the latest wdmysql version update
Title: Re: extracting old WD data from logfiles for import in to mysql
Post by: weatheroz on October 29, 2011, 01:32:56 AM
actually it should be working already with the latest wdmysql version update

Nope, still not working. :( All the missing data is being replaced with 0 (zeros).

Fields missing from the old data files based on the standard WD mysql fields are :-

average_windspeed, gust_windspeed, daily_rainfall,    monthly_rainfall,    yearly_rainfall,indoor_temperature,    indoor_humidity, extra_temperature_sensor_2,    extra_temperature_sensor_3,    extra_temperature_sensor_4,   extra_temperature_sensor_5,   extra_temperature_sensor_6, actual_solar_reading, max_daily_temperature,    min_daily_temperature,    icon_type,    current_weather_desc,    barometer_trend_last_hour,    max_gust_current_day, max_humidex,    min_humidex,    max_windchill,    min_windchill,    davis_vp_uv,    max_heat_index,    min_heat_index, max_average_windspeed_day

There are other items such as the WMR station batteries and the like which I don't have, but I'm guessing they don't work either.

Not much good importing old data if all you have is "0" as the data stored.

From my perspective I haven't done anything with mysql for my weather station because I can not make it worthwhile, as you have no historical data to go with. Unless of course you set up mysql from day one with WD, and most people don't think about it when they first setup.
Title: Re: extracting old WD data from logfiles for import in to mysql
Post by: weatheroz on November 12, 2011, 06:37:45 PM
*BUMP*

Just wondering if Brian has had some spare to time to look at this one again. ;)

Title: Re: extracting old WD data from logfiles for import in to mysql
Post by: weatheroz on January 16, 2012, 06:29:45 PM
*BUMP*  ;)


Just noticed a problem when catching up after a machine crash..... when WDmysql catches up the old data, it is putting the current date, rather than the actual date of the data in to the db.  :(