Take the 2-minute tour ×
Graphic Design Stack Exchange is a question and answer site for Graphic Design professionals, students, and enthusiasts. It's 100% free, no registration required.

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!

share|improve this question
7  
"Data merge" for business cards would only take seconds in InDesign. Is there a specific reasone you are choosing Illustrator? –  KMSTR Jan 24 '13 at 9:02
    
Assuming you have access to it, I'd strongly recommend following KMSTR's advice: Place (File > Place) your Illustrator-format business card graphics into an InDesign document (without any text), add and format the text in InDesign (which has better text support than Illustrator anyway), then use InDesign's data merge function, which is purpose-designed for cases like yours. –  user568458 Jan 24 '13 at 13:14
1  
Note that an ID data merge also means a much simpler document, since the un-merged doc is a "single layer," and you can selectively merge specific records (one, some, or all). –  horatio Jan 24 '13 at 15:11
    
Well, I'm not actually making business cards. I used that as an example, because I figured it would be one of the most generic examples that others might have had a use for. What I'm doing is creating slides for a video reel. I have a TON of these with about 4 or 5 variables. for each slide. The guys making the video need it in AI with one layer for each set of data. Sorry for being misleading. I was trying to simplify my question, but it seems it caused more confusion. –  Jeremy Miller Jan 24 '13 at 18:36
1  
@JeremyMiller, did it work, though? Even if it was more effort than it was worth, it would still be a good idea to post the steps in case someone else comes across your questions. It's alright to post an answer to your own question if you were able to solve it –  JohnB Jan 25 '13 at 3:15
show 5 more comments

5 Answers 5

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.

share|improve this answer
    
This answer is a pretty low quality. Please consider re-wording to elaborate on your response. –  ckpepper02 Feb 4 at 14:39
add comment

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
share|improve this answer
    
The problem with actions is that it will not create a new layer. I tried doing this, and I was able to create actions to paste everything. The trouble was that the video guys needed everything on separate layers. –  Jeremy Miller Feb 7 at 17:33
add comment

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!

share|improve this answer
add comment

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
share|improve this answer
    
Thanks for the input. I managed to find a better way to save as separate EPS files by creating variables and dynamic content. But this requires creating an XML file to import the data. In order to do that I'd have to create change a spreadsheet into an XML first. Anyway, I was looking for a simple way to do this, turns out there is no simple way! Thanks anyway for the input. –  Jeremy Miller Jan 29 '13 at 21:46
add comment

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">bob@seospam.com</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:

Business Card

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

Bob Martin,Head Honcho,bob@seospam.com,973-555-6544
Nick Ferry,Asst. Head Honcho,nick@seospam.com,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,bob@seospam.com,973-555-6544\nNick Ferry,Asst. Head Honcho,nick@seospam.com,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>";
 ?>
share|improve this answer
    
+1, but two thoughts: first up, while the original asker clearly knows what to do with code like this, what would you suggest for a non-coding designer looking for the simplest way to run this code? Any suggestions that don't involve having to install, figure out and run a local PHP/webserver stack? Also, this code will go wrong if there's a comma within a cell (e.g. addresses, or if a job title was "Founder, CEO and Head Honcho") - maybe use a parsing function like fgetcsv or str-getcsv? –  user568458 Jan 24 '13 at 13:11
    
@user568458, you're right, there are a few reasons why the code I provided would fail. I would certainly advise the use of str_getcsv(). My webserver unfortunately does not support it hence my use of explode() instead. This sort of processing could be done in any language, I merely provided a PHP example for clarity. For the non-coding designer, I would suggest following KMSTR's advice of using InDesign. My route solution requires some customization of the code, anyone looking for a turn-key solution would probably be better off with InDesign. –  JohnB Jan 24 '13 at 13:22
    
Thanks for the tip. I have MAMP running on my machine, but this seems like a ton of work (for me at least) that might take longer than just copying pasting... Mostly because I have no idea how to do any of this you're talking about! –  Jeremy Miller Jan 24 '13 at 18:38
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.