Filemaker Pro to Zotero?

Cowherd

I looked for a way to do this when I started using Zotero in 2014. There was nothing. How'bout now?

I have a FilemakerPro database with some 30,000 records and need to consolidate them into Zotero but not one-by-one.

There is still no Filemaker export format (Tab Separated, csv, DBF, Merge, HTML Table, XML, xlsx) that is also a Zotero import format.

Is there an interim format that could serve as a vehicle for moving records in two or more steps?

Desperate scholar.

bwiernik

Are those all of the FileMaker export formats? What does the XML output look like? Can you export a few items, copy the output to http://hastebin.com, and post a link here?

Cowherd

I have done my best within my limitations as a Mac user. Did something useful show up in the bin? Thank you!

Cowherd

My attempt to "Post a Link":
file:///var/folders/zx/k5n7w6q55llgw78vf0zzh9500000gn/T/com.apple.mail/com.apple.mail.drag/Leaf%20BMW

dstillman

To share via hastebin, you have to paste the text you want to share, click the save button, and then share the unique URL it gives you. What you posted here is just a local link to something on your own computer that we can't see.

Cowherd

Thank you for your patience with my limitations.

How 'bout this?
https://hastebin.com/cowoxavici.sql

bwiernik

There’s no actual item data there.

What we need you to share is the contents of the XML output file. Export it to XML, open it in Notepad (Windows) or TextEdit (Mac), copy the contents to the clipboard, and the upload to hastebin.

Cowherd

Two versions: https://hastebin.com/zowagovuto.sql

and Just text: https://hastebin.com/raw/zowagovuto

bwiernik

That’s SQL output, not XML. You can see that looking at the file extension at the end (.sql not .xml).

Cowherd

The file on my laptop indicates it is xml: file:///Users/robertcowherd/Downloads/Leaf%20BMW.xml

I have copied and pasted this content as "raw" text at:

https://hastebin.com/raw/ucumaromed

Does that work?

With gratitude for your patience.

emilianoeheyns

Can you upload the XML file to https://transfer.sh/ ?

Cowherd

How's this?

https://transfer.sh/Q1Qzv/Leaf BMW

emilianoeheyns

That file is indeed XML but it looks like just a tabular dataset. The column headers are:

date
done
excerpt
flag
Keyphrase
Keywords
pages
passages small
ref class
ref course
ref note
ref reading
ref source
sort
Subject 1
Subject 2
Subject 3
subjects
summary
Sources::footnote
course
error ID
pages sort
Zotero Note

I take it these columns were chosen by you manually? They're not somehouw standardized?

If you have a mapping between these and Zotero item fields (and what type of item you'd want it to become), I might be able to whip something up for you.

What kind of computer (I need to know the operating system) do you use?

emilianoeheyns

The columns "subject 1", "subject 2" and "summary" are numbers. I suspect they're pointers into another table, but that table is not in the XML file.

Can you put up a CSV and XLSX export?

Cowherd

It sounds like you are thinking it is possible to make the transfer, no?

I use MacBook Pro with a current os X.

The filemaker pro database was set up with these headers in 1992. Prior to Zotero. I am able to change the field names to correspond with the destination fields in Zotero if that helps.

I will look at the subject 1, 2 and summary fields and correct them to be text.

Then I will export as CSV and xlsx and upload to transfer.sh site. Does this sound right?

Thank you!

emilianoeheyns

I'm thinking something can be done. But also it's fairly likely you'll have to do manual cleanup (either before import or after) to get the full results you want. The DB looks like it has some fields that are only loosely structured.

Cowherd

The Filemaker Pro database source is actually two relational databases with the Source data in one database in relation to the Notes database in a second database. The structure is such that for each Source item there may be none, one, or multiple items in the Notes database. I am not clear on how this relational arrangement could be maintained in the transfer process. Does that make sense?

emilianoeheyns

It's easier to judge if I have the actual data in hand.

Does https://campsoftware.com/blog/index_files/Export-FileMaker-Tables-to-JSON.php do anything for you? Looking around I don't see any free (or cheap, even) options to convert filemaker pro databases.

Cowherd

CSV dataset with only the most relevant fields: https://transfer.sh/pu212/Leaf BMW.csv

I am now realizing I need to study the Zotero field structure so I can translate my Filemaker Pro fields to match the Zotero fields. Is that available somewhere?

Cowherd

xlsx dataset: https://transfer.sh/smg2t/Leaf BMW.xlsx

emilianoeheyns

That CSV has just 4 columns (without headers), the first of which is a formatted bibliography item. Parsing that out is significant work, you could use one of the options at the bottom of https://www.zotero.org/support/kb/importing_formatted_bibliographies to parse those into structured items, but with 30k items, none of those options are going to be fun for you.

If your database doesn't have separate fields for authors, titles, etc, and everything is preformatted, the options above are all that's available. You don't have separate fields for author(s), title, etcs? The XML file had a field for pages, abstracts and keywords, but not titles or authors or other fields. It did have a formatted version of the item, but as said, that's going to be a fair amount of work for you to pass through tools like AnyStyle.io

Cowherd

Sorry for that. I have full access to the related database and with a little bit of work can bring in each separate field from the Source database as a separate field in my Notes database (AuthorLast, AuthorFirst, EditorLast, EditorFirst, TitleChapter, TitleBook, etc.) This is where it would be useful to understand Zotero's data structure so I can match field names from the start.

emilianoeheyns

Yeah that xlsx is all formatted bibliography items. If that's all that's available to you, you have quite a bit of work ahead of you, regardless of which reference manager you end up using.

I can give it a go running it through the anystyle tools in bulk, but cleanup will still need to be done.

Cowherd

Please press pause on that process. I DO have the separate fields available. I just need to do some work to get my Notes Database to read the separate fields in the linked Sources Database so it can all be brought together into the Notes Database as independent fields and then exported.

It sounds like it might go something like this: Filemaker Pro > xlsx > anystyle.io > Bibtex > Zotero.

Am I getting closer?

Thank you!

emilianoeheyns

I'm not sure if it's documented anywhere, but you can just look at the fields that are shown in Zotero for the item type you want. Any field you see there is available by the name shown there. Here's the list of fields I use, which I think is comprehensive, but I can't guarantee it: https://hackmd.io/JaRdjEh7SoiUMia6K1UIdQ

emilianoeheyns

If you have separated fields, easiest would probably be FMPro > RIS > Zotero

Cowherd

I don't see RIS as an available export format from FMPro.

Just:
Tab separated
CSV
DBF
Merge
HTML Table
FMPro Advanced
XML
xlsx
Is one of these translatable to RIS?

emilianoeheyns

Sorry, I meant FMpro > (whatever structured format has the data you want transferred, which may be xlsx) > RIS > Zotero. If you don't need to put anystyle in there, so much the better, although anystyle is pretty good.

Cowherd

Given my personal limitations, I am stumped on how I might translate the dataset from xlsx into the format required to be interpretable by Zotero as RIS formatted data.

By the way, I have 10,000 to 25,000 records depending on whether I want all my sources, even the ones without notes...

Cowherd

[Wait, could it be I just need to] Use Excel to assign the RIS headers as given in the Wikipedia entry on RIS format[?]

Then export it as [what?]

And import it to Zotero[?]

emilianoeheyns

That's where I come in. I might be able to put together a python script that does that transformation. If the data you need can be put in Tab-separated or commaseparated values format, that means you can run it without doing anything complicated, because MacOS has python already installed. For xlsx you would either have to hand me the full xlsx so I can do the transform for you, or you'd have to install extra python packages to run the script yourself, but if xlsx allows exporting something that can't be put in CSV (I don't know how well FMPro exports newlines in CSV fields for example).

Once that script works, it doesn't matter to me whether it's for 10 or a million records.