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.

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.