Merlin import of EXCEL files

Posted on by

Merlin can import  lots of file formats, here a list of the currently supported formats:

  • MS Project (.mpx, .mpp, .xml)
  • OmniOutliner (.ooutline, .oo3)
  • OmniPlan (.omniplan)
  • NovaMind (.nmind)
  • MindManager (.mmap)
  • Merlin 1 (.merlin)
  • Merlin 2 (.xml)
  • Text files (.txt, .csv, .tsv)
  • OPML files (.opml)

So you look at the list above, have project WBS saved in MS EXCEL and don’t know how to proceed?

Import from EXCEL is also possible, it cannot work however seamlessly. Merlin cannot know where project related information is contained in any particular EXCEL file, which EXCEL columns contain which Merlin column information or how contained values are to be interpreted in each and every case.

An MS Project, Merlin, OmniPlan, Mindmap file contains data in a predefined way. That means, a developer know exactly where to find  the value of task’s title or task’s duration and map it to Merlin title and duration of a newly created task. So when considering importing EXCEL files into Merlin to create tasks out of the sheet, you have to keep in mind that you will perform some additional configuration steps. EXCEL is not a project management software after all, isn’t so?

So, first step is to open your EXCEL file in Microsoft EXCEL and save the sheet as a text file. It can be a comma separated values (.csv) or tab delimited text (.txt) file. As next you open this file with Merlin, select correct separators, quotes, line endings, map your information to Merlin columns and let Merlin create the new tasks for you.

The workflow in detail:

Lets suppose you have an EXCEL sheet.
Your headers are on row 1 and your data are on following rows. Headers: Title | Work | Responsible | Note
Microsoft Excel004

Step 1: You open your EXCEL file in Microsoft Excel, and save as ‘Comma Separated Values (.csv)’
EXCEL creates a file with the file ending csv, separated by semicolon (;)
Note

    If your EXCEL file contains more than the WBS you want to use in Merlin (for example some commenting text on the first or trailing rows), copy the cells with the column header and the data, paste into a new worksheet, and select this sheet when saving into a text file.

Step 2:  You drag the csv file onto Merlin’s application icon, or use Merlin’s menu File > Open
Merlin will show the import assistant dialogue
Step 3: First things first…configure the import
– Select at the bottom of the dialogue the correct separator: Semicolon (;) most probably.
Tip:
If you are not sure, just open the csv file with a text editor to see the separator symbol

BBEdit001
– Select the wrapping way, by single, double quotes, or nothing at all.
Tip:

    If you are not sure, just open the csv file with a text editor to see if your text is quoted

– Select the correct Line ending.
Tip:

    If you are not sure change between Mac/DOS/Unix and select the one which lets Merlin interpret the correct amount of columns and records contained in your CSV.

– Select the correct encoding. Change between Western (Mac OS Roman) if the file comes from a Mac user and Western (ISO Latin 1) or Western (Windows Latin 1) if the file comes from a Windows user and see the values in the records. Do they show valid texts and values or do they convert into gibberish?
Tip:

    If you are not sure what is correct, just open the csv file with a text editor like TextWrangler (free) or BBedit showing the encoding.

– Leave Outline levels to 1, as you are not using any levels.
– Do not include first row. It contained the headers remember?
Step 4: Now you can map csv fields to Merlin columns…
– Click onto ‘Show all import fields’
– Now drag your headers from record 1 on the left side of the dialogue to Merlin columns on the right side. That means; ‘Title’ to ‘Title’, ‘Work’ to ‘Given Planned Work’, ‘Responsible’ to ‘Assigned Ressources’, ‘Note’ to ‘Notes.
Merlin011
– Click on ‘Ok’
– Check your created tasks in Merlin.
Merlin012

10 thoughts on “Merlin import of EXCEL files”

  1. I am having difficulty with the export/import functions of Merlin. I need to take project data from Excel spreadsheets and import them into Merlin. Very simply the sheets have:

    Client Name
    Project Name
    Task Name
    Estimated Start Date
    Estimated Finish Date
    Actual Start Date
    Actual End Date
    Priority
    Completion %
    Comments

    All of which map to Merlin fields. However, Merlin doesn’t seem to support importing in a hierarchical manner.

    For example, create a simple project plan with one activity, some sub-activities, and some sub-sub activites in Merlin and Export to CSV. Then import that file immediately back into Merlin. The formatting is corrupted.

    Is there an example of a multi-level project plan exported from Merlin that I can use as a template?

    Thanks

  2. Please make sure to enable ‘outline levels’, when exporting your project with levels into a tab delimited csv. Should you open such a csv file in a text editor, you will see the title column spliced into the maximal amount of defined levels. Titles for tasks on level 1 are placed in the first column, titles for tasks on level 2 are in second column, etc… If you try to re-import this file in Merlin you should make sure the outline level is set accordingly (it was 3 in my case). This will bring back the activities with their level.

    So I would suggest you format your CSV file containing these extra columns and the titles placed accordingly.

    Best regards, Vicky

  3. During meeting, I capture project plan data (key milestones/tasks/sub-tasks, notes, start date, and due date) in Excel.

    I am trying to import the sub-tasks and both start & end dates, and am not having any success. I’ve got the information on a CSV file. Tasks are in row A, subtasks in row B, notes in row C, etc…

    How can I import this data and have the sub-activities, start date, and end date automatically populate?

  4. Please note that data can be either in column A or in B.
    Now you can set outline levels to 2 and map the fields to the appropriate fields (tasks > titles, notes > notes, dates > given planned earliest start or given planned earliest end, etc… )

    Best regards, Vicky

  5. Trying to import from a ticket tracking system into Merlin. Created user defined fields in Merlin to handle certain meta data from source that is not in Merlin. However, user defined fields are not showing to be mapped to as destination fields in Merlin. Admittedly I am typically a MS Project user so I have no doubt I am missing something, but working with a client that uses Merlin, so when in Rome…

  6. That’s right, you cannot map to user defined fields, as you are not importing into an existing file. Merlin creates a new project out of the csv file.

    You may consider mapping such information to the fields ‘subtitle’, ‘additional title’ or ‘notes’

    Best regards, Vicky

  7. Hi

    How can I import only resources from an external excel sheet into merlin.

    Cheers Markus

  8. Hmm, you can import activities over CSV.
    Resources can be inserted over the library (and can be contacts of your Address book, MS Entourage, LDAP server)
    For more information

    To import the resource’s names of an CSV file containing only a list of resources you could do the following:
    – Open the cvs file in Excel or Numbers.
    – Insert one more row underneath the header and just before your resources rows.
    – Enter a dummy title underneath your title column.
    – Now select the rows containing the resources’ names and copy.
    – Insert a column called “Assigned Ressources”
    – Click into the assignment resources cell on the dummy activity row and paste the resource names.
    – Separate this text with a semicolon “;” (it should look like: resource a;resource b; resource c)
    – You may remove all other rows than the first 2 from your sheet
    – Save in a new CSV file

    Drop this file into Merlin and map title to title and assigned resources to assigned resources.

    See Merlin creating a new file with the dummy activity and your resources assigned onto it. Now you may delete the activity and keep your resources.

    Best regards, Vicky

  9. A perhaps easier way of importing resources:

    1. Insert a column to the left of your resources column in Excel, for example. In your new, empty column A, create “Dummy activity 1” and so forth (as many as you have resources; just hover over cell A2’s bottom right corner and double click to copy a series “Dummy Cell 2” etc. Column B should now be a list of your resources name. Make sure cells A1 and B1 are column titles.

    2. Save as .txt, open with Merlin, and map column A to Merlin’s “Title” and column B to Merlin’s “Assigned Resources”

    3. In Merlin, open resources window. Paste your resources from your newly created Merlin project into the project you are working on, voilà. Delete the intermediary project if you don’t need it.

  10. Hello, I have an existing project in merlin and I have for each activities only the titles. I have been sent the list of subtitles for each activity in an excel file. Can I import this list of subtitle and match it with the existing list of activities in merlin ?

    thanks

Comments are closed.