Importing spreadsheet data as layers into Adobe Illustrator

Question

I have a spreadsheet with a TON of content that I need to import to layers via Illustrator. I have several columns that need to be imported for some business cards. So for instance name, email, phone, title.

Is there a way to import this spreadsheet into illustrator so that I can have one layer for each row via setting up some variables in the Illustrator file?

If not, this is going to take a VERY long time. I have about 100 I need to do.

** EDIT **

I initially posted this question in a general manner, using an example of business cards. Some folks came up with a great answer to this question, which was to use InDesign instead of Illustrator. Which in the example of creating business cards would have been the proper way to go. Trying to simplify my query obviously returned the wrong answers, so I'll state the more complex example of what I'm trying to do:

I am creating slides for a video based on data from a spreadsheet. The guys doing the video need the file in illustrator, and they need the data from the spreadsheet on a separate layer per data set.

So that's why I have to use illustrator, and why I have to do it the way that I'm trying.

I got about 75% of the way there by creating variables and data sets using Illustrator. I did a sample test of 5 data sets (Like I said I have about 100 in total), and I was able to create a new layer and paste the data set. But unfortunately, the way the AI actions work, I cannot select the new layer created, so it pastes everything on one layer.

In addition to this issue, I would first have to create an XML file, because the data sets cannot be imported from a spreadsheet. I haven't looked into this, but this would be another process in itself.

So that's where I'm at. Thanks for your help!

Answer 1

It's not impossible, but it would take some legwork. You would need to export the spreadsheet into a format that is easily readable and modified, then translate that into a vector format that has the same attributes.

Luckily for you, both of those exist: CSV and SVG.

Take a look at a very basic SVG file with just some text:

<svg width="252" height="144" xmlns="http://www.w3.org/2000/svg">
<text>
<tspan x="0" y="0" font-family="'Helvetica" font-size="12">Bob Martin</tspan>
<tspan x="0" y="14.4" font-family="'Helvetica'" font-size="12">Head Honcho</tspan>
<tspan x="0" y="28.8" font-family="'Helvetica'" font-size="12">[email protected]</tspan>
<tspan x="0" y="43.2" font-family="'Helvetica'" font-size="12">973-555-6544</tspan>
</text>
</svg>

In Illustrator, this would look something like this:

Now take a look at CSV, the less-hectic of the two:

Bob Martin,Head Honcho,[email protected],973-555-6544
Nick Ferry,Asst. Head Honcho,[email protected],973-555-6544

I don't know how strong your programming background is, but hopefully you can see where this is going. You simply need to tokenize the CSV and inject the values into your SVG "template". You'll probably want to set display="none" on the <text> elements so that the groups are hidden by default when you open the file in Illustrator.

You'll of course have to cater to your specifications, but the following PHP code worked for me:

<?php
$csv = "Bob Martin,Head Honcho,[email protected],973-555-6544\nNick Ferry,Asst. Head Honcho,[email protected],973-555-6544";
$csv_arr = explode("\n", $csv);

echo "<svg version=\"1.1\" id=\"Layer_1\" xmlns=\"http://www.w3.org/2000/svg\" xmlns:xlink=\"http://www.w3.org/1999/xlink\" x=\"0px\" y=\"0px\"\n";
echo " width=\"252px\" height=\"144px\" viewBox=\"0 0 252 144\" enable-background=\"new 0 0 252 144\" xml:space=\"preserve\">\n";

foreach ($csv_arr as $card) {
    $row = explode(",", $card);
     echo "<text display=\"none\">\n";
     echo "<tspan x=\"0\" y=\"0\" font-family=\"'Helvetica\" font-size=\"12\">{$row[0]}</tspan>\n";
     echo "<tspan x=\"0\" y=\"14.4\" font-family=\"'Helvetica'\" font-size=\"12\">{$row[1]}</tspan>\n";
     echo "<tspan x=\"0\" y=\"28.8\" font-family=\"'Helvetica'\" font-size=\"12\">{$row[2]}</tspan>\n";
     echo "<tspan x=\"0\" y=\"43.2\" font-family=\"'Helvetica'\" font-size=\"12\">{$row[3]}</tspan></text>\n";
}
echo "</svg>";
 ?>

Answer 2

Just read your comment saying you need the final output in .ai format. Here's a suggestion, basically, Illustrator to Indesign to Illustrator again.

• Place (File > Place) your Illustrator-format video slide graphics into an InDesign document (without any text), add and format the text placeholders in InDesign (which has better text support than Illustrator anyway)
• Use InDesign's data merge function to fill in the gaps. Here's an Adobe tutorial, quick summary:
  • Prepare a CSV file (can be created in Excel). If you have any entries that are URLs to a file to place, prepend the column name with @ (e.g. the first row might be Name,Job Title,@photograph)
  • Open the Datamerge window (Window > Utilities > Data Merge, or in older versions, Window > Automation > Data Merge)
  • From the Data Merge flyout menu (top right) choose the data source as the CSV.
  • It'll give you a list of fields, one for each column. For each one, click it, then click the text frame or table cell where you want it to go. Toggle between the code and example content with the Preview check box.
  • Use 'Create Merged Document' from the Data Merge flyout menu. Settings will depend on how your layout works.
• You should now have an unsaved InDesign document with one page (or chunk) per line in your CSV file.
• File > Export and choose EPS. This will create a huge pile of Illustrator-compatible EPS files in the chosen directory.
• This might be enough, but if you do need to convert to .ai format, there are two options...
  • Put on some good music or a radio show, take a deep breath, and spend a mindless 20 minutes opening (or importing) each one and saving as .ai format.
  • Create an Action that saves the current document as .ai, and run it as a Batch. If you need a step-by-step guide to using Batch Actions to do this there's one here.

The artboards might be off, but if the person you're sending these for needs to do something else with them anyway, that might not be a problem.

If the artboards are a problem, there are a few different ways you can export each page as a PDF which can then be opened in Illustrator, though depending on which version you are using, PDFs rather than EPS may create more (or, less) compatibility problems:

• There's an InDesign script that lets you export each page as various file formats
• You can export as a PDF then use Acrobat's Extract Pages feature

Answer 3

So this is where I started and didn't fully get me 100% of the way there. I was able to use a tutorial similar to this: http://www.adobe.com/uk/designcenter/illustrator/articles/ill10datadrvn.html - the one I actually used was from the Adobe Classroom in a book from CS3 (yeah it was an old book I had laying around).

The tutorial uses an XML file that you import to create variable data sets and dynamic text areas.

The issue with this is that I'd first have to take my spreadsheet and modify it so that it is in the exact XML format that is outputted from AI when you export your data sets. I didn't bother trying to figure this out, because I had already wasted plenty of time and I wasn't sure if that would even work.

What I did do was use the XML file from the tutorial and I managed to get about 85% of the way done. Here's what I did.

What I tried to do was create a custom action that created a new layer, then pasted the new data set into that layer. unfortunately, the way Illustrators Action works does not allow you to select a new layer. It just skips that step entirely. So what I ended up with was a newly created layer for each data set with all of the data sets pasted onto the original layer.

It was almos there, but not what I needed. In the end I just copied/pasted everything onto new layers manually because it seemed to be the most straightforward quickest way to achieve what I wanted.

If anyone has any idea how to make the actions select a newly created layer then that would have been what I needed, but I couldn't to get that to work.

Anyway, thanks again for all of your input. I appreciate it!

Answer 4

I know that I'm a little late to the party here, but couldn't you simply add a few steps to the end of your action here?

1. Select all
2. Collect in new layer
3. lock all
4. import data
5. lather rinse repeat

Answer 5

This is a pretty late answer, but as it's still unanswered, I'll put it. You can use the script talked about here to import all the variables with ease from a spreadsheet.

But to put each item in a new layer, you will need to use your Actions. You said that Illustrator does not allow you to select the new layer via actions, which is true- so you have to take advantage of how the newly-created layer is already selected. You would need to have your action duplicate your dynamic artwork after it's been populated with new data (you must also keep selecting the original dynamic artwork every time). Then you can use the "Send to Current Layer" command to send your new populated duplicate to the new layer. The Paste-in-Front pastes in the top of a stacking order of a selection, so by also using a Select-All, you can guarantee the new layers will be added above all previous layers.

Or, you could simply use the "Release to Layers" command in the layers' flyout menu to have taken your result from the previous experiment and instantly made it into your desired format.

Answer 6

The only way I know of, is to import your spreadsheet into a Word document, convert into text, and you paste quick as possible your data by using a shortcut of alt-tab to reach the document.