Data import tribulations

[Bigbaywx]

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

[Bigbaywx]

We are limited to how many attachments so this is a followup showing the error returned from the import.php test script.

[jay_hoehn]

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]

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

[jay_hoehn]

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]

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

[jay_hoehn]

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]

Screenshot at 2020-07-05 05-39-52.png (56.33 KiB) Viewed 5368 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

[jay_hoehn]

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]

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 (6.64 KiB) Viewed 5360 times

[jay_hoehn]

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]

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]

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

[jay_hoehn]

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

[meteocharleroi]

Hi,

I am a new user of meteotemplate and I will try to upload 12 years of data using the same source as in this post.

Any advice how you succeed to do it ?

Thanks.

[jay_hoehn]

meteocharleroi,

If you follow the instructions the import should work.

Be careful with the date, time formats. If you have specific problems then post the error messages.

Jay

[meteocharleroi]

meteocharleroi,

If you follow the instructions the import should work.

Be careful with the date, time formats. If you have specific problems then post the error messages.

Jay

Hi,

I have find the solution for almost all problems.

I was able to import 6 months of data but I have a problem with précipitations.

In the file dowloaded from Wearherlink, there is no any fiels with totalday precipitation but only the amount of precipitation since the last value reported.

I see that you succeed to import the same data for Bigbaywx. Did you find something for that ?

Thanks.

[jay_hoehn]

meteocharleroi,

I am sorry. I don't know how Doug handled that issue. Maybe he will chime in here and let you know.

Jay

[Bigbaywx]

IIRC what I had to do was to use the bulk DB function and for past years manually input the totals. Wasn't quick and wasn't accurate on a day to day basis but in the end it had the correct values for the year.

[meteocharleroi]

Thanks to all of you.

I think I will manage this with xls formula before import like I have done to convert wind from letter to degrees.