Excel Importer

Patriot allows large numbers of clients and related data to be imported using a combination of Microsoft Excel and Patriot's XML import capabilities. Patriot supports three data import merge strategies: Overwrite, Merge and Skip. The merge strategy that you choose controls how Patriot imports data that conflicts with existing data.

You can also export clients from Patriot to the same .xml file format used to import clients. Ie. clients may be exported and then re-imported in a "round-trip" fashion - but beware that Patriot does not export or import any historical log data (e.g. signal and activation history) associated with clients.

Using the Excel Spreadsheet

An excel spreadsheet can be used to enter data and prepare it for importing into Patriot. You can download an empty copy of this spreadsheet from the downloads section of the Patriot support website.

Data can be entered into the Excel spreadsheet manually, or automatically using a conversion tool. Please contact Patriot support for information about automatically populating the spreadsheet with a standard conversion tool. The spread sheet is split into several worksheets. The first worksheet, Control, contains instructions and buttons to use for import and export.

Excel conversion spreadsheet
The Patriot data conversion spreadsheet consists of multiple worksheets for loading client and related data.

Importing Data

Import is used for loading Comma-Separated-Values (CSV) files. These are normally CSV files of account data that have been exported from Patriot. These CSV files should be placed in the same file folder as the spreadsheet, and must be in separate files, named to match up with the correct worksheet. Each worksheet consists of a set of columns, headed with a user friendly description of the column in row one and a machine readable column name in row two. Both of these header columns must be present for the import/export to work - do not delete these rows.

Manually Entering Data

Data can also be manually entered into the spreadsheet. When performing manual entry take note of the following,

Many fields have additional helpful comments. Look for a Red Triangle in top right corner of the column header to indicate a comment is available.

Example of a column hint
Hovering over some column headers will display hints about what the column is used for.

Client Numbers

Some columns expect a Client Number to be entered. In all cases this client number must be entered in raw format, not the display format seen with the Patriot client program. The raw Client Number format is defined as:

Client ID Area No Port ID
1-16 characters 4 characters 2 characters

The [Client ID] part is commonly 4 digits, but can be anything from 1 to 16 characters. Examples of raw client numbers are for 1234BASE01, or 1234000101. Column which expect a client number will mention this in the column comment.

Multi-Area Clients

You can include multi area clients, and have the areas linked back to the base area. On the Clients tab, set the Base Account column to empty for the base area, and set to the raw client number of the base area for each of the other linked areas.

Import Linked Areas

Importing Linked Areas

When linking areas to base, you should also define which field groups are linked, and which are overridden by the area. This can be done on the BaseOverrides tab. Enter a row per area which is linked to a base area, and specify True for False for each field group. Don't enter rows on this tab for base areas or area not linked to base areas. Only add one row for client area.

Importing Linked Areas Field Groups

Importing Linked Area Field Groupings

Billing Customers

Billing Customers can be entered on the Billing Customers worksheet. Each customer should have a unique Customer Reference to identify them during the import. Enter the matching Customer Reference on the Client Charges worksheet as well as for the Default Customer Ref field on the Clients worksheet as required.

User merging is supported for billing customers. To reference an existing customer in Patriot, simply enter them on the Billing Customers tab as normal, ensuring that their details match the existing customer. Then, enable user merging during import.

Installer Charges

Importing Installer Charges is supported by data import spreadsheet using the Installer Charges worksheet. This worksheet works differently from the others, because it uses the Installer's user ID as the primary reference for the imported data, rather than the Client Number as per the other worksheets. For this reason, it is only possible to import charges for Installers who already exist in Patriot (and therefore have a user ID). Importing a new Installer and charges in a single import is not supported.

Installer Charge Import
Importing Installer Charges

Equipment

Installers can be assigned to equipment by entering their details on the Equipment Installers worksheet. Each Installer must have a unique Installer Reference to identify them during the import.

When entering equipment on the Equipment worksheet, enter the matching Installer Reference to link the equipment to the installer.

User merging is supported for equipment installers. To reference an existing installer in Patriot, simply enter them on the Equipment Installers tab as normal, ensuring that their details match the existing installer. Then, enable user merging during import.

Camera Types

If you want to import in the Camera Details then the 'Camera Type' must be entered. The Camera type will need to be a number that matches the correct type of camera the account will be monitoring. A list of camera type numbers and the corresponding camera type is listed below.

Camera Type Id Camera Type Description
1(Not Used)
2IP
3Ozvision
4Not Used
5Adpro
6Heitel
7Camera SDK Plugin
8Dedicated Micros
9Videofied
10Visonic
11Adder
12VideoIQCVR
13AdProPlatinum
14ONVIF
15Milestone
16Risco
17Hikvision
18VideoIQRialto
19Mobotix
20Bosch
21Paradox
22WeR
23Dowshu
24Dahua
25UDP_IPN
26Davantis
27UDP_IPE
28Virtual
29Avigilon
30ParadoxDirect
31Standard Media
32RTSP Camera
33Risco VUPoint
34Samsung Hanwha
35Mobotix-EventStream
36IDIS
37Tiandy
38Surgard Visual Verification
39NX Witness

Export

Once the spreadsheet has been filled with all the clients and their related data that you wish to import, select the Controls tab and click on the Export button. This will create an .xml file in the same directory as the Excel spreadsheet. Depending on the size of the imported data set, this process may take some time. A progress bar is shown along the bottom of the screen for reference. A pop-up message will let you know that the conversion has completed.

Import Clients into Patriot

The resulting xml file created can now be imported into Patriot using the Data Merge feature.

Related Pages