Quark Forums

Can anyone help on this with Quark 7? Search and replace from an Excel list, line by line, through 20 separate Quark docs (each Quark doc 32 pages long) and replace new item numbers for Quark from Excel. I had been doing this by hand ... 5000 items to search and replace on 20 quark docs. Usually takes days. I want to make this script more automated. Can anyone shine a light on how to make this work.

Here is the Quark search / replace script:

display dialog "Search for this number:" default answer "Search"

set searchstring to text returned of result

display dialog "Replace with this number:" default answer "Replace"

set replaceString to text returned of result

tell application "QuarkXPress"

activate

my SearchAndReplace(searchstring & "", replaceString & "")

end tell

on SearchAndReplace(searchString, replaceString)

set sourceFolder to choose folder with prompt "Choose the folder to process."

tell application "Finder" to set theFiles to files of folder sourceFolder whose kind contains "QuarkXPress"

repeat with oneFile in theFiles

tell application "QuarkXPress"

open oneFile

tell document 1

tell every story

set (every text where it is searchString) to replaceString

end tell

end tell

end tell

end repeat

end SearchAndReplace

this works quite well ... but really need some help getting the list line by line out of excel searching the documents and replacing the text with new text. Thank you in advance for help or suggestions...

 Excel is scriptable, but would a script be able to find the 'search' and 'replace' numbers?

 Does the 'search' string exist in Excel, in the same line as the required 'replace' number?

Could you make a text file of sets of search and replaces, and get the script to run on them?

These are random thoughts, it's hard to visualise exactly what is going on. If you're doing this a lot, look at XCatalog from EmSoftware, which enables you to build 'links' into your Quark file which mean you can update the data from a text file (or an on-lne Filemaker database).

 Sounds interesting, but we need to know more!

Thank you for your reply...

I only do this project once each year. Generally an intern handled the actual search replace, however we have no intern this year and much to do with fewer resources.

The excel rows are formatted exactly as formatted in quark with only a few exceptions...

excel format
0123456 | \t | .123456

quark formats
0123456 | \t | .123456

or

0123456 | \r | .123456

there are quark tables but they only contain this format
0123456 | \r | .123456

thank you in advance ...

 Excel is scriptable but it is rather a pain to do so.

 I would strongly suggest you save your Excel document as tab delimited text file.

Providing that your Excel doc has rows of data in the form of Column A: Old Number,  Column B: New Number, the resulting text file will be formatted as:

Old Number 1 [tab] New Number 1 [return]
Old Number 2 [tab] New Number 2 [return]
Old Number 3 [tab] New Number 3 [return]
...

You can easily covert that into a list of lists using the followinf code:

001   set Search_File to (path to Your_TabDelimited_File) as text

002   set Search_Rows to read file Search_File using delimiter {return}
003   set Search_Items to {}
004   repeat with This_Row in Search_Rows
005      set end of Search_Items to my GetTextItem(This_Row as text, tab, 0)
006   end repeat

007   on GetTextItem(ThisString, ThisDelim, ThisItem)
008      -- ThisString -> String to look in
009      -- ThisDelim -> Text element that delimit the string
010      -- ThisItem -> Number of the element to return (0 for all)
011      copy the text item delimiters to OldDelims
012      set the text item delimiters to ThisDelim
013      if class of ThisItem is list then
014         set fromItem to (item 1 of ThisItem) as integer
015         set toitem to (item 2 of ThisItem) as integer
016         set arrItem to (text items fromItem thru toitem of ThisString)
017      else
018         set arrItem to every text item of ThisString
019      end if
020      set the text item delimiters to OldDelims
   
   
021      if class of ThisItem is list then
022         return arrItem as text
023      else
024         if ThisItem is not equal to 0 then
025            return (item ThisItem of arrItem) as text
026         else
027            return arrItem -- return every items
028         end if
029      end if
030   end GetTextItem

Notes: • 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.

 Now, it is just a matter of opening each Quark document in sequence and stepping through the Search_Items pairs.

HTH

... Thank you very much for the script solution ... I try very hard to figure things out before bothering others ..

I hope to "...learn to fish" ... so I will be able to help others at some point.

Thank you for your time and expertise! One question what does arrItem represent?

 Well, I have been using this handler for ages and it does much more that what I used it for in your sittuation.

The parameter ThisItem can also receive a list of two numbers ({x,y}) in which case, the handler will return items x thru y of the resulting list given the supplied delimiter.

in that context arritem stands for array (of) item(s) or list if you will.

HTH

Thank you for sharing your experience. I am very grateful.

Best regards

JLM

 What my codes does is generate a list (SearchList) containing all your search and replace pairs:

{{Search_Item_1,Replace_Item_1}, {Search_Item_2,Replace_Item_2}, {Search_Item_3,Replace_Item_3},...}

So, for each Quark document you will need to do the following:

 

001   tell application "QuarkXPress Passport"
002      activate
003      repeat with oneFile in theFiles
004         open oneFile
005         tell document 1
006            repeat with ThisPair in SearchList
007               set {searchstring, replaceString} to ThisPair as list
008               tell every story
009                  set (every text where it is searchstring) to replaceString
010               end tell
011            end repeat
012         end tell
013         close document 1 saving yes
014      end repeat
015   end tell

Notes: • 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.

 Note that I did not test this but the it should be very close.

HTH

I have been able to create a text file in the proper format. I have built a script that searches through text boxes separately, though entering the data manually,

Your script generates the list, but I have been unable to join the search replace with the list yours generates and actually search through quark...

I have tried stepping through -- I even changed mine to index and search through each page, text box and document sequentially --- reflect searching through each text box on each page and search (I can do this one at a time manually inserting the required data but it is no faster than doing it by hand)

I can't seem to get the rows of information to propagate a search through the original list ... I don't seem to grasp the "how to" get one source file to work with the search replace functionality because each row has to be searched separately ...

I am, frankly, not understanding how to step through each row of the text file and apply the source row data to the search replace ...

I just do not seem to be able to do this on my own.

If I may ask for more help on this? I am learning a great deal, but I am spending my good Karma points at blazing speeds this week ... I feel like a baby trying to walk and just can't get up off the floor to my feet... JLM

 Try this:

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

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

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

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

022   --Process
023   tell application "QuarkXPress Passport"
024      activate
025      repeat with oneFile in theFiles
026         open file oneFile
027         tell document 1
028            repeat with ThisPair in Search_Items
029               set {searchstring, replaceString} to ThisPair as list
030               tell every story
031                  try
032                     set (every text where it is searchstring) to replaceString
033                  end try
034               end tell
035            end repeat
036         end tell
037         close document 1 saving yes
038      end repeat
039   end tell

040   on GetTextItem(ThisString, ThisDelim, ThisItem)
041      -- ThisString -> String to look in
042      -- ThisDelim -> Text element that delimit the string
043      -- ThisItem -> Number of the element to return (0 for all)
044      copy the text item delimiters to OldDelims
045      set the text item delimiters to ThisDelim
046      if class of ThisItem is list then
047         set fromItem to (item 1 of ThisItem) as integer
048         set toitem to (item 2 of ThisItem) as integer
049         set arrItem to (text items fromItem thru toitem of ThisString)
050      else
051         set arrItem to every text item of ThisString
052      end if
053      set the text item delimiters to OldDelims
   
   
054      if class of ThisItem is list then
055         return arrItem as text
056      else
057         if ThisItem is not equal to 0 then
058            return (item ThisItem of arrItem) as text
059         else
060            return arrItem -- return every items
061         end if
062      end if
063   end GetTextItem

Notes: • 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

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 textbox

TerriersBark123ruff (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.

 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               set {searchstringA, searchstringB, replaceStringX, replaceStringY} to ThisPair as list
030               set searchstring to (searchstringA & tab & searchstringB) as text
031               set replaceString to (replaceStringX & tab & replaceStringY) as 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 029.

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

and add the following handler at the end of the script:

066   on FindReplace(FindWhat, ReplaceBy, ThisString)
067      copy the text item delimiters to OldDelims
066      set the text item delimiters to {FindWhat}
069      set TempList to every text item of ThisString
070      set the text item delimiters to {ReplaceBy}
071      set NewString to TempList as text
072      set the text item delimiters to OldDelims
073      return NewString
074   end FindReplace

 

HTH

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: Can’t make some data into the expected type." number -1700 to item It 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

moneyslinger:

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: Can’t make some data into the expected type." number -1700 to item It 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?

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