activity-browser
activity-browser copied to clipboard
LCA-ActivityBrowser
unstable excel database importer
Roughly 40-50% of the time when importing a database from excel that references another database, the 'relink database' dialog is not shown and the import simply fails or errors occur in other ways. The issue is difficult to replicate, but I've experienced it multiple times, for example working on #613 or #587. I've experienced this on the stable and (conda) dev versions on Windows 10 and Linux (Mint 20).
Another issue when importing occurred, not sure if I should make this a new GH issue so I'm instead commenting here: This time it seems to be related to the parameters in the file. The import dialog keeps loading forever (or until I cancel) The database does not show up until I reload the project or close/re-open AB. Some, but not all parameters got loaded
Traceback (most recent call last):
File "C:\Users\m\miniconda3\envs\ab\lib\site-packages\activity_browser\app\ui\wizards\db_import_wizard.py", line 681, in run
self.run_local_import()
File "C:\Users\m\miniconda3\envs\ab\lib\site-packages\activity_browser\app\ui\wizards\db_import_wizard.py", line 796, in run_local_import
self.archive_path, self.db_name, self.relink
File "C:\Users\m\miniconda3\envs\ab\lib\site-packages\activity_browser\app\bwutils\importers.py", line 109, in simple_automated_import
bw.parameters.recalculate()
File "C:\Users\m\miniconda3\envs\ab\lib\site-packages\bw2data\parameters.py", line 1499, in recalculate
ActivityParameter.recalculate(obj.name)
File "C:\Users\m\miniconda3\envs\ab\lib\site-packages\bw2data\parameters.py", line 791, in recalculate
k: v for k, v in ActivityParameter._static_dependencies(group).items()
File "C:\Users\m\miniconda3\envs\ab\lib\site-packages\bw2data\parameters.py", line 618, in _static_dependencies
database = ActivityParameter.get(group=group).database
File "C:\Users\m\miniconda3\envs\ab\lib\site-packages\peewee.py", line 6438, in get
return sq.get()
File "C:\Users\m\miniconda3\envs\ab\lib\site-packages\peewee.py", line 6889, in get
(clone.model, sql, params))
bw2data.parameters.ActivityParameterDoesNotExist: <Model: ActivityParameter> instance matching query does not exist:
SQL: SELECT "t1"."id", "t1"."group", "t1"."database", "t1"."code", "t1"."name", "t1"."formula", "t1"."amount", "t1"."data" FROM "activityparameter" AS "t1" WHERE ("t1"."group" = ?) LIMIT ? OFFSET ?
Params: ['HT_today_Calciner_f3461a5ccb7d291e8b1ce6fff0f386be', 1, 0]
Another issue:
This DB did not have any issues before, but is now broken wheb I try to import it
IndexError: list index out of range
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "C:\Users\m\miniconda3\envs\ab\lib\site-packages\activity_browser\app\ui\wizards\db_import_wizard.py", line 681, in run
self.run_local_import()
File "C:\Users\m\miniconda3\envs\ab\lib\site-packages\activity_browser\app\ui\wizards\db_import_wizard.py", line 796, in run_local_import
self.archive_path, self.db_name, self.relink
File "C:\Users\m\miniconda3\envs\ab\lib\site-packages\activity_browser\app\bwutils\importers.py", line 109, in simple_automated_import
bw.parameters.recalculate()
File "C:\Users\m\miniconda3\envs\ab\lib\site-packages\bw2data\parameters.py", line 1499, in recalculate
ActivityParameter.recalculate(obj.name)
File "C:\Users\m\miniconda3\envs\ab\lib\site-packages\bw2data\parameters.py", line 791, in recalculate
k: v for k, v in ActivityParameter._static_dependencies(group).items()
File "C:\Users\m\miniconda3\envs\ab\lib\site-packages\bw2data\parameters.py", line 618, in _static_dependencies
database = ActivityParameter.get(group=group).database
File "C:\Users\m\miniconda3\envs\ab\lib\site-packages\peewee.py", line 6438, in get
return sq.get()
File "C:\Users\m\miniconda3\envs\ab\lib\site-packages\peewee.py", line 6889, in get
(clone.model, sql, params))
bw2data.parameters.ActivityParameterDoesNotExist: <Model: ActivityParameter> instance matching query does not exist:
SQL: SELECT "t1"."id", "t1"."group", "t1"."database", "t1"."code", "t1"."name", "t1"."formula", "t1"."amount", "t1"."data" FROM "activityparameter" AS "t1" WHERE ("t1"."group" = ?) LIMIT ? OFFSET ?
Params: ['HT_today_Calciner_f3461a5ccb7d291e8b1ce6fff0f386be', 1, 0]
Update 22-Mar-2022:
Found another instance of this problem when helping someone today
An interesting point here is that I am missing the same parameter in both examples above, which is not used in this latest database I tried to import. This implies to me that the problem is on the project level, not in the databases that are imported
~~Yet another case: #686 of excel imports failing with the could not link exchanges error~~
user error
I experienced another instability of the excel importer. The pop-up window to link databases appears multiple times during the process. I discovered that the import already succeeds after the first pop-up, but I still have to click through a number of identical messages (clicking 'OK' or 'Cancel' has the same effect). It seems that the number of pop-ups that is generated increases when doing multiple imports.
Thanks for the extra info! Could you possibly share the database? If it contains confidential information, perhaps you could change numbers or activity names to protect that information, while still allowing us to replicate your problem.
Thanks for having a look Marc! In my case, it seems to happen with any excel database. As an example, see the file below. recovery_SvN.xlsx
For clarification: every time I import this or another database (during the same session), the number of pop-ups increases.
Here as a crash I couldn't reproduce.
The AB [v2.6.9] window closed after importing a database.
In contrast with :
The pop-up window to link databases appears multiple times during the process. --- https://github.com/LCA-ActivityBrowser/activity-browser/issues/614#issuecomment-997966476
only one linking pop-up emerged.
After the window closed, the terminal remained open, with the conda environment active. There were no error or warning messages in the console or in the program window. In the console, the import seemed complete.
When I launched again from the same active environment, the database seemed properly imported and the crash seemed inconsequential. The next import worked properly.
I had another crash. The AB was supposed to give the relink popup, but instead crashed.
To make this hopefully more repoduceable:
AB open without having done anything else.
In this case I changed the name compared to the default suggested name in the name popup.
Clicked import database on the final overview of the wizard and it crashed with:
Extracted 1 worksheets in 0.03 seconds
Applying strategy: alter_database_name
Applying strategy: csv_restore_tuples
Applying strategy: csv_restore_booleans
Applying strategy: csv_numerize
Applying strategy: csv_drop_unknown
Applying strategy: csv_add_missing_exchanges_section
Applying strategy: csv_rewrite_product_key
Applying strategy: normalize_units
Applying strategy: normalize_biosphere_categories
Applying strategy: normalize_biosphere_names
Applying strategy: strip_biosphere_exc_locations
Applying strategy: set_code_by_activity_hash
Applying strategy: link_iterable_by_fields
Applying strategy: assign_only_product_as_production
Applying strategy: link_technosphere_by_activity_hash
Applying strategy: drop_falsey_uncertainty_fields_but_keep_zeros
Applying strategy: convert_uncertainty_types_to_integers
Applying strategy: hash_parameter_group
Applying strategy: convert_activity_parameters_to_list
Applied 19 strategies in 0.22 seconds
Could not link exchanges, here are 10 examples.:
I re-started AB and re-imported with the suggested name and the re-link popup appeared and everything worked as expected.
I had a crash similar to https://github.com/LCA-ActivityBrowser/activity-browser/issues/614#issuecomment-1175034151. Note that the import works just fine via bare brightway.
- AB open without having done anything else.
- Name unchanged compared to the default suggested name in the name popup.
- Clicked
import databaseon the final overview of the wizard and it crashed with:
Extracted 6 worksheets in 0.16 seconds
Applying strategy: alter_database_name
Applying strategy: csv_restore_tuples
Applying strategy: csv_restore_booleans
Applying strategy: csv_numerize
Applying strategy: csv_drop_unknown
Applying strategy: csv_add_missing_exchanges_section
Applying strategy: csv_rewrite_product_key
Applying strategy: normalize_units
Applying strategy: normalize_biosphere_categories
Applying strategy: normalize_biosphere_names
Applying strategy: strip_biosphere_exc_locations
Applying strategy: set_code_by_activity_hash
Applying strategy: link_iterable_by_fields
Applying strategy: assign_only_product_as_production
Applying strategy: link_technosphere_by_activity_hash
Applying strategy: drop_falsey_uncertainty_fields_but_keep_zeros
Applying strategy: convert_uncertainty_types_to_integers
Applying strategy: hash_parameter_group
Applying strategy: convert_activity_parameters_to_list
Applied 19 strategies in 24.92 seconds
Could not link exchanges, here are 10 examples.:
I tried re-starting and had the same behavior. Two more users tried and experienced the same crash.
Successful import with the python script:
import brightway2 as bw
project = 'myProject'
datapath = r'my/datapath/filename.xlsx'
bw.projects.set_current(project)
imp = bw.ExcelImporter(datapath)
imp.apply_strategies()
imp.match_database('cutoff38')
imp.statistics()
imp.write_database()
Is the conda environment the same between the AB and python script?
If not, could you conda list bw2io in both environments?
And if it is the same conda environment, could you share the full conda list output?
Hi,
I'm the one experiencing this bug. I tried importing via the python script above but that did not work. The conda environment is indeed the same between AB and the python script. I'll copy the errors below and the full conda list output. The python code works until:
imp.apply_strategies()
Applying strategy: csv_restore_tuples
Applying strategy: csv_restore_booleans
Applying strategy: csv_numerize
Applying strategy: csv_drop_unknown
Applying strategy: csv_add_missing_exchanges_section
Applying strategy: normalize_units
Applying strategy: normalize_biosphere_categories
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "C:\Users\bspre\.conda\envs\ab\lib\site-packages\bw2io\importers\base.py", line 69, in apply_strategies
self.apply_strategy(func, verbose)
File "C:\Users\bspre\.conda\envs\ab\lib\site-packages\bw2io\importers\base.py", line 48, in apply_strategy
self.data = strategy(self.data)
File "C:\Users\bspre\.conda\envs\ab\lib\site-packages\bw2io\strategies\biosphere.py", line 37, in normalize_biosphere_categories
db = migrate_exchanges(db, migration="biosphere-2-3-categories")
File "C:\Users\bspre\.conda\envs\ab\lib\site-packages\bw2io\strategies\migrations.py", line 33, in migrate_exchanges
assert migration in migrations, u"Can't find migration {}".format(migration)
AssertionError: Can't find migration biosphere-2-3-categories
And a second error when trying to match the database:
imp.match_database('cutoff38')
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "C:\Users\bspre\.conda\envs\ab\lib\site-packages\bw2io\importers\base_lci.py", line 324, in match_database
raise StrategyError("Can't find external database {}".format(db_name))
bw2io.errors.StrategyError: Can't find external database cutoff38
Full output of conda list:
packages in environment at C:\Users\bspre\.conda\envs\ab:
Name Version Build Channel
activity-browser 2.8.0 pyhd8ed1ab_0 conda-forge
appdirs 1.4.4 pyh9f0ad1d_0 conda-forge
arrow 1.2.3 pyhd8ed1ab_0 conda-forge
asteval 0.9.31 pyhd8ed1ab_0 conda-forge
astunparse 1.6.3 pyhd8ed1ab_0 conda-forge
beautifulsoup4 4.12.2 pyha770c72_0 conda-forge
blinker 1.6.2 pyhd8ed1ab_0 conda-forge
brightway2 2.4.3 pyhd8ed1ab_0 conda-forge
brotli 1.0.9 hcfcfb64_9 conda-forge
brotli-bin 1.0.9 hcfcfb64_9 conda-forge
brotli-python 1.0.9 py39h99910a6_9 conda-forge
brotlicffi 1.0.9.2 py39h99910a6_3 conda-forge
bw2analyzer 0.10 pyhd8ed1ab_0 conda-forge
bw2calc 1.8.2 py39hcbf5309_0 conda-forge
bw2data 3.6.6 pyhd8ed1ab_0 conda-forge
bw2io 0.8.7 pyhd8ed1ab_0 conda-forge
bw2parameters 1.1.0 pyhd8ed1ab_0 conda-forge
bw_migrations 0.2 pyhd8ed1ab_0 conda-forge
bzip2 1.0.8 h8ffe710_4 conda-forge
ca-certificates 2023.5.7 h56e8100_0 conda-forge
certifi 2023.5.7 pyhd8ed1ab_0 conda-forge
cffi 1.15.1 py39h68f70e3_3 conda-forge
charset-normalizer 3.2.0 pyhd8ed1ab_0 conda-forge
click 8.1.6 win_pyh7428d3b_0 conda-forge
colorama 0.4.6 pyhd8ed1ab_0 conda-forge
contourpy 1.1.0 py39h1f6ef14_0 conda-forge
cycler 0.11.0 pyhd8ed1ab_0 conda-forge
dill 0.3.6 pyhd8ed1ab_1 conda-forge
docopt 0.6.2 py_1 conda-forge
eidl 1.5.1 pyhd8ed1ab_1 conda-forge
eight 1.0.1 pyhd8ed1ab_4 conda-forge
et_xmlfile 1.1.0 pyhd8ed1ab_0 conda-forge
fasteners 0.17.3 pyhd8ed1ab_0 conda-forge
flask 2.3.2 pyhd8ed1ab_0 conda-forge
fonttools 4.41.0 py39ha55989b_0 conda-forge
freetype 2.12.1 h546665d_1 conda-forge
future 0.18.3 pyhd8ed1ab_0 conda-forge
gettext 0.21.1 h5728263_0 conda-forge
glib 2.76.4 h12be248_0 conda-forge
glib-tools 2.76.4 h12be248_0 conda-forge
gst-plugins-base 1.22.4 h001b923_1 conda-forge
gstreamer 1.22.4 hb4038d2_1 conda-forge
icu 72.1 h63175ca_0 conda-forge
idna 3.4 pyhd8ed1ab_0 conda-forge
importlib-metadata 6.8.0 pyha770c72_0 conda-forge
importlib-resources 6.0.0 pyhd8ed1ab_1 conda-forge
importlib_metadata 6.8.0 hd8ed1ab_0 conda-forge
importlib_resources 6.0.0 pyhd8ed1ab_1 conda-forge
inflate64 0.3.1 py39ha55989b_3 conda-forge
intel-openmp 2023.1.0 h57928b3_46319 conda-forge
itsdangerous 2.1.2 pyhd8ed1ab_0 conda-forge
jinja2 3.1.2 pyhd8ed1ab_1 conda-forge
kiwisolver 1.4.4 py39h1f6ef14_1 conda-forge
krb5 1.20.1 heb0366b_0 conda-forge
lcms2 2.15 h3e3b177_1 conda-forge
lerc 4.0.0 h63175ca_0 conda-forge
libblas 3.9.0 17_win64_mkl conda-forge
libbrotlicommon 1.0.9 hcfcfb64_9 conda-forge
libbrotlidec 1.0.9 hcfcfb64_9 conda-forge
libbrotlienc 1.0.9 hcfcfb64_9 conda-forge
libcblas 3.9.0 17_win64_mkl conda-forge
libclang 16.0.3 default_h8b4101f_1 conda-forge
libclang13 16.0.3 default_h45d3cf4_1 conda-forge
libdeflate 1.18 hcfcfb64_0 conda-forge
libffi 3.4.2 h8ffe710_5 conda-forge
libglib 2.76.4 he8f3873_0 conda-forge
libhwloc 2.9.1 h51c2c0f_0 conda-forge
libiconv 1.17 h8ffe710_0 conda-forge
libjpeg-turbo 2.1.5.1 hcfcfb64_0 conda-forge
liblapack 3.9.0 17_win64_mkl conda-forge
libogg 1.3.4 h8ffe710_1 conda-forge
libpng 1.6.39 h19919ed_0 conda-forge
libsqlite 3.42.0 hcfcfb64_0 conda-forge
libtiff 4.5.1 h6c8260b_0 conda-forge
libvorbis 1.3.7 h0e60522_0 conda-forge
libwebp 1.3.1 hcfcfb64_0 conda-forge
libwebp-base 1.3.1 hcfcfb64_0 conda-forge
libxcb 1.15 hcd874cb_0 conda-forge
libxml2 2.10.4 hc3477c8_0 conda-forge
libxslt 1.1.37 h0192164_0 conda-forge
libzlib 1.2.13 hcfcfb64_5 conda-forge
lxml 4.9.2 py39h0942119_0 conda-forge
m2w64-gcc-libgfortran 5.3.0 6 conda-forge
m2w64-gcc-libs 5.3.0 7 conda-forge
m2w64-gcc-libs-core 5.3.0 7 conda-forge
m2w64-gmp 6.1.0 2 conda-forge
m2w64-libwinpthread-git 5.0.0.4634.697f757 2 conda-forge
markupsafe 2.1.3 py39ha55989b_0 conda-forge
matplotlib-base 3.7.1 py39haf65ace_0 conda-forge
mkl 2022.1.0 h6a75c08_874 conda-forge
mrio_common_metadata 0.2.1 pyhd8ed1ab_0 conda-forge
msys2-conda-epoch 20160418 1 conda-forge
multiprocess 0.70.14 py39ha55989b_3 conda-forge
multivolumefile 0.2.3 pyhd8ed1ab_0 conda-forge
munkres 1.1.4 pyh9f0ad1d_0 conda-forge
networkx 3.1 pyhd8ed1ab_0 conda-forge
numpy 1.23.5 py39hbccbffa_0 conda-forge
openjpeg 2.5.0 ha2aaf27_2 conda-forge
openpyxl 3.1.2 py39ha55989b_0 conda-forge
openssl 3.1.1 hcfcfb64_1 conda-forge
packaging 23.1 pyhd8ed1ab_0 conda-forge
pandas 2.0.3 py39h1679cfb_1 conda-forge
pathos 0.3.0 pyhd8ed1ab_0 conda-forge
patsy 0.5.3 pyhd8ed1ab_0 conda-forge
pcre2 10.40 h17e33f8_0 conda-forge
peewee 3.16.2 py39habf9222_0 conda-forge
pillow 10.0.0 py39ha9166d5_0 conda-forge
pint 0.21 pyhd8ed1ab_0 conda-forge
pip 23.2 pyhd8ed1ab_0 conda-forge
platformdirs 3.9.1 pyhd8ed1ab_0 conda-forge
pooch 1.7.0 pyha770c72_3 conda-forge
pox 0.3.2 pyhd8ed1ab_0 conda-forge
ppft 1.7.6.6 pyhd8ed1ab_0 conda-forge
psutil 5.9.5 py39ha55989b_0 conda-forge
pthread-stubs 0.4 hcd874cb_1001 conda-forge
pthreads-win32 2.9.1 hfa6e2cd_3 conda-forge
py7zr 0.20.5 pyhd8ed1ab_1 conda-forge
pybcj 1.0.1 py39ha55989b_2 conda-forge
pycparser 2.21 pyhd8ed1ab_0 conda-forge
pycryptodomex 3.18.0 py39ha55989b_0 conda-forge
pypardiso 0.4.2 pyhd8ed1ab_1 conda-forge
pyparsing 3.1.0 pyhd8ed1ab_0 conda-forge
pyperclip 1.8.2 pyhd8ed1ab_2 conda-forge
pyppmd 1.0.0 py39h99910a6_1 conda-forge
pyprind 2.11.2 py39hcbf5309_1004 conda-forge
pyside2 5.15.8 py39h56cbfbc_2 conda-forge
pysocks 1.7.1 pyh0701188_6 conda-forge
python 3.9.16 h4de0772_0_cpython conda-forge
python-dateutil 2.8.2 pyhd8ed1ab_0 conda-forge
python-tzdata 2023.3 pyhd8ed1ab_0 conda-forge
python_abi 3.9 3_cp39 conda-forge
pytz 2023.3 pyhd8ed1ab_0 conda-forge
pyxlsb 1.0.10 pyhd8ed1ab_0 conda-forge
pyzstd 0.15.9 py39h95af829_0 conda-forge
qt-main 5.15.8 h2c8576c_11 conda-forge
qt-webengine 5.15.8 h5b1ea0b_0 conda-forge
requests 2.31.0 pyhd8ed1ab_0 conda-forge
salib 1.4.7 pyhd8ed1ab_0 conda-forge
scipy 1.11.1 py39hde5eda1_0 conda-forge
seaborn 0.12.2 hd8ed1ab_0 conda-forge
seaborn-base 0.12.2 pyhd8ed1ab_0 conda-forge
setuptools 68.0.0 pyhd8ed1ab_0 conda-forge
setuptools-scm 7.1.0 pyhd8ed1ab_0 conda-forge
six 1.16.0 pyh6c4a22f_0 conda-forge
soupsieve 2.3.2.post1 pyhd8ed1ab_0 conda-forge
stats_arrays 0.6.5 pyhd8ed1ab_0 conda-forge
statsmodels 0.14.0 py39hbaa61f9_1 conda-forge
tabulate 0.9.0 pyhd8ed1ab_1 conda-forge
tbb 2021.9.0 h91493d7_0 conda-forge
texttable 1.6.7 pyhd8ed1ab_0 conda-forge
tk 8.6.12 h8ffe710_0 conda-forge
tomli 2.0.1 pyhd8ed1ab_0 conda-forge
typing-extensions 4.7.1 hd8ed1ab_0 conda-forge
typing_extensions 4.7.1 pyha770c72_0 conda-forge
tzdata 2023c h71feb2d_0 conda-forge
ucrt 10.0.22621.0 h57928b3_0 conda-forge
unicodecsv 0.14.1 py_1 conda-forge
unicodedata2 15.0.0 py39ha55989b_0 conda-forge
unidecode 1.3.6 pyhd8ed1ab_0 conda-forge
urllib3 2.0.3 pyhd8ed1ab_1 conda-forge
vc 14.3 h64f974e_17 conda-forge
vc14_runtime 14.36.32532 hfdfe4a8_17 conda-forge
voluptuous 0.13.1 pyhd8ed1ab_0 conda-forge
vs2015_runtime 14.36.32532 h05e6639_17 conda-forge
werkzeug 2.3.6 pyhd8ed1ab_0 conda-forge
wheel 0.40.0 pyhd8ed1ab_1 conda-forge
whoosh 2.7.4 py39hcbf5309_7 conda-forge
win_inet_pton 1.1.0 pyhd8ed1ab_6 conda-forge
wrapt 1.15.0 py39ha55989b_0 conda-forge
xlrd 2.0.1 pyhd8ed1ab_3 conda-forge
xlsxwriter 3.1.2 pyhd8ed1ab_0 conda-forge
xorg-libxau 1.0.11 hcd874cb_0 conda-forge
xorg-libxdmcp 1.1.3 hcd874cb_0 conda-forge
xz 5.2.6 h8d14728_0 conda-forge
zipfile-deflate64 0.2.0 py39ha55989b_3 conda-forge
zipp 3.16.2 pyhd8ed1ab_0 conda-forge
zstd 1.5.2 h12be248_7 conda-forge
Hi @BenSprech, what do you mean you are the one experiencing this bug? Can you provide the output from brightway of the databases in Python? Are you trying to copy the database between different computers/systems? If so would it be possible to get the environmental information from the original environment?
Sorry for all the questions, but the more information that we can get the better and quicker we can figure out what is going on
Hey @Zoophobus, no problem. I received a database from @mixib that I want to import, but I can't because of the above. I am really not familiar with brightway and python (that's why I use AB). If you give me instructions I'm sure I can provide you with the BW output of the databases.
I guess it would be possible to get the environmental information from the original environment, right @mixib ?
At my end, the import crashes with the AB wizard while it is succesful with the script (as described in my previous comment), in the same environment with:
python 3.9.16 activity-browser 2.7.4 bw2io 0.8.8
The databases preloaded in my project before importing are:
cutoff38, ecoinvent (cut-off) v3.8
biosphere3, version compatible with the ecoinvent version.
Hi @BenSprech ,
could you print the output from bw.databases here?
For the problem with the biosphere strategy could you try inserting the following block before line 8 of the script from @mixib:
from bw2io.migrations import create_core_migrations
create_core_migrations()