Pau asks how to take a CSV file from one website and convert it for importing to Google Calendar. In Episode 773, I turn on the macro recorder and attempt to fix the file.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey. Welcome back to the MrExcel netcast. I’m Bill Jelen.
Well, this is one of the harder questions I've received, and we're going to try and do it in a day or two.
Someone had a CSV file that they're downloading from the web every day and they want to convert that CSV file over to Google Calendar.
Well, I went out to Google Calendar to their help desk and they basically say, hey, we need three fields -- we need a start date, a start time and a subject -- and then there were some other fields that you could add, for example, description and some other fields that I didn't think were relevant.
So, we're going to try this with a macro recorder.
We'll go to TOOLS, MACRO, RECORD NEW MACRO, and let's call it CONVERTCSV, click OK.
Now, the very first thing we're going to do is go out and navigate to that CSV file we download each day, and, boy, this is a horrible looking CSV file.
You know, I've seen some bad looking CSV files.
This is one.
There's just all kinds of blank rows throughout.
Great way to get rid of the blank rows is to select all cells, so we click right here in this box, and then click the AtoZ button, and basically, then, that forces us to sort by column A, gets all the blank rows to the bottom.
Now, let's just take a look at that column A. We have text there that says Friday, May 30th, and Excel will never recognize that with the Friday.
I'm going to choose that column and go to DATA, TEXT TO COLUMNS, choose FIXED WIDTH, and it's going to see that there's three words there and so it's going to suggest breaking it into three columns.
I don't want to do that.
I'm going to double click the second line so we get Friday in one column, May 30th in the next column, click NEXT, and I'm going to say, hey, I don't want the Friday, just do not import it, and the second column I'm going to leave as GENERAL, and, sure enough, that will actually solve the problem.
We get real live dates and it puts May 30th 2008.
It puts the current year there.
So, in column A, I'm going to put START DATE.
In column B, where we have TIME, I'm going to relabel that to be START TIME.
TIME ZONE, I don’t think, is relevant.
I'm going to delete that column, and then CURRENCY is going to be the subject.
Now, the problem is the CSV file then has 5 additional columns and I only have 1 space left, basically a DESCRIPTION field, and so I'm going to come out here to column I, and try and build a formula that will take all the other columns and concatenate them together into a description field.
Now, in this particular case, I want to get down to the end of my data set.
I'm going to use that with END DOWN, but of course we want to have RELATIVE turned on.
So, here in the STOP RECORDING toolbar, or in Excel 2007 on the DEVELOPER tab, we're going to turn on RELATIVE REFERENCE.
This is already turned on here.
I'll press END DOWN to get down to the end of the data set and go right to the blank column.
Now, at this point, I want to use CONTROL+SHIFT+UPARROW to select all of the cells and then hold down the SHIFT key while I go down one so that way I don't select the heading.
Now, at this point, it's a really long concatenation formula.
Basically, I'm going to take the heading from D1 and the value from D163, concatenate with that with the heading from E1 and the value from E163.
Let me go ahead and build this formula and we'll come right back.
Okay.
Let's take a quick look at the formula.
So, we start out with =D163.
They happen to be my last row. &, and then “ space ”, &. Now, the heading from E1, I want to put $ signs throughout, so $ E $ 1 and &, and, here, I’m going to put a : and a space within “. So, basically, we're going to get the heading from E1, a :, and then the value from column E. I repeat this all the way throughout.
So, I grab the heading from F1 with $ signs and then the value from F163, heading from G1 with $ signs, value from G163, the heading from H1 with $ signs, and H163.
Now, depending on your data set, you might build a different formula in here.
It can be, you know, as long as you need it to be.
Once, I've entered that formula, I'm going to press CONTROL+ENTER to enter that formula throughout.
Now, without changing the selection, I'm going to press the COPY icon and then PASTE, VALUES.
Up until I61, I'm going to put a heading and, of course, Google says we can only use about 12 headings.
The heading they're expecting is the word DESCRIPTION, and now I can delete columns D through H because I've basically taken all that data and put it over in I.
Alright.
At this point now, I want to take this file and save it back out as a CSV file, so FILE, SAVE AS, and I’ll call it GOOGLECALENDAR, click SAVE, gives me the warning, I say yeah, that's okay, and now we'll close that file.
FILE, CLOSE, save changes, NO, and we'll stop recording.
Okay.
So, there, we went through the steps to fix the file.
Fairly tedious to go through column A and do the data text to columns.
Now, tomorrow, in tomorrow's netcast, we’ll actually take a look at this code and see how we need to generalize it or if the macro recorder got it right.
Well, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Well, this is one of the harder questions I've received, and we're going to try and do it in a day or two.
Someone had a CSV file that they're downloading from the web every day and they want to convert that CSV file over to Google Calendar.
Well, I went out to Google Calendar to their help desk and they basically say, hey, we need three fields -- we need a start date, a start time and a subject -- and then there were some other fields that you could add, for example, description and some other fields that I didn't think were relevant.
So, we're going to try this with a macro recorder.
We'll go to TOOLS, MACRO, RECORD NEW MACRO, and let's call it CONVERTCSV, click OK.
Now, the very first thing we're going to do is go out and navigate to that CSV file we download each day, and, boy, this is a horrible looking CSV file.
You know, I've seen some bad looking CSV files.
This is one.
There's just all kinds of blank rows throughout.
Great way to get rid of the blank rows is to select all cells, so we click right here in this box, and then click the AtoZ button, and basically, then, that forces us to sort by column A, gets all the blank rows to the bottom.
Now, let's just take a look at that column A. We have text there that says Friday, May 30th, and Excel will never recognize that with the Friday.
I'm going to choose that column and go to DATA, TEXT TO COLUMNS, choose FIXED WIDTH, and it's going to see that there's three words there and so it's going to suggest breaking it into three columns.
I don't want to do that.
I'm going to double click the second line so we get Friday in one column, May 30th in the next column, click NEXT, and I'm going to say, hey, I don't want the Friday, just do not import it, and the second column I'm going to leave as GENERAL, and, sure enough, that will actually solve the problem.
We get real live dates and it puts May 30th 2008.
It puts the current year there.
So, in column A, I'm going to put START DATE.
In column B, where we have TIME, I'm going to relabel that to be START TIME.
TIME ZONE, I don’t think, is relevant.
I'm going to delete that column, and then CURRENCY is going to be the subject.
Now, the problem is the CSV file then has 5 additional columns and I only have 1 space left, basically a DESCRIPTION field, and so I'm going to come out here to column I, and try and build a formula that will take all the other columns and concatenate them together into a description field.
Now, in this particular case, I want to get down to the end of my data set.
I'm going to use that with END DOWN, but of course we want to have RELATIVE turned on.
So, here in the STOP RECORDING toolbar, or in Excel 2007 on the DEVELOPER tab, we're going to turn on RELATIVE REFERENCE.
This is already turned on here.
I'll press END DOWN to get down to the end of the data set and go right to the blank column.
Now, at this point, I want to use CONTROL+SHIFT+UPARROW to select all of the cells and then hold down the SHIFT key while I go down one so that way I don't select the heading.
Now, at this point, it's a really long concatenation formula.
Basically, I'm going to take the heading from D1 and the value from D163, concatenate with that with the heading from E1 and the value from E163.
Let me go ahead and build this formula and we'll come right back.
Okay.
Let's take a quick look at the formula.
So, we start out with =D163.
They happen to be my last row. &, and then “ space ”, &. Now, the heading from E1, I want to put $ signs throughout, so $ E $ 1 and &, and, here, I’m going to put a : and a space within “. So, basically, we're going to get the heading from E1, a :, and then the value from column E. I repeat this all the way throughout.
So, I grab the heading from F1 with $ signs and then the value from F163, heading from G1 with $ signs, value from G163, the heading from H1 with $ signs, and H163.
Now, depending on your data set, you might build a different formula in here.
It can be, you know, as long as you need it to be.
Once, I've entered that formula, I'm going to press CONTROL+ENTER to enter that formula throughout.
Now, without changing the selection, I'm going to press the COPY icon and then PASTE, VALUES.
Up until I61, I'm going to put a heading and, of course, Google says we can only use about 12 headings.
The heading they're expecting is the word DESCRIPTION, and now I can delete columns D through H because I've basically taken all that data and put it over in I.
Alright.
At this point now, I want to take this file and save it back out as a CSV file, so FILE, SAVE AS, and I’ll call it GOOGLECALENDAR, click SAVE, gives me the warning, I say yeah, that's okay, and now we'll close that file.
FILE, CLOSE, save changes, NO, and we'll stop recording.
Okay.
So, there, we went through the steps to fix the file.
Fairly tedious to go through column A and do the data text to columns.
Now, tomorrow, in tomorrow's netcast, we’ll actually take a look at this code and see how we need to generalize it or if the macro recorder got it right.
Well, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
