Batch File Structure

The following section gives you the guidelines to create a batch file for Import metadata to all SharePoint Lists (Batch File Mode) task option.

1.

The first row of the batch file should contain the Field Names as headers. In manage metadata task, data type of the columns must be the same across all lists. SharePoint Columns that are read-only will not be updated by Dockit.

2.

The first field should be named as Destination Path. The 'Destination Path' field should be followed by other field names separated by  delimiter characters such as comma (,), semi-colon (;) etc. The delimiter character used in the batch file should be same as 'Format' string value available in the registry key (HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4\Engines\Text).

The following table shows the equivalent Format string for 'List Separator' in the registry key:

List Separator Equivalent Format String
, (comma) CSVDelimited
; (semi-colon) Delimited(;)
* (asterisk) Delimited(*)

3.

The first column of all the rows should contain the Destination Path of an item e.g., http://sharepoint/engg documents/samplefolder1. In this case, the Destination Path should be either list URL or sub-folder URL in a SharePoint list.

4.

Reference column: Reference column is one of the columns in the batch file (unique reference), which will be used to identify the list item in the SharePoint list. Dockit will search the specified reference value under the specified Destination Path. Dockit will then retrieve the Absolute URL of the matching list item or matching folder from the SharePoint list automatically and then proceed to perform the specified action.

The Reference column can be any of the following:

  1. Name - The column contains the filename or folder name or item ID or wild card characters.
  2. Title or any metadata column value in SharePoint [The column value must be a unique record].

The following table explains the behavior of wild card characters:

Destination Path Name Description
http://sharepoint/engg documents/Folder1 * Traverse all items and sub-folders presented in the Base URL and perform the specified action.
http://sharepoint/lists/technical lists/Folder2   It will perform the specified action for the top level folder only.

Note: Dockit provides an option to select the reference column as 'Name' in 'Batch File Options' step in task setup wizard.
5.

The column 'Name' is an optional column in the batch file. By using this column, you can provide the file name (Sample.txt) or item ID (1_.000) or folder name (folder1) under the Destination Path. For instance, please see the batch file snippet given below:

Destination Path Name
http://SharePoint/technical documents/Folder1 Sample File.txt
http://SharePoint/lists/technical lists 1_.000
http://SharePoint/technical documents/Folder1 Folder2
http://SharePoint/technical documents/Folder2 *
http://SharePoint/technical documents/Folder3   

Dockit automatically combines the value (Sample File.txt) specified in the 'Name' column with the Destination Path (Folder1). The combined Absolute URL becomes 'http://SharePoint/technical documents/Folder1/Sample File.txt'. Dockit performs the specified operation (update / delete) for this item.
6. Dockit provides an option to select an action column in 'Batch File Options' step in the task setup wizard. Dockit also provides an option to select the reference column in 'Batch File Options' step in the task setup wizard.

If you need to perform item level or folder level actions, specify the actions in any one of the metadata columns in the batch file. The specified actions to be performed for the list items in SharePoint as shown in the example below.

Example 1:

The example below updates the 'Designation' column in a SharePoint list. The column 'Title' is a reference column and the column 'Action' is action column in the batch file.

Note: The reference column 'Title' must have unique values in the SharePoint list. You can also use wildcard character '*' for processing the first few records.

Destination Path Title Designation Description
http://sharepoint/technical documents/Folder1 Sample Title 1 Manager Updates the 'Designation' column as 'Manager' if the list item under Destination Path has 'Title' as 'Sample Title 1' .
http://sharepoint/lists/Project Tasks Sample Title 2 Production Executive If any item or folder has the specified 'Title' value in the specified list or folder, Dockit perform the delete operation for referenced item.
http://sharepoint/lists/Annual Sales Sample Title 3 Sales Executive If any item or folder has the specified 'Title' value in the specified list or folder, Dockit performs the update operation. If the list item does not exists, Dockit creates a new item under 'Annual Sales' list.
http://sharepoint/technical documents/Folder2 Sample Title 4 Product Manager Dockit creates a new item with the specified 'Title' value under 'Folder2' with specified metadata.
http://sharepoint/technical documents/Folder3 * Marketing Manager Dockit updates all items and sub-folders under Folder3.
http://sharepoint/technical documents/Folder4 Sample* Production Manager Dockit updates all items that start with Sample in its reference value under Folder4. The maximum limit of processing list items is 50.
http://sharepoint/technical documents/Folder5 *Title* Sales Manager Dockit updates all items that contain Title in its reference value under Folder5. The maximum limit of processing list items is 50.

7.

The column to specify an 'Attachment' is also an optional column in the batch file. You can give any name to this column. Using this column, you can specify the files to be attached to the list item. Dockit provides an option to specify an 'Attachment' column from the batch file in 'Metadata Settings' step in the task setup wizard.

If you want to attach a document to a list item in SharePoint, please specify the file path in any one of the metadata column in the batch file as shown in the example below:
 
Destination Path Name Attachment
http://sharepoint/technical list 1_.000 C:\My documents\Sample Workbook.xls
http://sharepoint/technical list 2_.000 C:\My Documents\Sample Folder
http://sharepoint/technical list 3_.000 Sample Workbook.xls; Sample Text.txt;
http://sharepoint/technical list 4_.000 C:\My documents\Sample Workbook 1.xls; C:\My documents\Sample Workbook 2.xls
 
8.

The column 'New Name' is an optional column in the batch file. By using this column, you can provide the new name for your existing folder and/or file to be renamed in the destination SharePoint library. Please see the sample snippet below:

Destination Path Name New Name
http://sharepoint/technical documents Sample File.txt New Document.txt

Dockit will rename the file 'Sample File.txt' as 'New Document.txt'. While this renaming operation does not create any new versions, it will change the values of 'Modified Date' and 'Modified By' to the current update time and user respectively. Please note that the file name (to be renamed) must contain its file extension, since Dockit uses the value under New Name column as the alternate name for the list item regardless of its type / extension. The New Name field will not be carried forward as a metadata field into SharePoint.

9.

The column 'New Folder' is an optional column in the batch file. Using this column, you can provide the relative path to be created in the respective destination location given under 'Destination Path' column prior to creating the list items. For instance, please see the batch file snippet given below:

Destination Path New Folder Title
http://sharepoint/technical documents/folder1 folder2/folder3 Title1

Dockit automatically creates the folders (folder2/folder3) specified in the column 'New Folder' underneath the specified destination location (folder1). The new list item 'Title1' will be created in the new destination location 'http://sharepoint/technical documents/folder1/folder2/folder3'.

10.

For CSV and TXT formats, all field names and their corresponding metadata values should be enclosed within double quotes (“ ”) e.g., “John Doe”. When using accented characters such as [á é í ó] in folder / file names, the CSV / TXT batch file must be in UNICODE encoded format.

11.

For Microsoft Excel file formats ( XLSX), do not enclose the field names and their corresponding metadata values within double quotes.

12.

Ensure date values are in 'Date Only' or 'Date And Time' format.

13.

Lookup data type value will be assigned based on column value only. Lookup columns referring to non-existing list and column will not be updated by Dockit.

14.

People or Group data type value should be user name, Active Directory (AD) group name or SharePoint group name. User or group name referring to non-existing user or group will not be updated by Dockit. The user Login Name (domain\username), Display Name (John Doe) or E-mail address (someone@example.com) can be used to denote a user account.

15. Business Data Catalog (BDC) column values must be provided with the appropriate filter name to use in order to query the LOB records. Dockit will be unable to update the BDC column values if the filter name is incorrect or does not exist. You must provide the BDC column value in the format: <filter name>:<value>. For example: "Company Name:Vyapin"

The following section describes the data types supported by SharePoint and an example of how to state the value in the batch file for update/create.

SharePoint Data Type

Remarks

Example
Yes/No

If any other value appears in the batch file, other than Yes/No, default value will be assigned.

"Yes" "No"
Text

The text length should not exceed 255 characters.

"This is sample text"
Multi-line Text

The text length should not exceed 255 characters (preferred).

"This is sample text.\nThis is sample for multi-line text"
Date and Time

Date or Date & Time can be given in this field. Dockit provides an user option to specify the date mask to use for the date fields in the batch file in the task setup wizard.

"12/30/2006 10:50 AM" "1/25/2007"
Number

The fixed or floating number can be given to this field. The value should not exceed the numeric boundary.

"10" "3.41"
Currency

The fixed or floating number can be given to this field. Do not use any currency symbol ($) with this field value. The value should not exceed the currency boundary.

"100" "10000" "1500.67"
Choice (Single value)

Text or Number value can be given to this field. Do not use multiple values unless defined in SharePoint to accept multiple values.

"Male" "15"
Choice (Multiple values)

Text or Number values can be given to this field. The values can be of single or multiple. Multiple values should be separated with ';'.

"Windows 2000;Windows XP;Windows 2003;Windows Vista"
Lookup (Single value)

Text or Number value can be given to this field. Do not use multiple values unless defined in SharePoint to accept multiple values.

In this case, you must specify the column value in the batch file. Dockit will search for the value in the corresponding column of the referenced list and assign the ID of the first item matching the column value to the lookup column of an updated item, only if the column value exists.

Note: If the specified column value is of folder type, the ID of the folder will not be assigned.

"Windows XP"
Lookup (Multiple values)

Text or Number values can be given to this field. The values can be of single or multiple. Multiple values should be separated with ';'.

In this case, you must specify the column value in the batch file. Dockit will search for the value in the corresponding column of the referenced list and assign the ID of the first item matching the column value to the lookup column of an updated item, only if the column value exists.

Note: if the specified column value is of folder type, the ID of the folder will not be assigned.

"Stock Details;Accounts;Product Details"
People or Group (Single Value)

User name, AD Group name or SharePoint Group name can be given to this field. The user Login Name (domain\username), Display Name (John Doe) or E-mail address (someone@example.com) can be used to denote a user account.

Do not use multiple values unless the equivalent column defined in SharePoint can accept multiple values.

Dockit will search for the value in the site users list and assign the ID of the matching the user or group name to the People or Group column of an updated item, only if the user or group name exists.

"VSSDomain\Administrator"
"BUILTIN\Administrator"
"Backup Owners"
"someone@example.com"
"John Doe"
People or Group (Multiple value)

User name, AD Group name or SharePoint Group name can be given to this field. The user Login Name (domain\username), Display Name (John Doe) or E-mail address (someone@example.com) can be used to denote a user account.

The values can be of single or multiple. Multiple values should be separated with ';'.

Dockit will search for the value in the site users list and assign the ID of the matching the user or group name to the People or Group column of an updated item, only if the user or group name exists.

"VSSDomain\User1; BUILTIN\Administrators;Guest Members;Temps"
Hyperlink or Picture

The http URL of any resource or picture can be given to this field. 

"http://sharepoint/Shared Documents/My sample file.doc"
"http://sharepoint/Pictures/sample chart.bmp"
Target Audiences (Multiple value)

(Applicable only for a MOSS / Microsoft SharePoint Server 2010 library)

Global Audience name, Security / Distribution Group name and SharePoint Group name can be given to this field. Global Audience and Security / Distribution Group should be crawled and stored in User Profile Store of SSP.

The values can be of single or multiple. Multiple values should be separated with ';'.

Dockit will search for the value in the respective group list and assign the ID of the matching group name to the Target Audiences column of the existing file, only if the group name exists. Moreover, in order to assign the values that belong either to Global Audience or Security / Distribution Group require Dockit Server Agent to be installed in the destination SharePoint server.

"All site users; Admins; Viewers; Administrators; SPUsers; Contributors; Designers
Publishing Schedule Start Date

(Applicable only for Collaboration Portal /  Publishing Portal Site)

Date or Date & Time can be given in this field. Dockit provides an user option to specify the date mask to use for the date fields in batch file in the task setup wizard. "12/30/2006 10:50 AM" "1/25/2007"
Publishing Schedule End Date

(Applicable only for Collaboration Portal /  Publishing Portal Site)

Date or Date & Time can be given in this field. Dockit provides an user option to specify the date mask to use for the date fields in batch file in the task setup wizard. "12/30/2006 10:50 AM" "1/25/2007"
Publishing Image

(Applicable only for Collaboration Portal /  Publishing Portal Site)

The http URL of any picture can be given to this field. Relative URL also can be given to this field.  "http://sharepoint/Pictures/chart.bmp"

"Pictures/chart.bmp"

<img src='/sites/collaboration portal/PublishingImages/NewsArticleImage.jpg' />
Publishing Hyperlink

(Applicable only for Collaboration Portal /  Publishing Portal Site)

The http URL of any resource can be given to this field. Relative URL also can be given to this field. "http://sharepoint/Documents/Sample File.doc"

"/Documents/Sample File.doc"

"<a href='/sites/collaboration portal/PublishingImages/NewsArticleImage.jpg'> NewsArticleImage.jpg</a>"

Business Data (BDC) / External Data (BCS)

(Applicable only for SharePoint Enterprise Edition)

The BDC column value along with the filter name can be specified in this field. Dockit will search for the specified column value in the LOB entity using the given filter and assign the corresponding ID (when the BDC column value match is found) of the LOB record to the BDC column in SharePoint. Specify this value in filter name: value format. If the filter name contains colon (:) in its name, specify this value in FILTER:filter name, QUERY:value, where FILTER and QUERY are reserved words.

Dockit also automatically fills-up the column values associated with the BDC column during update or create.

"Company Name:Vyapin"
"ID:1000"
"Description:Dockit Migrator for SharePoint software"

If the filter name contains colon (:) in its name, please use the following syntax,

"Filter:<Filter Name>,Query:<Query Value>"

For ex:

"Filter:Company Name,Query:Vyapin"
"Filter:ID,Query:1000"
"Filter:Description,Query:Dockit Migrator for SharePoint software"

Note: The 'Filter:' and 'Query:' are reserved words.
Managed Metadata (Single Value) Text or Number value can be given to this field. Do not use multiple values unless defined in SharePoint to accept multiple values.

In this case, you must specify the term label or synonyms of the term label or column value with full term path in the metadata file. Dockit will search the specified value from the term set selected for the corresponding managed metadata column and assign the ID of the matching term value to the managed metadata column of the imported file, only if the column value exists. The term path should be separated with '|'.

Note: If the specified column value is unavailable in the term store, Dockit checks if the term set is open to creating new terms. If yes, Dockit creates the new terms under the term path specified in the metadata file. Otherwise, the specified term will not be assigned for the specific file / folder.

If you specify the term label in the metadata file, Dockit will search the specified term value and assign the first matching term value for the corresponding managed metadata column.

"Continent|North America|Country|United States|States|Alaska"

"Alaska"
Managed Metadata (Multiple Values) Text or Number values can be given to this field. The values can be of single or multiple. Multiple values should be separated with ';'.

In this case, you must specify the term label or synonyms of the term label or column value with full term path in the metadata file. Dockit will search the specified value from the term set selected for the corresponding managed metadata column and assign the ID of the matching term value to the managed metadata column of the imported file, only if the column value exists. The term path should be separated with '|'.

Note: If the specified column value is unavailable in the term store, Dockit checks if the term set is open to creating new terms. If yes, Dockit creates the new terms under the term path specified in the metadata file. Otherwise, the specified term will not be assigned for the specific file / folder.

If you specify the term label in the metadata file, Dockit will search the specified term value and assign the first matching term value for the corresponding managed metadata column.

"Continent|North America|Country|United States|States|Washington;Continent
|North America|Country|United States|
States|California;Continent
|North America|Country|United States|
States|Alaska"

"Washington;California;Alaska"
Geolocation Location co-ordinates or name can be given to this field. The co-ordinates value must have latitude and longitude values seperated by ';'.
"30, -60"
"New York, United States"
Task Outcome Text or Number value can be given to this field. Do not use multiple values for these column type.
"Success"
"1"
Related Items The http absolute URL of any list item (including file) within the site collection can be given to this field.  The values can be of single or multiple. Multiple values should be separated with ';'. "http://sharepoint/Documents/Sample File.doc; http://sharepoint/Documents/Sample File.doc1" "http://sharepoint/Lists/Custom List/1.000""
The following tables below describe the syntax to be used in the batch file for single values / multi values for the following data types in SharePoint:
  • Choice*
  • Lookup
  • Person or Group 

Single Value

Example

Final Result
In case single value contains ';' as part of its value, you can separate the values using ';#'. ";#Windows Live Spaces; Mesh;#" Windows Live Spaces; Mesh
In case single value contains ';#' as part of its value, escape these characters by preceding them with a semicolon and separate the values using ';#'.

* - The proposed delimiter ';#' is a reserved character pattern in the Choice field column. The single value should not contain ';#' as part of its value to be assigned to the Choice field column in SharePoint.
";#Windows Live;;#Mail;#" Windows Live;#Mail

Multi-Value

Example

Final Result
In case multi-value contain ';' as part of its value, you can separate the values using ';#'. ";#Windows Live Spaces; Mesh;#Windows Azure;#Windows HPC;#" Windows Live Spaces; Mesh; Windows Azure; Windows HPC
In case multi-value contain ';#' as part of its value, escape these characters by preceding them with a semicolon and separate the values using ';#'.

* - The proposed delimiter ';#' is a reserved character pattern in the Choice field column. The multi-value should not contain ';#' as part of its value to be assigned to the Choice field column in SharePoint.
";#Windows Live;;#Mail;#Windows Photo Gallery;#" Windows Live;#Mail; Windows Photo Gallery

 

Sample format of a CSV / TEXT batch file:

Note: Refer Point #2 for the separator to be used in the batch file

"Destination Path","Name","Title","Issue Date","Designation","Department","Action","Attachment"
"http://sharepoint/Shared Documents","Sample Brochure.doc","Sample Brochure",”11/06/2006”,”Director”,"Senior Management","Update","C:\My Documents\Sample Brochure.doc;C:\My Sales\Sample Sales Template.xls"
"http://sharepoint/list/Sales List","10_.000","Title10","12/05/2008","Sales Manager","Sales","Delete"
"http://sharepoint/list/Sales List/Folder1","","Title11",12/06/2008","VP Sales","Corporate Sales","New","C:\My Sales\Sample Product Demo.ppt"