Quark Forums

I have a catalog with approximately 4000 products in a Quark document. Each product has an existing SKU number. The catalog is being re-printed for a different client and needs ALL the SKU numbers to be changed (everything else stays the same). I have an Excel spreadsheet with two columns of information: Column A has the existing SKU number and Column B has the new SKU number. Is it possible to some how automate Quark's Find & Replace feature (or other feature?) to search for each existing SKU number (column A from the spreadsheet) and replace it with its corresponding new SKU number (column B from the spreadsheet) in the Quark document? Replacing each SKU number manually would be a painfully slow process. I only have regular QuarkXPress...NO Quark Server or Dynamic Publishing Products. Thanks, Jon :-)

Hi, Jon,

I know Hans Haesler (AppleScript guru from Switzerland) has an AppleScript for that. Not sure whether that's a solution for you.

It is called "SuchenErsetzen7x_02.app" and you can find it at http://www.fachhefte.ch/. Maybe somebody in the AppleScript forum can help you.

Best
Matthias

Hi Matthias, Thanks for the quick response! I'd like to try the AppleScript but it all appears to be in German? Do you know if he has an English version of the script? Thanks so much, Jon :-)

 Hans helped me many years ago and I know is English is excellent, so there may be hope!

Actually this really shouldn't be that hard to do - what is your timescale? If you want to send me a sample page of the Quark and the Excel relating to it, I'd be happy to have a look at it and quote you.

If you do a lot of this, there's an XTension called XCatalog which allows you to place 'links' onto your text which mean you can update it from a text file or database. And another called XData which would enable you to flow your text into Quark, automatically format it AND place the links, in seconds. They are made by Emsoftware (www.emsoftware.com). I don't work for them! (but wish I did...)

Hi Jon (hi Emma :-),

Matthias has sent me a link to your request.

The S&R script, which he has recommended, could do the job. But it expects a text file, where each paragraph contains an old number, a separator and a new number.

Some years ago, I have done a couple of scripts which replaced the SKU numbers in an XPress document. I'll have to dig it up. Right now, I don't remember if the script "talked" to an Excel file. But I have done many script where the information is extracted from an Excel document.

I guess that you have already sent some files to Emma. Feel free to send me an XPress document and the corresponding Excel document as well.

Best regards, Hans

Hi Hans, I am just now getting caught up on this. I haven't had a chance to prepare any samples yet but I will work on it early next week and will you a Quark page and an Excel spreadsheet. Thanks so much!! Jon :-)

Hi Jon,

good news: I'm ready! :-)

I have translated the S&R droplet and made a special SKU version of it which handles Excel files as well.

When an Excel document is dropped on the icon of the droplet, the file is opened, the script extracts column A and column B, then it loops through the stories of the XPress project and replaces the old SKU numbers by the new ones.

Hopefully, this will work with real life documents as well as it does with my simple test files...

Best regards, Hans

Hi Hans, That is good news! Thank you so much! Where do I go to download the program? Thanks again! Jon :-)

Hi Jon,

you're welcome, but: easy now! ;-)

Well ... the first version is ready and working with ... my simple test files.

There is no use to send you the droplet, without having tested it with your sample files. As I have explained, today, in a private email exchange.

Meanwhile I have received them and ... the task is not quite clear. I'll contact you by email.

Best regards, Hans

Hi Jon,

I think I've got it. While yesterday it seemed impossible, I have found the way around the many obstacles. :-)

The script had to be rewritten completely, because the replaced SKU numbers have to be marked with a color.

The job has been quite difficult: The Excel file has two columns with 9500+ numbers each. The numbers consist of five to eight digits. And a few are followed (or preceeded) by one or two letters, like "WS".

The XPress project must be opened, Excel must be running. Then drag and drop the Excel file on the icon of the droplet. 

When nothing is selected, all stories of the document are treated. The script checks if the current story contains one SKU number, at least.

When a text box is selected, only the numbers contained in that box are replaced. And when some text is selected, the script tries to replace the numbers of the selection.

The script needs the Scripting Addition "Satimage.osax" for locating the positions of the numbers. For each "old" number the new one is extracted from the list. Then the script replaces the number and applies a red color.

This evening I'll test it with the eight-pages document. And then I'll send you the droplet with some more explanations.

 Best regards, Hans

Everybody:

Hans has been so kind to translate his Search&Replace Script into English and make it public.

You can find it here: http://www.fachhefte.ch/apple_scripts/mac_os_x_e/qxp_8x/

What it does: It takes a text file with search & replace pairs and replaces all of them in one go.
There is a small instruction file included in the SearchReplace_X.sit.

Enjoy!
Matthias

P.S.: And a BIG THANK YOU to you, Hans!

Hi Matthias,

you're welcome. :-)

Just to make it clear: This SearchReplace script is not the one which searches and replaces the SKU numbers, but
the translation of the script SuchenErsetzen which you have mentioned in your reply to Jons request.

Okay, "SearchReplace" might be used for the job. One would just have to save the Excel file into a tab-delimited file. But then the replaced numbers wouldn't be marked with the special color. Well ... perhaps by adding some XPress Tags before and after each new number ...

Best regards,
Hans

I also wanted to thank Hans for his script. His script saved us a lot of time! Thanks again!!!! Jon/Linda