How can I import and export scheduling data in the on-prem desktop version?

DESKTOP VERSION

Data Import & Export

Data can be imported in into Schedule it via a simple copy and paste or from CSV (comma separated values) files that can be created by Microsoft Excel and almost any other application that can create a text report or export.

A CSV file is a simple text file with a list of data separated by a comma and can be created and opened with any text editor like Notepad. All Schedule it data can also be exported to a CSV file for importing into other systems.

Quick 'Copy and Paste' Import

Importing from a CSV File

Import Example

Import/Exporting with Excel

Auto Import/Exporting

Exporting

---

Quick 'Copy and Paste' Import

Resource and event information can be copied and pasted directly from Excel and other applications for quick importing. You can choose a quick import from the menu Data > Import > Quick Import, or from the Settings > Resources link online. Remember your resources are all your staff, clients, rooms, locations, equipment etc.

---

Importing from CSV a File

To download a sample file for importing download this file.

For a more advanced import file download this file.

Both the above files can be open with Notepad or Excel. For other integration options from various packages like Outlook, Excel and many more Click Here.

Events, Groups, Resources (Staff, Equipment, Rooms etc) and Users can all be imported from CSV files and the new items either added to the database or used to update the same item already existing. All fields must be comma separated and surrounded with a double quote if a comma is in the text.

The start of each line in a CSV file specifies the type of item that is about to be imported. E.g.

'Event', ...
'Group', ...
'Resource'', ...
'User'', ...

If you use fixed IDs that you specify (in column 2), you MUST use a unique ID for every entry. An ID must never be reused.

Data Menu... Import Events/Resources : Import any data found in a CSV file into the database. (When importing Events the second field always contains the event ID. If this value is '0' the event will always be added as a new item and a new ID automatically generated. If the value is higher than '0' the event will be added (or replaced if already present) using the ID provided.)

Data Menu... Merge Events into database (Standard merge) : Merges events from a CSV file (Generally created with the option 'Export Events Only') and adds the new events to the database replacing the current events IDs with new IDs during the merge. This is different from an 'Import' as a 'Merge' will ignore the IDs as they are changed and new events created with new IDs, during an 'Import' events with matching IDs will be replaced.

Data Menu... Merge Events into database (Fixed point merge) : Merges events from a CSV file (Generally created with the option 'Export Events Only') and adds the new events to the database but at a defined date and resource. This is useful for creating common event lists that can be inserted many times for different dates and resources.

Data Menu... Import All Data : Clears the database of all data and settings then creates a database from the data in a CSV file (Generally created with the option 'Export All Data'). Can be used for restoring a backup or creating common database formats from a CSV file.

---

Import Example

The best and quickest method of creating an import file is to modify a standard export file created by Schedule it. In this example we will assume you want to import a resource like an employee, staff member, customer or client but the steps are similar for Events, Groups or any data you want to import.

1, In your database create 1 resource with names you recognise in the fields you want to find, e.g. 'MyName' in the name box, 'MyTelephone' in the telephone number box, and all the default settings you want to use for the new resources, then choose 'Export ALL' from the export menu.

2, Open the csv file you just created with Wordpad, Notepad or Excel.

3, Delete all lines except line 1 (starting SETTING,DATABASE), and the line starting RESOURCE with the resource name you recognise e.g. 'MyName' as the name in column 3.

4, You can edit and create copies of the RESOURCE line for each resource you want to create, changing the name (in column 3) and the ID (in column 2) to zero. You can find the words MyName and MyTelephone or any other words you choose to identify a field then you know which column to change and where to put your data.

(To link a resource to a group you must include the group IDs in column 14 surrounded by commas. E.g. If you have a group with ID 10 then column 14 in a resource should be ,10,)

5, Save the modified csv file (Save As CSV (MSDOS)) then choose 'Import Resources/Events/Groups' from the import menu to import your new resources.

Note : You MUST include ALL columns in every import file even if you are only want to import a name. All columns must be present with default data.

---

Auto Import/Exporting

There is the option to auto import and export on a time so the actions can be done without any user intervention.

Update = Update existing data
Replace = Wipe the database first then import data
Merge = Import events but replace any IDs so events can be imported without any conflicts

---

Exporting

Export Events Only : This will export the visible events in the schedule to a CSV file ready for processing in another package or for storage/backup. The events exported are influenced by the active filter.

Export All Data : This exports all the data and settings in a database to a CSV file. This can be used for backups or as a default database that can be imported to create new databases.

Export To Template : This will export the visible events and resources to any template selected from the 'Reports' folder. These templates can be customised to only export the required fields and in any layout. The events exported are influenced by the active filter.

Events can be exported to a CSV, ICS, or VCS file and imported back. ICS or VCS files are calendar files that can be open in most calendar applications. Calendar applications can also export events to these file formats. CSV files are just a comma separate values file that are used by programs for the output of data. This format can be opened and edited by programs such as Notepad, Wordpad or Excel.

---

Excel Delimiter/Separator

The delimiter needed when importing a CSV (Comma Separated Values) file is a Comma. Depending on your settings Microsoft Excel may not use this or allow this to be changed and it will use the Windows default delimiter. This is a Windows setting that will be used by all programs that refer to it (Excel in this case). To change it to a comma...

1, Click the Start button, and then click Control Panel.
2, Open the Regional and Language Options dialog box.
3, Do one of the following: In Windows Vista/7, click the Formats tab, and then click Customize this format. In Windows XP, click the Regional Options tab, and then click Customize.
4, Type the comma delimiter/separator in the List separator box.

---

Excel Dates causing Import Errors

Excel can sometimes recognise dates and try to change them to a more readable version of the data it has in a cell. Whilst this looks great when the data is exported it does not export the actual data in the cell but a different value.

All dates in a CSV file must be 'yyyy-mm-dd hh:mm' to import correctly.

E.g. '2013-12-31 19:00'

Depending on your local date format Excel may change this to be seen as '12/31/2013 19:00' which is also the text it will export causing an error when trying to import. To stop this you must stop Excel changing or recognising date columns or manually change the columns with dates to standard text so you can see the true value in the cells.

---

Event Columns

Whilst the columns are listed below you MUST include every column in any event import file. Export a sample event first then use this as your template for importing other events, changing just the columns you need.

1, Data Type (EVENT)
2, ID
3, Linked Resources (a comma separated and surrounded list of Resource IDs)
4, Title
5, Notes
6, Start
7, End
8, Internal (web Sync flag)
9, Internal (Resources answers, stock used)
10, Visual style
11, Background Colour
12, ID of user that last modified
13, Is template
14, Names of linked resources + other meta data
15, Diamond marker type
16, Information when created
17, Information when modified + other meta data
18, Web Sync status
19, Completed % value
20, Event is locked (0/1)
21, Email address
22, Event is Private (0/1)
23, ID of Parent Event
24, Minutes distance to Parent Event + other meta data
25, Total of event expenses
26, Type of relationship to parent event
27, -
28, Priority value
29, Series ID
30, Remotely added (0/1)
31, Remotely updated (0/1)
32, Custom 1 value
33, Custom 2 value
34, Custom 3 value
35, Custom 4 value
36, Custom 5 value (check box 1)
37, Custom 6 value (check box 2)
38, Email template to be used for event alerts
39, Text Colour
40, Alarm Date
41, Alarm Type
42, Alarm Period
43, Alarm Datum
44, Skill (a comma separated and surrounded list of Skill IDs and score)
45, ID of user that created
46, Custom 7 value
47, Custom 8 value
48, Custom 9 value
49, Geo Location (latitude/zipcode, longitude, resource ID going to, resource ID coming from)

---

Resource Columns

Whilst the columns are listed below you MUST include every column in any event import file. Export a sample event first then use this as your template for importing other resources, changing just the columns you need.

1 (A), Data Type (RESOURCE)
2 (B), ID
3 (C), Name
4 (D), Rules
5 (E), PositionV
6 (F), Text Color
7 (G), BackGround Color
8 (H), Marker Type
9 (I), Event Color
10 (J), -
11 (K), Price Type
12 (L), Price
13 (M), Email Address
14 (N), IDs of Groups Belongs to (a comma separated and surrounded list of group IDs)
15 (O), -
16 (P), -
17 (Q), Double Booking Allow
18 (R), Double Booking Warn
19 (S), - Internal Use
20 (T), - Internal Use
21 (U), Exclude From Auto Resizing
22 (V), - Internal Use
23 (W), Data Field 1
24 (X), Data Field 2
25 (Y), Data Field 3
26 (Z), Data Field 4
27 (AA), Data Field 5
28 (AB), Data Field 6
29 (AC), Data Field 7
30 (AD), Data Field 8
31 (AE), Data Field 9
32 (AF), Data Field 10
33 (AG), Status
34 (AH), Email Template
35 (AI), Used Qty
36 (AJ), Event Color (text)
37 (AK), Warn if missing skills
38 (AL), Block if missing skills
39 (AM), Skills
40 (AN), Skill Units
41 (AO), Picture Image
42 (AP), Location





(staff, equipment, users, rooms, resources, courses, tutors, delegates, bulk, batch, locations, courses)

Last updated, 2 January 2013, 11:51