Articles in this section

See more

Import data from a CSV file

Avatar
Toshi Davila
Updated
Follow

1. Overview

The CSV import to Assembla Tickets can be performed in the page Tickets > Settings > Export/Import of your Assembla space. This importer requires a CSV file (maximum size 500KB). If your CSV file is larger than 500KB, you should break it into individual files of 500KB or less.

2. Encoding and charset

The CSV file is expected to be in Unicode format, specifically UTF-8 without BOM. If the file is not in UTF-8 without BOM, our parser won't be able to read the file properly.

3. Header row

The first row of the CSV file must contain comma-separated keywords for the reserved standard ticket fields. Here are the requirements the header row should obey:

  1. The standard field keywords must be separated by commas (,)
  2. For the standard ticket fields (non-custom fields), there is a list of specific/reserved keywords that must be used if you use any of them. That list is below in item 5.
  3. The summary field is the only field that is required
  4. If your header row has one or more custom fields in it, you must include a second header row to specify the type of each custom field. If the custom field does not already exist in the space, it will be automatically created during the import process.
  5. This is the list of the valid standard ticket fields:
    • assigned_to_id - "Assigned To" - it contains the user IDs which are necessary for importing the users correctly
    • description - "Description" - it contains the ticket's description. Any newline char must be represented as \n
    • due_date - "Due Date" - it contains the ticket's due date. The format is explained here. 
    • estimate - "Estimate" - it contains the ticket estimate data. It can be None/Small/Medium/Large, or points, or hours (numeric), depending on your estimate-type setting in the Tickets tool.
    • milestone - "Milestone" - it contains the ticket's milestone. The milestone is automatically created if it does not already exist in that project.
    • number - "Number" - it contains the ticket number in the project. This is normally ignored, unless the project doesn't have any tickets yet (and tickets with repeating numbers won't be created in such cases).
    • permission_type - "Permission Type" - it contains the permission type (Public, Private, or none). If none, it is defined as an internal ticket in the Ticket tool. If Public or Private, it defined as a ticket in the Support tool (if the Support Tool is enabled in that project).
    • plan_level - "Plan Level" - it contains the ticket type. It can be an regular ticket (with no value), Epic, or Story.
    • priority - "Priority" - it contains the ticket priority. It can be Lowest, Low, Normal, High, Highest. This field is not case sensitive.
    • status - "Status" - it contains the ticket status data. It automatically creates any non-existent statuses.
    • summary - "Summary" - it contains the ticket summary data. This is the only REQUIRED field.
    • tag_names - "Tags" - it contains the ticket tags. If more than one tag, they should be surrounded by double quotes as any single string, and separated by commas.
    • working_hours - "Work Remaining" - the hours remaining for the ticket to be finished.

The following field keywords are ignored by default:

  • assigned_to_name
  • completed_date
  • created_on
  • last_status_or_milestone_change
  • reporter (the reporter is always the user who imports the tickets)
  • space_id
  • total_estimate
  • total_invested_hours
  • total_working_hours
  • updated_at

If the field name is not one of the valid keywords mentioned above, it will be considered as a Custom Field and must be defined in second row, indicating the custom field type.

4. Basic considerations about the column data

  1. Any data that has a comma in it (most commonly found in the summary and in description fields) must be surrounded by double quotes " ".  For example, see the summary value in line 3 of example 7.1 below.
  2. For any data that contains double quotes, the entire cell data should be surrounded by double quotes.  For example, see the summary value in line 2 of example 7.1 below.
  3. If cells are blank in the columns of the standard ticket fields, that ticket will be created using the default value for that standard ticket field.  For example, see the status value in line 2 of example 7.1 below - Status will automatically set to 'New', if that is the first status in the Ticket tool settings.
  4. If you want multiple lines inside your description field, instead of using any kind of newline character, just use \n and it will be automatically converted into a newline when read by the parser.  For example, see example 7.2 below.
  5. The very same logic described on items 1 and 2 above is also applied to custom field names on the first line.
  6. If any line has more or less columns than specified in the header line, the parsing will fail and stop on that line.  The tickets prior to that will be created, but the import will stop at the problematic point in the CSV file.

5. Custom Fields and the second header row (definitions row)

If you have custom field columns in the first row, you will also need to use the second row in your CSV file to define each custom field by type (expressed by specific keywords).  These are also separated by commas. The custom field keywords are:

  1. text - for custom field of type "Text"
  2. numeric - for custom field of type "Numeric"
  3. list - for custom field of type "List"
  4. teamlist - for custom field of type "Team List"
  5. date - for custom field of type "Date"
  6. datetime - for custom field of type "Date Time"
  7. checkbox - for custom fields of type "Checkbox"

A. Notes about the custom fields definitions row 

  • The type keywords are not case sensitive. For example, you can use "text" or "Text" or "TEXT".
  • For every cell in the column of a standard field (non-custom field), just leave it blank.

B. Due Date, Date and Datetime fields

Fields of type Date expects two possible formats: YYYY-MM-DD or DD-MM-YYYY. For the first case, the dashes (-) can be omitted or replaced by periods (.) or forward-slashes (/). For the second case, the dashes can be replaced by periods (.) or forward-slashes (/) but cannot be omitted.

While custom fields of type DateTime follow the same pattern for the date, the time format is more flexible. Generally speaking, the supported format in this case is DD-MM-YYYY HH:MM:SS but seconds, minutes, and hours can be omitted. If seconds are omitted, it will default to zero for seconds. That applies for minutes and hours as well. The separator for the time components must be colons (:) but that can be omitted as well. If omitted, the time components must be expressed by two digits. But that is not necessary when you use a colon as the separator, so for example the following is allowed:  2013-05-22 5:4:2

C. Checkbox custom fields

Any value that is either yes, true, or 1 (case insensitive) will be considered as a "yes" (checked) value, otherwise it will be considered as a "no" (unchecked) value, except if the value is left empty.

D. Numeric custom fields

Only integer numbers and floating numbers are allowed in this case. The dot (.) should be used as the floating part separator. Here are examples of the allowed formats: 1.45, .32, and 10.

6. User data columns

When any user data column is present (such as "Assigned To"), there are two columns (such as assigned_to_id and assigned_to_name), one suffixed with "_id" and the other suffixed with "_name". The latter is disposable, because the import process doesn't need it. That is kept just in order to make it more readable, especially for people that load CSV files as spreadsheet reports.  For instance, user data comes in two columns when you export a CSV ticket report in the Tickets-List page. If you omit the "_id" suffix and the assigned_to column has the user id value, that will also work.
​

That does not apply for the reporter column because it is ignored in the import process anyway.

For custom fields, if you have a custom field of type teamlist in the middle of the report that you are exporting into CSV, two columns will be generated for it. If your custom field is called Tester, then in the exported CSV file you will see Tester_id and Tester_name.  The latter is not used in the import process - only the IDs matter. So if you want to import a CSV file that you manually created, make sure your custom fields of type teamlist have the user IDs in their respective columns and that the custom field name ends with "_id". In the same way as the assigned_to field, the "_id" suffix here is also optional, but if you have the "_name" field, you'll need the "_id" field as well. The "_id" in the name is only necessary when the field with "_name" suffix is present. 

Also, it's important to remember that only the users who are part of that project workspace will be considered. If you have users in your CSV that are not members in the Team page of the project in which you're importing the tickets, the user data will be discarded.

7. Examples

A. CSV file with non-custom fields, a summary with double quotes in it, and a multi-line description:

number,summary,reporter,assigned_to_id,assigned_to_name,status,priority,estimate,tag_names
5,"This ticket summary has ""double quotes"" in it",Jon Hale,,,,,Large,"PM,Design"
9,"This ticket summary has a comma in it, did you see it?",Mark Robbs,dBE5iwS7qr4BRircPG94x2,Jon Hale,Accepted,Highest,Small,PM


B. CSV file with a multi-line description:

number,summary,status,milestone,description
10,"Cycle tool refactoring, and other stuff",Accepted,Backlog,
12,App server maintenance,,Current,"This is \n a multi-line \n description."


C. CSV file with custom fields (Date, Datetime, Teamlist, Checkbox):

summary,status,Deadline,Reviewed On,Reviewed By,Audited?
,,date,datetime,Teamlist,CHECKBOX
"Cycle tool refactoring, and other stuff",Pending Review,2014-01-01,,,no
App server maintenance,Pending Test,,2013-10-10 22:43,dBE5iwS7qr4BRircPG94x2,YES


D. CSV file with custom fields (List, Numeric, Text):

summary,status,App Section,Developers Needed,Side notes
,,list,Numeric,text
Summary A,First Status,Mobile App,4,This requires Brent
Summary B,Second Status,Stats Generator,2,We should meet with Design team first

Comments

0 comments

Please sign in to leave a comment.