Why importing processes from Excel does not work?

Question

Dear openLCA community,

i would like to export a process in excel to make some changes which would take forever in the openLCA front-end editor. When exporting a process in openLCA to Excel, a .xlsx file is generated. However, when i try to import such a file, regardless of whether I've made changes or not, openLCA seems busy but at the end is not importing anything. The log files do not show any error. More strange, however, is that when i manually convert the .xlsx to .xls with MS Excel the import of the .xls file works fine. Unfortunately, this is no solution since a lot of data gets lost when converting from .xlsx to .xls.

Does anybody experienced this problem as well and has a nice solution or go around?

If not, is there maybe another solution for not typing in data for processes manually?

Thanks a lot and best regards!

Answer 1

Dear all,

i still dont know the actual cause of the problem but i figured out a go around:

1. Open the database from which you want to export
2. Export one or more processes as a JSON-LD
3. Create a new and empty database
4. Import the JSON-LD to the new database
5. Export the processes as an Excel
6. Now you can make changes in the Excel tables; if you like rename it within the Excel and change the UUID (changing one character is sufficient); save
7. Import the Excel in any database

This works fine for me

Answer 2

Hi Walther,

"forever" is maybe not so nice but I agree that changing many processes via the user interface is not ideal. We use Python and SQL typically for that.

The import of Excel dataset works (so, "Why importing processes from Excel does not work?") is not correct:

Export process

Import process into empty database

Process in the previously empty database.

However, if you make a copy of the excel file and only change some numbers and text, e.g., then it will not be recognised as a new dataset, since the process is recognised by its UUID, and an overwrite or entering a copy of existing elements is only possible with JSON-LD files.

If you enter a different UUID in the excel however, then it will be recognised as a different process dataset, and can be imported (the yellow colour is not needed of course).

(and it is good to also change the name which I didn't do now).

Comments

Dear Andreas,

sry i didn't mean to be harsh, i appreciate your work and openLCA a lot :)

However i followed exactly the instructions from the manual and the process you showed, however the import is still not working. I use the current version of openLCA, but also tried v1.7 and also on linux, however the problem stays persistent. I also checked for different database settings (empty, with reference date) and different sources of processes (ecoinvent, soca, self build processes).

Here is an exemplary log:
INFO - write database configurations to C:\Users\zeug\openLCA-data-1.4\databases.json
INFO - initialize database folder C:\Users\zeug\openLCA-data-1.4\databases\test, create=false
TRACE [15:38:04.669] @org.openlca.core.database.derby.DerbyDatabase><init>>92 - database url: jdbc:derby:C:/Users/zeug/openLCA-data-1.4/databases/test
TRACE [15:38:04.670] @org.openlca.core.database.derby.DerbyDatabase>connect>148 - connect to database: jdbc:derby:C:/Users/zeug/openLCA-data-1.4/databases/test
TRACE [15:38:04.671] @org.openlca.core.database.derby.DerbyDatabase>connect>155 - Create entity factory
TRACE [15:38:04.747] @org.openlca.core.database.derby.DerbyDatabase>connect>158 - Init connection pool
TRACE [15:38:04.748] @org.openlca.app.db.Cache>create>45 - create cache
TRACE [15:38:04.751] @org.openlca.app.db.Cache>close>33 - close cache
TRACE [15:38:04.753] @org.openlca.app.db.Cache>evictAll>79 - evict all from caches
TRACE [15:38:04.754] @org.openlca.core.database.EntityCache$Loader>registerDescriptorDaos>159 - register descriptor DAOs
TRACE [15:38:04.756] @org.openlca.core.database.NativeSql>query>27 - execute query select version from openlca_version
TRACE [15:38:04.759] @org.openlca.core.database.derby.DerbyDatabase>createConnection>201 - create connection: jdbc:derby:C:/Users/zeug/openLCA-data-1.4/databases/test
TRACE [15:38:04.834] @org.openlca.app.db.Database>activate>48 - activated database test with version8
TRACE [15:38:04.860] @org.openlca.app.navigation.NavigationRoot>loadChilds>47 - create database navigation elements
TRACE [15:38:45.412] @org.openlca.io.xls.process.input.ExcelImport>run>29 - import file C:\Users\zeug\Documents\openLCA\exports excel\46c3a9ed-5b97-4951-b687-d31f4c5f14ec_00.00.001.xlsx
TRACE [15:41:00.225] @org.openlca.app.navigation.NavigationRoot>loadChilds>47 - create database navigation elements
TRACE [15:41:00.259] @org.openlca.app.db.Cache>evictAll>79 - evict all from caches

I think it has to be an issue with .xlsx and .xls, also the import command says only .xls (@org.openlca.io.xls.process.input.ExcelImport>run>29 - import file).

Thanks a lot for your help

---

Hi, not a problem with the "does not work" headline, I only wanted to be precise. The log file does not help too much I am afraid, these are not errors. If you put a different UUID in the excel files, as I had described, it should work also for you (I tested with openLCA 1.10.3, Windows 10, Excel 365).

---

Dear Andreas,
the problem also occurs for other users with different configurations  but i could not figure out a final solution yet. However, it works when use new databases directly from soca and ecoinvent .zolca and an empty test-database. Then processes can be imported and exported by excel without any problems. It also works to import processes from these databases to my database (soca + own processes).
But the main issue is that i can not import processes which i exported from my database to any other of the databases. Also changing the UUID is not changing anything.
Is there maybe a specific pattern or checksum for UUIDs?

Thanks a lot for solving this issue

---

Dear Andreas,
Using openLCA 1.10.3 or 1.11.0, I am able to export a process to Excel.
I am also able to re-import it, but it creates a duplicate process.
I expected that it would overwrite the existing process that has the same uuid

Steps to replicate:
- Export an openLCA 1.11 process to Excel (File/Export/Excel/Processes): it creates an excel file for each process. Or use the Export to Excel button on the process page itself.
- Create a new database
- Import one of the excel file created
- Re-import the exact same file
- Observe the process being duplicated in the database

I also tested with an incremented version number.

---

Yes that's correct, in 1.x, the excel import always never updates a process. JSON-LD import allows you to specify wether existing UUIDs should be overwritten. We could add this as an option to the excel import, an update yes/no option. Let us know.