Transferring Black Stars from an Excel Spreadsheet to an InDesign Table

[John Kirk]

Hello,

I am creating a large table in InDesign, with the help of a veteran InDesign user.  I also have some experience with InDesign.  We are having difficulty transferring a certain special character (from 0-5 black stars) when importing data from an Excel file into our InDesign table template.

The stars are used to rate the items listed in the big table.  We tried several approaches, including saving the data as an .xls file and .csv format, before attempting to drop the data into the table.  We have tried several fonts.  Nothing seems to work.  The stars always end up as rectangular boxes in the InDesign table.  All other data (mostly text, a few numbers) transfers properly.

The number of stars shown in the spreadsheet is a result of an REPT function, which takes a number and transforms it into black stars.  For your info, transferring the data into an Apple Numbers program works fine.

Due to the exacting nature of the Indesign table design, data transfer works best if we can execute a simple cut and paste directly from an Excel file, rather than deal with reformatting the table for comma delimited data.  We believe the time and energy spent to manually enter the stars is the same or less than a reformmating effort.

Any ideas are appreciated.  Thanks for your time.

nttconductor

[John Mensinger]

The stars always end up as rectangular boxes in the InDesign table.

When you select those boxes, what font name shows in InDesign's Character fields?

[John Kirk]

Hello John,

Thanks for the response.

I have sent my InDesign specialist a note regarding your question.  I want to verify I am giving you the right response.  I hope she will respond promptly, but I know she is in transit today.

My understanding is she has tried several different fonts, starting with typical text fonts like Garamond and Optima, and is now trying to receive the data with fonts that specifically include the black star, specifically, Wingdings and ITCZapfDingbats.

I will verify ASAP.

[John Kirk]

Hi John,

I did talk with my designer yesterday, and were able to solve the problem using the GREP Find/Change functions as decsribed in the method below.  Thank you very much for your time.

John

[Diane Burns · Accepted solution]

At the very least, you might try a simple Find/Change (brute force) method. After the table is set up and data is in place:

1. In the Find field: select and copy the square rectangles that appear where the stars should be, and paste in the Find field.

2. In the Change field: on the pasteboard, create a text frame with stars, using Dingbats or some font that has stars, copy then paste in the Change To field

GREPers could probably tell you how to do this with a GREP Find/Change in one fell swoop, but you could at minimum create 5 Find/Change sequences for each number of stars.

[John Kirk]

Hi Diane,

Thank you for the suggestion.  I have passed the information along to my designer, and will wait for her comments and then respond.  I  really appreciate both John's and your suggestions.  If we can fix this, it will save time and energy.  I'll be back!

nttconductor

[John Kirk]

Hi Diane,

My designer followed up with me on Saturday, and this brute force Find/Change strategy will work.  This was one of the first methods she tried to solve our problem, but the first attempt led her to believe it wasn't quite working properly, with some minor formatting issues.  But she tried it again with our latest version of the table, and a test sample of 25 entries worked fine.  Thank you so much for your help.  If the problem persists, I will report back to this thread.

Thanks again to John Mensinger for your suggestion as well.

John

[Creamer Training]

You could set up a table style that links to a cell style that links to a paragraph style. The paragraph style could be set to a symbol font.

In Excel, enter the letter that matches the character you want and set the column to the symbol font.

For example, H = the star in Zapf Dingbats Std, 

Your table style can auto-apply a cell style if it's the first or last column. Otherwise, you can select the column and apply the cell style manually. Either method will work if you link to the Excel file and the file updates.

[John Kirk]

Thanks Creamer Training,

As with the other suggestions, I will pass them along to my InDesign design partner, and see whether this sounds like a good solution.  This one sounds pretty good to me.  I can easily use the Find/Replace All function in Excel to transform all black stars into the letter H.  Zapf Dingbats is a font available on my Excel program, though that may be irrelevant.  The point is to send the letter "H" from Excel, which InDesign can then read as "★".

I'll be back to report in a day or two.

nttconductor

[John Kirk]

Hello again CT,

Nothing new to report.  In the words of my designer:

"This is another good suggestion.  I have nothing to add except a hearty thank-you".

We now have at least two good methods to solve our issue with our data table.

nttconductor

[Creamer Training · Accepted solution]

BTW, you can format the H's in Excel to your font so they look like stars too. It won't affect the data import but will give you a better visual in Excel.

I may be biased, but I would rather use the table/cell styles to automate the import process rather than running a find/change each time the data updates. If your designer needs any help with the table styles, feel free to direct message me.

[John Kirk]

Thanks,

My designer is quite busy the next few days, but I expect she will have time to try this approach pretty soon.  I will let you know how it goes.

[John Kirk]

Good morning,

Te Creamer Training solution completely automates the process.  My designer is a "happy camper".

To review the key instructions:

"You could set up a table style that links to a cell style that links to a paragraph style.  The paragraph style could be set to a symbol font.

In Excel, enter the letter that matches the character you want and set the column to the symbol font.

For example, H = the star in Zapf Dingbats Std.

Your table style can auto-apply a cell style if it's the first or last column.  Otherwise, you can select the column and apply the cell style manually.  Either method will work if you link to the Excel style and the file updates."

My job as the Excel spreadsheet owner was to change all the stars into strings of capital H characters.  The character strings are generated in response to a number (0,1,2,3,4,5), so the REPT (text, number) function is used.  Once all the stars were converted to strings of 'H', it did not matter whether I displayed the data in Excel as 'H' (most fonts), or as stras in Zapf Dingbats font.  In both cases, the data transfer t the proper InDesign cells worked without error.

Thanks again for your time.  We're so glad we asked the experts for help!

nttconductor