Help to database issues

GrampsAIO-5.1.3-2_win64 and Windows 10

Hi!

All my source references are written in uppercase. It’s a big job to change this in Gramps.

I have therefore tried to correct directly in Gramps SQLite database with e.g. SQLite Studio or with an ODCB connection to Access, but the fixes do not work in Gramps.

The text is corrected in SQLite, but in Gramps the text is still in uppercase.

Is there anyone who can tell me why, and what I can do about it?

Thanks in advance
Finn Poulsen

Why not edit .gramps file with a text editor, i.e notepad++ ?

I’m going to assume you are directly opening the Gramps SqLite db. The reason your changes are probably not working, is that essentially all the actual data is stored in ‘blobs’, which you probably did not edit (because it would be really hard). So even if you modified one of the columns, nothing would show up in Gramps. They are there to allow easier searching, mostly as a future feature.

As noted, you might have better luck editing the Gramps XML file (.gramps). Save in uncompressed to make editing easier. And then import to a new tree to see if it worked.

If you still want to work with SQL, you could try editing the (experimental) Sqlite export/import addon exported db, and then import it to a new tree again. The format of the db from this addon does not use blobs, but puts everything into columns.

Hi PLegoux and Remskriver

Thank you for your help.

The Gramps file (.gramps) does not seem to be easier to edit in than via the Gramps application. The SQLight Export can apparently not be re-imported into a new “genealogy” book, just nothing happens.

I am an Access nerd and had hoped I could solve my problem with Access.

I have been working with Grampe for about 3 weeks and have spent a lot of time customizing my imported data, but what is missing, such as. converting uppercase to lowercase takes time.

I love Gramps, a shame it’s hard to work with data. I think I’ll probably return to my original program Legacy.

Many greetings
Finn

You may just be a bit early. Gramps only recently added the ability to use different database back-ends. And switching from BSDDB to SQLite just happened in the 5.1 release.

One of the major goals of the 5.2 roadmap calls for changing to “Store objects as JSON rather than pickled blobs”

Finn, when you tried to edit the Gramps XML file, it is important that you create the file using the “Export…” option not “Make Backup…”
then for output format choose Gramps XML (family tree)
for Export Options you MUST clear the check mark for “Use Compression”

then produce the XML file.
It should be much easier to edit.

Note that compressed .gramps & .gpkg (packaged with media) archives can be decompressed into .gramps files (in plain text XML format) using 7zip.

There will be another file inside the archive with the exact same filename as the archive. So, extract into a folder to avoid accidentally overwriting the compressed archive.

Thank you all for the great answers and suggestions.

I had overlooked “Use compression”, if I disable this feature, the XML file is quite correct to edit.
I’ve done that now. My problem is solved with simple search and replace or manual editing and re-imported into a new Gramps database file.

I have chosen not to use other programs to make sure my data gets back correctly in Gramps. If I make a mistake it can be hard to spot and it can ruin my data completely. It could otherwise be an interesting task to get it via Access, but not this time :slight_smile:

I am pleased that there are plans to change the database structure in Gramps. “Pickled blobs” are a very opaque way of storing data and are, in my opinion, not the right way for this kind of important data.

Thanks again. Take care of yourself in this Corona time

Many greetings.
Finn

@StoltHD,

You can do that by exporting from Gramps to SQLite, and then in SQLite export each table to csv. You would only have to write the set of commands once, and then you could run them anytime you need to refresh the CSV files.

You will want to combine some of the files to make them useful. If you know some SQL, you can create your own views (joining the tables) and export those instead. Otherwise you will need to do the joining in whatever application where you import the CSV files.

Some things require more complicated SQL, for example if you have a set of person attributes and you want to pivot the data to output one row per person with a column for each attribute, rather than having one row per combination of person and attribute. Or again, you might do that in whatever application where you import the CSV files.

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.