Currently using a Word table as a database -- is there a better way to do this?

Question

I am doing some research on minerals for a book. I will eventually need to track about two thousand minerals. This is currently done by a ~800 line Word table where each line more or less looks like this picture

The functionality I need is (a) to be able to sort the table on any set of columns (b) see the visual thumbnail for each line (c) but maintain a full resolution copy of the image (d) and be able to click on any active links in the table entry.

Currently the Word file is ~600MB in size and Word (Office 365 version) is clearly beginning to creak at the seams (the autosave no longer works, for example, and sometimes I am unable to select anything in the document.)

I currently run a python program that reads the Word file and produces a .pdf file because the output I need is to map each line of the table to one output page, with picture, notes, and other attributes appropriately scaled and positioned. The .pdf file is about ~2GB in size, so I run it through a .pdf file shrinker afterwards and get a reasonable ~200MB final output.

So my question is, is there a better way to do this? I did take a quick look at Access, and was unable to figure out how to get it to handle the pictures.

I can't really split the Word file in two because I'd lose the sort functionality. While it's true that once the table is finished, I would not need to sort it any more, I am continuing to add entries to the table still and the sorting allows me to check for several different error types.

So, is there a flag I can set in Word to allow "large" files safely? If so, then is there a way to have the same functionality but have one mineral per page, nicely formatted with appropriate styles? If not, should I switch to some other document software? (Note: I originally had this in Google Docs but that doesn't offer the Sort functionality. Add-ons kind of offered sort but each one broke in one way or another on a much smaller table.)

Answer 1

If you are staying with a Microsoft Office product you should use Excel for this application. Sorting is built-in (see screen clip), the file can be saved directly to PDF, a worksheet can have over 1 million rows, multiple worksheets are standard so you could classify items into various categories on different worksheets. And I’m sure a Python app can be written that would read the Excel data from the workbook.

Answer 2

As you are already familiar with Python, I strongly recommend bringing your project onto Google Sheets with the Google API.

1. Copy and Paste your table into a new Google Sheets table
2. Familiarize yourself with the Sheets API (API key&auth, Python Connection, accessing and reading your table)
3. Expand your Python script to fetch the data from Sheets and generate your PDF

Making Python scripts for Google Sheets is a powerful data science skill nowadays, because Google Sheets is very flexible, efficient and can work like an online database.

Here is a good video tutorial for starters: Google Sheets - Python API, Read & Write Data (YouTube)

Cheers!

Answer 3

The simple answer appears to be that Word tables have the functionality I need (each table row adjusts to content including image, sort is easy, links inside cells work) so I will just have to be careful not to exceed any hidden file size limits.

An alternative would be to find some other good document editor that supports importing from Word accurately.

As long as Excel treats images in cells as objects rather than values, Excel is going to be unusable for my purpose.