Can you link text data to an Illustrator graph using variable importer script?

Question

I have an excel file linked to my illustrator file using the Variable Importer script found here: https://github.com/Silly-V/Adobe-Illustrator/blob/master/Variable%20Importer/VariableImporter.jsx

My data is set up like this:

Without splitting up the spreadsheet, I would like to achieve the following result using a bar graph in Illustrator:
My first idea was to try linking the variable HC_current within the Illustrator chart (so rather than showing "328" it would say "HC_current" and the data would cycle through). This did not work.

Has anyone come across this before? Any ideas on how to link text data to a chart in Illustrator? Your help is appreciated!

Answer 1

So here's another different way to create a data-driven single-bar graph inside of Illustrator. In summary, it uses an art structure of strategically-grouped text frames along with text-wrap and a monospace font, and added appearance effects to create a decimalized single-bar graph.

Here is what the structure looks like:

Layer View:

Outline View:

Let me describe what's going on here.

Structure

This is a system composed of grouped text frames. The text frames are the exact same size, and in my example were a result of arbitrary choice in my font width of a single character which ended up making for an embarrassingly long text frame width.

The hierarchy is such that the textframe called "hundreds" is topmost in the stacking order and is also most deeply nested in the grouping; yet another containing group contains the afore-mentioned group, adding on the 'tens' textframe. Finally, the 'ones' textframe is contained in the outermost containing group (in my example called "Single Bar") with the hundreds and tens group just previously described. The reason for this setup is to enable the use of Text Wrap to nudge monospace characters rightward across their textframes - which are all the same width and stacked on top of each other in the system.

Text Wrap

This very old-school feature is seeing great resurgence in my experimentations. In this case, it's the backbone of how it works. The groups which contain the 'hundreds' text frame as well as the one which has the 'hunds wrap' group plus the 'tens' textframe both have Text Wrap applied to them via the Object > Text Wrap > Make menu item.

Now when text is entered into one of these frames which is not the 'ones' textframe, it should nudge over the text inside the text frames which are below it in the stacking order.

Since the three very wide textframes are positioned exactly on top of each other in their xy coordinate location, it can be very tedious to enter new text into all of them to preview the graph. That's why it would be necessary to make use of the variable data feature early on by importing a whole spreadsheet of text-states and then be able to cycle through the datasets inside Illustrator.

Text Width & Monospace Font

This technique uses a monospace font, which has the feature of having all its characters take up the same space and would serve as a helpful visual aid when constructing the specific data format to be used as the input for this project. The magic is all in how added text characters will grow the text by a factor of some amount and how much more readable a monospace font would make when writing data like "123456" to stand for number "6" rather than six of any one character such as "******".

Here is the sample data I used for testing.

Since this example involves three decimal denominations, the hundreds, tens and ones, each of the text frames have their character's width set to the appropriate amount so as to reflect the relationship of one's given decimalization to another's. The width for the characters of the 'tens' text frame are to be 1/10 the width of the 'hundreds' textframe, etc.

Appearance Styling

To create a solid bar appearance on top of the text, an added fill is present on all the groups as well as the 'ones' textframe which have the "Outline Object" and "Convert to Shape" effects placed on them with any added width or height so as to make sure all characters are concealed and the space between characters disappears.

The Outline Object effect will cause the fill of the characters inside the text frame to be recognized as 'having mass' by Illustrator and the characters in adjacent textframes are nudged by the mass of the characters rather than the bounding box of the text frame or other factors.

This way it's possible to work within a completely relative measurement system without having to worry about precise numbers when doing the setup. In this example, I have created the containing visual area for the bar graph at a certain position, width and height, but created the variable decimalized fields from another arbitrary unit. This was not an issue because an added fill to the parent "Single Bar" group with a Transform effect scaled the relatively-sized bar to the landing area.

The Process

If the text character widths are properly assigned and the entire structure is properly grouped & stacked, entering varying amount of (monospace) characters will make the text-based 'bars' grow and shrink from the left side.

The reason it's not simply just textframes on top of one another and grouping is required is due to Illustrator's awkward spacing issues with text-wrap when grouping isn't used. If they aren't grouped at all, or not in the same kind of hierarchy as in this example, Illustrator is bound to create some gap space in the textframes in either all or some situations, such as when a field is empty.

To avoid such issues, use the same layer structure as in the above screenshots.

Special Illustrator Issue

With this setup you will observe that it's possible to produce accurate data representation using the three decimalized textframes for any number 1-1000. However there's a catch when switching to a dataset that has both the hundreds and the ones fields in use.

It becomes apparent that upon switching, the 'ones' field gets sent all the way to the left side, as if the Text Wrap is no longer acting upon it. This is a way of Illustrator telling the eager variable-data user that it is still in control and has a mind of its own, and it doesn't much appreciate being hacked in this way to do the user's bidding.

To solve this, use a workaround to appease Illustrator by pushing more of its buttons - specifically you have to hide and show (in the Layers panel) the group called "hunds wrap". Once it gets hidden and shown, the faulty Text Wrap wherever it is, becomes restored.

Of course pushing extra buttons won't do when exporting a variable document set, so an Action will need to be created to select the "hunds wrap" group (via Attribute notes - these will need to be added to this group) and hide-then-show it. This should make the 'ones' text snap back to the so-called 'front of the line'.

Answer 2

Sorry, the only way charts work, and as Adobe has now itself implemented the VariableImporter methodology on populating variable graphs, is to have all your charts in separate .txt files.

This may seem like a lot of work, but in modern spreadsheet programs there are usually macro/scripting capabilities which would aid in transforming your concise data into the expanded format needed for the Illustrator variable data procedure.

The setup effort for such a tool would pay off especially when similar projects are expected in the future.

In your case you may want to research or ask on extensibility forums for Excel regarding a routine to take each row of an active sheet and save it as a separate .txt file within some directory.

I myself am curious about such needs from users, as such users are rare and I'm interested in taking note of their workflow procedures and spreadsheet formats when it comes to the need of taking one sheet or Excel workbook and creating the separate graph states as individual text files. Originally I thought this would be mostly a case of people wanting to export all Excel graphs to .txt, but maybe it turns out that users are more interested in taking sections of one sheet to create a multitude of graphs.

Answer 3

I believe this video might be useful:

The creator of the video covers not only how to use Illustrator variables but also specifically how to auto-create a bar chart specific to date. He goes over how to arrange the spreadsheet, how to create a CSV file, how to use the Silly-V script (Vasily gave an indepth answer above; he's the creator of a great Illustrator script for importing CSV / spreadsheet data.)

In the video, the creator shows how to use Illustrator variables also for text and images, how to setup an action that can be repeated, etc. If you have multiple data sets (e.g., say one per state, or whatever), you will need to run a batch process on the saved action (e.g., save as PNG, PDF, or whatever), which the video covers.

It is a long video (nearly 1.5 hours), but the creator does a very thorough job of covering several types of Illustrator variables. One drawback of the process is that for graphs, data for each graph has to be saved in a separate text file. So if you have to run the action 50 times, you need 50 uniquely named text files containing graph data.

In this video, that's only one number per file, but if you're going to generate donut charts, for example, you will need each file to contain an n-tuple of data (where n = however many numeric values needed per donut chart).

As for creating donut charts, they're not in Illustrator per se, but the AI template you create can combine a pie chart with a filled circle in a separate layer sitting above the pie chart layer, knocking out the center of the pie chart (visually speaking).