How to save time using the HootSuite Bulk Uploader

Clearly one of the key advantages of uploading a bulk of tweets is the saving of that precious resource . . . time. However, HootSuite require the upload to be done using a CSV file. For those unsure of what this is, it is a special file format which can be created using Microsoft Excel. Rather than saving the file as an Excel workbook, it needs to be saved as a CSV (Comma delimited) file. More about how that is done later.

The raw data you need to use – date, time, tweet and URL (optional) must be formatted in Excel in a particular way, or the CSV file, which is produced from it, will not load correctly.


Date and Time

Quite why HootSuite require both of these pieces of information in the same cell is beyond me. But by asking for this, it makes file production long winded, unless you know a few Excel tricks. Many people will probably opt to cut and paste tweets into HootSuite scheduler one at a time, due to the time they perceive it takes to produce a correctly formatted CSV file.

What I’m going to show you is how you can make just two master files you can use time and again. We will be producing two Excel files – One Master file (which I call ‘Hootsuite Master’) where you can make simple daily changes, and another Master file (which I call ‘Master Uploader’), which is automatically update by changes made to the ‘Hootsuite Master’ file, but has all the formatting required to produce a CSV file. Once you’ve done this, bulk uploading will take no more than one or two minutes a day.

Let’s start with creating the ‘Hootsuite Master’. If you follow these instructions carefully you shouldn’t have any problems.

In this file we will have four headings in the first row of an Excel workbook. Cell A1 needs to be Date, cell B1 needs to be Time, cell C1 must be left blank (it requires a formula, more of that later) and D1 should read Tweet. If you want to add a URL, a link to Amazon for example, you can put it into the content of the tweet, you don’t need a separate column. Cell E1 needs to be blank for the moment.

The format for the date needs to be either dd/mm/yyyy (UK style) or mm/dd/yyyy (US style). Now I come from the UK, but I’d recommend the US style as it makes life easier when uploading the file. HootSuite’s default is US, although you can elect to choose UK, but why give yourself another task?

Here is the first trick which many people not totally familiar with Excel won’t realise. Rather than typing in the date, enter the following formula into cell A2:

=TODAY()

Then once you’ve pressed ‘enter’ today’s date will appear. When you return to the file tomorrow, rather than having to retype tomorrow’s date in, the system will automatically change it to the correct date.  Also, if you want to produce tweets for tomorrow, but want to upload them today, then type in this formula:

=TODAY()+1

You’ll now see the date as tomorrow’s date.

Next, we need to put a time into cell B2. HootSuite will only allow intervals between tweets of five minutes, or multiples of five. So let’s say you are going to start tweeting at 7am and you want to tweet every ten minutes. In cell E1 type in the time between tweets. If it’s 10 minutes you need to type in:

00:10

If you want to change the interval in the future you ONLY need to update this cell. So if you decide every fifteen minutes, then type in 00:15.

In cell B2 you need to put in your start time – 7am. Excel uses a 24 hour clock, so AM and PM aren’t needed. 7am is 07:00 and 7pm is 19:00. So on this occasion we need 07:00 in cell B2

The number of tweets you want to send is important, let’s say it’s 25. What we are going to do now is copy data from one cell into other cells, starting with the date. Click on cell A3 and type in =A2, then press enter. Cell A3 will now be showing today’s date. Now click on cell A3 again, and you will see a very small black square in the bottom right of the cell. Place the cursor on the square, and it will change to a black cross. Now hold down the left mouse key and drag the mouse down until you’ve reach cell A26. You will now see today’s date in all 25 date cells. Any changes you now make to cell A2 will now change all the other date cells automatically.

Now, we can’t quite use the same approach with time, as all the times of tweets need to be different. We already have 07:00 in cell B2. We are now going to put a simple formula in cell B3, which is:

=B2+$E$1

Using the same click’n’drag technique as before, click on cell B3, dragging down to cell B26. You will now see the different times each tweet will be sent out. If tomorrow you want to start tweeting at 09:20, then just put 09:20 into cell B2, press enter, and all the other cells will change accordingly. If you want the intervals to change, type the new interval into cell E1, press enter, now all the intervals will have been reset.

So, we now have the date and time of each tweet, but HootSuite wants the data in just ONE cell. This is why cell C1 has been left blank. Type (or cut’n’paste) the following formula into cell C1:

=TEXT(A1,"mm/dd/yyyy")&TEXT(F1," ")&TEXT(B1,"hh:mm")

Press enter and cell C1 should now read Date Time. Again, using the click and drag technique, click on cell C1 and drag down to cell C26. The date and time are now shown together, with a small gap between them. It is very important that you don’t type anything into cell F1.

Now it’s time to enter your tweets. These can be manually written in, copied and pasted from other files, etc. Once they are in you can reuse them. I’d suggest having a separate Excel spreadsheet with all the tweets you want to use more than once.


140 Characters

What Excel won’t tell you is how many characters you’ve typed. Again, there is a quick and neat solution. In cell E2 type in the following formula:

=len(D2)

Then to provide a warning for you, click ‘Conditional Formatting’, which is within the ‘Home’ menu bar. Drag the mouse over ‘Highlight Cells Rules’ and then click on ‘Greater than…’ In the box type 140, then click OK. Now if you type more than 140 characters into cell D2, the cell next to it will show the number of characters, but the cell will change to a light red background with dark red characters, warning you that the character limit has been exceeded.

Finally click on cell E2, and do a click and drag down to cell E26. Now all your tweets have a measuring system.

Your ‘Hootsuite Master’ is now complete. Don’t forget to save it!!


URLs

Before I explain how to create the ‘Master Uploader’ it is important to use a URL shortener for your tweets, so the ‘Hootsuite Master’ file can accurately measure the characters. My recommendation is the URL shortener www.bitly.com as all their shortened URLs are 20 characters long, tying in neatly with how Twitter makes all URLs 20 characters, regardless how short or long they are. Also, bitly has some great stats for measuring how many times your URLs within your tweets are clicked, once they’ve been dispatched into Twitterland.


Master Uploader

This is required to produce a CSV file which HootSuite will recognise and accept.

(Note: HootSuite claim you need double inverted commas around the date/time and tweet. This isn’t correct. I’ve successfully uploaded tweets without them.)

With your ‘Hootsuite Master’ still open, you need to create a new workbook, not worksheet. Open a new file, then immediately delete sheets two and three. This can be done by placing the mouse first over sheet two (bottom left of your workbook), then click the left mouse button, then select delete. Do the same for sheet three.

You now have a workbook with just one sheet. Now we are going to create the file, which is very simple. In cell A1 type in:

=

Then go to Hootsuite Master and click on cell C2, then press enter.  You will now see the date and time for the first tweet in cell A1 of the ‘Master Uploader’ file.  In the formula bar delete the dollar signs from the formula, but only the dollar signs. Click and drag Cell A1 down to cell A25. You will now have all dates and times of your 25 tweets.

Now click on cell B1 and type in:

=

Doing as you did before, go to ‘Hootsuite Master’, click on cell D2 (your first tweet) then press enter. Your first tweet will now appear in cell B2 in your ‘Master Uploader’. Again, remove the dollar signs from the formula in the formula bar. Click and drag cell B1 down to cell B25. You will now have all your tweets loaded. Now save the file.

To do updates in the future first open ‘Hootsuite Master’ and carry out updates, such as starting the tweets at a different time etc. Then when you open ‘Master Uploader’ all the changes will immediately appear in the ‘Master Uploader’ file.


Creating and uploading the CSV file

The first thing is you need to save the ‘Master Uploader’ as a CSV file. Click the green tab ‘File’ in the top left hand corner. Then click ‘Save As’. Click on the ‘Save as’ bar in the pop-up and select ‘CSV (Comma delimited), name the file, then click ‘save’. When the warning message pops up click ‘No’. When the system tries to get you to do a further save, just click the red cross in the top right of the pop-up screen. Your CSV file will now be saved in the directory you decided to put it in.

The final step is to now upload the CSV file onto HootSuite. From your HootSuite dashboard select ‘Publisher’ and then click on ‘Schedule in bulk’. In the pop-up select the CSV file you’ve just saved. As the date was set in US format you don’t need to choose the format, as we are using the default. Click ‘Submit’ and your work is done.


Special feature

Microsoft Excel cannot shuffle cells, and sending tweets out in a different order throughout the day means you get to hit different time zones with each tweet at different times of the day. But just because Microsoft Excel don’t provide the feature, do not despair, because there is a downloadable app which does. It’s available with a 5-day free trial at http://www.ablebits.com/excel-random-sorting/index.php (The price of the app is just £11.95.)

When downloaded it provides a separate menu tab within Excel. To use it just highlight your tweets (nothing else, but the tweets – don’t select the heading) within ‘Hootsuite Master’ file, click on the tab titled ‘AbleBits.com’ and click ‘Random Sorter’. Make sure the radio button ‘All cells in the range’ is selected, then click ‘Shuffle’. Hey presto, your tweets are then randomly shuffled.


I hope you’ve found this article useful. Comments are very much appreciated and can be shared by clicking here.


HootSuite Pro Social Media Management. HootSuite Pro social media dashboard.