none
Marge Data from Excel to Project with Marge Key RRS feed

  • Question

  • Hello.

    I want to load data from Excel into Project using an import map. Loads the milestone's base start date and actual end date.

    I select the join of data from the wizard, set the marge key, and read it. However, the data is not read where the marge key should match, and a new task is created in the last row.

    The marge key is a text custom field with the content "[project name] _ [task name]". It should match, but will not load properly.

    What is the cause?
    Tuesday, March 10, 2020 6:18 AM

All replies

  • aoiiicoelacanth,

    How did Marge get into the mix? I thought Homer kept better wraps on her :-)

    When you say "join of data" do you mean "append" or "merge". If you selected "append" then indeed the import task will be appended to (added at the end of) the file. But even if you did select "merge", using a text based field as the merge key can result in some unexpected results. For example, I did a little test using the Project and Excel files shown below

    Project before import

    Excel file

    Result in Project after import using Text1 as the merge key. Note that the actual finish date is dumped into both milestones.

    You're much better off if you use the ID or Unique ID fields as the merge key or even a custom number field since it's easy to get crosswise with the Unique ID if the Excel data doesn't faithfully replicate the unique ID sequence.

    So, it's not clear exactly what you mean by, "...will not load properly", but I suspect it is due to the text based merge key you used.

    John


    • Edited by John - Project Tuesday, March 10, 2020 4:58 PM corrections
    Tuesday, March 10, 2020 4:55 PM
  • John,

    Thank you for your support! And Sorry to trouble you about my poor English. My mother language is Japanese.

    "Join of data" means "marge". So I try again with number marge key.   In conclusion, it did not work.


    I put an arbitrary number in the number field (Number 4) as a test.  All cells that do not have numbers are originally 0.  And make a text field (Parameter) .


    Next, I prepared Excel sheet that has 4 datas.  A column is marge key "Number 4" (1, 2, 3 and 4). This is a numeric data type.  B Column is "Parameter" which is text data type (A,B,C and D).


    This Excel sheet was imported using "marge" with the merge key set to Number 4.  Then, the second row of the Excel sheet (merge key: 1) was imported to the first row of the Project whose marge key is 0. 
    Even though there is a row whose marge key is "1"...  And the remaining three data were added to the bottom row. Even though there are rows whose key is 2,3 and 4.

       

    This phenomenon is the same as when using text data for the merge key...:( I have no idea about this reason.

    Wednesday, March 11, 2020 6:22 AM
  • aoiiicoelacanth,

    Based on the description of your test, this is what I think your Project file looks like before the import. The Text1 field has been renamed as "Parameter":

    And this is what the Excel Worksheet looks like:

    To do the import, this is what the import map should look like. Note the merge key is "Number 4". Also the import was set to "merge" the data and the import does include headers:

    When the import is finished, the Project file will look like this:

    If that is not how you set it up, then you are not doing the import correctly.

    Hope this helps.

    John


    Wednesday, March 11, 2020 4:21 PM