To save a lot of re-typing, I’m trying to create a new DT RTF files from the text in Excel contents. And I’m also wanting to populate existing DT RTFs with the text from Excel cells. I’m not having any luck, other than a very clumsy work-around I’ve explained below. Are there any simple settings I could modify to allow me to paste just the Excel text into DT?
I’ve done some trial and error:
When trying to make a new file in DT, I get a new .png file in DT when I use Copy/Paste from Excel to New>With Clipboard, same result if I try Drag and Drop from Excel into the DT file list.
When trying to copy text content from Excel into an existing DT RTF; using either Drag and Drop or Copy New>With Clipboard, I get the Excel Cell contents within a Table in DT.
I have a workaround, but it’s clumsy. Copy the Excel Cells>Create New RTF in DT>Paste and Match Style. This produces editable text in the RTF file. If I then want to create a new DT RTF, I can copy the text, then create New>With Clipboard.
Given that Excel is a spreadsheet, it is not surprising that DEVONthink seems to want to treat that input as a spreadsheet, or an image of the spreadsheet.
That being said, specifically what do you want the target in DEVONthink to result in ? Text conversion of the cells smashed together? Tab delimited text? I’m confused, may be me, about what you say in last paragraph:
I get your point about the vagueness of my post. Hopefully this will clarify.
I’m looking at two different targets.
In the first case, I want to create a new RTF in DT, whose name matches the text in the Excel cell. This would be a Copy the Excel Cell, Go into DT , the Right Click>New>With Clipboard. The result being a new file with the same name as the text within the Excel cell.
In the second case, I want to append an existing DT RTF with the text in the selected cells from a particular row in Excel. In the screenshot, DT is to the right. The top right is what I’m going for, editable content where the Excel cells appear as editable text (which I can create by having the Excel data on the clipboard and then Edit>Paste and Match style) The bottom right (which I do not want) is the result of Drag and Drop of the selected cells
And from my previous post, I’ve found that once I have have the text in the RTF, as shown on the attachment, I can copy the line of interest in the RTF, then create a new file, with New>With Clipboard.
Have you tried exporting the Excel file as a csv and then opening that csv with Numbers (but not saving)? I suspect some of the formatting issues you’re experiencing are specifically due to Excel and “what it copies”, but csv is the “basic unit” of a spreadsheet, and I would’ve thought copying and pasting from csv would give you what you need without the formatting nonsense (I’m not at my computer currently so can’t test it, but I work with csv files outside DT fairly regularly and haven’t noticed weird copy/paste shenanigans).
This will likely require one of those “workaround workflows” that people need to sometimes create. This means there will be intermediary steps to transfer the information from Excel into DEVONthink.
I would do this by copying the cells in Excel and then pasting them into BBEdit. BBEdit is a text editor, which means it has no formatting. I tried this in the macOS included TextEdit, but that is an RTF editor and, thus, allows formatting. It’s the formatting attached to the copy that is causing this issue.
BBEdit has a free version, so if you don’t have a text editor, you can download and try it. The steps would be: copy Excel cell(s), paste into BBEdit, Select All, cut (or copy), and paste into DEVONthink.
If this is a one-off project, then this might be the simplest and fastest way to complete it. However, if you plan to do this repeatedly (or have similar issues with other projects), I would suggest investing in BBEdit and setting up a text filter that can be triggered through an app like Keyboard Maestro (KM).
You can set a keyboard command in KM that will copy, remove formatting via BBEdit’s text filter, and place the text in the clipboard, ready for pasting. Thus, your workflow would be hitting the special key command for this, then pasting into DEVONthink. It sounds complicated, but it really isn’t once it’s set up.
I’m sure other apps can achieve similar results. You’ll have to find one that fits into your workflow.
TextEdit has a plain text mode. No formatting just plain plain text.
Not saying people shouldn’t support BBEdit, that thing is a beast and comes in handy in many ways. But if you’re looking for a free solution TextEdit does lots of useful things too.
Ah, great. I don’t use TextEdit, so I wasn’t aware of this. If that’s the case, then this should be able to work as a way to clean out the markup formatting hidden in Excel text.
I’m using Excel for Mac, Version 16.100.2 (25082415) with DT4 on a MacMini M1 2020 running Sequoia 15.6.1. I tried to save my Excel file to the CSV option suggested by https://discourse.devontechnologies.com/u/mslogica, unfortunately the same result as with the.xlsx file format, Copy in Excel, then Paste into DT RTF shows the copied content in a table within the RTF. So, I switched back to the .xlsx version. But I kept on trying.
I found that if I just copied a single cell in Excel and then Pasted it into DT, I got a single line of text in DT. But I verified that as previously, If I copied a range of cells, DT pasted the data as a table. But then I discovered that when I copied the whole table within the DT RTF and then pasted it elsewhere within the RTF, instead of pasting a copy of the table, it pasted the data as lines of text which is just what I wanted, of course then I needed to delete the table in the RTF.
And then I tried another option. I copied the range of cells in Excel, but when I pasted them in DT I used the Paste and Match Style option. Voila, the cell contents were pasted into DT as lines of text, just as I wanted. I’m going to try to stick with this work around as it seems the least tedious
Thanks for your efforts, but I think I found a simpler solution.
I copied the range of cells in Excel, but when I pasted them in DT I used the Paste and Match Style option. Voila, the cell contents were pasted into DT as lines of text, just as I wanted. I’m going to try to stick with this work around as it seems the least tedious
I have TextEdit set to auto-use plain text these days.
TextEdit and the built-in plain text editor in DEVONthink have a handy “select a rectangle of text” function for working with columnar text selections in plain text files
Hold down Option while dragging the crosshairs to select a text rectangle, which you can then cut, copy, paste in a sensible fashion (i.e. it inserts the rectangle of text as a columnar paste).
Preview allows similar selection of columnar text (for example, oftentimes tabular data in Preview doesn’t “select” sensibly, this helps to recreate by pasting into Text Edit, Excel, etc.
BTW, I’ve tended to leave text snippets in an unsaved plain text TextEdit file (which is restored when I reopen TextEdit if I don’t close it before Quitting TextEdit), but given DEVONthink does the same columnar text mangling (which I only just experimented with when composing this post), I may keep the text snippets file/s in DEVONthink now.
I’m trying to move as much of my text composition into DEVONthink (or co-ordinated through it for things like Word files), so it’s great I can bring that text mangling along for the ride!
I deal with a large amount of RTF(D) and plain text files, often using TextEdit to check against DEVONthink behaviors as they both use the same text engine.
Columnar selection is a deep cut most likely have never heard of (and quite a niche feature). I used it in graphic arts and prepress over 32 years, but haven’t had much call for it since.
I actually do compose the majority of my text directly in DEVONthink. Not just because I support and love the app, but because it covers 90% of my editing needs. For the 10%, I have BBEdit, of course, to look at the internals of PDFs or a quicker view of .plist files. I use CotEditor for some specific tasks as the outline and syntax highlighting is powerful and custom setup for me. And though I’m not a big fan of it, I have Visual Studio Code for another duty of mine, mostly since it’s easy to spin up a live webserver in it. Less used, but an old classic SubEthaEdit… for old times’ sake.
