Sounds good. I’ve done the work to get it in through SQLite commands, but it’s been very cumbersome, with a lot of intermediate steps to get null values recorded properly. The import function is the way it’s meant to be done, just not 100% why it isn’t.
I did establish that the ValueError: could not convert string to float: '' is referring to the blank fields; inserting None, Null, or fluffernutter, results in ValueError: could not convert string to float: 'fluffernutter'. But the python line it references, File "/usr/share/weewx/weeimport/weeimport.py", line 856, has explicit logic to check for None- so I’m not sure why it fails even with None values for all null values in the string. On Sun, Mar 23, 2025 at 6:15 PM gjr80 <[email protected]> wrote: > Thanks, I've a couple of days with not much free time, but when I have a > chance I will run this through a test system and see if we can work out > what's going on. > > Gary > On Saturday, 22 March 2025 at 23:28:24 UTC+10 Andrew McGinnis wrote: > >> Here they are. CSV is 2025-03-20T00:00 to 2025-03-21T00:00, with every >> field in the schema, in the same order. The .conf has every field_map >> mapped 1:1 since they are the same. No units were specified beyond the >> mandatory datetime field, as the documentation says units is not required >> if the usUnits field is populated (it is). raw_datetime_format isn't >> specified, as the documentation says it's ignored if the datetime field is >> unix_epoch already. >> >> On Sat, Mar 22, 2025 at 5:02 AM gjr80 <[email protected]> wrote: >> >>> Let's take it from the top. Please post the csv data (preferably the >>> file) and import config file that caused the original error. Unless you are >>> talking about a fairly obvious error it is extremely difficult (and time >>> consuming) to work through a failed weectl import session without those >>> two pieces of the puzzle. >>> >>> Gary >>> On Saturday, 22 March 2025 at 09:12:47 UTC+10 Andrew McGinnis wrote: >>> >>>> So odd thing happens when I try to weectl --import. >>>> >>>> I dumped the data from the database I want to take it from (as well as >>>> the production one) via >>>> sudo sqlite3 -header -csv /var/lib/weewx/weewx.sdb 'select * from >>>> archive where datetime >= 1742443200 and datetime <= 1742529600;' > >>>> 20th_pi4b8.csv >>>> >>>> I copied both over to my PC and ran both of the output files through >>>> Excel to reconcile between the two, saving out a single 20th.csv with the >>>> data cleaned up as I want it to be. The schema/header and values are laid >>>> out exactly as my database schema is. The values are unmodified from the >>>> sqlite output, beyond copy/paste in Excel. >>>> >>>> Copied that cleaned 20th.csv back to the production weewx pi, and ran: >>>> >>>> pi@pi4b8:~/weeimport $ weectl import >>>> --import-config=/home/pi/weeimport/20th.conf --update --dry-run >>>> Using configuration file /etc/weewx/weewx.conf >>>> This is a dry run. Nothing will actually be done. >>>> Starting weectl import... >>>> A CSV import from source file '/home/pi/weeimport/20th.csv' has been >>>> requested. >>>> Using database binding 'wx_binding', which is bound to database >>>> 'weewx.sdb' >>>> Destination table 'archive' unit system is '0x01' (US). >>>> Imported records will overwrite existing database records. >>>> All WeeWX UV fields will be set to None. >>>> All WeeWX radiation fields will be set to None. >>>> This is a dry run, imported data will not be saved to archive. >>>> Starting dry run import ... >>>> Traceback (most recent call last): >>>> File "/usr/share/weewx/weeimport/weeimport.py", line 856, in >>>> map_raw_data >>>> _value = float(_row[self.map[_field]['source_field']].strip()) >>>> ValueError: could not convert string to float: '' >>>> >>>> During handling of the above exception, another exception occurred: >>>> >>>> Traceback (most recent call last): >>>> File "/usr/share/weewx/weectl.py", line 75, in <module> >>>> main() >>>> File "/usr/share/weewx/weectl.py", line 67, in main >>>> namespace.func(namespace) >>>> File "/usr/share/weewx/weectllib/__init__.py", line 90, in dispatch >>>> namespace.action_func(config_dict, namespace) >>>> File "/usr/share/weewx/weectllib/import_cmd.py", line 85, in >>>> import_func >>>> weectllib.import_actions.obs_import(config_dict, >>>> File "/usr/share/weewx/weectllib/import_actions.py", line 58, in >>>> obs_import >>>> source_obj.run() >>>> File "/usr/share/weewx/weeimport/weeimport.py", line 406, in run >>>> _mapped_data = self.map_raw_data(_raw_data, self.archive_unit_sys) >>>> File "/usr/share/weewx/weeimport/weeimport.py", line 904, in >>>> map_raw_data >>>> self.map[_field]['unit'] == 'degree_compass': >>>> File "/usr/lib/python3/dist-packages/configobj.py", line 554, in >>>> __getitem__ >>>> val = dict.__getitem__(self, key) >>>> KeyError: 'unit' >>>> >>>> I looked at the file in nano, and noticed it was saved in DOS format. >>>> I changed it to be not-DOS/Mac (...native then I guess?), but same result. >>>> >>>> If I go in through >>>> pi@pi4b8:~/weeimport $ sudo sqlite3 /var/lib/weewx/weewx.sdb >>>> SQLite version 3.34.1 2021-01-20 14:10:07 >>>> Enter ".help" for usage hints. >>>> sqlite> DELETE FROM archive WHERE datetime >= 1742443200 and datetime >>>> <= 1742529600; >>>> sqlite> .mode csv archive >>>> sqlite> .import 20th.csv archive >>>> 20th.csv:1: INSERT failed: datatype mismatch >>>> 20th.csv:1443: INSERT failed: datatype mismatch >>>> sqlite> .quit >>>> >>>> The data is in there though (and in the correct columns- no offset >>>> errors). Doing weectl database --rebuild-daily --date=2025-03-20 >>>> rebuilds successfully. That said, my graphs look... off, where the data is >>>> highly variable in it's 10-min interval that the graph uses for plot >>>> points. You can see it's very smooth once I'm back to using directly >>>> captured data. >>>> Current (trailing 24hr): >>>> [image: image.png] [image: image.png] >>>> Yesterday (00:00-23:59) >>>> [image: image.png] [image: image.png] >>>> >>>> The sqlite output for '*select datetime,outTemp from archive...;*' for >>>> Mar-20 13:30-15:30 (imported data) and Mar-21 13:30-15:30 (direct) have a >>>> similar number of missing records for the outTemp and the >>>> extraTemp/extraHumid observations, but the imported data has ' >>>> *epochtime,""*' in a csv output for the blank records, where the >>>> direct's csv output is just '*epochtime,'* >>>> >>>> The graphs are being generated (with a 10-minute resolution) very >>>> differently. If I take a 10-min period of records: >>>> -the graph point for the imported data is the sum of the 10 records, >>>> divided by 10, even though 8 were actually recorded- averaging in every >>>> blank as a 0 >>>> -the graph point for my direct observations, the same period also had >>>> blanks, but the average was taken of the records present. >>>> >>>> I imagine everything in the background is likewise factoring what are >>>> meant to be blanks, as zeros. >>>> >>>> So, the sqlite import added "" for blank records to the database it >>>> would seem. Before I go even more leeroy jenkins on my database, what do I >>>> need to do? Go do it again, but add/insert *None* for every blank >>>> observation? add/insert *""* for every blank? Or better, what am I not >>>> seeing/missing in my weectl import that it's erroring out? >>>> >>>> Here is the first two lines of the csv file I'm trying to import for >>>> context: >>>> >>>> pi@pi4b8:~/weeimport $ head -2 20th.csv >>>> >>>> dateTime,usUnits,interval,altimeter,appTemp,appTemp1,barometer,batteryStatus1,batteryStatus2,batteryStatus3,batteryStatus4,batteryStatus5,batteryStatus6,batteryStatus7,batteryStatus8,cloudbase,co,co2,consBatteryVoltage,dewpoint,dewpoint1,ET,extraHumid1,extraHumid2,extraHumid3,extraHumid4,extraHumid5,extraHumid6,extraHumid7,extraHumid8,extraTemp1,extraTemp2,extraTemp3,extraTemp4,extraTemp5,extraTemp6,extraTemp7,extraTemp8,forecast,hail,hailBatteryStatus,hailRate,heatindex,heatindex1,heatingTemp,heatingVoltage,humidex,humidex1,inDewpoint,inHumidity,inTemp,inTempBatteryStatus,leafTemp1,leafTemp2,leafWet1,leafWet2,lightning_distance,lightning_disturber_count,lightning_energy,lightning_noise_count,lightning_strike_count,luminosity,maxSolarRad,nh3,no2,noise,o3,outHumidity,outTemp,outTempBatteryStatus,pb,pm10_0,pm1_0,pm2_5,pressure,radiation,rain,rainBatteryStatus,rainRate,referenceVoltage,rxCheckPercent,signal1,signal2,signal3,signal4,signal5,signal6,signal7,signal8,snow,snowBatteryStatus,snowDepth,snowMoisture,snowRate,so2,soilMoist1,soilMoist2,soilMoist3,soilMoist4,soilTemp1,soilTemp2,soilTemp3,soilTemp4,supplyVoltage,txBatteryStatus,UV,uvBatteryStatus,windBatteryStatus,windchill,windDir,windGust,windGustDir,windrun,windSpeed,pm4_0,pm4_0out,pm2_5out,pm1_0out,pm10_0out,co2out >>>> >>>> 1742443200,1,1,30.13063837,62.49347962,,30.12772479,0,1,1,1,1,,,,604.5454057,,1106,1500,58.65679821,,,59,57,58,40,51,,,,72.61,72.37,72.47727273,78.30909091,76.82,,,,,,,,61.1239978,,,,67.94440487,,56.89523489,54,74.66,1,,,,,,,,,,,0,,,,,92,60.999998,1,,2.8,0.6,1.6,30.04971646,,0,0.88889,0,,,,,,,,,,,20.12999882,1,,,,,45.88235294,,,,70.7,,,,,1,,,1,60.999998,202.5,2.678862144,202.5,0.038218048,2.293082881,2.4,,2,,2, >>>> >>>> On Sun, Mar 16, 2025 at 4:27 PM gjr80 <[email protected]> wrote: >>>> >>>>> That should be fine. No need for 'empty' fields to be included in the >>>>> header line and data lines, but in this case it is probably the >>>>> simplest/quickest approach. Just be very careful that you don't end up >>>>> with >>>>> any missing or extra commas as you will likely then put the wrong data in >>>>> the wrong field. >>>>> >>>>> Gary >>>>> On Sunday, 16 March 2025 at 05:57:55 UTC+10 Andrew McGinnis wrote: >>>>> >>>>>> Via a dump of every column in the table that exists, whether I'm >>>>>> using it or not, the csv content could be: >>>>>> >>>>>> >>>>>> "dateTime","usUnits","interval","altimeter","appTemp","appTemp1","barometer","batteryStatus1","batteryStatus2","batteryStatus3","batteryStatus4","batteryStatus5","batteryStatus6","batteryStatus7","batteryStatus8","cloudbase","co","co2","consBatteryVoltage","dewpoint","dewpoint1","ET","extraHumid1","extraHumid2","extraHumid3","extraHumid4","extraHumid5","extraHumid6","extraHumid7","extraHumid8","extraTemp1","extraTemp2","extraTemp3","extraTemp4","extraTemp5","extraTemp6","extraTemp7","extraTemp8","forecast","hail","hailBatteryStatus","hailRate","heatindex","heatindex1","heatingTemp","heatingVoltage","humidex","humidex1","inDewpoint","inHumidity","inTemp","inTempBatteryStatus","leafTemp1","leafTemp2","leafWet1","leafWet2","lightning_distance","lightning_disturber_count","lightning_energy","lightning_noise_count","lightning_strike_count","luminosity","maxSolarRad","nh3","no2","noise","o3","outHumidity","outTemp","outTempBatteryStatus","pb","pm10_0","pm1_0","pm2_5","pressure","radiation","rain","rainBatteryStatus","rainRate","referenceVoltage","rxCheckPercent","signal1","signal2","signal3","signal4","signal5","signal6","signal7","signal8","snow","snowBatteryStatus","snowDepth","snowMoisture","snowRate","so2","soilMoist1","soilMoist2","soilMoist3","soilMoist4","soilTemp1","soilTemp2","soilTemp3","soilTemp4","supplyVoltage","txBatteryStatus","UV","uvBatteryStatus","windBatteryStatus","windchill","windDir","windGust","windGustDir","windrun","windSpeed","pm4_0","pm4_0out","pm2_5out","pm1_0out","pm10_0out","co2out" >>>>>> >>>>>> 1741612440,1,1,,,,,1.0,,,1.0,,,,,,,,,,,,58.0,,,58.0,,,,,75.02,,,77.36,,,,,,,,,,,,,,,,,,,,,,,,,,,,,238.51020260006104,,,,,,,,,,,,, >>>>>> 0.00999881889763705 >>>>>> ,,0.0199976377952741,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, >>>>>> >>>>>> where the larger font size one is the rain amount that I'm >>>>>> inserting. Then one observation line for each record period that I need >>>>>> to >>>>>> change (that way, no change is required to the column name line for >>>>>> periods >>>>>> that have a different mix of observations with data)? >>>>>> >>>>>> Only 8 observations got recorded in that period I have above (I have >>>>>> more than 8 XXXbatteryStatus observations alone), but at this point I'm >>>>>> just seeking to add the rain observation. >>>>>> >>>>>> On Fri, Mar 14, 2025 at 9:41 PM gjr80 <[email protected]> wrote: >>>>>> >>>>>>> See comments below. >>>>>>> >>>>>>> Gary >>>>>>> >>>>>>> On Saturday, 15 March 2025 at 03:47:33 UTC+10 Andrew McGinnis wrote: >>>>>>> >>>>>>> If you want to use weectl import to import data into existing >>>>>>> archive records, each record in your import source data (in your case >>>>>>> your >>>>>>> CSV data) needs to include the existing data for all fields as well as >>>>>>> the >>>>>>> updated rainfall data. You then need to use the (as yet undocumented) >>>>>>> --update command line option when running weectl import. >>>>>>> >>>>>>> >>>>>>> So rather than just the records I'm *missing*, I need to go from >>>>>>> the first missing record to the last missing record, inclusive of all >>>>>>> in-between records? I can easily do that, just not 100% on whether it's >>>>>>> required. >>>>>>> >>>>>>> >>>>>>> No, you don't need 'in between records'. Your import data should >>>>>>> only include the records you intend to update/alter, you do need the >>>>>>> data >>>>>>> from the other fields of the records you are updating/importing. >>>>>>> Perhaps an >>>>>>> example might make it clearer. Say your archive table contains the >>>>>>> following: >>>>>>> >>>>>>> dateTime outTemp inTemp rain outHumidity >>>>>>> 1742000400 23.1 20.1 1.0 80 >>>>>>> 1742000100 23.2 20.1 2.0 81 >>>>>>> 1741999800 23.2 20.1 5.0 84 >>>>>>> 1741999500 23.3 20.2 0.0 83 >>>>>>> 1741999200 23.5 20.2 0.8 83 >>>>>>> 1741998900 23.5 20.2 0.6 84 >>>>>>> 1741998600 23.4 20.1 0.6 82 >>>>>>> 1741998300 23.3 20.1 0.8 83 >>>>>>> 1741998000 23.3 20.2 0.0 84 >>>>>>> 1741997700 23.3 20.1 1.0 82 >>>>>>> >>>>>>> Lets say you want to update the rain value in records timestamped >>>>>>> 1741999500 and 1741998000 (ie the 0.0 values) and your new rain >>>>>>> values (per archive period values) for these two records are 0.2 and 0.4 >>>>>>> respectively. So you might start with your import data as follows: >>>>>>> >>>>>>> dateTime rain >>>>>>> 1741999500 0.2 >>>>>>> 1741998000 0.4 >>>>>>> >>>>>>> If you import this data using the --update command line option (or >>>>>>> without the --update command line option if you deleted the two >>>>>>> archive table records concerned) your archive table will be: >>>>>>> >>>>>>> dateTime outTemp inTemp rain outHumidity >>>>>>> 1742000400 23.1 20.1 1.0 80 >>>>>>> 1742000100 23.2 20.1 2.0 81 >>>>>>> 1741999800 23.2 20.1 5.0 84 >>>>>>> 1741999500 0.2 >>>>>>> 1741999200 23.5 20.2 0.8 83 >>>>>>> 1741998900 23.5 20.2 0.6 84 >>>>>>> 1741998600 23.4 20.1 0.6 82 >>>>>>> 1741998300 23.3 20.1 0.8 83 >>>>>>> 1741998000 0.4 >>>>>>> 1741997700 23.3 20.1 1.0 82 >>>>>>> >>>>>>> Each row in your import data is considered to be an archive record >>>>>>> in itself and any fields that are not included in your import data will >>>>>>> be >>>>>>> empty. To keep the rest of the existing data in the records being >>>>>>> updated >>>>>>> (in this case outTemp, inTemp and outHumidity), your import data >>>>>>> needs to include values for outTemp, inTemp and outHumidity. Your >>>>>>> import data would be: >>>>>>> >>>>>>> dateTime outTemp inTemp rain outHumidity >>>>>>> 1741999500 23.3 20.2 0.2 83 >>>>>>> 1741998000 23.3 20.2 0.4 84 >>>>>>> >>>>>>> Importing this data will give you: >>>>>>> >>>>>>> dateTime outTemp inTemp rain outHumidity >>>>>>> 1742000400 23.1 20.1 1.0 80 >>>>>>> 1742000100 23.2 20.1 2.0 81 >>>>>>> 1741999800 23.2 20.1 5.0 84 >>>>>>> 1741999500 23.3 20.2 0.2 83 >>>>>>> 1741999200 23.5 20.2 0.8 83 >>>>>>> 1741998900 23.5 20.2 0.6 84 >>>>>>> 1741998600 23.4 20.1 0.6 82 >>>>>>> 1741998300 23.3 20.1 0.8 83 >>>>>>> 1741998000 23.3 20.2 0.4 84 >>>>>>> 1741997700 23.3 20.1 1.0 82 >>>>>>> >>>>>>> Which I suspect is the desired result. Of course this is a very >>>>>>> simple and unrealistic example but hopefully you get the idea. >>>>>>> >>>>>>> >>>>>>> >>>>>>> Alternatively, you could avoid using the --update command line >>>>>>> option by first deleting the records to be imported from the WeeWX >>>>>>> database >>>>>>> and then importing your merged/updated CSV data. >>>>>>> >>>>>>> >>>>>>> If you'll save me the research, how do I delete specific >>>>>>> records/periods, either a single period record, or a range? I just >>>>>>> recently >>>>>>> noticed that the soilMoist1 observation, on my weewx testing >>>>>>> install, recorded about 84 hours of very wrong values, that my >>>>>>> production >>>>>>> weewx install didn't (both getting the data from the same mqtt topic). I >>>>>>> know how to drop entire observation columns via weectl database >>>>>>> drop-columns NAME, but in this case it's just range of periods >>>>>>> within the column that need purging. Or is it as simple as, zeroing out >>>>>>> the >>>>>>> values in a range via sqlite> update archive set soilMoist1=0.0 >>>>>>> where dateTime > {startepochtime} and dateTime <= {endepochtime]; ? >>>>>>> >>>>>>> >>>>>>> >>>>>>> In terms of deleting records I use the sqlite3 utility to allow me >>>>>>> to use SQL commands to interact with the database. Some people use GUI >>>>>>> based sql editors to manipulate the database; I don't so I can't help >>>>>>> you >>>>>>> there. In terms of sqlite3 the following commands can be used to >>>>>>> delete archive table records: >>>>>>> >>>>>>> $ sqlite3 /var/lib/weewx/weewx.sdb >>>>>>> sqlite> DELETE FROM archive WHERE dateTime=1741999500; >>>>>>> sqlite3> .q >>>>>>> $ >>>>>>> >>>>>>> In this case we deleted the archive record timestamped 1741999500. >>>>>>> >>>>>>> To delete a range of archive records by timestamp use something like: >>>>>>> >>>>>>> sqlite> DELETE FROM archive WHERE dateTime>=1741969500 AND >>>>>>> dateTime<=1741999500; >>>>>>> >>>>>>> which would delete all archive records timestamped from 1741969500 >>>>>>> to 1741999500 inclusive. >>>>>>> >>>>>>> Note that you may need to install the sqlite3 utility on your system >>>>>>> using something like (for debian based systems): >>>>>>> >>>>>>> $ sudo apt install sqlite3 >>>>>>> >>>>>>> -- >>>>>>> You received this message because you are subscribed to a topic in >>>>>>> the Google Groups "weewx-user" group. >>>>>>> To unsubscribe from this topic, visit >>>>>>> https://groups.google.com/d/topic/weewx-user/1KX3-8lbU34/unsubscribe >>>>>>> . >>>>>>> To unsubscribe from this group and all its topics, send an email to >>>>>>> [email protected]. >>>>>>> >>>>>> To view this discussion visit >>>>>>> https://groups.google.com/d/msgid/weewx-user/4506f570-fb2c-40f2-95ab-a98c02a59011n%40googlegroups.com >>>>>>> <https://groups.google.com/d/msgid/weewx-user/4506f570-fb2c-40f2-95ab-a98c02a59011n%40googlegroups.com?utm_medium=email&utm_source=footer> >>>>>>> . >>>>>>> >>>>>> -- >>>>> You received this message because you are subscribed to a topic in the >>>>> Google Groups "weewx-user" group. >>>>> To unsubscribe from this topic, visit >>>>> https://groups.google.com/d/topic/weewx-user/1KX3-8lbU34/unsubscribe. >>>>> To unsubscribe from this group and all its topics, send an email to >>>>> [email protected]. >>>>> >>>> To view this discussion visit >>>>> https://groups.google.com/d/msgid/weewx-user/6b7b89a9-5703-4ae9-8c01-bdaa310ff7can%40googlegroups.com >>>>> <https://groups.google.com/d/msgid/weewx-user/6b7b89a9-5703-4ae9-8c01-bdaa310ff7can%40googlegroups.com?utm_medium=email&utm_source=footer> >>>>> . >>>>> >>>> -- >>> You received this message because you are subscribed to a topic in the >>> Google Groups "weewx-user" group. >>> To unsubscribe from this topic, visit >>> https://groups.google.com/d/topic/weewx-user/1KX3-8lbU34/unsubscribe. >>> To unsubscribe from this group and all its topics, send an email to >>> [email protected]. >>> >> To view this discussion visit >>> https://groups.google.com/d/msgid/weewx-user/9a43dbf7-5658-49ef-819f-b00a1d5ba510n%40googlegroups.com >>> <https://groups.google.com/d/msgid/weewx-user/9a43dbf7-5658-49ef-819f-b00a1d5ba510n%40googlegroups.com?utm_medium=email&utm_source=footer> >>> . >>> >> -- > You received this message because you are subscribed to a topic in the > Google Groups "weewx-user" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/weewx-user/1KX3-8lbU34/unsubscribe. > To unsubscribe from this group and all its topics, send an email to > [email protected]. > To view this discussion visit > https://groups.google.com/d/msgid/weewx-user/60ea0db7-f287-47b7-8336-eb04af1beb41n%40googlegroups.com > <https://groups.google.com/d/msgid/weewx-user/60ea0db7-f287-47b7-8336-eb04af1beb41n%40googlegroups.com?utm_medium=email&utm_source=footer> > . > -- 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 [email protected]. To view this discussion visit https://groups.google.com/d/msgid/weewx-user/CAH3iRs3Mi%2BPQpLCFontESrc%3D1TrzCu00phtkaOMRB_SoHR1HKQ%40mail.gmail.com.
