Lovely chat this morning with a technical support gal named Bonnie from a company that I wish not to name (WNTN) because I have been told that even negative mention is advertising. Anyways, Bonnie from WNTN, and her highly technical skills, were not able to provide me with what I needed: a simple list of book, author, and purchase date in a text file, spreadsheet, or a .csv. I wanted the information for a groundbreaking article (not) that I was writing for you. An article that must now wait because I’m on a bit of a rant.
Bonnie from WNTN was, unfortunately, not a very gifted technical support person. I explained that I needed a list: book, author, purchase date. Period. You would have thought I had asked for free drone delivery of a new Jaguar with all the amenities. Rather than waving her magic wand over the data to retrieve the list of three pieces of information from my file, Bonnie from WNTN sent me a link as the solution; a link I had already explored and rejected as unusable. Yes, the list I wanted was there; but it was surrounded by pictures, icons, links, advertising and myriad other items that clutter a copy/paste function. I explained that I needed a straight text list. Bonnie from WNTN said she would let the technical people know of this idea for the future and asked if there was anything else she could do for me.
Thank you, Bonnie from WNTN, I guess I’ll figure this out for myself.
Have you ever need to copy a list from a highly detailed web-page and paste it into a spreadsheet program? It’s not fun. If you are lucky, your first scroll through the data will get you what you need, but chances are you’ll give up, or you’ll use Alt-A to select all of your data. The Alt-A approach will then will tie up your PC for many minutes and often just as you close in on the data needed, your PC freezes. And if you are lucky enough to paste the data, then what do you with the mess of unusable icons, links, and text that confront you?
At the end of this post, in a very boring sequence of steps, I share what I did to finally get my list. It took less than five minutes to pull 62 rows out of 2,786 rows of junk. In case you don’t want to read all of my steps, I’ve also included a table of helpful keyboard commands that I used to get to my end results. Some people prefer to mouse-click, right-click, and scroll, scroll, scroll. I do not. I prefer fast and easy keyboard commands that save my wrist and my elbow from tendonitis. Here are just a few of the gems that I use daily:
Now the boring part. Below are the steps I used to grab my data and create the list that I needed for my article.
- I opened up the web-site and selected ‘show all of the list on one page’. This luckily was an option.
- Using CTL with the minus sign, I shrunk the page so that I could see the pattern of the list, but I could not read it. It was very tiny. It looked like the list to the right:
- I highlighted all of the data that held the information I wanted (title, author, purchase date) and copied it. As you can guess from the picture to the right, the information I needed came saturated with ads, empty space, icons and URLs, all of which are memory intensive.
- I opened my spreadsheet program and pasted this mess into the first tab. And waited, waited, waited while all this junk made its appearance.
- Next I highlighted the entire spreadsheet with all of the junk by placing my cursor into cell A1 and holding my SHIFT key and pressing END HOME. With all of the data highlighted, I used CTL-C to copy the Excel version of the data. Excel is picky. You can try to copy from the web site and directly paste it as text. For me though, this did not give me the data in rows and columns the way I needed it. Thus I had to paste all of the junk into the first spreadsheet and then copy it again and continue to the next step.
- Next I clicked on Sheet2. With cell A1 selected, I used one of my favorite sequences, the Paste Special Values command. First I selected ALT-E and then S. This gives you the Paste-Special Box. Select V for Values and hit enter. The total key command sequence is Alt-E, S, V, Enter. Now I have the entire list in a text format. It’s still a mess, but at least it is a text mess.
- I then deleted the original version of the spreadsheet on Sheet1. with all of its icons and URLs, to release the mass of memory it was hogging. AND I SAVED MY WORKBOOK.
Now that I have my text mess, it was time to pull out the data I wanted. This is a multi-stage process and you will have to do it differently depending on your data, but these are the basic steps:
- First I inserted a new Column A and added a row number so I can alway sort back into the original order. To do this, a) Highlight Column A and select Alt-I, C (Insert, Column). An empty Column A should appear. b) Next, with your cursor in cell A1, do the SHIFT END HOME sequence to highlight all of the data. Now, while still holding the SHIFT Key, use your back arrow until you just have column A highlighted. Select Alt-E, I, S (Edit, Insert, Series) and you will get the Series window. Make sure that the Step Value is set to “1” and select OK by hitting your enter key. Column A will now be filled with row numbers.
- Now the hard part: Out of the 2,786 rows of data, how can I find the purchase lines that I am interested in keeping. I reviewed the data and found that amidst the whole mess, Title was in column D, Author in E, and purchase date was in column G. So I will sort by those columns and with any luck my purchases will group together. Here we go again, CTL-HOME brought me to cell A1. Next SHIFT-END HOME highlighted my data. Then Alt-D, S (Data, Sort) brought up my Sort dialog box. I chose to sort first by Column G-Date because their was nothing else in this column except the purchase dates, then author (E) and finally title (D). This grouped my data nicely so that I could delete all of the rows not needed. Out of 2,786 rows of data, I kept the 62 that had my purchases on it.
Tedious to read…I know. If you stuck with this article, you are probably a techy like me, or you really need to grab some information from the web. The nice part about these keyboard commands is they save your wrists and elbows from serious pain, and they save significant time. Once you learn these commands, your fingers can fly through the steps, while your coworkers and friends watch in awe!
Please let me know if you have any questions about these steps. Just like Bonnie from WNTN, I’d be happy to help!