Dear all,
I would like to copy the whole content of a txt file to the system clipboard using a macro. I read some stuff here and there but its is still not clear to me if this is possible. Can anybody provide some hints?
Thanks!
Hallo
What exactly means »txt file« in the context of calc?
…
as often: ask not about you think it can be solved, ask about the initial task which should be solved.
1 Like
Hello,
my text file is a data file with entries organised in rows and columns. It’s basically a csv file with a different delimiter.
Such a files is updated by another process and from time to time I need to read the data and paste it into a calc sheet.
Instead of doing this by hand I was thinking of using a macro which copies the content automatically. The next step would also be to paste the content but for now I would be happy to manage the first part only.
and why do you NOT ask for »how to import .csv into calc« ??
2 Likes
Well, because technically it is not a csv, it’s a structured text file…
… and LibreOffice’s “CSV” filter is technically not “CSV”-specific, but rather for “structured text files”.
Btw, I would also be more than happy to use python as macro language, the problem is that I have python3 installed and need to import pyperclip as module and this seems to fail as soon as I try to run the python script from LO…
Your suggestion to link the text file in a new sheet is intriguing me… I’ll give it a try and record a macro that copies stuff from one sheet to another. One issue that I see is that when using paste special → unformatted text, there’s no way to keep the formulas without them being evaluated (and I don’t want to overwrite the formats…). Conversely, when using paste special → paste special the dates get a weird format with a ’ before the actual date…
Dear all,
I finally managed to get this to work using @mikekaganski’s tip, thanks for it.
I’m using this line to paste the data:
dispatcher.executeDispatch(document, ".uno:PasteUnformatted", "", 0, Array())
Is there any way to get the “Text Import” window that pops up right after this to be automatically accepted instead of clicking the OK button?
Maybe check this out, for a more automated import.
https://forum.openoffice.org/en/forum/viewtopic.php?t=23727
If you wish to import directly to calc, you could try to import your file instead of insert from clipboard - and can possibly even record this as macro.
@lovecraft22 This has been going on for almost two days now. Can you believe that if your question were phrased like this
“An external application generates a text file like this sample_data.txt. I need to get this data in a spreadsheet for further processing like this sample_spreadsheet.ods. I plan to somehow copy the contents of the text file to the clipboard to paste it into the spreadsheet. Is there a better way?”
then in half an hour you would have two solutions - a macro that reads data directly into a spreadsheet, and a step-by-step instruction to link data from an external file and update it on demand or on a timer.
And in another 20 minutes you would have a third solution - a database that uses a text file as a data source and (if necessary) after pre-processing a query, gives the data to your spreadsheet. In one hour you would have three solutions.
Now for almost two days of “talks in general” you have moved one step.
I hope you can understand hints. If not, then I’m hinting again - files with sample input and output data would allow us to answer your question faster and more accurately than an incomplete verbal description.
4 Likes
You can try macro TEST, but usage of system clipboard for this operation isn’t safe. You can work with bigger file and macro will do next commands, but system clipboard could still copy the data from file, so there probably will occur some errors. Or you can try it for more files and it could be also problematic.
global gsClipboard$ 'for System clipboard
Sub TEST
dim sUrl$, s$
sUrl="d:/myfile.txt" 'your file
s=loadFileString(sUrl) 'default encoding is UTF-8
putToClipboard(s)
msgbox getFromClipboard
End Sub
rem -------------- clipboard --------------
Function LclipboardgetTransferData( aFlavor as com.sun.star.datatransfer.DataFlavor)
if (aFlavor.MimeType="text/plain;charset=utf-16") then
LclipboardgetTransferData()=gsClipboard
end if
End Function
Function LclipboardgetTransferDataFlavors()
dim aFlavor as new com.sun.star.datatransfer.DataFlavor
aFlavor.MimeType="text/plain;charset=utf-16"
aFlavor.HumanPresentableName="Unicode-Text"
LclipboardgetTransferDataFlavors()=array(aFlavor)
End Function
Function LclipboardisDataFlavorSupported( aFlavor as com.sun.star.datatransfer.DataFlavor) as boolean
if aFlavor.MimeType="text/plain;charset=utf-16" then
LclipboardisDataFlavorSupported=True
else
LclipboardisDataFlavorSupported=False
end if
End Function
Sub resetClipboard
dim oClip as object
oClip=createUNOService( "com.sun.star.datatransfer.clipboard.SystemClipboard")
oClip.setContents(createUNOListener("Lclipboard", "com.sun.star.datatransfer.XTransferable"), Null)
gsClipboard=""
wait 50
End Sub
Sub putToClipboard(optional s$)
if isMissing(s) then s=""
dim oClip as object
oClip=createUNOService( "com.sun.star.datatransfer.clipboard.SystemClipboard")
oClip.setContents(createUNOListener("Lclipboard", "com.sun.star.datatransfer.XTransferable") ,Null)
gsClipboard=s
End Sub
Function getFromClipboard() as string
dim oClip, oConv, oCont, oTyps, i%
oClip=CreateUNOService("com.sun.star.datatransfer.clipboard.SystemClipboard")
oConv=CreateUNOService("com.sun.star.script.Converter")
oCont=oClip.getContents()
oTyps=oCont.getTransferDataFlavors()
for i=lbound(oTyps) to ubound(oTyps)
if oTyps(i).MimeType="text/plain;charset=utf-16" then
getFromClipboard=oConv.convertToSimpleType(oCont.getTransferData(oTyps(i)), com.sun.star.uno.TypeClass.STRING)
exit for
end if
next
End Function
rem -------------- load file --------------
Function loadFileString(sUrl$, optional sEncoding$) as string 'read whole file to string
if isMissing(sEncoding) then sEncoding="UTF-8" 'default encoding is UTF-8
dim s$, oSfa as object, oTextStream as object, oStream as object, s1$
oSfa=CreateUNOService("com.sun.star.ucb.SimpleFileAccess")
if oSfa.exists(sUrl) then 'the file exists
oStream=oSfa.openFileRead(sUrl)
oTextStream=CreateUNOService("com.sun.star.io.TextInputStream")
oTextStream.InputStream=oStream
if (Trim(sEncoding)<>"") then oTextStream.Encoding=sEncoding
s=oTextStream.readString(array(), false)
oTextStream.closeInput
oStream.closeInput
else 'the file doesn't exist
msgbox(sUrl & " doesn't exists", 16, "Problem with input file")
stop
end if
loadFileString=s
exit function
bug:
bug(Err, Erl, Error, "loadFileString")
End Function