Quark Forums

Platform: Mac and PC
Applications: Quark XPress 5, 6 and 7*, Microsoft Excel
XTensions: none

* See note regarding Quark 7 at step #11

Hi all,

This tips is for those of you that need to import large Excel data (read: that would extend a single page) in Quark XPress. I did supply it in the General Forum before but thought it should reside here as well.

TheThis tip uses the following spreadsheet as an example:

--------|-----A----|------B------|----C----|---D---|...
---1---| Names: | Numbers: | Cities: | ...
---2---| Name_1 |Number_1| City_1 | ...
---3---| Name_2 |Number_2| City_2 | ...
---4---| Name_3 |Number_3| City_3 | ...
---5---| Name_4 |Number_4| City_4 | ...
...
Apologies if this "table" does not show up properly in your browser... The preview from my end is quite acceptable.

To ease the process of importing a large quantity of data into a Quark table, I have seprated the steps into 5 portions:

• Analyze your spreadsheet for table headers and cell data style.
  
• Prepare your Quark "template" with the apropriate number of "masters" that will reflect on you different spreadsheets (if more than one).
  
• Add style sheets definition for all of your data style.
  
• Prepare your Excel document for importing it into Quark.
  
• Put your final document together.

I kniow that this might quite cumbersome at first glance but once you get the gist of it, it becomes quite simple

[list=1:661fc6dedd]In Excel:

For each spreadsheet, do the following;

• Mark down the names of each column.
  
• Determine the style you will want to give the data under those headers.
  In the following example, I have 3 columns named "Names","Numbers"and "Cities", each requiring its own style sheet.
  

In Quark:

Go to the Master page that will contain your first spreadsheet;

Delete any "Auto Text" box on that Master Spread

Create a Table making sure you select the "link cell" feature.

Format The table elements as you wish.

Add a text box (or text boxes) to set the "headers" of your tables (facultative)

Copy the table and headers to the other page (if your are using facing pages).(facultative)

Create a small text box next to the "Chain Icon" at the top of your page.

Link the "Chain Icon" to that box.

Link the first cell of the table to the small text box.

Delete the small text box. (Your table should now be linked to the chain icon.)
Note: Quark 7 will no longer accept a "text cell" as the last member of an automatic text chain. To circumvent this, you will need to link a small text box after your table and make sure the size or inset values will prevent any text from actually be shown in that box. Since the box is too small to contain text the text will flow to the next text box in the chain (which should be the first text cell of the next page).

Repeat steps 8 thru 11 for any other page of that Master Spread. (facultative)

Repeat steps 2 thru 12 for each required spreadsheet.

Go to the first page of your document.

Apply the Master Page (or add a new page based on it)

Create a "Paragraph Style Sheet" for your Names (aptly named "Names")

Create a "Paragraph Style Sheet" for your Numbers(aptly named "Numbers")

Create a "Paragraph Style Sheet" for your Cities(aptly named "Cities")

For steps 16, 17 and 18, you might want to associate a corresponding "Character Style Sheet" the each of the Paragraph Style Sheets";

Back in Excel:
Most of you already know this but Quark allows you to import text containing its own Tags (XPress Tags). That will greatly help you with this task: The only thing you need to do is to append those "tags" to your Excel file prior to saving each spreadsheet as text. You can do this using Excel's Cell Format:

Select a spreadsheet;

Select the column containing the Names;

Set the "Type" value (under "Custom") to "@Names:"@

Slect the column containing the Numbers;

Set the "Type" value (under "Custom") to "@Numbers:"@

Slect the column containing the Cities;

Set the "Type" value (under "Custom") to "@Cities:"@

Save this spreadsheet as "Text (Tab delimited).

Repeat steps 20 thru 27 for each spreadsheet.

Close the Excel document.

In Word:

Open each "tab delimited" files;

Replace every "tab Character" by "<\b>";

Replace every "Paragraph Mark" by "<\b>";

Save your files as text;

Back in Quark:

Apply the proper Master page to the last page of your document. (You might have to add a new page)

With the "Content Tool" select the first text box of your table.

Select "Get Text" form the "File" menu and locate the appropriate Text File.

Make sure that "Include styles" is selected and click "Open".

Repeat steps 34 thru 37 for each text files.

[/list:o:661fc6dedd]
Voilà!
There's a few steps but fairly straight forward ones!

Hope this helps!

the explaination seemed quite correct and I've tried to follow the whole process this morning but I was stopped at point 10: an error message shows, saying that an automatic text box cannot contain text on a master page..

is there a way to go on after this?
thanks for the help

olive:

the explaination seemed quite correct and I've tried to follow the whole process this morning but I was stopped at point 10: an error message shows, saying that an automatic text box cannot contain text on a master page..

is there a way to go on after this?
thanks for the help

That is true... I am curious though, how come you have text in there, where did it come form ? (Step 3 should have dealt with that)

Just wondering

I'm still wondering too... didn't find the answer yet!

olive:

I'm still wondering too... didn't find the answer yet!

The only thing I can think of is that you typed in your column headers into the table itself. If you choose to have table headers, they must be set appart from your table and NOT BE LINKED to any other box.

If you want, you can send me your Quark doc, I may be able to see what is wrong.

Hope this helps!

Hi there,
So this post seemed really promising - up until i got stuck on step 3-4.

i know that this tip was created for 4.1 passport (i think?) and (question 1.) wanted to know if it could still be applied to 7.1 - or if possible an updated tip could be posted?

my dilemna is that i've been spending way too many late nights formatting and reformatting restaurant menus every time they change their prices or items. (question 2.) is there a better way without purchasing a third party ext? possibly in scripting or maybe its even simpler than that.

Thanks!
*j

Michel, this isn't working for me. I'm concentrating on the master page box and text flow, rather than XPress Tags at this stage.

When I do stages 8-10, I seem to lose the internal linking of the table. So when I import, the text goes from the first cell of the table on page 1 to the first cell of the table on page 2, etc.

Is the temporary text box supposed to overcome this? I can't see what function it is performing.

So, I created a text box. I alt-click on Chain tool. Click in the chain icon, click in the small box, click in first cell.

Am I missing something?

Emma:

Michel, this isn't working for me. I'm concentrating on the master page box and text flow, rather than XPress Tags at this stage.

When I do stages 8-10, I seem to lose the internal linking of the table. So when I import, the text goes from the first cell of the table on page 1 to the first cell of the table on page 2, etc.

Is the temporary text box supposed to overcome this? I can't see what function it is performing.

So, I created a text box. I alt-click on Chain tool. Click in the chain icon, click in the small box, click in first cell.

Am I missing something?

You are doing it wrong Emma.

The temporary box is there to save you form having to manually chain all your cells together.

That is a trick used when you need to join two text chains rather then re-linking all the boxes.

• So after having created your linked cells table (Chain 2) you want to link it with the Chain icon (Chain 1).
  
• To link Chain 1 and Chain 2 (in that order) you need to create a temp text box and make it the last member of Chain 1 (this is done in step #9).
  
• Next, you also need to make that temp box part of Chain 2 but you cannot (as you found out) make it the first member, so let's make it member #2 by linking the first Cell TO that temp text box (that is step #10).
  
• At this point, if you look at your text chain it goes from Chain Icon to First Table Cell to Temp Text Box to Second Table Cell to Third, etc.
  
• The only thing left to do is to delete the temp box to remove it from the chain (Step #11).
  

Hope this help!

OK, I've got you now. So my master page shows a nice table, and the chain links to it and the cells link each to the next. But NOW, when I import my text, it is no longer creating a new page to take the overflow.

Auto insert at end of story is on. Even if I option drag a new page, with the cursor in a cell, link to current chain is grayed out.

It's odd, because the master page looks completely right now. Is this where Migman had got to (see my original thread)?

Emma:

OK, I've got you now. So my master page shows a nice table, and the chain links to it and the cells link each to the next. But NOW, when I import my text, it is no longer creating a new page to take the overflow.

Auto insert at end of story is on. Even if I option drag a new page, with the cursor in a cell, link to current chain is grayed out.

It's odd, because the master page looks completely right now. Is this where Migman had got to (see my original thread)?

You are right, Q7 broke this somehow. I've amended the tip tip to show the workaround for this situation.

Thank you for pointing this out!

Hi Scripting_Ace,

Thanks for the insight into producing mail merge. I have tried your method and are pondering over step 22 – 26. I'm not very familiar with Excel but can't find Number Format under Custom.

My base requirements are two text fields (text boxes) in Quark. One for the name and other registration number of 7 characters. I've produced an Excel file but don't think I have successfully opened it in Word. All the individual cells in Excel has come out as tabs and paragraph breaks in Word. I'm having to change these individual by replacing them with <\b>.


Can you help?

Oscar

tse:

Hi Scripting_Ace,

Thanks for the insight into producing mail merge. I have tried your method and are pondering over step 22 – 26. I'm not very familiar with Excel but can't find Number Format under Custom.

Sorry about that, I do not where I got "Number Format" from but I I meant to say was (and I edited first tip according to this): Under "Custom" format set the TYPE to "@Name:"@, "@Numbers:"@ and "@Cities:"@. You do have to put the quotes there as you wish to add a text value. The second @ character is there as a wildcard that will be replaced by whatever text value your cell already has.

tse:

My base requirements are two text fields (text boxes) in Quark. One for the name and other registration number of 7 characters. I've produced an Excel file but don't think I have successfully opened it in Word. All the individual cells in Excel has come out as tabs and paragraph breaks in Word. I'm having to change these individual by replacing them with <\b>.

ONce opened in Word, your Tab Delimited Excel file should look like:
@Name:Name 1 TAB @Number:RegNum 1 RETURN
@Name:Name 2 TAB @Number:RegNum 2 RETURN
@Name:Name 3 TAB @Number:RegNum 3 RETURN
etc.

Since you seem to have two boxes by "record", you need to send each "field value" to the next box by inputting the "Go to next box" character ("<\b>") in steps 31 and 32.

Note: you might also need to removed extra quote mark as Excel sometime marks text value as quoted strings when it saves a tab delimited file.

Hope this helps!

I'm stuck at step 30, opening the txt files in Word. When I select the txt file, before I can actually open it, Word prompts me to "Convert from File" and then gives me a long list to choose from. Which one do I choose?

I have Word 2002. Do I need some kind of addon to open the txt file I created in Excel so that I can do steps 30-33?

Do I need QuarkConverter? http://www.editorium.com/14846.htm

Hi Indiana627 (is this your dog's name?)

You don't really need to open it in Word. You can open it in any text editor and to the appropriate find and changes.

However, something tells me that you did not save your file as a text file. If I recall, Word should need to convert the resulting text file prior to open, it would if it thought the file was in a strange format.

Hope this helps!