Importing Data into Custom Metadata Fields

[killerspots]

Hello everyone,

I am looking for a way to import data into my custom metadata fields from an csv or other transfer-file, maybe even automated in a longer run.

I am keeping data in sync between an excel-file and my DevonThink workspace and I’d like to automate this somehow.

I have an excel-file where I track all my accounting-movements, with date of payments, categories, and other informations. Each individual transfer gets an specific ID-Code in excel. In DevonThink I have created an md-field for this code.

My idea is: When I enter this ID-code in DT, I want a script to look into an external file which I have exported out of excel and fill the other md-fields with the external information out of the excel-file.

Is there something for this already available or would it be necessary to start with a script at the beginning? Can anyone help me with this task?

Many thanks in advance.

[cgrunenberg]

@killerspots

Is there something for this already available or would it be necessary to start with a script at the beginning?

I can’t remember such scripts but of course I can’t remember everything burried in this forum To automate this process it’s probably better to import the CSV file into DEVONthink and use the imported file instead of the original Excel file.

However, my first question would be what’s the goal of this workflow? Initially it sounds like unnecessary redundancy.

[killerspots]

what’s the goal of this workflow? Initially it sounds like unnecessary redundancy.

Funny, this is what my wife always says about new workflows and even when I brought DT into our house.

I don’t like going into different places for different information as long as I can have one place for all information.In DT I can keep the documents, but not in excel. So I’d like to find a way to merge those information from two places together in one place (DevonThink). Since Excel only works as an overview for all my account-stuff and I almost automatically get all information from the outside (banking, transfer informations, etc) into excel, I’d like to keep it that way and keep using it as a transfer-station to get all stuff together and have a nice overview, but push those information on into DT and put informations into the metadata of the actual documents. This way, I have everything in one place.

For some people, this probably will sound like an “unnecessary redundancy”

[rkaplan]

@cgrunenberg

However, my first question would be what’s the goal of this workflow? Initially it sounds like unnecessary redundancy.

This may not be the situation for the original poster, but I can describe a similar situation I am currently working through.

I have a very large SQL database with about 1.5 Tb of data which has been the mainstay of my practice for over a decade. I plan to move that all to DT3 - partly because of better features in DT3 and partly to avoid the cost/effort to operate a dedicated server. The SQL software vendor offers the option to export the files along with XML files describing the folder structure for the files as well as associated metadata.

So I need to figure out a way to take the metadata in the XML files and import it into custom metadata in DT3. I might write Applescript or Javascript to do that directly, or I could do so using a CSV file as an intermediary format if that were to make it easier.

Either way it will certainly require a custom script - but I don’t think that keeping the metadata from the prior database is “unnecessary.”

[cgrunenberg]

@rkaplan

I have a very large SQL database with about 1.5 Tb of data which has been the mainstay of my practice for over a decade.

1.5 TB of data including any binary data (like files) or more or less text-only? In the second case it’s unlikely that this will work unless not all data is stored in one database and not all databases are opened concurrently. Otherwise the search and AI index would require way too much RAM exceeding the possibilities of every Mac out there.

[rkaplan]

@cgrunenberg

1.5 TB of data including any binary data (like files) or more or less text-only? In the second case it’s unlikely that this will work unless not all data is stored in one database and not all databases are opened concurrently.

Almost all of the data are files

And I do plan to divide it among about 2 dozen databases, with only a small number of those ever opened concurrently.

[cgrunenberg]

@rkaplan

Almost all of the data are files

Only (plain) text files or any kind of files?

[rkaplan]

@cgrunenberg

Only (plain) text files or any kind of files?

About 70% PDF files, 15% Word files, and 15% audio files

[killerspots]

Any ideas of how I could start with this?
Many thanks.

[BLUEFROG]

Hold the Option key and choose Help > Report bug to start a support ticket. Include any real world data, screencaps, etc. that may be useful.

[nigelesque]

Hi, I’m just ten days into using DT, very much feeling my way (and already loving it!). I have a query similar to the original post in this thread.

Context: I’m a researcher with thousands of pdfs. I use Zotero as my reference manager (but not for pdf work etc.). Zotero can produce ‘Scannable Cites’ for embedding in any text file, which if exported as an rtf into Word or LibreOffice will turn into a proper citation.

Aim: I want to be able to able to bring Zotero Scannable Cites into a custom metadata field in DT. I can export these from Zotero into a csv file, which includes a column for the filename as it appears in DT.

How can I create a script to bring this information in?

[chrillek]

Can you show a sample CSV, preferably as code, not image, and explain what column(s) should go where?

[nigelesque]

Thanks so much for responding. So here is a sample of four records, (copied in as txt – is that what you meant?).

The aim would be to take the last column (‘Scannable Cite’) and match up to the file with name in the penultimate column (‘Name’). Is that doable?

Key,Item Type,Author,Title,Date,Num Pages,Name,Scannable Cite
UF46F3FJ,book,"Hicks, Stephen R. C.",Explaining Postmodernism: Skepticism and Socialism from Rousseau to Foucault,2004,200,hicks-s-2004-Explaining Postmodernism,"{ | Hicks, 2004 | | |zu:4247823:UF46F3FJ}"
ZVATLPNW,book,"Ahrens, Sönke","How to take smart notes: one simple technique to boost writing, learning and thinking: for students, academics and nonfiction book writers",2017,170,ahrens-s-2017-How to take smart notes,"{ | Ahrens, 2017 | | | zu:4247823:ZVATLPNW}"
F85JTBC3,book,"Brown, David",Tradition and imagination: revelation and change,1999,402,brown-d-1999-Tradition and imagination,"{ | Brown, 1999 | | | zu:4247823:F85JTBC3}"
IWTNI9MV,book,"Ricoeur, Paul",Lectures on Imagination,2024,331,ricoeur-p-2024-Lectures on Imagination,"{ | Ricoeur, 2024 | | | zu:4247823:IWTNI9MV}"

[nigelesque]

…by ‘match up’ I mean insert that text into the relevant custom metadata field.

[chrillek]

So, you’d like to put
{ | Hicks, 2004 | | |zu:4247823:UF46F3FJ}
in the metadata for the file
hicks-s-2004-Explaining Postmodernism

This stand-alone JavaScript code should do the trick. I couldn’t really test it, though, lacking the corresponding records (and time).

It requires the CSV document to be currently open in DT’s front most window. The name of the custom metadata field is “cite”, you can set it to whatever you want in the call to addCustomMetaData.

(() => {
  const app = Application("DEVONthink 3");
  const docWindow = app.viewerWindows()[0];
  const rowCount = docWindow.numberOfRows();
  const columnCount = docWindow.numberOfColumns();
  for (let i = 2; i <= rowCount; i++) {
    const name = docWindow.getCellAt({column: columnCount-1, row:i});
	const cite = docWindow.getCellAt({column: columnCount, row:i});
	const targetRec = app.search(`name: ${name}`);
	if (targetRec.length > 0) {
	  app.addCustomMetaData(cite, {for: 'cite', to: targetRec[0]});
	}
  }
})()

[nigelesque]

Thank you so much for this.

Can you please clarify how I run the script.

(Baby steps!)

[chrillek]

To try it out, create a CSV file with just one entry.
Import your CSV file into DT. That will create a new record. Select it.
Copy/paste it into Script Editor. Then set Script Editor’s language selector in the upper-left corner to “JavaScript”.
Click on the right pointing triangle in Script Editor to run the script.
Check if the record referenced in the CSV contains the correct metadata now.

If you get an error message, open Script Editor’s Protocol by pressing Cmd-3 or clicking on the three horizontal bars at the bottom. Post the content of the “Events” and “Answers” tabs.

If everything works fine, try it out with a larger CSV.

Read up on “Automation” in the DT handbook to learn how to install the script so that you can run it from DT’s script menu.

[BLUEFROG]

@nigelesque

ahrens-s-2017-How to take smart notes

So the Name column is the actual filename of the document in your DEVONthink database?
And you’re using it versus the Title?

[nigelesque]

Thank you again for your help. However, I haven’t yet got this to work.

EVENTS TAB:

app = Application("DEVONthink 3")
app.viewerWindows()
app.viewerWindows.byId(6481).numberOfRows()
app.viewerWindows.byId(6481).numberOfColumns()

Result:

undefined

REPLIES TAB:

app = Application("DEVONthink 3")
app.viewerWindows()
--> [app.viewerWindows.byId(6481)]
app.viewerWindows.byId(6481).numberOfRows()
--> -1
app.viewerWindows.byId(6481).numberOfColumns()
--> -1

Result:

undefined

.CSV UPLOADED

Key,Item Type,Author,Title,Date,PPP,name,scannablecite,Chicago
ZVATLPNW,book,"Ahrens, Sönke","How to take smart notes: one simple technique to boost writing, learning and thinking: for students, academics and nonfiction book writers",2017,170,ahrens-s-2017-How to take smart notes.pdf,"{ | Ahrens, 2017 | | | zu:4247823:ZVATLPNW}","Ahrens, Sönke. 2017. How to Take Smart Notes: One Simple Technique to Boost Writing, Learning and Thinking: For Students, Academics and Nonfiction Book Writers. North Charleston, SC: CreateSpace."

Note that I changed your script from ‘cite’ to ‘scannablecite’ to match the custom metadata identifier.

[chrillek]

And the CSV you imported is currently selected in DT and visible in the window? If it is, there’s no reason that numberOfColumns() returns -1

That gives

app = Application("DEVONthink 3")
	app.viewerWindows()
		--> [app.viewerWindows.byId(6240)]
	app.viewerWindows.byId(6240).numberOfRows()
		--> 4
	app.viewerWindows.byId(6240).numberOfColumns()
		--> 8

As it should
And please post the stuff from the protocol as code, included in three backticks:
```
stuff goes here
```
That’s easier to read than normal text.
I fixed your post accordingly.

@nigelesque

Note that I changed your script from ‘cite’ to ‘scannablecite’ to match the custom metadata identifier

That’s not relevant yet as the script doesn’t even get to that point.