Lessons

Lesson Info

Data Merge

The next thing we're gonna go over is data merge which sounds boring, again, sometimes I think we just need to come up with our own naming on some of these operations that we do. But it is what it is, we're merging data and what I'm going to do is I'm going to take data that I have in an Excel file and I'm gonna use that to help automate my layout. So think of like if you had an Excel file that has all your part numbers in it, so all your parts, all your part numbers, all the descriptions, maybe the price, and also photos for that and you want it to automatically create a layout for you on the fly, we can do that. So we're going to use data merge for that. And let's actually look at what I am going to create. I think, let's do that, yeah. This is what I'm going to try to end up with here. And what I wanna do is I wanna be able to lay one out and have it populate with all the other ones that are here. So I need to go into my blank one, which I don't have anything for. I have a missing l...

ink. I accidentally linked my CSV file and now it keeps looking for it. So let's actually look at what we're starting with. We can go back to the finder and I started with this Excel file. And again, if you don't have Excel that's alright because a lot of times you're just given this Excel file or what we really wanna end up with is a CSV file which we'll do in a second. So I just wanted to look at what we have in this file itself. Move this off to the side a little. So I have this spreadsheet and I have names, so everybody's name that's there. I have their title, I have a quote that I wanted them to put in the little annual report here, and then I also have a list of photos. And this is where it gets a little tricky, the header needs to be called at photos but, and I am not sure if they've fixed this, it seemed to be improved, but long ago you used to have to, at least on the Mac, put an apostrophe before the at because Excel uses at as a special function. So if it yells at you if you put at and it tells you you can't do it, you might need to put an apostrophe before that and that's actually what I have. It doesn't show up here, it's just here. And so it let's it know that that is an actual at. So then what I did is I just have the name of the file. Now, I don't have the whole path name and that's because where this lives, I have the CSV file that I'm going to create from this worksheet here sitting in the same folder as all the photos. If I have that in the same place when I go to bring this all into InDesign, then I don't need to put the whole entire path file or the path name where this lives because it knows it's gonna look first in that same folder. If it's not there, I am gonna have to put Macintosh user, my user, et cetera, et cetera, wherever it lives and that's kind of a pain. It's easier just to throw it all in. So we wanna make sure that we have that as well. The other thing to keep in mind is that we are going to make a CSV file which is a Comma Separated Values file and it sees commas as a way to create a new column so if you have commas in your actual text, you need to make sure that you enclose that text in quotes. So I did that here because this one has a comma and this one has a couple commas. So I made sure that that's in quotes. You could, if you wanted to, just put everything in quotes but that's a quick way to keep everything together. So just a couple things to know when you bring that in, especially if you are getting text from somewhere else. And on a Mac at least, a quick way to, I usually deal with that, is I will check the CSV file and just use the space bar and it will bring up the text in a real simplified version. I can at least see that the naming is proper and that I have quotes on there as well. Alright, so I have this text and then I would export that out as a CSV file but hopefully when somebody gave you the list they asked you what format you needed it in and they gave you this CSV file. And that's what we need, that's what needs to sit in with all the images so it knows where those images are coming from. Alright, so back to our InDesign file. Alright so I have this setup and the reason I have something in here already is because I kinda wanted to get an idea of what it looks like. Otherwise we're working with the variable information, which we'll see in a minute isn't super helpful. So generally what I do is I'm going to set up how I want these finished items to look. And then I'm going to delete what's there. I'm actually gonna overwrite what's there. The other thing to keep in mind, what I'm doing in this version, because there's two ways to work with data merge, in this one I'm going to take this layout and dupe that layout across the page but it wants to dupe everything that's on your page. So let's say I wanted a divider line between these, I could do that as well even though there's no variable information. But this information here is sitting on a master page so you need to make sure that the stuff you don't want duplicated lives on a master page. And you can't have anything else in that document, I can't have a whole nother page that has information in it. So it is slightly limited or it's something like you start with this and you add to your file later. So just keep that in mind that there are some limitations but once you get that knowledge of what is and isn't doable it really speeds up your workflow. So again, what I wanna do is I wanna create this and I have six names in here and I think four will fit on this page and it will create new pages for me as needed to fill in all of those. So again, I just plopped in a picture and stylized everything again using paragraph styles. So I've got team name and team info and then I've got her image in here. So what I wanna do is style that and I think, "Okay, great, I like the way that looks." The other thing to keep in mind is that when I step this I'm gonna tell it how much space is between each of the iterations and I wanna make sure that this is wide enough to accommodate the names that I need but if I make it too wide, it's going to take the edge of that frame as the beginning of the gap between the two items. So basically what I wanna do is make this as small as possible and then I am going to go ahead and delete her image. Just leave the frame there, so I selected the image and deleted that, and now I need to tell it where this information is coming from and what it looks like. So I'm gonna open up the window menu, go down to utilities, and choose data merge. And I'm going to choose a data source, it's already there, it remembered the one I had. So I would just go out to my data source. We are in data merge and I'm gonna choose that CSV file. So I'm gonna choose that. Now it's already there, it's the one that's already open in here. And here are my different categories I had or the different columns. Name, title, quote, and photos. And it knows it's a photo because I put that at symbol there. So it knows it's a photo. So the first thing I wanna do is I wanna drop the photo in here so I'm just gonna grab this and drop it in. And now, it shows that that's ready for a photo in here. Then I'm gonna come down here to the text and because I already have something in here I'm gonna select all that text, make sure not to select the return, I'm just gonna grab the name and then I'm just gonna click name. Now, if I don't have anything in here, let's say I was putting it in a new text frame over here, I can grab this item and just drop it in. Name, and then hit return, and then I would do the next thing which is someone's title. And I can actually style this information. So I can look at that. But again, I styled it first just so that I could sorta see actual stuff instead of these place holders. I'm gonna delete this frame that's here. Select their title, I just click title, and again, anything that we need to do, any punctuation we need to put in between, we would just put here. If I needed a comma or a space or whatever. If I needed title and then title part two or maybe I have address and address part two, et cetera. This is quote, so there we go. So now I have name, title, quote, and all the styling is done through Paradox Styles and I have the photo ready to go. Alright, so now the next thing we need to do is start that data merge and that's what this little symbol here is, create merged file. So I'm gonna click on that, move this a little bit out of the way so we can kinda see. It gets a little crowded. I can choose to merge all records or individual records or a range of records. Now if you've got 700 of these and you're just testing out to see how it looks, you might try just doing 10 or something like that. So just keep that in mind that you can do a test. Because what's going to happen is it's actually going to create a whole new file so it's not even going to do it in this file. So we create one, if it doesn't look good we just close that file and forget it ever existed and come back to this original one. And then you have two options, like I said, multiple records or single records and there's a little icon here to kind of show you what's going to happen. Basically it says that it's going to create this same, everything that's here on this page is going to duplicate across the page. With single records, and we'll see that in a second, we're gonna take the same information and it's gonna duplicate a new page for everything that's on that page. Alright so I'm gonna go ahead and I want it to tell me when I have overset text as well and if images are missing but the great thing is I can say multiple records. Let's actually set up this multiple records. This will be grayed out if you're not doing multiple records and here's where I tell it how much margin I want it to have. So in this case, the top, and you tell it how far down from the top it's going to be, how far from the bottom, the furthest down it will be, the left side, and then I'm not worried about the right side because the right side's gonna just come across. Well, the right side is going to tell me, the one that I do at the end, like how many across it can create. And I want it to range by rows first, so I want it to go across first or you can have it go down first. And then how much space between the columns. And I'm not putting space between rows because I'm only doing one, I only have enough for one on this one. But I can actually preview the multiple record layout. And actually, I'm gonna cancel that really quick. I just wanted to show you that on here I can also say preview to make sure, I could show you this, that I can make sure that it's fitting where I wanted it to be. So I can do preview and I can actually go through each record. A long time ago there was a bug that if you did this then it didn't work but it works fine now. So I can go through and make sure each one actually lines up where I need it to. So I'm gonna go back into this multiple records, everything's set up, it remembers the last thing you did which is nice but now I can also preview multiple record layout. So now, I can see, oops, I only have enough for three. I was hoping for four and that might be because I put too much gap between my columns. Remember, if I had this frame pulled out too far, it's definitely going to change it. I was thinking I wanted .875 between each of these images but I realize it's between here and here. So I can actually make this smaller. And when I make it smaller, there she goes, but still not enough for her name. So we wanna pull that in a little bit. And then I'll pull the frame back out and hopefully that'll fit. So I'll say okay and I can also go to the next page and I see that it created a whole new page to accommodate those last two people. Now again, I am getting some wraparound issues here, that's because my frame wasn't wide enough. I'll have to manually pull those out. Alright, and then in options the other thing I wanna do is I wanna make sure that I'm filling the frame proportionally so that I don't get any white gaps around this image. It looks like there is but it's just because of this dashed line that's here. I can also tell it to center the image in the frames if I want and also link the images to create a new link which will put them in the links panel. I can remove blank lines for empty fields so if you did have say address one and address two but there's isn't an address two line, if there's something below it, everything will move up to accommodate the fact that there wasn't one in there. And I can also limit to how many pages I want in that document. So again, that's great when you're running a test and you have a lot of them, you can limit that the first time around. So I'm gonna say OK and when I do that it's going to create a new document. It will tell me if I have overset text at all. I'll move this out of the way a little bit, let's just move this over here. So now I have that set, there's page one, there's page two. But I am gonna need to fix a few things. So we move that out, and do the same thing here. So I'm able to merge it in that way. Alright so something else that I can do with that same data merge, actually I'm gonna use one of my other files here, is I am going to merge... Let's see. I wanna make sure that this stuff is on the page, there we go. So let's say I wanted this to say something like, I'm actually gonna delete all of this and just say, so I'm gonna say congratulations and I'm actually gonna make this a little bit bigger. Let's make this, oh sure, we'll do that. Congratulations and then I want her name and I might have another column that says just first name and maybe just the first names in there but in this case I'm just gonna use what I have. I want it to say congratulations and name so I've gotta come in here and choose that data source. And I'm gonna say congratulations and then drag name into here, exclamation point. And then I hit return so I might change this to team info, on becoming the new and then title, let's just say. So anyway, I have that all set up and now I can go ahead and merge that and maybe I want this to be, this is the document and each page will have a different team member on it. So we're gonna do the same sort of data merge but we're gonna do the single record instead. So come here and say single record and there's no options here for us. Same with the images, center and frame et cetera, remove blank lines. And I'm gonna go ahead and say OK and when I create that I get a new document that has information on each page. Whoops, I didn't do the image, I forgot one thing. So we'll close that up, don't save. We'll go back to this here, I forgot to tell it that I would like the images also to update. There we go, same thing. Now we'll do that merge document. And now I can look at the pages and I can say congratulations Kate and John and Jim, Helen. Alright so we can create that. So think about if you were doing, it's basically like you would do data merge when you do a mailing list. The only thing is it does create one page for each individual item that's there and that may or may not work for your workflow. What I generally do is do that and then I will take that and you can save out an InDesign file and place it inside another InDesign file and that's usually where I take, let's say I had three by five cards or something and I wanted to do four of those on a page. So it automates getting the information from the Excel file into InDesign but when it comes to the layout and if you've got thousands of names, you're probably printing them somewhere that has software that let's you take those names and automate them as well but this is great for a lot of in-house stuff especially if you wanna just be able to put that information quickly into your InDesign layout. So that's called data merge. I know it doesn't sound exciting but just think of all the time it will save when you're doing things like creating a catalog and you've got all the information that's in here. And the good thing is that we can regenerate that super easy from our original InDesign file each time that there is a change that's made. In fact, you can actually link your file as well. And you'll lose styling is the only really kinda drawback to that but I do have this actually linked so that if there were changes to that CSV file it would update in here as well. And there are more robust third party plugins that actually let you use data merge and keep that two-way interaction. You make a change in InDesign, it goes back to the CSV file so that's a third party option. But just know that that is possible and the automation of that is completely possible.