Skip to content

Excel worksheet for HootSuite bulk upload

July 2, 2011
tags:

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:
=A2&" "&B2
  • 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:
=LEN(G2)
  • URL: no special formula, just the long URL that HootSuite will automatically shorten for me.

Completing the CSV upload

  1. Create a new Excel file.
  2. 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.
  3. 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.
  4. 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.
  5. Delete the header row.
    This is important! HootSuite won’t recognize the header row.
  6. 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.
  7. 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.

Advertisements
19 Comments leave one →
  1. July 5, 2011 5:36 pm

    Hello Becca,

    This is a great article! Thank you!
    We’d like to send you a hootkit — a small package of HootSuite treats. Please, email us your postal mailing address at community [at] hootsuite.com.

    We would also like to add this post to our User-Created Tutorials, if you don’t mind.
    http://help.hootsuite.com/forums/198011-user-created-tutorials

    Best,
    Mike from HootSuite

    • Dave permalink
      September 11, 2011 10:54 pm

      Mike from HootSuite

      The bulk Uploader is a major hassle. I thought that paying for the upgrade would make loading tweets a lot easier, and it doesn’t. I am a fairly techie guy, I am familiar with CSV, and I can’t get the damn thing to format the date so that HootSuite will accept it. I am NOT impressed. I even renamed the template offer by Hootsuite and manually updated the dates. Nothing. Perhaps you can offer a better template, one that works. Software is supposed to save me time, not cause me frustration.

      Dave

      • November 4, 2011 5:03 pm

        Hi Dave,

        Try using this Bulk Scheduler Template I’ve created in Google Docs. Access the file and “Make a copy” to begin using it: http://ow.ly/7jBQu

        Warning: Using excel is not recommended as when exporting a CSV it often adds characters that don’t work with HootSuite.

        Kemp from HootSuite

  2. July 12, 2011 1:51 pm

    This is a great article! Thank you!We’d like to send you a hootkit — a small package of HootSuite treats. Please, email us your postal mailing address at community [at] hootsuite.com.
    +1

  3. July 13, 2011 7:53 am

    I am too stumped! I followed your specs exactley and some of my items post and some don’t. I can’t see why this would be an issue on the files end. Some of the files (even ones that I had 2 errors on) have loaded…just not every time. This seems to be a Hootsuite issue in my opinion.

    I got the same response.

  4. July 30, 2011 2:31 pm

    Great article!! I have encountered the same problems… But in my case I have to tweet in Portuguese and I need to use chars such as á, é, ã… And this created a nightmare to me, because of this encoding thing…

    Come on… It’s 2011 already, should this still be a problem? Feels like 1996 internet all over again…

    I wonder how they do that in Japan, where HootStuite is very active.

    • November 4, 2011 5:05 pm

      Hi Andre,

      The issue your experiencing is because of how Excel exports CSV’s with foreign language characters in it. Try using Google Docs Spreadsheets. It works for our Spanish, Portuguese and Japanese speaking users.

  5. August 1, 2011 9:43 am

    Hey Becca! I finally got the bulk scheduling running on Hootsuite and I wanted to share it with you and your readers!

    On the Mac, I did not create a separate Excel spreadsheet. I just copied the formulas onto a new sheet and hit the Edit>Paste Special command. Sometimes Excel gives you different prompt screens. On one of them, you can select to paste as “UTF text”. On the other one, you can just select to paste as Values. Then I open TextEdit and go to its preferences. Under the Open and Save pane, I changed to UTF-8 both drop down menus on the ormal Text File Coding section. After that, I copied the lines on the new sheet and paste them onto a new TextEdit file. Finally, I saved the file as a .csv file and everything worked perfectly!

    Again, thanks for your post! Helped a lot!

  6. August 8, 2011 1:59 pm

    Thanks so much for this tutorial, it’s very helpful! And thanks André for the additional tips. I’m still having problems with the internal server errors, though. I’m also using a PC with MS Office 2007, but I’m using formulas for the dates/times which is probably causing me more trouble than it’s worth. I’m having the same problems as Linda, and agree it’s something HootSuite probably needs to work out. Very helpful tips, though, thanks again!

  7. August 12, 2011 10:32 am

    Becca, as a newbie to scheduling posts this article is a godsend! I’ve set up a spreadsheet in Excel and now need to get busy writing some tweets to upload. Looking forward to seeing them all tweet out automatically 🙂

    Hopefully I won’t have any problems uploading them as some others have. Kyle

  8. August 25, 2011 11:10 am

    I did all of these steps very carefully and I’m still getting errors???? Now what?

    • November 4, 2011 5:05 pm

      Try using Google Docs Spreadsheets and not Excel.

  9. September 2, 2011 6:09 pm

    When I tried to create a “DateStamp” column using your formula, I could only get it to display the European dates and military time as a big decimal number. How do you set it to display the combination properly?

  10. October 2, 2011 11:06 pm

    Hello Becca,

    Thanks for the great article. Do you have any experience posting to a Facebook Page instead of Twitter? I was able to resolve any problems with the CVS formatting and made the Bulk uploader work. But what is not working are the thumbnails pictures from the posts (and they DO appear if I post on Hootsuite manually.) Pictures add a lot of attention on a Facebook page and I would like them to be visible. Any experience with this? Any advice? Thanks!

    Paulino
    @paulinobrener

  11. October 24, 2011 8:30 am

    Ok, this is what I did. Browsing the internet , I found http://www.hootsuitebulkupload.com/, and downloaded the file that was being promoted in that site. Once I created all my tweets, following the steps of the worksheet, I exported the file and attempted to upload it. As you all could guess, the result was the typical “500 internal error”. I was getting very frustrated until I found this post. Then I opened my Wordpad and in the “save as” window y change the encoding from “ANSI” to “UTF-8”, and that was it! For the records I wrote all my tweets in spanish. For example “¿Has escuchado acerca de la ley de la atracción? Pues bien, así es como funciona: Tu tienes lo que tu mente visualiza”, but it gave no errors at all. Try it! 🙂

  12. November 26, 2011 3:42 pm

    Hopefully someone can make use of this Hootsuite bulk importer Excel template which mostly follows the logic on this page.

    http://dl.dropbox.com/u/72321/Unlikely%20Use%20Case/bulk-schedule-template.xlsx

  13. February 9, 2012 4:20 pm

    @Lenin, I am the author of http://www.HootSuiteBulkUpload.com. I haven’t been able to get accented characters to upload correctly to HootSuite, always getting “500 Internal Error” If you have found a way to upload them, would you mind dropping me a note? patrick@HootSuiteBulkUpload.com

  14. Lihsa permalink
    July 11, 2012 6:39 pm

    **FINALLY** figured out that you have to create the date and hour columns as a TEXT format. It will automatically format as either a number, date or format. So you have to manually go in and alter the format setting for these columns.

  15. FollyBeachBound permalink
    January 9, 2013 6:55 pm

    Earlier comment on using OO/Calc? Forget it! This is a great tool working with Excel!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: