Author Topic: extracting old WD data from logfiles for import in to mysql  (Read 4422 times)

0 Members and 1 Guest are viewing this topic.

Offline weatheroz

  • Contributor
  • ***
  • Posts: 139
    • http://www.loganvillageweather.com
extracting old WD data from logfiles for import in to mysql
« 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.

Offline weatheroz

  • Contributor
  • ***
  • Posts: 139
    • http://www.loganvillageweather.com
Re: extracting old WD data from logfiles for import in to mysql
« Reply #1 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.

Offline xykotik

  • DonkeyTailWX DW6891
  • Forecaster
  • *****
  • Posts: 694
  • I'll deal with it tomorrow
    • DonkeyTail Weather
Re: extracting old WD data from logfiles for import in to mysql
« Reply #2 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
phpMyAdmin
MySQL
PostGreSQL

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
Bulk Insert
SQL Queries on Text Files
Using SSIS


Facit solem suum oriri super bonos et malos et pluit super iustos et iniustos.

Springtime in Seattle...  March comes in like a lion and out like a wet lion.

Offline Weather Display

  • Forecaster
  • *****
  • Posts: 2611
    • West Coast Road Weather Data
Re: extracting old WD data from logfiles for import in to mysql
« Reply #3 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

Offline weatheroz

  • Contributor
  • ***
  • Posts: 139
    • http://www.loganvillageweather.com
Re: extracting old WD data from logfiles for import in to mysql
« Reply #4 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


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
Bulk Insert
SQL Queries on Text Files
Using SSIS

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


Offline weatheroz

  • Contributor
  • ***
  • Posts: 139
    • http://www.loganvillageweather.com
Re: extracting old WD data from logfiles for import in to mysql
« Reply #5 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. :)


Offline Weather Display

  • Forecaster
  • *****
  • Posts: 2611
    • West Coast Road Weather Data
Re: extracting old WD data from logfiles for import in to mysql
« Reply #6 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
« Last Edit: October 17, 2011, 01:28:56 PM by Weather Display »

Offline weatheroz

  • Contributor
  • ***
  • Posts: 139
    • http://www.loganvillageweather.com
Re: extracting old WD data from logfiles for import in to mysql
« Reply #7 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. :)

Offline Weather Display

  • Forecaster
  • *****
  • Posts: 2611
    • West Coast Road Weather Data
Re: extracting old WD data from logfiles for import in to mysql
« Reply #8 on: October 17, 2011, 07:10:23 PM »
there is alot of improvements to the latest wdmysql.exe

Offline weatheroz

  • Contributor
  • ***
  • Posts: 139
    • http://www.loganvillageweather.com
Re: extracting old WD data from logfiles for import in to mysql
« Reply #9 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. :(

Offline Weather Display

  • Forecaster
  • *****
  • Posts: 2611
    • West Coast Road Weather Data
Re: extracting old WD data from logfiles for import in to mysql
« Reply #10 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?

Offline weatheroz

  • Contributor
  • ***
  • Posts: 139
    • http://www.loganvillageweather.com
Re: extracting old WD data from logfiles for import in to mysql
« Reply #11 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.
« Last Edit: October 18, 2011, 12:26:05 AM by weatheroz »

Offline Weather Display

  • Forecaster
  • *****
  • Posts: 2611
    • West Coast Road Weather Data
Re: extracting old WD data from logfiles for import in to mysql
« Reply #12 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

Offline weatheroz

  • Contributor
  • ***
  • Posts: 139
    • http://www.loganvillageweather.com
Re: extracting old WD data from logfiles for import in to mysql
« Reply #13 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. ;)

Offline Weather Display

  • Forecaster
  • *****
  • Posts: 2611
    • West Coast Road Weather Data
Re: extracting old WD data from logfiles for import in to mysql
« Reply #14 on: October 24, 2011, 06:51:46 PM »
actually it should be working already with the latest wdmysql version update

Offline weatheroz

  • Contributor
  • ***
  • Posts: 139
    • http://www.loganvillageweather.com
Re: extracting old WD data from logfiles for import in to mysql
« Reply #15 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.

Offline weatheroz

  • Contributor
  • ***
  • Posts: 139
    • http://www.loganvillageweather.com
Re: extracting old WD data from logfiles for import in to mysql
« Reply #16 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. ;)


Offline weatheroz

  • Contributor
  • ***
  • Posts: 139
    • http://www.loganvillageweather.com
Re: extracting old WD data from logfiles for import in to mysql
« Reply #17 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.  :(

 

anything