wee_import - dateTime not converting/importing properly

[William]

Hi,

I'm trying to import historical data into my new weewx installation. Following the documentation and the sample import config, I can't seem to get the date and time to parse correctly. I'm using weewx version 4.10.2 and wee_import version 0.7, as both were pulled down through the apt package repository today.

In my archive data, my date and time are stored in a RecDate field and uses the format YYYYmmddHHMM. Example: 201105060957 would be more appropriately readable as 2011-05-06 09:57.

In the import configuration file I have raw_datetime_format = %Y%m%d%H%M, and in the FieldMap stanza, dateTime = RecDate, unix_epoch.

When I do a dry run on a single record, it shows a wild date (year 8342!) with the message:

Unique records processed: 1; Last timestamp: 8342-10-07 16:55:57 EDT (201105060957)

When I run it on a different data set it sees 202303110321 as timestamp: 8380-09-24 00:25:21 EDT

Am I missing something in the config? I'm fairly certain the Python strptime format is correct, and have tested it in a Python script: 

dateTime = datetime.strptime('201105060957', '%Y%m%d%H%M')

returns

datetime.datetime(2011, 5, 6, 9, 57)

Any feedback would be greatly appreciated. Thank you!

[Rainer Lang]

try

raw_datetime_format = %Y%m%d%H%M (that you already have)

and in the field map just

datetime = RecDate

assuming RecDate is the header name of the date/time - drop the unix_epoch addition

if it still doesn't work, please provide the header line and the complete first 2 lines of your import file

--
You received this message because you are subscribed to the Google Groups "weewx-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to weewx-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/42a18d84-9140-42c1-9172-c417f42659cfn%40googlegroups.com.

[gjr80]

As you have confirmed python will quite happily parse a numeric date-time given a suitable format string. In this case the problem is within wee_import itself; wee_import assumes that a numeric date-time field is a unix epoch timestamp and uses the raw date-time data as is (WeeWX records date-time data as unix epoch timestamps). Ultimately when WeeWX tries to interpret this raw data as a unix epoch timestamp the result is a nonsense date-time well into the future.

This will need a change to wee_import, let me think about how to best do this.

Gary

[William]

Thanks Ranier to you and Gary both for the replies. I was fairly sure I tried dropping the unix_epoch unit last night and got the same result, but just tried it again to confirm. My data originated from the old AmbientWeather Virtual Weather Station software that conveniently dumped the data to an easily-readable CSV that I could ingest into a database, and the CSV files I'm using for wee_import are simply a dump from this SQL database back into CSV format. My hopes are to eliminate this older, no-longer-supported software and Windows altogether (and my scripts to get this data into a database), and go with a Linux-supported software that is continued to be routinely updated, like weewx.

Here's the header and two first rows of data for 2011-05-06 at 09:57 and 09:58.

"RecDate","WindDir","WindSpeed","WindGust","IndoorHumidity","OutdoorHumidity","IndoorTemperature","OutdoorTemperature","BarometricPressure","TotalRain","CH1Temperature","CH1Humidity","CH2Temperature","CH2Humidity","CH3Temperature","CH3Humidity","Evapotranspiration","UVindex","SolarRadiation","WindChill","IndoorHeatIndex","OutdoorHeatIndex","DewPoint","SeaLevelPressure","PressureAltitude","CloudBase","AirDensity","VirtualTemperature","VaporPressure","WindDirRate","WindSpeedRate","WindGustRate","IndoorHumidityRate","OutdoorHumidityRate","IndoorTemperatureRate","OutdoorTemperatureRate","BarometricPressureRate","TotalRainRate","CH1TemperatureRate","CH1HumidityRate","CH2TemperatureRate","CH2HumidityRate","CH3TemperatureRate","CH3HumidityRate","EvapotranspirationRate","UVindexRate","SolarRadiationRate","WindChillRate","IndoorHeatIndexRate","OutdoorHeatIndexRate","DewPointRate","SeaLevelPressureRate","RainDaily","RainHourly","Last24HrRain","RainRate","WindRunDaily","DegDaysHeatingDaily","DegDaysCoolingDaily","MoonPhase","MonthlyRain","DegDaysHeatingMonthly","DegDaysCoolingMonthly","WindRunMonthly","DegDaysHeatingYearly","DegDaysCoolingYearly","WindRunYearly"
"201105060957","0.000","0.000","0.000","38.000","73.000","74.500","49.600","30.009","0.010","0.000","0.000","0.000","0.000","0.000","0.000","0.000","0.000","5.000","49.600","74.270","52.921","41.313","30.009","-82.216","2071.683","-708.443","51.277","0.261","0.000","0.000","0.000","0.000","0.000","0.000","0.100","0.000","0.000","0.000","0.000","0.000","0.000","0.000","0.000","0.000","0.000","0.000","0.100","0.000","0.100","0.096","0.000","0.010","0.000","0.000","0.000","0.000","0.011","0.000","3.094","0.010","0.011","0.000","0.000","0.011","0.000","0.000"
"201105060958","0.000","0.000","0.000","38.000","73.000","74.500","49.600","30.012","0.010","0.000","0.000","0.000","0.000","0.000","0.000","0.000","0.000","5.000","49.600","74.270","52.921","41.313","30.012","-84.984","2071.683","-711.878","51.277","0.261","0.000","0.000","0.000","0.000","0.000","0.000","0.098","0.003","0.000","0.000","0.000","0.000","0.000","0.000","0.000","0.000","0.000","0.000","0.098","0.000","0.098","0.094","0.003","0.010","0.000","0.000","0.000","0.000","0.021","0.000","3.095","0.010","0.021","0.000","0.000","0.021","0.000","0.000"

MySQL (technically MariaDB) wraps all the fields in quotes, and tried wee_import both with and without them just to see if that made a difference. Unfortunately it did not.

Here's the full output from a wee_import dry run:

Using WeeWX configuration file /etc/weewx/weewx.conf
Starting wee_import...
A CSV import from source file '/home/william/wximport/2011_sample.csv' has been requested.
Using database binding 'wx_binding', which is bound to database 'weewx'
Destination table 'archive' unit system is '0x01' (US).
Missing derived observations will be calculated.
This is a dry run, imported data will not be saved to archive.
Starting dry run import ...
2 records identified for import.
Unique records processed: 2; Last timestamp: 8342-10-07 16:55:58 EDT (201105060958)
Finished dry run import
2 records were processed and 2 unique records would have been imported.

Thanks again!

[gjr80]

Fixed at commit 036b9b1 to appear in WeeWX v5.0.0 when released (or v4.10.3 should such a release occur).

In the interim, if you are using WeeWX v4.9.0 or later you can use the updated wee_import files as follows:

1. Move aside the existing weeimport.py as follows (depending on your install you may need to prefix the following commands with sudo):

$ mv /home/weewx/bin/weeimport/weeimport.py /home/weewx/bin/weeimport/weeimport_orig.py

for setup.py installs or

$ mv /usr/share/weewx/weeimport/weeimport.py /usr/share/weewx/weeimport/weeimport_orig.py

for package installs

2. Download the updated weeimport.py (depending on your install you may need to prefix the following commands with sudo):

$ wget -P /home/weewx/bin/weeimport/ https://raw.githubusercontent.com/weewx/weewx/issue_856_wee_import_dateTime/bin/weeimport/weeimport.py

for setup.py installs or 

$ wget -P /usr/share/weewx/wee import/ https://raw.githubusercontent.com/weewx/weewx/issue_856_wee_import_dateTime/bin/weeimport/weeimport.py

for package installs

3. Perform your import, wee_import should now allow importing numeric only non-unix epoch timestamps using a date-time format string such as %Y%m%d%H%M. Users are strongly advised to use the --dry-run command line option when first running wee_import to confirm that date-time data is correctly parsed. The WeeWX log (by default weewx.log) should be checked after the import to ensure data was correctly imported.

Note also that the delimiter setting in the csv import config file must be enclosed in quotes, eg delimiter = ','. The existing example csv import config file and the wee_import documentation does not mention this requirement.

Documentation will be updated in due course for the v5.0.0 release.

Gary

[William]

Gary, this worked perfectly! Thank you! Now to import 6.9 million rows of data :)