Can not import master data from Excel using Excel Mapping Tool

Hi! Experts :

I’m using v4.0 sp3. I would like to move master data from production database to new database using Excel mapping tool. I’ve successfully exported the data from production database to excel file using the tool. But when I am in new database and use the Excel Mapping tool to import the excel, nothing happen! It just pop up a confirm box asking me close the excel and I press Yes… nothning happen… No error message and the data does not import.

I have to create a new database with only the master data and all the original setting from production DB by next week. Could anyone help me, please?

From memory, I think there is a setup checklist that allows you to copy setup data from one company to another (note company not database). Why not use this to create a new company and then copy the settings directly from the other company?

You could then backup just that company and restore to your new target DB.

Did you recreate mapping definitions in new DB?

Without them, you can not IMPORT data… BTW, there exist a function Export definition, but I can’t figure out how to import these back, as TWO tables are involved, 8601 Mapping Header & 8602 Mapping Lines.

After trying hours and hours, I’ve finally solve it. So I would like to share my experience by answering my question.

To Modris, I’m actually create a new company in the same database. so the definitions should be the same. Because this situation happens just to a few tables I import. Most of other tables works fine. And I use “Copy Data” in “Setup Checklist” to copy most of the settings first and then I use excel mapping tool to import the tables not included in the “Setup Checklist”. The table 8601 and 8602 are included in the Setup Checklist. And when I export data, Nav does export table definition with data. (definition in first 4 lines) In the excel mapping tool, you go to functions button, you’ll see “import data” function. That is where you can import data from excel.

These are the steps I did to copy data : (I’m using v4.0 sp3)

  1. “Administration” → “IT Administration” → General Setup" → “Setup Checklist”
  2. “Functions” → “Copy Data…”
  3. Pick a “Copy from” company and select all the tables listed and press OK. Nav will copy all the tables you select to new company, including 8601 and 8602.
  4. Go back to the old company you would like to copy data from. “Administration” → “IT Administration” → General Setup" → “Setup Checklist”
  5. “Functions” → “Excel mapping tool”
  6. Create a new Mapping setup and choose the table you would like to export and excel file name.
  7. “Functions” → “Insert fields from table”. It will update the fields of the table and map it to excel cell.
  8. “Functions” → “Export data”. Data of the table is now exported to the excel.
  9. Go to the new company. “Administration” → “IT Administration” → General Setup" → “Setup Checklist” → “Functions” → “Excel mapping tool”
  10. Create a new Mapping setup same as you created in step 6 and choose the table you would like to import and excel file which you would like to import from.
  11. “Functions” → “Insert fields from table”. It will update the fields of the table and map it to excel cell. It should be the same as step 7.
  12. “Functions” → “Import data”. If everything is fine. The data will be in new company.
    You can export 1 table at a time using excel mapping tool. So export all the tables using step 6~8 and then continue.

The last step is where I screw up, Nav shows no message and the data is not imported.
Surprisingly, the solution is very easy. You delete the mapping setup you just create in step 10 and recreate a new mapping setup with exactly the same setting and import it again!!! If it still not work, go back to step 6, delete the mapping setup and redo it from step 6.
I have no idea why it’s working but this is what I did to copy most of the master data and settings from old company.

The copy data function doesn’t copy all the setting correctly. Sometimes you just have to redo it again and again. But now I still can’t figure out how to copy the relation between contact and customer/vendor. Now in my new company the contact and customer/vendor data are all there but they are not connected as the old company.
If anyone knows how to rebuild the relation, please tell me. Thanks

Hope this solution helps.

is stored in T5054 Contact Business Relation.