How can I import and export scheduling data ?

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 on Jan 2, 2013
    Schedule it Ltd
    /faq/10098/how-can-i-import-and-export-scheduling-data

    Subscribe to our newsletter and receive our free scheduling tips and articles to help you improve the planning in your business. Unsubscribe anytime.

    GET STARTED RESOURCES
    Login Support and Help
    Register Blog
    Features Privacy Policy
    Online Pricing Terms and Conditions
    On-Prem Pricing Industry Sectors
    Latest Version Reviews and Feedback
    Live Webinar Status
    APPS POPULAR SEARCHES
    Web App Team Scheduling
    iOS App Employee Planning
    Android App Customer Scheduling
    Zapier App Engineer Scheduling


    Start Your Free Trial Today



    Copyright © 2003-2020 Schedule it Ltd. All rights reserved.
    Schedule it on LinkedIn   Schedule it on Twitter   Schedule it on YouTube   Schedule it on Facebook
    Join Us - Live Webinar