A simple spreadsheet problem....

Scripting Forums AppleScript | Mac OS X
#1

I am reading a tab delimited text file that has a header row and only 1 row of data with:


set Spreadsheet to (every paragraph of (read ProcessDATA))
set the rowData to item 2 of Spreadsheet
set AppleScript's text item delimiters to tab
repeat with i from 1 to the count of Spreadsheet
end repeat

I would like to future proof the script for possible order changes in the future, so instead of declaring the value of a specific cell in item 2 as below

display dialog (text item 1 of (item 2 of Spreadsheet))

I would like to get the data based on the header title which will not change even though its order position may.

Is there a way to do this?

#2

I’m not sure that I understand well.
What is supposed to be stored in the header ?
Is it the index of the interesting value of row 2 ?

If it’s that you may use :

set ProcessData to ((path to desktop as text) & "tsv.txt") as alias
set Spreadsheet to every paragraph of (read ProcessData)
set oTIDs to AppleScript's text item delimiters
set AppleScript's text item delimiters to tab
set theIndex to text item 1 of item 1 of Spreadsheet
set maybe to text item theIndex of item 2 of Spreadsheet
set AppleScript's text item delimiters to oTIDs
display dialog maybe

Yvan KOENIG running Yosemite 10.10.5 in French (VALLAURIS, France) vendredi 4 septembre 2015 17:27:12

#3

Thank you Yvan,

I am not sure that I explained myself my spreadsheet is:

for example I would like to use the header title of “Mlsid” to pass the corresponding data in row 1

So even if the order changes to userFirstName and then mlsid I would still get it

I hope that is clearer and once again, thank you

#4

I apologize but for me whose first language is French, it’s always obscure.
To make things even more obscure, you wrote that your table has one header and one row but you posted a table with one header and nine rows.

Yvan KOENIG running Yosemite 10.10.5 in French (VALLAURIS, France) vendredi 4 septembre 2015 17:45:07

#5

Haha, yes I am fairly new to scripting and most of it is self educated so my language may not be correct either :slight_smile:

I have a video automation system that produces a video from each tab delimited text file

my .txt has only 1 row of data and 1 header with tab seperation

the header names will always be the same for each job.

I would like to place data from row2 based on calling header name

if my spreadsheet is:

then

display dialog Mlsid

would show “98593275” even if the column of Mlsid is in column 4

Thanks!

#6

Thanks. It seems that this time I understood.

set ProcessData to (path to desktop as text) & "txt.txt"
set {theHeaders, theDatas} to paragraphs of (read file ProcessData)

set theKey1 to "Mlsid" # first item to extract
set theKey2 to "UserMobile" # 2nd item to extract

set oTIDs to AppleScript's text item delimiters # save the original TIDs
set AppleScript's text item delimiters to {tab} # define the new TIDs

set nbColumns to count text items of theHeaders # extracts the number of columns

repeat with i from 1 to nbColumns
	if text item i of theHeaders is theKey1 then
		set theMlsid to text item i of theDatas # extracts the first value
		exit repeat
	end if
end repeat

repeat with i from 1 to nbColumns
	if text item i of theHeaders is theKey2 then
		set theUserMobile to text item i of theDatas # extracts the 2nd value
		exit repeat
	end if
end repeat

set AppleScript's text item delimiters to oTIDs # restore the original TIDs

{theMlsid, theUserMobile}

Yvan KOENIG running Yosemite 10.10.5 in French (VALLAURIS, France) vendredi 4 septembre 2015 19:54:44

#7

Yvan,

Thank you so much! This is perfect and I cannot thank you enough!

You have made my day and week.

Once again, Thank you!

#8

Thanks for the feedback.

Yvan KOENIG running Yosemite 10.10.5 in French (VALLAURIS, France) vendredi 4 septembre 2015 20:29:27