>

Data Import

From DARSwiki
Jump to: navigation, search




Preface

This article will be updated on a regular basis. Updates may be required in response to updates to DARS or changes to business processes or errors.

Refer to DARSWiki Conventions for information on icons and other conventions which may may apply to this article.

Ensure you are familiar with the Data Protection laws before adding data to records in DARS. Refer to DARSWiki Further Help for further information and relevant links.

Please think twice before printing this article. If a printed copy is necessary, ensure it is printed double-sided and always recycle old versions.


Introduction

This article serves as a reference guide to the data import functionality of DARS.

In this article you will learn the various tasks and functions associated with data imports, as well as any relevant business processes.

You will require the Local Site Admin role(s) in order to access and use the functionality described in this user guide. If you are unsure of your role and access rights to the DARS system, please contact the DARS Helpdesk (see DARSWiki Further Help for contact details).

To add or update information in DARS, you can add or edit an individual record with the appropriate information. When you add or edit many records at once, this process may take a long time, depending on the number of records affected. Batch entry, which includes the Import process, is an alternate method to add or update records.

Import is particularly useful if your college/department maintains multiple databases or if you often move information in and out of DARS.

Import and Batch Entry

In Import, you can create a batch to add new records or update existing records. You can create an import file with an outside application, such as Microsoft Excel. When you select the file to import, you can map each header of the file with its corresponding field in the batch. The fields necessary to import or update information depend on the selected batch template.

With batch entry, you enter and update information on records quickly because you do not open each individual record. Instead, you create a batch template to specify the fields to add or update in each type of record. The programme lists the selected fields as fields in a spreadsheet layout, called a batch, so you can efficiently enter information in to DARS.

When adding details to a batch, the data is not written to the constituent records until the whole batch has been saved, validated and then committed. All three of these steps need to take place in order to complete the process and update DARS with the new constituent information.


Batch Entry

The overall process steps to import data into DARS via batch entry are:

DataImport 1-1-Batch-Overview.jpg

This document covers each step in detail.

Regardless of the type of batch created, you will use the same process when using batch entry to add or update records in DARS.


Add a New Batch

To use batch entry to add records, you must define the basic properties of the batch. These properties include the batch number and description, the design used to create the batch, and the batch owner.

  1. Click Batch Entry from the DARS menu (the location will differ in various parts of the system).
  2. On the Batch Entry page click Add.
  3. DataImport 1-2-Batch Entry.jpg

  4. Select the required Batch Template from the drop-down list, and click Field Options to customise as required. (You can also customise the fields from within the created batch.) You may need to check with your Super User or line manager as to which one to use. See Customize Batch Fields for further information.
  5. Depending on the batch template chosen, the Batch Number may default to <Next available batch number>, or you will be given the option to enter one. Remember to use the DARS Naming Conventions, or check with your Super User for guidelines.
  6. Enter a Description for your batch, so it can be easily identified in the list.
  7. Select the Owner of the batch from the drop-down list.
  8. Tick Enable auto-save.
  9. Projected # and Projected amount: For non-revenue batches these two fields are greyed out.
  10. Click Save to create the batch. The new batch opens for batch entry. It is also added to the list of uncommitted batches.


Customize Batch Fields

You can customise the fields in a batch from the Add batch dialogue box or from the Batch [number] data entry screen after a batch is created.

  1. After selecting the batch template on the Add batch dialogue box (Add New Batch) click on Field Options – OR on the batch data entry screen, click Customize Fields in the Main toolbar.
  2. On the Customize fields for batch dialogue box you can change the fields that will appear in the batch, or set default values. The available fields are displayed on the left, and the fields that are currently part of the batch are displayed on the right.
  3. DataImport 1-3-Customize Batch fields.jpg

  4. In the field defaults section you can set default values for fields. For example, you could set the default Constituent type, so you don’t have to enter it for each entry. You can also indicate if a field is hidden from the batch or is required (mandatory).
  5. When finished click Save.


Enter Batch Data

When you enter data into a batch, the batch is uncommitted. You can add a batch from the Batch Entry page, or the program can create a batch during a business process. When a batch is uncommitted, the data has not yet created or updated records in DARS. Until the batch is committed, you can continue to add to or edit it.

  1. If not already on the required batch data entry screen, navigate to the Batch Entry screen and select the batch you wish to add entries to.
  2. Click Edit > Edit batch . The batch data entry screen opens in a new window. The layout is similar to a spreadsheet, with each revenue record on a separate row and toolbars at the top. Revenue batches, for example, have quite a few more options in the drop down menus and icons, compared to a constituent batch.
  3. DataImport 1-4-Enter Batch Data.jpg

    Two toolbars are available on the Batch Entry window:

    • The Main toolbar includes options to save and export, edit solicitors, constituents and the batch, run processes and manage batch row messages.
    • The Revenue toolbar (only available in revenue batches) include options to apply payments, edit pledges and recurring gifts, add revenue details such as tributes and declarations, and add or delete notes.
    UsefulInfo.jpg Note: The batch data entry screen opens in a new window and this isn’t always obvious. To return to the Batch Entry screen, close the batch data entry window.
  4. You can use the mouse to enter data, however for faster data entry use the keyboard.
  5. GoodIdea.jpg
    • Use [Tab] to move forward (to next field or row) and [Shift]+[Tab] to move back.
    • In fields that include a drop-down list, such as Application, press [Enter] to scroll through options in the list using the [↑] and [↓] arrow keys.
    UsefulInfo.jpg Note: Any fields highlighted in yellow are mandatory fields. The designation field also needs to be completed for each entry.
  6. To download the batch entries to Excel – for checking or reconciliation purposes for example – click the Export icon in the Main toolbar. A standard Windows Browser download dialogue box opens. Select to open or save the file.
  7. To delete a row added in error click row number beside it to select – the row will turn blue - and press [Delete] on the keyboard.
  8. When you are finished entering data to the batch save it , then:
    1. For Revenue batches: Click Update Projected Totals.
    2. If you are ready to commit the batch entries, i.e. to add the information to constituent records, go to the Validate Batch section.
    3. If not just save and close the window. You can return to it later as required.


Validate Batch

When you validate a batch, you check that the data in each line entry is correct or valid. Always validate the batch before you try and commit it, so that you can sort out any errors ahead of trying to write the information to the database.

Once validated, the batch status should be updated (if required) and then the batch can be committed. In order to validate the batch all the mandatory fields must be completed, a designation must be entered, and the projected totals must match the actual totals.

  1. To validate the batch click Validate in the Main toolbar on the Batch Entry screen. If successful, you will see a pop up stating Validation Complete.
  2. If Validation fails, the row containing the invalid data will be highlighted along with a message explaining the cause of the error. The error must be corrected before the batch can be committed.
  3. Once validation is successful click Save and close the batch.


Update Batch Status

When a batch is ready to be committed you may need to update its status first. If the batch is in the final state of the workflow, the update status option is disabled.

  1. When the batch is ready to commit, select it from the Batch Entry screen and click Update Status.
  2. UsefulInfo.jpg Note: If the update status link is not enabled, it means the batch is ready to commit – see Commit Batch.
  3. Select Proceed to Final Review in the Next Step drop down list.
  4. DataImport 1-5-Update Batch Status.jpg

  5. Select your name from the Assign to: drop down list.
  6. Add any comments.
  7. Click Save.


Commit Batch

Until a batch is committed, you can enter data into it (or delete data from it) at any time, saving and validating it as you complete each data entry session. Use Batch search to find the batch if required. Once data entry and the batch workflow are complete, you can commit the batch. When you commit the batch, the programme uses the data entered in the batch to create or update records in DARS. Once a batch is committed, you can view information about the data that did and did not successfully create or update records in your database.

  1. On the Batch Entry page select the relevant batch and click Commit. The Commit parameters dialogue box opens.
  2. DataImport 1-6-Commit Batch.jpg

  3. Ensure you tick the options in the example above. If it can be edited, the Exception batch number should contain your initials and the current date – for easy identification.
  4. UsefulInfo.jpg Note: The Create Exception Batch checkbox may be ticked by default and the Exception batch assigned to the next available batch number.
  5. Click Start. The batch commit process is started and the Batch commit status page opens. When it has finished, the status will show as and the Control and Exception (if applicable) reports appear under Reports in the Explorer bar.
  6. If a number of exceptions are shown in the Batch commit status section click View Exception Report to see which revenue items failed to commit to the database.
  7. If there are exceptions, a new batch will appear in the uncommitted batches list (with the batch number as indicated in the Batch commit process parameters) which contains all the non-committed revenue items. You can open this batch, fix the data, revalidate, save and commit the batch again.


Import

Through the import process, data is imported into DARS and added to a batch file. This batch file is then committed to DARS and the records are created or updated.

To run the import process you need to create a .csv (comma-separated value) file containing the data to import. The import file can be created with an external application such as Microsoft Excel. When you select the file to import, you can map each header of the file with its corresponding field in the batch. The fields necessary to import or update information depend on the selected batch design. To ensure you have the correct fields for your import you can generate a header file against a specific batch design.

GoodIdea.jpg
  • Files imported to DARS must be in .csv format and encoded as UTF‐8 to ensure foreign or special characters (including £ signs) appear correctly in an imported batch.
  • Before adding data to an Excel spreadsheet that is to be saved as a .csv you should format dates, currency, phone numbers and zip codes to formats required for DARS. For example, phone numbers and zip codes columns should be formatted as text to retain leading zeros.

The overall process steps to import data into DARS are:

"Process diagram showing Import process steps"

To enable easier mapping of fields to their corresponding header values in an import field, you can generate a .csv file that automatically includes the header values for a batch design. Header files may already be created, however there will be occasions where you will need to create a specific one.

Given that external data sources are many and varied, it is not possible to have a generic header file to cover all circumstances. Therefore, creating a specific header file to match the data you are importing is very useful for ensuring that data imported from external sources can easily map to the relevant fields in DARS.

After you add data into the rows of this header file, you can then add an import process for the file.

When you click Auto-map on the Add import process page, the program automatically matches the fields of the batch design to their corresponding header values in the import file.

  1. In the Administration functional area click Import.
  2. Click Generate Import Header File under Tasks in the Explorer bar.
  3. Select the batch template with the fields to include in the header file.
  4. DataImport 1-8-Generate Header file.jpg

  5. Under Collection fields, the grid displays the collection fields included in the selected batch design. Collection fields can support multiple entries. On the Quantity column, enter the number of entries to import for each collection field. For example, to import up to three phone numbers for a constituent, enter 3 against the Phones entry.
  6. Click Ok. A standard browser download dialogue box opens.
  7. Although the file you will be creating is a .csv file, initially there are no data rows (only headings) so you can select to open or save the file as desired. Refer to important note below. Make your selection and click Ok.
  8. If you choose to save the file, a standard Windows Save As dialogue box opens.
  9. Complete details and click Ok.

  10. You can enter data into the file immediately, or return to it later.
  11. ImportantInfo.jpg Note: To retain leading zeros in phone numbers and post codes, once there is data in a .csv file, you should always import it into Excel rather than opening it directly

Add Import Process

You can create an import process to generate a batch to add or update information from a file created in another application. For example, you may have an event during which you enter data into a Microsoft Excel spreadsheet.. After you save your spreadsheet as a .csv file, you can import the information into DARS. Remember to check currency, phone number and date formats, and change if required, prior to importing the file. See Steps in the Import Process for further information. You can add an import process from the Import page or the Batch Entry page.

On the Batch entry page (Administration > Batch > Batch Entry), click Batch Templates under Configuration in the Explorer bar. The Batch Templates page opens.

DataImport 1-9-Batch templates.jpg

  1. Select the batch design on which to base the import and click Create Import.
  2. Select the relevant batch template.
  3. The Add import process dialogue box opens
  4. DataImport 1-10-Add Import process.jpg

  5. Enter a unique Name to help identify the import process. Remember to use DARS Naming Conventions.
  6. Enter a Description to help identify and explain the import process.
  7. Select a Site, if required.
  8. Enter or select the Batch Owner, if required.
  9. Against Import type: select whether to import the .csv file once or recurring, and tick First Row is Header, if required.
  10. Select the import source: from the server or from local file (i.e. the workstation or network)

On the Map fields tab, the fields specified in the selected batch template are shown in the Batch template column.

DataImport 1-11-Add Import process2.jpg

To automatically match fields in the *.csv file to fields in the batch design, click Auto Map. The program attempts to match the fields of the selected batch design to similarly named columns in the selected import file. You can adjust the mapped fields as necessary.

For unmatched fields in the Batch template column, click on the drop down list in the adjacent Import file column field, and select the corresponding heading from the .csv file

UsefulInfo.jpg Note: When you import constituent records that do not include lookup IDs or system IDs into a batch, the programme automatically uses the name and address entered to verify the constituent does not already exist in the database. If the programme finds another constituent with the same information, it links the constituent in the import file to the existing constituent record. If the programme finds multiple constituents with similar information, it flags the constituent in the batch as a possible duplicate.

If the Map collection field button is enabled, select the relevant Import file column field and click Map collection field. The Collection field column map dialogue box opens. Enter the number of values to insert for each item and either auto-map or manually map the field as above.

Select the Set options tab.

DataImport 1-12-Add Import process3.jpg

  1. On the Import workflow sub-tab, select the relevant Batch commit option.
  2. On the Constituent matching sub-tab, Use batch [or global] template settings should be ticked. In most cases this should be left as default.
  3. On the Date formats sub-tab, Date format, Fuzzy dates, and Month/day format options specify how dates appear in the imported data. These dates should default to UK format. Edit if required, selecting the format from the drop down list, or enter a custom date format.
  4. Options selected on the Other sub-tab depend on what you are importing.
  5. Under Code table fields, select whether the import file contains the translation or ID value of data to import from code tables, or to add new entries. In most cases these options can be left as default.
  6. ImportantInfo.jpg Note: Unless you have DARS access rights to update code tables, you should always leave the ‘Add new entries’ option unchecked. If this option is selected, and you do not have access rights, the import will fail.
  7. Under Simple data list fields, select whether the import file contains the translation or ID value of data to import from simple data lists.
  8. Under Search list fields, select whether the import file contains the ID or Quick find value of data to import from search lists.
  9. UsefulInfo.jpg Note: For an import process based on the constituent update batch template, if you select to search list fields based on Quick find, the Constituent column within your .csv file must contain the constituent lookup ID.
  10. Save your settings and return to the Import page or Batch Entry page.


Run Import Process

After you add an import process to DARS, you must then run the process to import the data and add the batch to DARS. You can run an import process directly from the Import page, or from the Import list (process status) page.

  1. Navigate to the Import page, select relevant Import process line (not the Name link) and click Start Import; OR On the Import list page, click Start Process under Tasks in the Explorer bar.
  2. The Import list (process status) page opens. When the import process finishes, the Status field on the Recent status tab will display ‘Completed’, ‘Completed with exceptions’ or ‘Did not finish’ depending on the results of the process.
  3. DataImport 1-13-Import List.jpg

    UsefulInfo.jpg Note: Records are not committed to DARS until you commit the batch created from this Import process. See next section.

Confirm Import/View Exceptions

When you create an import process, the programme automatically creates a status record for it. On the Import list page, you can view the information about the process, including the most recent operation of the process, a history of the process, and any job schedules created for the process.

Navigate to the Import page as per instructions in previous section and click the Name link of the relevant import process. The Import list page for the selected import process opens.

The Import list page includes information about the most recent operation of the process and historical data about the process under the History and Job schedules tabs.

If the completed import process includes exceptions, the system creates an exception file. This file lists the records that were not processed. You can view these by clicking on Download Exception File. A standard Browser download dialogue box opens. The data in the exception file will need to be corrected and re-imported.

Once you have run the import process, click Go To batch Entry under Tasks in the Explorer bar of the Import list page, to access the batch that is created as a part of the process. See Commit Imported Data for further information.


Check and Commit Batch

When you run an import process, the program uses the selected batch template to import the information into a new batch. After you run the import process, the new batch is added to the Uncommitted Batches tab of the Batch Entry page.

  1. On the Batch Entry page select the relevant batch and click Commit. The Commit parameters dialogue box opens.
  2. DataImport 1-6-Commit Batch.jpg

  3. Ensure you tick the options in the example above. If it can be edited, the Exception batch number should contain your initials and the current date – for easy identification.
  4. UsefulInfo.jpg Note: The Create Exception Batch checkbox may be ticked by default and the Exception batch assigned to the next available batch number.
  5. Click Start. The batch commit process is started and the Batch commit status page opens. When it has finished, the status will show as and the Control and Exception (if applicable) reports appear under Reports in the Explorer bar.
  6. If a number of exceptions are shown in the Batch commit status section click View Exception Report to see which revenue items failed to commit to the database.
  7. If there are exceptions, a new batch will appear in the uncommitted batches list (with the batch number as indicated in the Batch commit process parameters) which contains all the non-committed revenue items. You can open this batch, fix the data, revalidate, save and commit the batch again.

Further Hints and Tips

GoodIdea.jpg
  • You can view the control and exception reports immediately after committing a batch by clicking on View Control Report or View Exception Report under Reports in the Explorer bar, or from the Batch entry page.
  • Depending on the type of batch, when a constituent name is entered in the batch the Constituent window at the bottom of the page is populated with personal and contact details. This window is collapsed by default. To expand click on the Constituent Window bar. You may need to drag the window up to see all details.

DataImport 1-14-Batch constituent view.jpg

You can use this to check you have the correct constituent, select the Information tab to check their Applications, or select the Declarations tab to check any existing details.

Personal tools
Namespaces
  • Page
  • Discussion
  • Variants
    Actions
    Navigation
    DARS User Support
    DARS Support Centre
    Advancing Oxford
    Tools