Text delimiter question

So I can get my text file into excel, I am just not sure where to delimit the text, so each text line gets put into a same column rows.

Is there a way to set the excel range to except only the lines that are in a text file? Some may be 3 lines or 20 lines long, all without any punctuation .


set tid to AppleScript's text item delimiters
set falias to (choose file with prompt "Select text file to add to Excel:" without invisibles)
--set theFile to choose file
set theText to read falias
--set newName to text 1 thru -5 of name of (info for falias)
tell application "Microsoft Excel"
	activate
	make new workbook
	--open text file filename (falias as text) origin Macintosh start row 3 data type delimited with return -- (a line feed)
	set value of range "A3:A20" of sheet 1 of active workbook to theText
	save active workbook --in ((path to desktop as text) & newName & ".xls")
end tell
set AppleScript's text item delimiters to tid -- whatever they were before - ALWAYS SET THEM BACK!

Do I have to set the delimeters if i am only asking for them to be set to a specific file?

This is related to this post as well, post #3 explains my outcome wish better, . http://macscripter.net/viewtopic.php?id=45197

Hi Randy,

Not sure what the content of the text file looks like but does this work?


set falias to (choose file with prompt "Select text file to add to Excel:" without invisibles)

set theText to read falias

set the clipboard to theText

tell application "Microsoft Excel"
	set newBook to make new workbook
	paste special (range "A3") what paste values
end tell

I’ve tried it on a sample text file I’ve created and each line gets a separate cell in the same column.
If required tabs can be used to split each line of data into columns.

HTH

Hi TecNik,

thanks . first my textfile . is just 4 AI files in a test folder. This varies from week to week, sometimes up to 20 items.

Km-K2-Pop&Ring prototype
Large pop & Cuff lines
Large pop idea [Converted]
Large pop idea

Your script works, but I have to paste manually, but this is a great start. Just looking into Excel paste options.

I was getting really over complicated, but I am learning about looping, a good thing.


-- DELIMITERS THAT SEPARATE THE ROWS AND COLUMNS OF DATA
-- (YOU CAN ADD OR DELETE ITEMS BELOW)

property delim_List : {"Tab", "Return", ",", ";"}

-- SCRIPTNAME FOR DIALOG BOXES
property script_Name : "Excel CSV/Text File Importer"

-- RESET OF TEXT ITEM DELIMITERS
set AppleScript's text item delimiters to ""

-- CHOOSE LINE DELIMITER
set line_Delimiter to (choose from list delim_List with title script_Name with prompt "How Are Your Records / Row Items Separated?")
if line_Delimiter is false then
	return
else if (item 1 of line_Delimiter) is "Return" then
	set line_Delimiter to return
else if (item 1 of line_Delimiter) is "Tab" then
	set line_Delimiter to tab
end if

-- GET THE FILE CONTENTS
set fileRef to (choose file with prompt "Select text file to add to Excel:" without invisibles)
set file_Contents to (read fileRef)

-- MAKE A LIST WITH EACH ROW OF DATA
set note_Items to {}
set old_Delims to AppleScript's text item delimiters
set AppleScript's text item delimiters to line_Delimiter
set note_Items to every text item of file_Contents
set AppleScript's text item delimiters to old_Delims

set row_Count to (count of note_Items)

set myList to text items of note_Items

tell application "Microsoft Excel"
	activate
	make new workbook
	tell active sheet
		tell used range
			set rc to row_Count
		end tell
		(*
		set lastRow to ((count of rows of used range of active sheet) - 13)
		repeat with myItem in myList -- Loop through the items in the list
			set value of range ("A3:A" & rc) to myItem --Do your method call here ("A3:A" & rc)
		end repeat
		*)
		-- select range "A"
	end tell
	
end tell

row_Count


Cheers, Randy

Hi TecNik,

It helped a lot, thanks, it lead me to this

Success, now to put the pieces together so it opens my formatted Excel worksheet inserts from my text file and closes the file.


set falias to (choose file with prompt "Select text file to add to Excel:" without invisibles)

set theText to read falias

set the clipboard to theText

tell application "Microsoft Excel"
	set newBook to make new workbook
	paste worksheet active sheet destination range "A3"
	
end tell


So I just had to change your paste line,

Cheeers!