How to import tickets into Assembla

api, assembla, ideas Add comments

I use Assembla for my projects, and I found that export/import tickets feature works tricky — it works based on a combination of JSON and CSV format. It seems the guys in Assembla are on a way to proprietary formats 😉 The only usage of it is only for moving tickets from one Assembla space to another.

So this post is about how to use what they offer to add lots of tickets that you have listed in your text file. I am lazy — instead of adding 90+ tickets manually, I’d better create a tool to do it for me.

Task

You have tickets titles and descriptions as text, and you want to add these tickets to Assembla.

My solution

Let’s investigate what format should our data be of so that Assembla could digest it. Go to a space → TicketsSettings → scroll down and find Export & Import → click “Export your tickets” links and open the dump file in a text editor. Though it’s indicated that “Tickets imported and exported in JSON format.“, it’s not. It’s a mix.

The dump file contains blocks that represent spaces, milestones, tickets, comments, custom fields values and tickets associations. Every such block contains 2 parts: list of fields and bigger part — actual data of this fields.

What we need is tickets part.

This is a format definition part:

tickets:fields, ["id","number","reporter_id","assigned_to_id","space_id","summary","priority","status","description","created_on","updated_at","milestone_id","component_id","notification_list","completed_date","working_hours","is_story","from_support"]

And this is an example of ticket data:

tickets, ["40999","1","dNltOqvXKr3RN3e","FoGbar3zTtab7rAJ","b3OWYeJe5aVNr","Property owner profile - create new","3","3","","2009-06-16 07:23:35","2009-07-05 10:43:58","94111","12",null,"2009-07-05 10:43:58",null,"0","0"]

So, the solution is to create a list of tickets data by the format given. Here is how I did that.

1. I have Excel spreadsheet with two columns — the ticket title and description.

2. Export it as CSV, and open it having it like this:

Home page: Update design,Set numbers after locations
Photos: hide step 2,Show only Step 1 until you select files and then show step 2 to upload files.

3. Use text replace to put every value in quotes (hint: use a line ending symbol as a replacement — even Microsoft Word can do that)

"Home page: Update design","Set numbers after locations"
"Photos upload: hide step 2","Show Step 2 only if files are selected."

(One way round is to load the CSV data into database by PHPMyAdmin and export it back as CSV — it will be quoted 🙂 )

4. Add Assembla header to the top of this file

5. Move ‘summary‘ and ‘description‘ columns go first, so it was

tickets:fields, ["id","number","reporter_id","assigned_to_id","space_id","summary","priority",...

and it becomes

tickets:fields, ["summary","description","id","number","reporter_id","assigned_to_id","space_id","priority",...

(We need all the columns because it seems that Assembla’s import engine is not smart enough to deal with missing fields).

6. Edit our tickets data lines (text replace again) so that it looked like Assembla tickets data, i.e. add ‘tickets, [‘ at the beginning, dummy values for the rest of the fields and the closing square bracket. Don’t forget that we moved ‘summary‘ and ‘description‘ columns to go first!. So we have:

tickets, ["Home page: Update design","Set numbers after locations","40999","1","dNltOqvXKr3RN3e","FoGbar3zTtab7rAJ","b3OWYeJe5aVNr","3","3","2009-06-16 07:23:35","2009-07-05 10:43:58","94111","12",null,"2009-07-05 10:43:58",null,"0","0"]
tickets, ["Photos upload: hide step 2","Show Step 2 only if files are selected.","40999","1","dNltOqvXKr3RN3e","FoGbar3zTtab7rAJ","b3OWYeJe5aVNr","3","3","2009-06-16 07:23:35","2009-07-05 10:43:58","94111","12",null,"2009-07-05 10:43:58",null,"0","0"]

By the way, I prefer to clear the values of some columns like ‘id‘, ‘number‘ (ticket number) and so on — so that Assembla assigned it itself.

OK, it seems your file is ready to be fed to Assembla. I advise you to create an empty free space and try on it first if don’t want to mess up your current space.

If the import engine has some parsing problems with your file (missing quote for example) you will get a notice. If you get an Application Error, some columns or their values are missing — check your file.

One more tip — your tickets will be added as ‘No milestone‘. If you want your new tickets to be added into a new milestone, paste a milestone defenition in the beginning of your file and replace milestone ID in the tickets data by it’s ID:

milestones:fields, ["id","due_date","title","user_id","created_at","created_by","space_id","description","is_completed","completed_date","from_basecamp","basecamp_milestone_id","updated_at","updated_by"]
milestones, ["1111","2009-09-09","Big shot of development","cv2gFo","2009-08-25 13:12:09","cv2gFo","bktPVqwKmr3OWYeJe5aVNr","Work hard to finish all that was discussed.","0",null,"0",null,"2009-08-25 13:12:09","cv2gFo"]

3 Responses to “How to import tickets into Assembla”

  1. nafg Says:

    You can get Excel to export with quotes, if I’m not mistaken.
    Would you be able to show a sample complete file? I’m not getting the big picture. Thanks.

  2. nafg Says:

    Another question – if you want to edit ticket numbers can you export your tickets, edit the numbers, and re-import them? Thanks.

  3. steveswl Says:

    I’m trying to convert the dump.js file from a space backup to something more readable like converting it from its CSV/JSON format to valid JSON. Any suggestions?

WP Theme & Icons by N.Design Studio
Entries RSS Comments RSS Log in