Making a CSV broadcast merge file in Excel

From Documentation

Contents 1 Overview 2 Step 1: Make sure the mobile numbers have zeros in front 3 Step 2: Remove the header row 4 Step 3: Deleting columns you don’t need 5 Step 4: Copy and paste into a new Excel Workbook 6 Step 5: Save your changes 7 Step 6: Close your document 8 Step 7: Use the file in a broadcast

Overview

This document is for people who have their records in Excel. It is highly likely that you will need to manipulate the customer data you wish to use for a broadcast in the mylkBroadcast SMS system. You will need to:

1. Re-order and remove columns
2. ‘Tidy up’ the data to make sure you don’t have any stray columns etc
3. Save the file as a “CSV” (Comma Separated Values) file for use with mylkBroadcast.

You can do all this in Excel quite easily but there are some important steps to follow. Each step is illustrated with screenshots. The first screenshot below shows an Excel workbook that needs to go through all the steps:

Step 1: Make sure the mobile numbers have zeros in front

First, check that the mobile numbers have their zeros at the front. If they don't, then make sure that the column (ie. all the mobile numbers) is formatted to “text” rather than “general”:

1. Select the column containing the mobile numbers (click on the column header – in this case “D”)
2. From the “Format” menu, select “Cells”
3. In the “Number” tab, make sure that the “Category” is set to “text”
4. Click “OK”

Your numbers should now have “0” in front. If they don’t, then your original file was incorrect, or you imported into Excel from another format (like CSV – in which case please read the “Importing and Modifying a CSV Broadcast file in Excel” HowTo file)

Step 2: Remove the header row

Your broadcast file should not have a header row, so if there’s one in your Excel worksheet, delete it. The screenshot below shows the Excel workbook with no header row, just data.

Step 3: Deleting columns you don’t need

Your broadcast file must only include the columns you will use in your broadcast message. There are basically two types of messages – simple ones that use a broadcast file which is just a list of mobile numbers, and ‘merge’ messages that use other details such as the recipient’s name. For more information about doing a broadcast see Broadcast page.

Step 4: Copy and paste into a new Excel Workbook

This step is necessary to make sure you don’t have stray data in the file that will cause problems in your broadcast. Select the columns in which you have data by clicking on the column headers, (in this case “A” and “B”) while holding down the Shift key.

1. From the “Edit” menu choose “Copy” to copy the data to your clipboard
2. From the “File” menu choose “New Workbook” to open a fresh new Excel spreadsheet
3. From the “Edit” menu choose “Paste” to paste your data into the new Workbook.

Step 5: Save your changes

1. From the “File” menu choose “Save”
2. Choose a name for your file (remember what it is :)
3. For the "Format" choose "CSV (Comma delimited)"
4. Click "Save"
5. A window will pop up that says "The Selected file type does not support workbooks that contain multiple sheets." etc. Click "OK"
6. A window will pop up that says that your file "may contain features that are not compatible with CSV (Comma delimited)." etc. Click "Yes"

Step 6: Close your document

1. From the "File" menu choose "Close" or click the close button in the corner of the window
2. Yet another window will pop up asking "Do you want to save the changes you've made" to your file. Click "Don't Save"

This is very important. If you try to save the file again, Excel will ask you all the same questions and you'll probably end up saving it in the .xls format that mylkBroadcast can't read.

Step 7: Use the file in a broadcast

Log into mylkBroadcast and click the "Broadcast" tab to use the CSV file to send out a message.