Importing Excel data into a custom Groove Forms tool

  • Article

Joe Levine offers suggestions for importing data in a custom Forms tool:

Groove users developing custom Forms tools may commonly want to seed the tool with existing data. In some cases, your main objective for rolling out the custom tool may be to share an existing set of data records among team members. And since there could easily be a large number of existing data records, the only practical solution is to import them.

You can import records from a Microsoft Office Excel workbook, saved as a CSV (Comma Delimited) file. However, the process for accomplishing a successful import can be a bit finicky, and it is important to be aware of the requirements. Specifically:

· Column header names in the Excel workbook must exactly match corresponding field names defined in the custom tool, including case sensitivity.

· Column headers for corresponding Number or Date fields must be qualified by appending the data type to the column header name. For example:
Cost:Double
Start_Date:Date

· Columns in the workbook must be ordered to match the order of columns in the Custom tool view in which you will import the records.

Identifying field names and updating Excel workbook column header names

Typically, you will have your Excel data before you begin to design your custom tool. And as a result, you will already know the names to use when defining fields in Groove. Unfortunately, it is not quite so simple. Although the Groove Forms tool uses the field labels you specify to create field names, the field names do not always exactly match the field labels. For example, the Forms tool appends additional characters to some fields to accommodate certain characters such as word spaces, and it appends numbers to field names, when necessary, to ensure that field names are unique.

To ensure that you match field names exactly, you can follow this sequence:

1. Have the Excel Workbook handy so that you can review the column header names and their placement order. For example:
clip_image002

2. Create the custom Forms tool, using the column header names for your fields, and lay out a view to match the order of column headers in the Excel Workbook.

3. Publish the Forms tool design.

4. Create a sample record in the Forms tool.

5. Right-click the sample record, click Properties, and then click the Fields tab.
You see the list of fields and their respective field names in the Forms tool design:
clip_image003

6. Edit the column headers in the Excel Workbook as needed to match the actual field names in the Forms tool design, and append data type qualifiers:
clip_image005

Note: Appending the “String” data type qualifier is optional. Number and Date data type qualifiers are required.

Importing records into the Forms tool

Once you are confident that the Excel Workbook is suitably prepared, you can attempt the import:

1. Go to the custom Forms tool.

2. On the File menu, click Import, and then click Comma Delimited File (CSV) .

3. Do the following in the Import Options dialog box:

· Select the form to import from the drop-down menu.

· Check Microsoft Office Excel.

· Select or accept the character set and locale

· Click OK.

The records should be imported. If you receive an error, review the field names and order of columns in the Excel Workbook and Forms tool design to make sure they match up, make corrections, and then try again.