This is the twelfth in a series of live lessons on using WordPress and Thesis as a Content Management System (CMS). We are using the CSV Importer plugin to import an existing database from a CSV file. The second step in doing this is to modify or sanitize the data so the plugin will treat it properly. In this lesson we configure the data so the plugin can import it.
Rick: Now we have to do some tricky CSS stuff. I’m sorry, not CSS stuff. Some tricky Excel stuff because now we have to make this data work. And the first thing we have to do to make this data work is we have to concatenate these topics together into a single… it doesn’t look like you have a 3rd… oh no, you do have a 3rd topic, okay. Pardon me, I take it back. Okay so we’re going to use all 3 topics and so, we need to create a new row, insert a new row… and this row is going to use a concatenate formula. So equals con… okay, concatenate. And it’s going to be L1 not LI. L1, comma, space and then double quotes, space, comma, double quotes. That’s going to put a space and a comma in between these 2. And then we’ll come over again to… move over one and it’s going to be m1. Oh you know what… oh yeah, okay. M1 and then double quotes, space, comma, double quotes and then L1 and then that’s it. Okay it says I have an error. Maybe I’m not permitted the space there, if that’s the deal. And Pam, if you’re an expert at this stuff, feel free to chime in here.
Okay, click Help. Okay let’s see, concatenate. Okay the concatenate function can join up to 255 characters so it’s…okay. So maybe my problem is…maybe my..okay. I wonder if my problem is just the designator. Oh and it’s not 1, it’s 2. That’s part of my problem also. And 2, okay… it’s still not right. So let’s see… yeah, so that should actually have worked. I have a specific example here of that so let’s try that again. L2 and then quotation marks and then a space and a comma… and quotation marks and another one… oh no. That’s what the problem is. I’m missing a couple of commas here because I need a comma after each element and now, it’s going to work. Okay, L2 and then that… so, I wonder if I need a space there. Concatenate L2 K2… no, pardon me. Not K2, M2. Okay, so now what I have here…no, that’s still not right. It’s the other way around. Space, backspace.Okay, that’s space. Okay so now, I have that. Okay so what I’ve done is I’ve combined these things together so if I come down here, I’m just going to stretch this all the way down, okay. And then what we want to do is we’re going to copy this and we’re going to copy that, select this, control H… that is replace. We want to find that condition there and replace it with a space and we’re going to replace all. Close, escape… let’s try that again. Okay space and then we’re just going to replace all. Okay so, I want a… let’s see, now replace all. Okay, well I don’t think this is going to matter but I’m a little concerned about it. Pam?
Rick: You had a question? You had a comment?
Pam: Oh I was just saying you’re kind of struggling with the Excel… what you’re doing is the hard way.
Rick: Am I?
Pam: If you just select your top formula that’s correct, doing a shift click to select the whole column and then a control D will copy that formula all the way down and you don’t have to mess with the replace.
Pam: So select that column…
Rick: I just wanted to get rid of this empty condition.
vOh, I know what you’re doing. Yes, you’d have to use an “if” statement. I’m sorry, I could’ve created it that way.
Rick: That’s okay.
Pam: But I see what your problem is. I lost what you were doing.
Rick: Well, let’s just see if we can make this work.
Pam: You’d need an if statement to check for… if it’s blank. Don’t add it into this concatenation.
Pam: I guess that’s just a little complicated. Takes longer to hold than it does to do.
Pam: You’d have to use 3 separate if statements.
Rick: And we do actually have to make another column here because we got to copy this one and then we’re going to paste the special and we need to paste the values.
Rick: Because we need the values and we need to be able to get rid of this here, right? And so in fact, we need to delete. Okay, we need to be able to get rid of that and then this is csv text topics. And now actually, maybe now that these are values, we may be able to get rid of this. You know, maybe the problem was that they weren’t values. That’s it.
Pam: Oh, good… didn’t see them.
Rick: The problem was they were values and so the values couldn’t be replaced with that. Okay so now we’ve made you know, we’ve concatenated these things together and now we’ll just have to do this a few more times. In a particular, we have to do this with those things that are… I’m sorry… those taxonomies that are hierarchical because a hierarchical taxonomy need to have some sort of indication of whether or not it’s a parent or not. And so what we have to do is we have to put a space and a comma in front of each one of these hierarchical taxonomies. And Pam, I’m going to turn you back on here again for a second. Do you know of an easier way to do this than to create another column beside this and to put the comma in and all the way down and then concatenate them together and that sort of thing? Is there an easier way to put a comma in front of each of these terms?
Pam: There probably is but I would just put in another column and I don’t use any concatenate formula because I’ve been using Excel I guess before they had that formula. I just do an equal sign and you know, your space comma ampersand H2 and then I paste that all the way down. I select a column and then place the values and then you’re done. You just eliminate column A.
Rick: Okay so then I could say equals and then you’re saying…
Pam: Open quote space comma close quotes…
Pam: Ampersand H2 and then enter.
Rick: Enter. Yeah, that’s way easier than my solution. And so then we just come all the way down again to the bottom.
Pam: Yeah, all the way down control D.
Rick: Oh, control D?
Pam: Control D, paste it down.
Rick: Maybe because I already started doing it the hard way.
Yeah. Drag the elevator box to the bottom. Do a shift click.
Rick: Okay. So now we have than and then if I just go back up to the top…
Pam: Now you got to highlight it so you can do control C or a copy like that. Paste values to get rid of the formula.
Rick: Right. Paste values. Okay so we’ve done that. Oh I lost my… and what was this? This was location.
Pam: Yeah, it’s right there in H1.
Rick: Oh it is. Okay, that’s where it is.
Pam: Here you go. Delete it again.
Rick: And then delete, okay. And so we need to do that for media type as well, I think. Let’s just go back and make sure I understand which ones are hierarchical. Manage taxonomies, so we had media type is hierarchical and location is hierarchical. And so all we have to do is media type now. Insert and then this is equals, double quotes, space, double quote, ampersand, E2. Okay, and then you said control D?
Pam: No. I’d drag the elevator all the way to the bottom and do a shift click. That selects the whole column.
Rick: Okay so you just do that, like what I’m doing?
Pam: No, not exactly but you get it.
Pam: See the little box over there to the far right, between the arrows? The elevator box, I just drag it all the way to the bottom and do a shift click in the last cell. And I think that’s faster than dragging.
Rick: Okay, paste values, okay. Well, especially with 3500 entries. Okay, so now we have… oh we have last one icky thing to do. Okay but we’ve got each of these things done. The last thing is author and the way this plugin works is this plugin interprets a comma as if it’s creating another term. And so, if we just leave this like this, it’s going to create 2 author names – West and Patiold, Martin, James C. And so what we want to do is we need to replace this comma and I tried doing this with various critical marks and it just didn’t work. So what I’m suggesting is replacing the comma with a semi-colon. And although what I’m really suggesting now is that we try it without this because I want to show you how to come back to life when it doesn’t work the first time. So I’m copying these and I’m creating a test… I’m creating a new test file. Let’s see, new blank worksheet, placing all that in there and I’m going to save it as… it needs to be saved as a csv and so instead of xls, I’m looking for csv. And then, book1 csv is fine. I’m going to save it to my desktop. Save it, say okay to those things.