One of the primary functions of Fuga is the ability to work with data from a spreadsheet. Working with data from a spreadsheet is very useful in that it means no more manually inserting repetitive information into your document. You can import data into your document directly, and also import it into the flow so you can use it in actions.
What is a Spreadsheet?
A simple way to think of a spreadsheet is as a highly flexible table which makes organizing large amounts of data easy. Spreadsheets are where Fuga gets the content to put into your InDesign document. Common programs for creating or editing spreadsheets include Excel, OpenOffice, NeoOffice, and Google Docs.
A Simple Spreadsheet Example
Here’s a basic spreadsheet with columns for name, ID number, date of birth, and expiration date:
Accepted Spreadsheet Formats
Fuga accepts files in Excell (.xls) or CSV (.csv) format. You can save or export to either for these formats from any spreadsheet editor. When saving a spreadsheet file as CSV, if you have export options make sure the character set is Unicode, the field delimiter is a comma, and the text delimiter is a quotation mark (these values are normally default).
How Fuga Finds Spreadsheets
To use the data from a spreadsheet in your flow, you need to tell Fuga where the spreadsheet file is located. Each action that uses a spreadsheet file provides the option of browsing for the file, however using this option to locate a spreadsheet can be problematic. This is because it’s likely that the spreadsheet and InDesign file will be moved between folders and computers so the path will be changing. Often times the file path is used in multiple actions in the flow, so when the file path changes it means remembering to go back and change the path in all of them.
To avoid this, we recommend creating a variable which stores the path for the CSV, and then using this variable in the action’s parameters. Then, you only have to update the variable in one place, and it updates across all the actions. There are multiple options for how to set this variable.
Setting the File Path Variable Using the “Ask” Action
The best way to set the file path variable is by using the Ask Action. This way no changes need to be made to the flow when the file path changes, since the file location will be specified each time the flow is performed. Since each computer has a different file structure, this option is essential when you’re building a flow that will be used on different computers, unless the flow is going to be edited on each computer.
Setting the File Path in the Variables List
Sometimes when you are creating a flow, it is inconvenient to have to browse for the spreadsheet file each time in the Ask action. In this case, you can use an action with browse functionality to copy the absolute path for the file, and then paste it as the default value of the variable (remember to take off the quotes, however), and then switch back to the Ask action method later.
How Fuga Works with spreadsheets
There are two main ways that you can use the content from your spreadsheet in Fuga. Using the Load Data action, you can import the data from a spreadsheet directly onto the document. This is best for content that doesn’t need to be used in the flow to make decisions or doesn’t need any special formatting. You can also use the Load Vars action to import data into a variable, in which case it can be used in other actions in the flow and then later put onto the document using other actions.
The Load Data and Load Vars actions only work with one row of the spreadsheet at a time. In many cases you’ll probably want to actually load the whole spreadsheet into the document. Do this by putting the Load Vars and/or Load Data actions into a Data Rows loop, and use the loop variable in the row field (for an example of this, see the examples on the Load Data and Load Vars action reference pages).
Importing Data Directly Into Your Document using Load Data
To tell Fuga to load the data from your spreadsheet directly into your document, use the Load Data action. The Load Data action loads data one row at a time into either XML Tags or objects. When loading data into objects (by their object name), each object to load into must be selected. When loading data into XML Tags, the objects can either be selected or just the object’s parent Super Group can be selected.
Mapping and Load Data
There are two ways that the flow knows which column to load into which tag/object. The first way is by creating Tags with the exact same names as the columns in the spreadsheet. These matches need to be case sensitive. That way, Fuga knows which tag relates to which column. If the tag names aren’t the same as the column names, you need to use the Mapping tab of the Load Data action to tell Fuga exactly which tag to load each column into. Mapping is all or nothing, so if you map one tag to a column you must map the rest of the columns you want to load, even if they have tags that match them exactly. For an example of mapping, see the Mapping Example from the Load Data action reference.
Loading Data into Tags vs. Objects
Simply put, in most cases it is best to Load Data into XML Tags. Using tags allows you to load the data into only a portion of the text in the object. This way you don’t have to create a different text frame for each piece of text you want to load into, since you can have text boxes that contain multiple tags. However, for simple flows, or if you’re not comfortable using tags, you still have the option of loading data into objects.
Importing Data into the Flow Using Load Vars
If you need to import data into the flow to use in other actions, the best way is to use the Load Vars action to load the data into a variable, which the flow can remember and use in actions later. For example, you might have a Promo column in the spreadsheet, to indicate if the advertisement is a promotion or not, that has a “Y” if it is and is empty if it isn’t. You would use Load Vars to tell Fuga to load the Promo column into a variable, and then you can use that variable to the If action, so that the If action can check to see if the advertisement in each row is a promotion or not, and do something differently if it is or isn’t.
Mapping and Load Vars
When using the Load Vars action, you must at least specify the names of the variables that you wish to load into in the Mapping tab. If the column names match the variable names exactly, the column names don’t have to be specified. If they don’t, however, you can specify them in the Column name column. For examples, see the Load Vars action reference article.
General Types of Data in Your spreadsheet
Fuga was designed to read from a spreadsheet all of the information that it needs to create an advertising box (or an ID card, or a calendar box, etc.). Each row of a spreadsheet can contain text to be loaded directly into the document, image file names, and additional information that helps determine the layout of the document, like if an advertisement is a promotion or not.
Text to Be Loaded Directly Into The Document
In most cases, you’ll probably be primarily using the spreadsheet to store text that will be loaded directly into the document via Tags and Load Data. In the spreadsheet above, the text from the column for name, ID, Data of Birth (DOB) and Expiration (Ex) is written the same way as it will be in our final document, so all the flow has to do is take this content from the spreadsheet and put it onto the document. If any actions need to be preformed on the text (for example, if you want to make the dates a different format) you can always use Load Vars to set the text as the value of a variable, use it in other actions, and insert it into the document later.
You can also use the Load Data action to place image files. When Fuga attempts to load data into an image frame, it will automatically assume that the data being loaded into it is an image or other placeable file, and thus try to locate the file based on the information in the column. This information can be the file name for the image or just part of the file name for the image.
In the Image Tab parameters for the Load Data action, you can specify what directory an image is located in (all directories besides the InDesign document directory need to be set using the Set Directory action). Among other options, you can tell Fuga to look for images whose file name is an exact match, or a partial match of the string in the column, and tell Fuga what file extensions to look for. For more on these parameters, see the section on the Image Tab in the Load Data action reference.
In general, there are two methods for specifying the image path or partial image path in your spreadsheet. You can either write the file name or partial file name of the image in a column that’s specifically for the photo (e.g., have a column named Image), or you can name your image files according to another column in the spreadsheet (for example, the UPC). If you name your images based on another column in the CSV, you can tag both an image frame and a text frame with the column name, and Fuga will know to place the text into the text frame, and try to search for the image for the image frame.
Additional Information About The Row
As touched on earlier in the section on Load Vars, you can also use a spreadsheet to save information that helps the flow determine how to treat each row, which is then loaded into the flow using the Load Vars action. The most basic example is using a column to specify if the advertisement is a promotion, or which type of advertising box to use (if you have multiple ones). You can also create columns to do more advanced things, like specify if an advertisement is a sub-advertisement, which version it should go on, and so on.
Advanced spreadsheet Data
Sometimes the information you need to organize in a spreadsheet can get a bit complicated. Here is how you can layout a spreadsheet to accommodate for some of the more advanced types of data.
There are times when you might want to specify that one row in a spreadsheet is connected to other rows of a spreadsheet. Taking our library card example, maybe some people have a joint library account with another person, so two names, dates of birth and photos need to be displayed. In order to let Fuga know that this account is linked with another account, we created a MainRow column that holds Y (for Yes) if the row is a main row, and an N (for No) if the row is a subrow. Then, if you are using a Data Rows Loop to load the contents of the spreadsheet, you can tell it to skip the rows where column MainRow = “N”. Then you can then have other actions within the main loop that deal with the subloop.
When a column has multiple values inside it, there are two different systems you can use. You can keep multiple values in multiple rows using the subrow system, as long as the data doesn’t conflict with the other data in subrows, and then you can use the Load Vars action to load multiple rows of data from one column into a delimited string. You can also keep multiple values in a delimited string inside the spreadsheet, and then use a delimited string loop to separate them out.
Tables can use a similar method as multiple values. For each column in the table, you can either have a delimited string of values or subrows containing multiple values.