Importing Data from CSV File

The following topic describes how to import data from a CSV file.

Description

To import objects:

Use the Current User -> Scripts... menu item to create a CSV Import script.
Click the Current Task -> CSV Import... menu item.
Select the CSV Import Script.
Choose a CSV File.
Click the Submit button.

Example 1

CSV file example:

Copy Code

Category,Name,Status,Handler,Submit Date
Folder,“Sample, Inc”,active,Mike Clinton,10/19/04 2:35 PM
Software Bug,Can’t login,003 Closed,,10/19/04 2:50 PM

Use the following script to get the values of the attributes:

Copy Code

String categoryName = (String) inputMap.get("Category");
String taskName = (String) inputMap.get("Name");
String statusName = (String) inputMap.get("Status");
String handlerName = (String) inputMap.get("Handler");
String submitDateStr = (String) inputMap.get("Submit Date");

Use the following script to import a task from the specified file:

Copy Code

Map taskMap = new HashMap();
String locale = sc.getUser().getLocale();
DateFormatter df = new DateFormatter(sc.getUser().getTimezone(), locale);
taskMap.put(CSVImport.TASK_NAME, inputMap.get("Name"));

taskMap.put(CSVImport.TASK_CATEGORY_ID,
     CSVImport.findCategoryIdByName((String) inputMap.get("Category")));

taskMap.put(CSVImport.TASK_STATUS_ID,
     CSVImport.findStateIdByName((String) inputMap.get("Status"),
       (String) inputMap.get("Category")));

taskMap.put(CSVImport.TASK_HANDLER_USER_ID,
     CSVImport.findUserIdByName((String) inputMap.get("Handler")));

taskMap.put(CSVImport.TASK_SUBMIT_DATE, inputMap.get("Submit Date") != null ?
     df.parseToCalendar((String) inputMap.get("Submit Date")) : null);

return taskMap;

Example 2

The CSV file and the CSV Import script shown below demonstrate how to import custom fields of various types.

CSV file:

Copy Code

Name,Category,UserUDF,URLUDF,MultiListUDF,TaskUDF,IntegerUDF,ListUDF,DateUDF,FloadUDF,StringUDF,MemoUDF
"Products","Folder","jsmith;pdagley;slaw","http://www.trackstudio.com - TrackStudio",
     "multiListValue3;multiListValue4","#2;#4;#5","54","listValue2","12/16/05 3:38 PM",
     "5.6","stringUdf value","memo UDF value"
"Customer Support","Folder","cparmenter;smanske","http://localhost:8888/TrackStudio - local TrackStudio instance",
     "multiListValue1;multiListValue2;multiListValue4","#15;#16;#18;#22;#23","33","listValue2","12/30/05 3:40 PM",
     "43.6","str val2","memval2"

CSV Import script:

Copy Code

Map taskMap = new HashMap();
String locale = sc.getUser().getLocale();
DateFormatter df = new DateFormatter(sc.getUser().getTimezone(), locale);

taskMap.put(CSVImport.TASK_NAME, inputMap.get("Name"));

taskMap.put(CSVImport.TASK_CATEGORY_ID,
   CSVImport.findCategoryIdByName((String) inputMap.get("Category")));

Map udfMap = new HashMap();

if (inputMap.get("MemoUDF") != null)
    udfMap.put("MemoUDF", inputMap.get("MemoUDF"));

if (inputMap.get("DateUDF") != null && !inputMap.get("DateUDF").equals(""))
    udfMap.put("DateUDF", sc.getUser().getDateFormatter().parse(
        df.parseToCalendar((String)inputMap.get("DateUDF"))));

if (inputMap.get("FloadUDF") != null)
    udfMap.put("FloadUDF", inputMap.get("FloadUDF"));

if (inputMap.get("IntegerUDF") != null)
    udfMap.put("IntegerUDF", inputMap.get("IntegerUDF"));

if (inputMap.get("StringUDF") != null)
    udfMap.put("StringUDF", inputMap.get("StringUDF"));

if (inputMap.get("ListUDF") != null)
    udfMap.put("ListUDF", inputMap.get("ListUDF"));

if (inputMap.get("MultiListUDF") != null)
    udfMap.put("MultiListUDF", inputMap.get("MultiListUDF"));

if (inputMap.get("TaskUDF") != null)
    udfMap.put("TaskUDF", inputMap.get("TaskUDF"));

if (inputMap.get("UserUDF") != null)
    udfMap.put("UserUDF", inputMap.get("UserUDF"));

if (inputMap.get("URLUDF") != null)
    udfMap.put("URLUDF",inputMap.get("URLUDF"));

taskMap.put(CSVImport.TASK_UDF_MAP, udfMap);

return taskMap;

Example 3

The following script shows how several linked objects are imported. This script imports tasks and if there is no user with the task handler's name in the system, the script creates a user with the specified name:

Copy Code

Collection list = new ArrayList();

Map taskMap = new HashMap();
taskMap.put(CSVImport.TASK_NAME, inputMap.get("Name"));
taskMap.put(CSVImport.TASK_CATEGORY_ID, CSVImport.findCategoryIdByName((String)
inputMap.get("Category")));
list.add(taskMap);

String msgHandlerName = (String) inputMap.get("Message Handler");
String msgHandlerId = null;
Map userMap = new HashMap();
if (msgHandlerName != null && msgHandlerName.length() != 0) {
   msgHandlerId = CSVImport.findUserIdByName(msgHandlerName);
   if (msgHandlerId == null) {
       userMap.put(CSVImport.OBJECT_TYPE, CSVImport.USER_TYPE);
       userMap.put(CSVImport.USER_NAME, msgHandlerName);
       userMap.put(CSVImport.USER_LOGIN, msgHandlerName);
       userMap.put(CSVImport.USER_PRSTATUS_ID,
            CSVImport.findUserStatusIdByName("administrator"));
       userMap.put(CSVImport.USER_COMPANY, "Co.");
       list.add(userMap);
   }
}

Map messageMap = new HashMap();
messageMap.put(CSVImport.OBJECT_TYPE, CSVImport.MESSAGE_TYPE);
messageMap.put(CSVImport.MESSAGE_TASK_ID, taskMap);
if(msgHandlerId != null )
    messageMap.put(CSVImport.MESSAGE_HANDLER_USER_ID, msgHandlerId);
else
    messageMap.put(CSVImport.MESSAGE_HANDLER_USER_ID, userMap);
messageMap.put(CSVImport.MESSAGE_MESSAGE_TYPE_ID,
     CSVImport.findMessageTypeIdByName(
     (String)inputMap.get("Message Type"),
     (String)inputMap.get("Category")));
list.add(messageMap);

return list;