Navision 2.01B Data extraction

I have a Navision 2.01B install.

I need to get my company data out, into another format (SQL, XLS, MDB, CSV …)

The install has no C/ODBC granule in the license file.

Suggestions?

You could use dataports. Or your partner license should have the necessary granules.

We haven’t had a Navision partner in years… Any good resources on dataports?

Well it depends on what you are trying to archive. Getting data out can be done by many means. Dataport is only one. C/ODBC as you know is another.

But it depends on what you are trying to do. Is it for example a “one time” export in connection with a migration to a different system, or is it a fixed export to use to integrate with a different system? Or something completely different.

And based upon what you need, then I would different tools.

PS: I assume that your Navision is the so-called “native” - not Microsoft SQL Server. Am I right?

Good intuition… it’s for a migration to a new CRM suite (not NAV).

Ideally, it’s a “two-time” export, once to get test data, once for final data, and the DB IS “native”.

I’m a “power user” (allegedly) I’ve done good stuff with perl DBI connectors from Foxpro to MySQL, LAMP apps, ODBC Excel VBS stuff… but this C/SIDE proprietary db and the limitations with the license and little documentation has me looking for directions to turn…

If I could “grab the essential tables” (assuming i know what that means) i’d do that… if i could convert the whole DB to SQL, i’d be done… i’m just very disoriented wrt what tools and options are available for this 10 year old setup.

This is the same question you asked on mibuso. Are you looking for a different answer?

With no c/obdc, no partner & limited docs - your best bet is dataports and if you’re just looking at main tables then it should not be to hard.

goto: http://www.microsoft.com/downloads/en/details.aspx?familyid=A3C9C3E3-11FC-446A-AC12-F6AD0749CB50&displaylang=en

download - print & read the Application Designers Guide - chapter on dataports.
w1w1adg.pdf

PS: you could always just hire someone to get the data you need out of nav too.

(Linked Posts: http://www.mibuso.com/forum/viewtopic.php?f=1&t=44666 )

I think Harry is right. If you don’t want a partner, then dataports are your best option.

But you should really consider contacting a partner, or one of the many freelancers here in Dynamics User Groups. If you clearly specify which data you want out, then I’m sure that you can get a very good price.

The ones i am aware of are

1)running the apporpriate tables and copying to Excel

This would work fine but i get “cannot access table” errors on some tables (the “items” table for one) even though i’m a superuser… can’t get past that…

2)Dataports

I’m trying them out now, but getting “you do not have permission” errors when trying to save them, so… researching what that could be…

3)Conversion

As i understand, tools don’t exist outside the Navision Partner network to do this. I have two answers from the two groups:

1)It’s easy, pay me and i’ll get it done

2)It’s very difficult to go from Navision 2.01 → SQL and you will corrupt data

I’d be thrilled to hire the job out, but I’d like to get a better feel for the cost and caveats of the conversion before before jumping into it. Right now i feel like a little old lady taking her car to the mechanic, they could tell me they need to replace the rings because the brakes squeak, and i’d be none the wiser.

4)From my research… those are pretty much the range of options, if the group has more, i’d love to hear them.

I’m actually pretty quickly getting over this Navision C/SIDE DB data extraction project learning curve thanks exclusively to the experience i’ve been able to tap on this group and the mibuso.com forum… so thanks for that!

If anyone’s interested in the job, it sounds like a quick buck… message me.

Again, thanks for all the valuable insights… For a small non-profit, a resource like this forum is really invaluable.

Well there are lots more options. But I think that we are focusing on the ones that makes sense. so my summary…

1/ Open tables and copy to Excel.
Advantages

  • You can do it yourself, no NAV skills required.
    Disadvantages
  • Slow.
  • Issues with large tables, might run out of memory
  • Permission issues with tables you can’t access. (This does seem strange though, you should have permission, it could be that your partner did something with their license).
  • Could be problems with delimiters, leading zeros etc in Excel.

2/ Dataports
Advantages

  • Dataports only need to be written once.
  • You probably have the license to do this.
    Disadvantages
  • You need to define exactly what is needed, then work with the new company to make sure the Data matches what they need.
  • You need some training to learn how to create dataports.
  • Its going to take a lot of your time becasue of the learning curve.
  • Issues with delimiters commas, quotes etc.

3 / Convert DB to SQL
Advantages

  • The new company understand the NAV SQL db, so there is no work involved there.
  • You have the SQL DB for ever to use for historical reference.
  • You just give it out as a job and everything is converted.

Disadvantages

  • Has to be done by someone skilled in NAV and SQL.
  • Has to be done twice.

4/ Open DB with 5.00 or later and use the Export To Excel Function.

  • Same really as copy paste to Excel, but more “user Friendly”

5/ C/ ODBC

  • Similar to Dataports, except your license wont allow this, so a partner needs to do it.

6/ C/FRONT

  • Way to complex.

7/ Use report to export data as code.

  • Way to complex.

8/ Use Reports to connect via ADO to an external SQL database

  • No real advantage over dataports, and more complex.

9/ Move to newer version and export as XML files.

  • No real advantage over dataports, and more complex.

So in summary, really its a choice between Dataports and SQL.

In my mind it all comes down to the value of your own time, and how much you will have to pay the CRM company depending on the data type.

If you do the Dataports, you first need to get up to speed. that will probably take you about a day, you will find enough information on MiBuSo and Dynamicsuser.net, to do this. And the tool linked earlier looks like a great start.

Then you need to work with the CRM company to define all the data that needs to be moved over. They may have a list ready that they can give you, in which case its easy. You then need to work out which tables in Navision have the data you need to get, unless they can accept the data in RAW Navision format (which is what everyone here is assuming).

The big thing though is history. Do you need to get any transactional data out? Most likely not, in which case you should be able to do all this your self. But do they need opening balances. If so you need to work out how which flow fields to include and map them to what you need.

but the great thing about Dataports, is that once they are written once its over. You can run them 20 times and not have to do any more work.

With SQL the big plus is that the CRM partner know how to work with the DB, so you just give it to them. But there are two disadvantages,
1/ you need to pay someone to do it,
2/ It needs to be done twice.

In reality it just comes down to the value relation of money vs time. All the other issues are misnomers, since both options give the same end result.

Of course why not simply try to do the dataports yourself, and if that doesn’t work, then get someone to convert the database.

Hey David,

That’s one of the best replies I have seen here for a very long time! That really sums it all up. Basically nothing left to say!