How to prepare your contacts for bulk import – FrontlineSMS Support

You can import contacts into FrontlineCloud as a CSV file (.csv comma separated value), Excel file (.xls or .xlsx) or vCard (.vcf file). These are common contact and phone formats.

Note that CSV and Excel contact file imports to your Frontline workspace are limited to 5,000 rows per upload. There is no such limit on vCard imports, so for basic imports that do not require and Groups or custom contact fields setting up, we recommend using vCard. A free CSV to vCard conversion utility for windows is available at www.csvtovcard.com.

Get your contacts

First you'll need to gather your contacts into a spreadsheet document. Most contact management programs such as Outlook and Google Contacts allow you to do this. Spreadsheet applications such as Google Sheets, OpenOffice or Microsoft Excel are great places to organise and combine lists of contacts so we recommend that once you have exported and obtained your contacts lists you load them into one of them for preparation into Frontline's contact format. For this article we will use Excel.

If you are using the vCard format, you will be limited to what fields you can import, but if you are simply looking at importing basics core fields (name, mobile number, email) then vCard is the easist format to use and you can skip the remainder of this Step.

Most applications have a shared basic structure for holding contact data, and all of them then have their specialist structures required to support that specific application. Here we introduce the contact fields and formats that Frontline requires to correctly import your contacts.

Once you have your contacts in some form that is visible in Excel, the first task is to ensure you have the correct columns present with the correct column headings. The example screenshot below shows the core columns with 'Group(s)' being optional but commonly used;

Name
Mobile Number
Email
Notes

The image above shows a 2 contacts as a raw (left) and visible in Excel (right). We suggest using Excel as it is easier to manage such lists in a grid but editing the raw CSV is easily done in simple software such as Windows NotePad and Apple's TextEdit. Note the "quoted" values in the CSV and the commas matching each of the rows.

If you need help getting the format correct, you could export a few contacts directly from Frontline as a .csv file (you can enter a few dummy contacts if you have none). Open this file in Excel, and add your contacts under the appropriate columns.

There are a few keys things to consider here:

Column names - These must be exact because they will tell Frontline which part of the contact the data in the column represents. Names should be as above e.g. Mobile Number not Phone Number and Email not E-mail
Phone Number formatting - This is a very important part of the process in getting your contacts correctly into Frontline. In order to robustly deliver and receive SMS Frontline tries to ensure all phone numbers are in international format with a leading +. For example +254 7## ### ### instead of 00254 7## ### ## and 07## ### ###.
Most spreadsheets, certainly Excel, will try to re-format your phone number column into a number which can mean the + is removed and occasionally the number is shortened into a Base E format that appears like 25472E+11 which is just a notation that allows the presentation of a large number using fewer characters.
The fix here is to ensure the column's format is set explicitly to Text. In Excel this is done by selecting the column, Format Cells>Text. If possible this formatting should be done before pasting in the data so that your data is not lost; so that you can avoid adding a new + to each number.
Another way around this is to type a single apostrophe (‘) before the plus (+) sign for every phone number for example, '+254 7## ### ###.

Note: If you are importing using vCards, you only need to ensure that your contact's mobile number is correctly listed as a mobile number; FrontlineCloud will ignore other numbers.

Note:If you are importing using Excel file(.xls or xlsx) the steps are similar to the way csv is imported however, you will need to ensure the mobile number column needs to be of type text this ensures that excel does not treat those values as numbers but as text. Excel import assumes that excel document has one sheet. If it has more than one sheet, the first sheet is chosen for import.

Once you have added content using your preferred spreadsheet application i.e. Microsoft Excel, Google Sheets e.t.c. you need to save/export that file as either .csv (CSV format), .xls or .xlsx (Excel format). The next step is uploading this file into Frontline (Step 2).

Note: When using Excel format, Frontline will only import the first sheet of contacts from the multi-sheet Excel file. We, therefore, require that you use only one sheet per Excel file.

Importing advanced fields

Importing batches of contacts using this method also allows you to add contacts directly into a Frontline Group, and also set 'Custom Fields' for users. The expected column headers for these are:

```
Group(s)
```
```
GroupName
```
```
Group1Name\\Group2Name
```

```
STRING-<Custom field name>
```
```
Any text up to 255 characters
```
```
DATE-<Custom field name>
```

dd/mm/yyyy  hh:MM:ss (e.g. 20/08/2015  12:42:00)

```
CURRENCY-<Custom field name>
```
```
Any Float
```

Macro for internationalising Phone Numbers:

An Excel macro for prepending a '+' to the phone number assuming the phone number column is B

Sub PrependB2Down()

For Each c In Worksheets(ActiveSheet.Name).Range(Range("B2"), Range("B2").End(xlDown)).Cells

c.NumberFormat = "@"

c.Value = "+" & c.Value

Next

End Sub

Get your contacts

Importing advanced fields

Macro for internationalising Phone Numbers:

0 Comments