How to use an excel list to search and replace in Quark docs

Discuss AppleScripting for QuarkXPress 10, 9 & 8 (and before)
Scripting_Ace
Posts: 684
Joined: 29 Jun 2004, 13:14

How to use an excel list to search and replace in Quark docs

Post by Scripting_Ace » 21 Jul 2009, 17:30

Try this:
001-- Quark docs
002set QFolder to "Please locate the folder containing your Quark documents..."
003set Doc_Folder to (choose folder with prompt QFolder) as [b]text

004[b]set [b]theFiles [b]to {}
005[b]tell [b]application "Finder"
006[b]tell [b]folder [b]Doc_Folder
007[b]repeat [b]with [b]i [b]from 1 [b]to [b]count [b]of [b]files
008[b]if [b]file type [b]of [b]file [b]i [b]is "xprj" [b]then
009[b]set [b]end [b]of [b]theFiles [b]to [b]file [b]i [b]as [b]text
010[b]end [b]if
011[b]end [b]repeat
012[b]end [b]tell
013[b]end [b]tell

014-- Search Items
015[b]set [b]QFile [b]to "Please locate your Tab Delimited Text file..."
016[b]set [b]Search_File [b]to ([b]choose file [b]with prompt [b]QFile) [b]as [b]text

017[b]set [b]Search_Rows [b]to [b]read [b]file [b]Search_File [b]using delimiter {[b]return}
018[b]set [b]Search_Items [b]to {}
019[b]repeat [b]with [b]This_Row [b]in [b]Search_Rows
020[b]set [b]end [b]of [b]Search_Items [b]to [b]my [b]GetTextItem([b]This_Row [b]as [b]text, [b]tab, 0)
021[b]end [b]repeat

022--Process
023[b]tell [b]application/font> "QuarkXPress Passport"024activate025repeat with oneFile in theFiles026open file oneFile027tell document 1028repeat with ThisPair in Search_Items029set {searchstring, replaceString} to ThisPair as list030tell every story031try032set (every text where it is searchstring) to replaceString033end try034end tell035end repeat036end tell037close document 1 saving yes038end repeat039end tell040on GetTextItem(ThisString, ThisDelim, ThisItem)041-- ThisString -> String to look in042-- ThisDelim -> Text element that delimit the string043-- ThisItem -> Number of the element to return (0 for all)044copy the text item delimiters to OldDelims045set the text item delimiters to ThisDelim046if class of ThisItem is list then047set fromItem to (item 1 of ThisItem) as integer048set toitem to (item 2 of ThisItem) as integer049set arrItem to (text items fromItem thru toitem of ThisString)050else051set arrItem to every text item of ThisString052end if053set the text item delimiters to OldDelims054if class of ThisItem is list then055return arrItem as text056else057if ThisItem is not equal to 0 then058return (item ThisItem of arrItem) as text059else060return arrItem -- return every items061end if062end if063end GetTextItemNotes: The line numbers included with this script are there to aid future discussions. In order to use this script, you will have to strip all of them.HTH

JLMahurin
Posts: 0
Joined: 14 Jul 2009, 05:54

How to use an excel list to search and replace in Quark docs

Post by JLMahurin » 23 Jul 2009, 17:59

Wow... you must eat applescript cookies with every meal ... I was tripping all over myself trying to see how to tie this together ... I think I'll get in the back seat on the bus and drool ... Unworthy to sit near the front of the bus...I can read what your doing and it makes so much sense the way you have noted it. I think perhaps I did not ask the original question properly.the original numbers in the Quark file are this:mix letters and numbers (tab) six specific numbers (tab) @random numbers (return)repacing with excel/ text file containing as follows: (tab) same six specific numbers (tab) @different random numbers (return)like this as an example:Quark textboxTerriersBark123ruff (tab) 123456 (tab) @speak23421 (return)Excel ROW (tab) 123456 (tab) @ meow24 (return)the only common parts are first "(tab) 123456 (tab) @" then ending with the line as a (return)I tried the script and it seems to be looking for the same string to find and to replace... am I reading it wrong?... I just purchased the 1,2,3 Applescript book by SAL for reference, but it is only helping to barely understand your statements ... After this I think I should start looking for much simpler things to do as scripts... I can see this one is over my head by several hundred feet.Thank you for your patience and understanding.

Scripting_Ace
Posts: 684
Joined: 29 Jun 2004, 13:14

How to use an excel list to search and replace in Quark docs

Post by Scripting_Ace » 24 Jul 2009, 04:17

Now it appears that you are seaching from strings that contain tabs. Since we are using TABS to delimit what should be searched for and what should be changed with, it does create a "situation"
There are couple of ways you can go around that:
if you are seacrhing for A tab B whishing to replace with X tab Y then you should set up your Excel doc as a four column sheet where texts A, B, X and Y corresponds to columns 1 through 4. You will need to change the script at line 029 and insert these new lines 030 and 031:

029 [b]set {[b]searchstringA, [b]searchstringB, [b]replaceStringX, [b]replaceStringY} [b]to [b]ThisPair [b]as [b]list
030 [b]set [b]searchstring [b]to [b]([b]searchstringA &[b] [b]tab[b] & [b]searchstringB) [b]as [b]text
031 [b]set [b]replaceString [b]to [b]([b]replaceStringX[b] &[b] [b]tab[b] & [b]replaceStringY[b]) [b]as [b]text
Or, you could insert a special character into your current Excel sheet that will be used in lieu of TABs something like "{tab}" (must be a string that you would not normally see in your text). With this done and your Excel file saved as a Tab Delimited text file. You will need to ad the following lines after line [b]029.

030[b]set [b]searchstring [b]to [b]my [b]FindReplace("{tab}", [b]tab, [b]searchstring)
031 [b]set [b]replaceString [b]to [b]my [b]FindReplace("{tab}", [b]tab, [b]replaceString)

and add the following handler at the end of the script
066 [b]on [b]FindReplace([b]FindWhat, [b]ReplaceBy, [b]ThisString)
067 [b]copy [b]the [b]text item delimiters [b]to [b]OldDelims
066 [b]set [b]the [b]text item delimiters [b]to {[b]FindWhat}
069 [b]set [b]TempList [b]to [b]every [b]text item [b]of [b]ThisString
070 [b]set [b]the [b]text item delimiters [b]to {[b]ReplaceBy}
071 [b]set [b]NewString [b]to [b]TempList [b]as [b]text
072 [b]set the text item delimiters to OldDelims073 return NewString074 end FindReplaceHTH

moneyslinger
Posts: 3
Joined: 31 Jul 2009, 13:27

How to use an excel list to search and replace in Quark docs

Post by moneyslinger » 15 Jul 2010, 01:39

Hello Scripting Ace,Just found your posts. I'm a designer and doing a Quark catalog. Need to do basic search and replace for sku numbers. I'm not a apple script guy (would love to be but I have no more time ;). I copied your original code and pasted into Applescript Editor. I played around and got it to actually run!!!!! When prompted I choose a folder, then choose tab delimited (two column) text file I saved from excel. First number is original sku, then tab and then replacement number. Basic stuff i believe.But then I get this error: error "QuarkXPress got an error: Cant make some data into the expected type." number -1700 to itemIt seems to be on the "open file oneFile" or line 026 of your original post. Is that because it did not find/match the first sku??If you can tell how to fix this I would be happy to post this script up here for everyone if i can get it to run.this is a 250 page catalog...i'll even pay for a working script ;))) FYI: catalog is actually in 10 separate quark section files that I will be doing a S/R using one master txt replacement source file that holds all the old sku/new sku replacement numbers. So it will not find/match a lot of numbers for each section. Thanks in advance....Lee

Scripting_Ace
Posts: 684
Joined: 29 Jun 2004, 13:14

How to use an excel list to search and replace in Quark docs

Post by Scripting_Ace » 15 Jul 2010, 02:25

moneyslinger wrote:Hello Scripting Ace,Just found your posts. I'm a designer and doing a Quark catalog. Need to do basic search and replace for sku numbers. I'm not a apple script guy (would love to be but I have no more time ;). I copied your original code and pasted into Applescript Editor. I played around and got it to actually run!!!!! When prompted I choose a folder, then choose tab delimited (two column) text file I saved from excel. First number is original sku, then tab and then replacement number. Basic stuff i believe.But then I get this error: error "QuarkXPress got an error: Cant make some data into the expected type." number -1700 to itemIt seems to be on the "open file oneFile" or line 026 of your original post. Is that because it did not find/match the first sku??If you can tell how to fix this I would be happy to post this script up here for everyone if i can get it to run.this is a 250 page catalog...i'll even pay for a working script ;))) FYI: catalog is actually in 10 separate quark section files that I will be doing a S/R using one master txt replacement source file that holds all the old sku/new sku replacement numbers. So it will not find/match a lot of numbers for each section. Thanks in advance....Lee

Hi Lee,
If you are indee geting this error at line 026 then it has nothing to do with your Tab Delimited file or wether or not your first sku was found.The purpose of line 026 is to open 1 of the Quark file located in the folder you have specified at runtime. In short, it would mean that Quark cannot interpret the content of oneFile a a valid argument for the open command.

What version of Quark XPress are you using?
Can you post your final script here?

moneyslinger
Posts: 3
Joined: 31 Jul 2009, 13:27

How to use an excel list to search and replace in Quark docs

Post by moneyslinger » 15 Jul 2010, 03:05

Thx for the reply...using Quark 8.1.6.2. For some reason my comments are run together. Once I correct that why I will post your script...but i just copied pasted script from our first post.Lee

Post Reply

Return to “QuarkXPress 8, 9 & 10: AppleScript”