Data import tribulations

Post Reply
Bigbaywx
Observer
Observer
Posts: 12
Joined: Mon Jun 22, 2020 8:47 pm
Station model: Davis Vantate Pro2
Software: weatherlink data logger

Data import tribulations

Post by Bigbaywx » Sun Jun 28, 2020 12:51 pm

I have some data from weatherlink that I'm trying to import into the template. When testing the import it returns two errors, one with date/time and the other with pressure.

The below example is how the data is being sent from weatherlink. The date time/group is one field and being php would correspond to;
"","","","","","","","","","","","","","","","","","","","","","","","","",""
"Date & Time","Barometer - in Hg","Inside Temp - °F","Inside Hum - %","Inside Dew Point - °F","Inside Heat Index - °F","Inside EMC","Temp - °F","High Temp - °F","Low Temp - °F","Hum - %","Dew Point - °F","Wet Bulb - °F","Wind Speed - mph","Wind Direction","Wind Run - mi","High Wind Speed - mph","High Wind Direction","Wind Chill - °F","Heat Index - °F","THW Index - °F","Rain - in","Rain Rate - in/h","ET - in","Heating Degree Days","Cooling Degree Days"
"1/1/20 00:00","29.65","74","34","44","73","6.8","18","20","18","89","15","17","0","","0.0","0","","18","18","18","0.00","0.00","0.00","0.977","0.000"
"1/1/20 00:30","29.65","76","34","46","75","6.7","16","18","16","90","14","15","0","","0.0","0","","16","16","16","0.00","0.00","0.00","1.021","0.000"
I've tried all the single field date/time examples from the import.php script and none of them satisfy the test script. I then went over to PHP net to look at other examples for date/time groups; https://www.php.net/manual/en/function.date.php

What I came up with over there for the date/time format is: n/j/yH.i

n---Numeric representation of a month, without leading zeros
j---Minutes with leading zeros
y---A two digit representation of a year
H---24-hour format of an hour with leading zeros
i---Minutes with leading zeros
.
I've also tried it with a space between the y and H as well.

The other issue is with the pressure and I know from the wiki that you have to change the database units from metric to imperial if that is what you are using and I am. You also have to stipulate the limits which I also change to inHg and have attached screenshots of both. I've also attached a snipped of the .csv file as well. And the error the test import script gives for this is the pressure is outside of the limits.

If anyone has any ideas on this please let me know. I'm sure it's something simple as it usually is but I've tried all the combinations I can think of at this point.

TIA
Doug
Attachments
Screenshot at 2020-06-28 08-39-44.png
Screenshot at 2020-06-28 08-39-44.png (54.83 KiB) Viewed 109 times
Screenshot at 2020-06-28 08-34-29.png
Screenshot at 2020-06-28 08-34-29.png (67.19 KiB) Viewed 109 times
Screenshot at 2020-06-28 08-32-33.png
Screenshot at 2020-06-28 08-32-33.png (38.79 KiB) Viewed 109 times

Bigbaywx
Observer
Observer
Posts: 12
Joined: Mon Jun 22, 2020 8:47 pm
Station model: Davis Vantate Pro2
Software: weatherlink data logger

Re: Data import tribulations

Post by Bigbaywx » Sun Jun 28, 2020 12:53 pm

We are limited to how many attachments so this is a followup showing the error returned from the import.php test script.
Attachments
Screenshot at 2020-06-28 08-41-37.png
Screenshot at 2020-06-28 08-41-37.png (47.47 KiB) Viewed 108 times

User avatar
jay_hoehn
Language Admin
Language Admin
Posts: 128
Joined: Sun Aug 20, 2017 1:45 pm
Location: Las Cruces, NM USA
Station model: Vantage Pro 2 Plus
Software: WeeWx
Contact:

Re: Data import tribulations

Post by jay_hoehn » Mon Jun 29, 2020 2:59 am

I think you are putting an "n" in the place for month. It should be"m". Try that and then we'll work on the pressure.

Jay

Bigbaywx
Observer
Observer
Posts: 12
Joined: Mon Jun 22, 2020 8:47 pm
Station model: Davis Vantate Pro2
Software: weatherlink data logger

Re: Data import tribulations

Post by Bigbaywx » Mon Jun 29, 2020 12:39 pm

Hi Jay;

From the php date/time coding the ‘m’ variable is for numeric representation of the month with leading zeros and the ‘n’ variable is for numeric representation of the month without leading zeros.

The .csv file with my data that I downloaded of which the first few lines I posted has the month without the leading zeros so the ‘n’ variable should be the correct one. But willing to try anything I gave the ‘m’ a shot and same error result.

Thanks for the input though, much appreciated.

Doug

User avatar
jay_hoehn
Language Admin
Language Admin
Posts: 128
Joined: Sun Aug 20, 2017 1:45 pm
Location: Las Cruces, NM USA
Station model: Vantage Pro 2 Plus
Software: WeeWx
Contact:

Re: Data import tribulations

Post by jay_hoehn » Wed Jul 01, 2020 9:15 pm

Doug,
You are correct. I was mistaken in my post. It has been awhile since I did my import. If you can provide a link to your data i would be happy to give it a try. I would only be using the test import feature on my site. I can then let you know my discoveries.
I was also thinking that if the quoted data below is what you are trying to import then the quote marks might be causing the issue. I put that little bit of data in an LibreOffice spreadsheet and saved it as a csv file and the quote weren't there any longer. You might give that a try.
Jay

Bigbaywx
Observer
Observer
Posts: 12
Joined: Mon Jun 22, 2020 8:47 pm
Station model: Davis Vantate Pro2
Software: weatherlink data logger

Re: Data import tribulations

Post by Bigbaywx » Sat Jul 04, 2020 11:10 am

Hi Jay;

The quotes don't appear to be causing the problem as the template import script is taking those into account and I have tried reformatting the data ten ways from Sunday. Glad to have you look at it as I might be over complicating things and just not able to see it.

The file can be found here: http://www.bigbaywx.com/template/test.csv

That is as downloaded directly from my account at weatherlink with nothing added to it or taken out from it and if anyone else wants to give it a try the more the merrier. I did a work around on the pressure situation by just zeroing the pressure out on the high pressure/low pressure settings and the test script accepts that now so at this point it's just with the date time group that it's throwing a fit.

Thanks!
Doug

User avatar
jay_hoehn
Language Admin
Language Admin
Posts: 128
Joined: Sun Aug 20, 2017 1:45 pm
Location: Las Cruces, NM USA
Station model: Vantage Pro 2 Plus
Software: WeeWx
Contact:

Re: Data import tribulations

Post by jay_hoehn » Sat Jul 04, 2020 7:13 pm

Doug,

I think the problem is the header rows in the csv file. I was able to get the test import to work without error. I downloaded your csv file opened it in a spreadsheet and deleted the top rows except the title row. Here are the test import results. You are going to have a problem with wind direction. Your csv file uses text and the import tries to use degrees. Not sure how to deal with that.

Hope this helps.

Jay
Testing CSV/text file...

Errors will be highlighted in red...

Loading CSV/text file from https://jayswoodfurniture.com/test.csv

File loaded successfully.
Header row in the file, skipping first line.

The tested file has 8954 data sets. This import test will load only the first line.


Starting to parse data.

Loading line 1...
Separating fields...
Decimal separator is a period, no need to do anything, can be parsed by PHP.
The data row contains 26 fields.

Parsing date and time...Date and time in a single field.
MySQL accepts date and time in format YYYY-MM-DD HH:MM, the parsed date and time is: 2020-01-01 00:00


Parsing variables...

Temperature
Raw value: 18
Database temperature units: F
File temperature units: F
Same units, no need to do any conversion.
Final temperature value: 18.0 F

Humidity
Final humidity value: 89 %

Pressure
Raw value: 29.65
Database pressure units: inhg
File pressure units: inhg
Same units, no need to do any conversion.
Final pressure value: 29.65 inhg

Wind speed
Raw value: 0
Database wind speed units: mph
File wind speed units: mph
Same units, no need to do any conversion.
Final wind speed value: 0.0 mph

Wind gust
Raw value: 0
Database wind speed units: mph
File wind gust units: mph
Same units, no need to do any conversion.
Final wind speed value: 0.0 mph

Precipitation
Raw value: 0
Database precipitation units: in
File precipitation units: in
Same units, no need to do any conversion.
Final precipitation value: 0.00 in

Rain rate
Raw value: 0
Database precipitation units: in/h
Rain rate is saved in the database in units per hour.
File rain rate units: in
Same units, no need to do any conversion.
Final rain rate value: 0.00 in/h

Wind direction
Final wind direction value: degrees

Solar sensor disabled in main settings, ignoring.

Parsing line 1 finished.
Checking validity of data based on your limits in Main settings...
Checking date and time...
Date/time seems sensible.
Minimum temperature allowed: -30 F
Maximum temperature allowed: 150 F
Temperature OK.
Minimum humidity allowed: 0 %
Maximum humidity allowed: 100 %
Humidity OK.
Minimum pressure allowed: 28 inhg
Maximum pressure allowed: 33 inhg
Pressure OK.
Minimum wind speed allowed: 0 mph
Maximum wind speed allowed: 200 mph
Wind speed OK.
Minimum wind gust allowed: 0 mph
Maximum wind gust allowed: 200 mph
Wind gust OK.
Minimum daily precipitation allowed: 0 in
Maximum daily precipitation allowed: 200 in
Daily precipitation OK.
Minimum rain rate allowed: 0 in/h
Maximum rain rate allowed: 500 in/h
Rain rate OK.
Minimum wind direction allowed: 0 degrees
Maximum wind direction allowed: 360 degrees
Wind direction OK.

Calculating dew point based on parsed values...
Temperature and humidity both valid... converting and calculating dew point....
Dew point calculated: 15.26 F
Calculating apparent temperature based on parsed values...
Temperature, humidity, and wind speed all valid... converting and calculating apparent temperature....
Apparent temperature calculated: 12.56 F

Summary
The following would be imported to database:
Date/time: 2020-01-01 00:00
Temperature: 18.0 F
Dewpoint: 15.26 F
Apparent Temperature: 12.56 F
Humidity: 89 %
Pressure: 29.65 inhg
Wind speed: 0.0 mph
Wind gust: 0.0 mph
Precipitation: 0.00 in
Rain rate: 0.00 in/h
Wind direction: degrees
Solar radiation: No solar sensor - ignored.



--- END ----

Bigbaywx
Observer
Observer
Posts: 12
Joined: Mon Jun 22, 2020 8:47 pm
Station model: Davis Vantate Pro2
Software: weatherlink data logger

Re: Data import tribulations

Post by Bigbaywx » Sun Jul 05, 2020 9:43 am

Screenshot at 2020-07-05 05-39-52.png
Screenshot at 2020-07-05 05-39-52.png (56.33 KiB) Viewed 57 times

Hi Jay;

I have had the same results as far as it goes. The problem isn't with the data but the date/time groups. Look at where your example stopped and go down from there on mine and see the date/time error. Did you get that on your test? If you didn't I'd appreciate it if you would leave your test file at that link and I'll try it. I opened mine up in a spreadsheet and deleted the rows as you did and the result for me is the same.

Regards,
Doug

User avatar
jay_hoehn
Language Admin
Language Admin
Posts: 128
Joined: Sun Aug 20, 2017 1:45 pm
Location: Las Cruces, NM USA
Station model: Vantage Pro 2 Plus
Software: WeeWx
Contact:

Re: Data import tribulations

Post by jay_hoehn » Sun Jul 05, 2020 4:27 pm

Doug,

OK. I think I figured it out. On your screenshot of the Date and Time Format you have "n/j/y H.i" it should be "n/j/y H:i" Notice the colon. You have a period. I was able to import directly from your link, no issues.

Jay

Bigbaywx
Observer
Observer
Posts: 12
Joined: Mon Jun 22, 2020 8:47 pm
Station model: Davis Vantate Pro2
Software: weatherlink data logger

Re: Data import tribulations

Post by Bigbaywx » Sun Jul 05, 2020 4:55 pm

Hi Jay;

Yeah, I saw that as well shortly after I posted the thread and I had changed it immediately. This is the way it's been since then and still no dice.
Screenshot at 2020-07-05 12-52-02.png
Screenshot at 2020-07-05 12-52-02.png (6.64 KiB) Viewed 49 times

User avatar
jay_hoehn
Language Admin
Language Admin
Posts: 128
Joined: Sun Aug 20, 2017 1:45 pm
Location: Las Cruces, NM USA
Station model: Vantage Pro 2 Plus
Software: WeeWx
Contact:

Re: Data import tribulations

Post by jay_hoehn » Sun Jul 05, 2020 5:17 pm

Doug,

I'm not sure where to go from here. The only other thing I can suggest to try is if you are willing to message me your credentials to log in to your weather site I can try it from here. Hard to know without seeing it live.

Jay

Bigbaywx
Observer
Observer
Posts: 12
Joined: Mon Jun 22, 2020 8:47 pm
Station model: Davis Vantate Pro2
Software: weatherlink data logger

Re: Data import tribulations

Post by Bigbaywx » Sun Jul 05, 2020 9:24 pm

Hi Jay;

Thanks for the help. You have a PM and Jachym has the same access as he is working on the v2 of the WeatherLink API issue.

Doug

Bigbaywx
Observer
Observer
Posts: 12
Joined: Mon Jun 22, 2020 8:47 pm
Station model: Davis Vantate Pro2
Software: weatherlink data logger

Re: Data import tribulations

Post by Bigbaywx » Mon Jul 06, 2020 8:43 am

Success! Was able to get the data import with Jay’s assistance. Not sure why it worked this time, just glad it did. :mrgreen:

User avatar
jay_hoehn
Language Admin
Language Admin
Posts: 128
Joined: Sun Aug 20, 2017 1:45 pm
Location: Las Cruces, NM USA
Station model: Vantage Pro 2 Plus
Software: WeeWx
Contact:

Re: Data import tribulations

Post by jay_hoehn » Mon Jul 06, 2020 2:35 pm

Doug,

Glad I could help, and especially glad you could get the import to work. The Template is a great piece of work by Jachym and I have enjoyed being part of the ride since the beginning.

Take care and stay safe,

Jay

Post Reply