Skip to main content
J
John Kirk
Participating Frequently
February 11, 2021
Answered

Transferring Black Stars from an Excel Spreadsheet to an InDesign Table

  • February 11, 2021
  • 3 replies
  • 914 views

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

This topic has been closed for replies.
Correct answer Dave Creamer of IDEAS

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.

 

3 replies

Dave Creamer of IDEAS
Community Expert
Dave Creamer of IDEASCommunity Expert
Community Expert
February 14, 2021

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.

David Creamer: Community Expert (ACI and ACE 1995-2023)
J
John KirkAuthor
Participating Frequently
February 15, 2021

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

Diane Burns
Diane Burns
Inspiring
February 13, 2021

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.

 

J
John KirkAuthor
Participating Frequently
February 13, 2021

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

J
John KirkAuthor
Participating Frequently
February 14, 2021

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

John Mensinger
Community Expert
John MensingerCommunity Expert
Community Expert
February 12, 2021

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?

J
John KirkAuthor
Participating Frequently
February 12, 2021

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.

 

 

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices