Excel worksheet for HootSuite bulk upload
I had a long list of blog posts that I wanted to post to Twitter. I’m talkin’ more than 140-links-long, so not something I wanted to post and/or schedule individually. I created an Excel worksheet that would help me automate some of the tasks involved in creating a bulk upload CSV for HootSuite. In January, I posted some hard-won tips hoping to help others using the HootSuite bulk scheduler. Today’s post uses some of those lessons and, I hope, provides a few more.
Planning the worksheet
A coworker provided me with a basic Excel spreadsheet listing the post titles (with embedded URLs) and blogger name. To craft the Twitter updates, I decided I needed several elements: topic, post author & blog, post title, and post link. As part of my regular Twitter regiment for this account, I use the event’s hashtag and categorize almost all tweets with a topic notated by brackets. For example, “[Registration] #HPDiscover registration is now open http://www.url.com.”; For the purposes of this content, I decided to combine the hashtag and topic by using “[#HPDiscover Coverage]“.
Constructing the worksheet
I decided that I needed a spreadsheet that would allow me to combine several columns into a final tweet that matches the HootSuite bulk upload template. I started with a spreadsheet with the following columns: date, time, topic, author, post title, and URL. But to reach that ultimate goal of matching the HootSuite template (which requires only time stamp, tweet content, and URL), I needed to concatenate some of that content to build the final tweet. I added several more columns: time stamp, final tweet content, and character count. To help me visualize how each element would combine, I color-coded the columns that would build the final tweet to make the final copy/paste simpler. You can see in this screen shot what my columns look like:
The red columns are the ones that I will keep as the final text for the scheduler upload. Here’s a rundown on each column and its formatting:
- Date: manually entered date following the HootSuite requirement of DD/MM/YYYY.
- Time: manually entered times following the HootSuite requirement of HH:MM (remember to use the 24 hour, or military, time format!).
- Date stamp: combines Date and Time columns into one. The Excel formula I used was:
- Topic: the text I decided to use was [#HPDiscover Coverage].
- Author: Twitter ID (since we are using Twitter, after all) and blog name for author. For this column, I created a list that included each blogger’s Twitter ID and the name of their blog. With over 140 entries, I hoped being able to select the blogger’s name from a list would save some time and effort (as well as eliminate typos). To do this, I created an additional worksheet in the Excel file, then used the Data Validation feature to create a dropdown list that displays whenever I select a cell in the column.
- Title: the same text the blogger used to title their post.
- Final Text: uses the Excel concatenation formula to combine Topic, Author, and Title into the final tweet. Since my Author column lists the Twitter ID, and you can’t begin an Excel cell with the @ symbol without confusing the auto-formula feature, I used this to add the @ symbol so my final tweet would generate a mention for the blogger. Here’s what this cell’s formula looks like:
=D2&" @"&E2&": "&F2
- Char count: uses an Excel formula to display the character count of the final tweet. I added this to help avoid troubleshooting the upload in HootSuite later. You’ll also see a red-shaded cell in the Char count column in the above screen shot: I used Excel’s conditional formatting feature to flag tweets that would likely be too long. That way I know immediately if I need to make manual adjustments to how the final tweet builds based on the previous columns’ inputs. I flagged counts over 110—allowing 18 characters for the shortened URL, plus a few pad characters to make retweets easier. I found instructions for the conditional formatting on this web page. The character count formula is simple:
- URL: no special formula, just the long URL that HootSuite will automatically shorten for me.
Completing the CSV upload
- Create a new Excel file.
- Select entire worksheet and set cell format to Text.
This is important! Doing this prevents Excel from applying any autoformatting to the time stamp, which would seriously confuse HootSuite.
- Copy the entire original worksheet, then paste the values into this new Excel file.
This is easy to do: select Paste Special and Values as the option. This is important! You don’t want any formulas or formatting to stick around or HootSuite will get confused.
- Delete the columns you don’t need for the final CSV upload.
In this case, I deleted Date, Time, Topic, Author, Title, and Char count. That leaves Time Stamp, Final Text, and URL—again, these are the only columns that the HootSuite uploader understands.
- Delete the header row.
This is important! HootSuite won’t recognize the header row.
- Create separate worksheets if you have more than 50 rows.
This is important! HootSuite uploader only allows 50 items to be scheduled at once. Save each spreadsheet as a separate CSV file and upload them each.
- Save as CSV.
If you’ve followed these steps, especially the ones with bold red warnings, you should be all set.
Some additional tips
- Check your Publisher after every upload attempt. When I tried uploading my CSV files from this worksheet tonight, the HootSuite servers were acting up. I kept getting 500 errors (internal server errors), so I’d try to upload again. Unbeknownst to me, some of the items scheduled each time I attempted an upload. Instead of recognizing those repeated uploads as a conflict, HootSuite appeared to just barrel through and schedule them anyway. Even though it appeared from the uploader that nothing had been accepted.
- Choose your times wisely. There are several tools that will analyze your Twitter stream and determine when you receive the most retweets. Ostensibly, this tells you the best time to post your content. I chose to use TweetWhen, a service provided by HubSpot. TweetWhen was produced in conjunction with the Science of Timing research done by Dan Zarrella. If you haven’t seen his research, you should!
- Add a Calendar Control for even easier date selection. This only works on Windows, so I didn’t bother to include it in my file. But it would add an elegant means of selecting your dates. You’d need to ensure your Developer toolbar is activated, then follow these Calendar Control instructions.
Please let me know if any of this was confusing—I’ll try to answer questions or fix any broken instructions. Would it be helpful if I uploaded my Excel file for you to download?
Update: 5 July
I get intermittent Internal Server errors using my CSV file that I generate in this manner. HootSuite support gives me what by now seems to be their automated response: “it means that the .csv you are using isn’t encoded properly. It needs to be saved in UTF-8 encoding. We recommend using a basic text editor like TextEdit or Notepad to save your file. This will also better allow you to check the formatting of your document.” Well, sorry, I still say boo to that. If I wanted to spend my day editing documents in Text Edit or Notepad, I probably wouldn’t be so keen on batch uploading—that’s trading one crappy user experience for another, IMHO.
SO! I discovered that I can use my process outlined above on Windows with Office 2007 without error. However, when I attempt it on Mac with Office:mac 2011 I get the server errors. What makes me not buy into the standard HootSuite support response is that when I encounter the server errors, some of my updates post but some don’t. If it was as simple as a file encoding error, you’d think the whole thing would fail. I’m stumped.