Apologies for naive question but just getting started with DT:
I have a large number of Excel sheets for which I want the data copied into DT. I’d like Column A to be the name of the Rich Text in DT and Column B to be the content of that Rich Text in DT.
Once saved as .csv, can this be done easily by importing into DT?
The answer is yes, I’m pretty sure it can be done. I think it will require a basic script to do, though. If you could post a screenshot of a representative Excel sheet and of the csv to go with it, it would make it easier to give you a fully fledged answer
Are you looking for each line in the Excel sheet to produce an individual RTF named according to An and containing Bn? Or each sheet to produce one RTF, named according to A1 and containing B1 to Bn?
(oh, blast, you won’t be able to post a screen shot because you’re new to the forum; perhaps you could upload and post the links here)
my understanding is that the csv will contain e.g., “apple, green, pear, green, orange, orange, plum, purple” and is to be split into one rtf for each line, so an rtf called “apple” and containing “green”, an rtf call “plum” and containing “purple” and so on; if I am correct then the fact that a csv is plain text has no immediate bearing, or? I’m not completely sure that’s what the OP meant though, which is why I asked for clarification.
Yes, perhaps RTF was a red herring: I would like the ability to add rich text edits later, within DT, but will only be importing plain text. (Though, it will have line breaks. Does that make it RTF?)
Here are two screenshots:
One is the type of Excel sheet I have. It is image file names (in column A) and then the transcription of the image (in column B).
One is a result I did manually: I want the RTF file in DT to be named the image file name from the spreadsheet (i.e., column A) and the transcription of that image to be in the content (thus, drawing from column B).
Ok, that is most certainly scriptable. I will offer a script if nobody else does, but my script will either be cumbersome (using offset of “,” and then redefining the text in each round) or I will need to read up about how to use delimiters in AppleScript. Whilst the latter would do me good, perhaps somebody who actually knows will beat me to it
(edit: I think I can use @pete31post here to put something together)
tell application id "DNtp"
set thetext to "apples,green,pears,greenish yellow,oranges,orange,plums,purple"
set thelist to my tid(thetext, ",")
set n to 0
repeat with theEntry in thelist
if n is equal to 0 then
set theTitle to theEntry
set n to 1
else
create record with {name:theTitle, type:rtf, content:theEntry} in incoming group of database "test"
set n to 0
end if
end repeat
end tell
-- thanks to the resident expert @pete31 for the following section
on tid(theInput, theDelimiter)
set d to AppleScript's text item delimiters
set AppleScript's text item delimiters to theDelimiter
set theOutput to text items of theInput
set AppleScript's text item delimiters to d
return theOutput
end tid
The above results in this (on my test database, it won’t work for you unless you happen to have a database called “test”):
A couple of questions, though: how do you want to trigger the script? Via a smart rule (which reacts to any csv file, for example)? Or as an a toolbar script based on files which you highlight in DT before triggering the script?
I see that your column B contains entries which in themselves contain commas; so you’d need to provide more information on your csv files: which delimiter do they use? If they use commas, you’d need to do a find/replace on your data prior to export, e.g. replacing every comma with “@” or any other symbol which does not otherwise occur in your text. Then the script could be adapted to replace any occurrence of “@” with “,”.
[Edit: From here: Here are the rules Excel uses to determine how it saves CSV files: 1. If your language / region / locale uses comma (,) for decimal separation then Excel will save using semi-colons 2. If it uses a dot (.) full stop or period, then it will delimit with commas (,).]
Instead of the Applescript text item delimiters, my method is to walk through the characters, breaking on comma (unless the comma is enclosed within quotes)
tell application id "DNtp"
try
set thetext to "apples,green,pears,greenish@ yellow,oranges,orange,plums,purple"
set thelist to my tid(thetext, ",")
set n to 0
repeat with theEntry in thelist
if n is equal to 0 then
set theTitle to theEntry
set n to 1
else
set cleanedEntry to my tid(theEntry, "@")
set c to 0
set restoredEntry to ""
repeat with theContent in cleanedEntry
if c is equal to 0 then
set restoredEntry to restoredEntry & theContent
else
set restoredEntry to restoredEntry & "," & theContent
end if
set c to c + 1
end repeat
create record with {name:theTitle, type:rtf, content:restoredEntry} in incoming group of database "test"
set n to 0
end if
end repeat
on error error_message number error_number
if the error_number is not -128 then display alert "DEVONthink" message error_message as warning
return
end try
end tell
-- thanks to the resident expert @pete31 for the following section
on tid(theInput, theDelimiter)
set d to AppleScript's text item delimiters
set AppleScript's text item delimiters to theDelimiter
set theOutput to text items of theInput
set AppleScript's text item delimiters to d
return theOutput
end tid
Seeing as you have the file open in Excel and you only have two columns of data, you could try this…
tell application "Microsoft Excel"
tell active sheet
tell used range
repeat with thisCell from 1 to (count cells) by 2
my createFile(value of cell thisCell, value of cell (thisCell + 1))
end repeat
end tell
end tell
end tell
on createFile(recName, recContent)
tell application id "DNtp"
create record with {name:recName, type:rtf, content:recContent} in incoming group
end tell
end createFile
Yeah - I just like to be good at things, and as far a AppleScript is concerned it’s rather apparent who can and who can’t thanks for the by 2 bit; as you can see from my script, I did solve that lack of knowledge, but knowing how to do it would have produced cleaner code
I learned a long time ago, life isn’t a competitiion. I’m as good as I am today, with emphasis on as I am. I strive to excel (no pun intended ) but don’t compare myself with others. I may use them for inspiration, but not comparision
Don’t you worry, I’m just doing a bit of jolly banter. I enjoy learning from others no end In this case, for example, I completely failed to consider the possibility of extracting directly from Excel - so I like that bit of lateral thinking (which, as is so often the case, was inhibited in me by a fixation error triggered by the mention of exporting to csv). So thanks again for your input - that is how I learn