Trouble importing file data into database events.

I have made this script to add a record to an AppleScript database events database. The data for the record is right in the script between the curly brackets as in examples that I’ve seen elsewhere. This works fine. The problem I have is that I have prepared a second script to gets this same data from a text file. This script reads the .txt file into a variable called theFileContents. This exact same text is not read correctly by the script. It places in the first field a “{”. it places in the second field a "" in the third field “J”. in the fourth field “o” and so on. I wold appreciate any help. I am also posting the two scripts.

--Script 1
tell application "Database Events"
	tell database "ClientCopay"
		set input to {{"Joe Person", "06/26/14", "06/26/14", "Anthem", "06/26/14", "130.00", "", "", "", "10.00", "20.00", "Cash", ""}}
		set the field_names to {"Client Name", "Dt/Service", "Mo/Service", "Payor", "Da/Claim", "Am/Billed", "Status", "Amt Paid", "Paid Date", "Co-Pay", "Co-Paid", "Action"}
		set theRecord to make new record with properties {name:""}
		tell theRecord
			repeat with record_data in the input
				repeat with i from 1 to the count of field_names
					set the field_name to item i of field_names
					set the field_data to item i of record_data
					make new field with properties {name:field_name, value:field_data}
				end repeat
			end repeat
		end tell
	end tell
end tell

--Script 2

set myfile to ("Macintosh HD:Users:therapy:Desktop:ClientCopay")
set theFileContents to (read file myfile)

set testData to {theFileContents}

tell application "Database Events"
	tell database "ClientCopay"
		set input to {theFileContents}
		set the field_names to {"Client Name", "Dt/Service", "Mo/Service", "Payor", "Da/Claim", "Am/Billed", "Status", "Amt Paid", "Paid Date", "Co-Pay", "Co-Paid", "Action"}
		set theRecord to make new record with properties {name:""}
		tell theRecord
			repeat with record_data in the input
				repeat with i from 1 to the count of field_names
					set the field_name to item i of field_names
					set the field_data to item i of record_data
					make new field with properties {name:field_name, value:field_data}
				end repeat
			end repeat
		end tell
	end tell
end tell

Hi,

the text file seems to contain just plain text, so read file puts the whole text into the variable theFileContents.
Then you coerce the text to list with the curly brackets.
Later you iterate (repeat with i from . ) thru this list. In this case item i of input means character i of input.

instead of the expected

{{"Joe Person", "06/26/14", "06/26/14", "Anthem", "06/26/14", "130.00", "", "", "", "10.00", "20.00", "Cash", ""}}

the variable theFileContents contains

"{{\"Joe Person\", \"06/26/14\", \"06/26/14\", \"Anthem\", \"06/26/14\", \"130.00\", \"\", \"\", \"\", \"10.00\", \"20.00\", \"Cash\", \"\"}}"

Can you suggest a way around this?

try this, but it would be easier to save the text file in an appropriate format

set fileData to read file "Macintosh HD:Users:therapy:Desktop:ClientCopay"
set {TID, text item delimiters} to {text item delimiters, ", "}
set theList to text items of text 3 thru -3 of fileData
set text item delimiters to TID
theList

Thank you for your help StefanK. I altered my script to create a text document with each item in a paragraph. I then imported the paragraphs from that text as a list and it works correctly. I’m including my corrected script.

set the clipboard to {}

tell application "Microsoft Excel"
	set colStart to (get first column index of selection)
	set rowStart to (get first row index of selection)
	set colCount to (get count columns of selection)
	set rowCount to (get count rows of selection)
	select row rowStart
	copy range selection
end tell

tell application "BBEdit"
	activate
	make new text document
	paste
	replace "\\t\\t\\t\\t\\t+" using "\\t" searching in text 1 of text document 1 options {search mode:grep, starting at top:true, wrap around:false, backwards:false, case sensitive:false, match words:false, extend selection:false}
	replace "\\t" using "," searching in text 1 of text document 1 options {search mode:grep, starting at top:true, wrap around:false, backwards:false, case sensitive:false, match words:false, extend selection:false}
	replace "," using "\\r" searching in text 1 of text document 1 options {search mode:grep, starting at top:true, wrap around:false, backwards:false, case sensitive:false, match words:false, extend selection:false}
	save text document 1 to file "Macintosh HD:Users:therapy:Desktop:ClientCopay" without saving as stationery
	close project window 1 saving no
end tell

set myfile to ("Macintosh HD:Users:therapy:Desktop:ClientCopay") as alias

set myList to every paragraph of (read myfile)

tell application "Database Events"
	tell database "ClientCopay"
		set input to {myList}
		set the field_names to {"Client Name", "Dt/Service", "Mo/Service", "Payor", "Da/Claim", "Am/Billed", "Status", "Amt Paid", "Paid Date", "Co-Pay", "Co-Paid", "Action"}
		set theRecord to make new record with properties {name:""}
		tell theRecord
			repeat with record_data in the input
				repeat with i from 1 to the count of field_names
					set the field_name to item i of field_names
					set the field_data to item i of record_data
					make new field with properties {name:field_name, value:field_data}
				end repeat
			end repeat
		end tell
	end tell
end tell

from Excel to AppleScript list there is a very easy way without BBEDit, clipboard and writing files

tell application "Microsoft Excel"
	set selectedItems to value of selection
end tell

for example when you select A1:C3 the script returns

{{value of A1, value of A2, value of A3}, {value of B1, value of B2, value of B3}, {value of C1, value of C2, value of C3}}