Hi there,
I am a newbie and need help with setting applescript to open text files copy the contents then paste into an excel cell the repeat in with the next text file and paste into a new cell in excel etc… until all the text files content has populated the excel cells.
Any advice would be magnificent.
Steve
Do you have samples of the text files. How big are they? How many rows and columns? Where do you want the second file’s data pasted after the first was finished? etc…
The more specific examples you give, the more we can help.
Thanks for taking your time to reply. I am basically being given pdfs that contain information for VCard.vcf. This information of around 10 lines is then turned into text file. This then needs to be pasted into excel cell where I have already set up formulas in excel to do the rest. The excel document will then be uploaded to a server to generate dynamic qr codes
Hi Did you find a solution? do you still need help?
Hi there,
So far I have got this working.
set theFile to “/Users/steve.vidler/Desktop/pdf to excel test/text/10.txt”
set theText to read theFile
set newName to text 1 thru -5 of name of (info for theFile)
tell application “Microsoft Excel”
activate
set value of cell 10 of column 1 to theText
end tell
So it opens up the path to the text file, copies it and then pasted it into row 1 column 1.
I can replicate this code 150 times and renmae the txt files to sequential numbering, but I am pretty sure I could repeat and increment the numbers.
how many lines are there is a single text file. After you paste it, where do you paste the second file in relation to the first file? i.e. specific row or column
i have managed to bodge the script so it now is working, probably a simpler way of doing it but I just incremented the numbers up to 150 and changed the .txt number and the cell number. It’s huge but it works.
set theFile to “/Users/steve.vidler/Desktop/pdf to excel test/text/1.txt”
set theText to read theFile
set newName to text 1 thru -5 of name of (info for theFile)
tell application “Microsoft Excel”
activate
set value of cell 1 of column 1 to theText
end tell
set theFile to “/Users/steve.vidler/Desktop/pdf to excel test/text/2.txt”
set theText to read theFile
set newName to text 1 thru -5 of name of (info for theFile)
tell application “Microsoft Excel”
activate
set value of cell 2 of column 1 to theText
end tell
set theFile to “/Users/steve.vidler/Desktop/pdf to excel test/text/3.txt”
set theText to read theFile
set newName to text 1 thru -5 of name of (info for theFile)
tell application “Microsoft Excel”
activate
set value of cell 3 of column 1 to theText
end tell etc…
Thanks for you help
Glad that you solved your problem.
Should you need to do something similar, here is a method that loops through all the text files.
FYI, you can format your code by putting 3 backticks (```) in the line above and the line below the code. This makes it easier to read and also to copy and paste as quotes will be straight and not curly, and there will be buttons.
I added class utf to the read in case your files have characters with accents, etc…
use scripting additions
set srcFolder to (path to desktop as text) & "pdf to excel test:text:"
tell application "Finder"
-- create list of text files
set textList to files of folder srcFolder as alias list
end tell
set fc to length of textList
repeat with xx from 1 to fc
set cellDrop to read (item xx of textList) as «class utf8» -- read contents of each text file
tell application "Microsoft Excel"
-- begin dumping at cell 2 to skip heading
set value of cell (xx + 1) of column 1 to cellDrop
end tell
end repeat
You can also deposit the text into a range, which offers more flexibility than a column.
For example:
set value of cell xx of range "D3:F8" to cellDrop
That would place the first text into cell D3 and then cycle through the cells (D3,E3,F3,D4,E4,F4,D5,E5,F5, etc…) as long as your range has at least as many cells as there are texts.
By your sample, each text file contains data that will only occupy one row in Excel after it is pasted, correct?
This works brilliantly, Many thanks.