Navision 2.01b data extraction

mpitcavage

I have a Navision 2.01B installation, we're upgrading to new (Not Nav) software and need data extracted from our 800mb Navision database

From what I see, I have a Navision license file that doesn't allow c/odbc connections.

Any suggestions which direction to start?

Alex_Chow

Run each table and copy and paste to Excel. Or use dataports.

mpitcavage

I'm running into some issues with the "run, copy to excel" method.. Although i'm logged in as someone with superuser rights, i'm getting errors that i don't have read permissions on some tables.

Also, i'm getting some hangups and memory errors on some of the larger tables.. so i'm not sure how feasible this method is. (I'll keep working it though, maybe throw the DB on a more powerful server if i can find one to dig past the memory limitations...

Any suggestions to get up to speed on Dataports? I'm going to start digging through documentation now.

And thanks for the quick response! It's given me some great direction and a good bite into the project.

David_Singleton

mpitcavage wrote:

I have a Navision 2.01B installation, we're upgrading to new (Not Nav) software and need data extracted from our 800mb Navision database

From what I see, I have a Navision license file that doesn't allow c/odbc connections.

Any suggestions which direction to start?

Your quickest, cheapest and simplest solution is to contact a Navision partner, and pay them to convert it to a SQL database for you. Also you will then have someone you can call to ask "What does this field do?".

Marije_Brummel

I'm not really sure if moving to SQL will add much. You may run into issues with double object names, illegal dates, characters etc.

What do you want to convert? When I implement NAV at a new customer I usually convert masterdata, current inventory, AR, etc.

If you are not familiar with how NAV works, you might want to call some help though. It can be tempting to export Item Ledger Entries in order to find current inventory rather than using the "Flowfields".

I think the best way to move forward is to use the dataports on Master Data and include the nessesairy flowfields. Or hire Alex and/or David.

Good luck on the project.

Oh and, just curious, why are you moving away from NAV?

mpitcavage

The backstory is that we're migrating from a Navision 2.01 bolted to a proprietary contact management database specific to our sector to an all in one solution by a pretty reputable provider.

The contact management stuff was in Foxpro and is very accessible, this Navision being in C/Side is kind of a hangup, because as you said, if you're not a Navision provider, conversion tools are not readily available.

The firm has done a number of Microsoft NAV data imports, and if my data resided in a newer more accessible MSSQL environment, i'd be able to shoot it over, they'd be familiar enough to convert, map, and do an initial import for our testing.

The last issue is i'm probably going to need to do it twice, once to get data for the testing phase, and once for the live data when we flip the switch.

David_Singleton

mpitcavage wrote:

The firm has done a number of Microsoft NAV data imports, and if my data resided in a newer more accessible MSSQL environment, i'd be able to shoot it over, they'd be familiar enough to convert, map, and do an initial import for our testing.

Are you sure?

If they had done this before, then they would know how extremely simple it would be to get this data into a newer version of the NAV executables. And if they have the tools for the newer versions they can still use them on older versions of Navision.

But Mark is right, that you probably don't need all that data, its just that I always found that getting the data in SQL made it just easier to handle. With dataports there always seem to be things like CSV delimiter issues etc. Copy paste is slow and cumbersome.

Anyway if you need help, just holler.

mpitcavage

Oh and, just curious, why are you moving away from NAV?

We had a separate CRM system with middleware that integrated our contact management to our accounting, the two separate systems worked best for our needs 12 years ago when we implemented it, but the company we're moving to is a more complete solution tailored more to our sector. A NAV upgrade would have required customizations this solution already accounts for.

mpitcavage

If they had done this before, then they would know how extremely simple it would be to get this data into a newer version of the NAV executables. And if they have the tools for the newer versions they can still use them on older versions of Navision.

I must have miscommunicated.. they are not Navision Partners and don't have any Navision tools.. They are looking to receive the data in a "common format" like SQL, XLS, MDB, CSV, TXT... C/SIDE is not on their list, and i have been tasked with either extracting or converting the existing company data residing in the native Navision DB to something "accessible"

David_Singleton

mpitcavage wrote:

If they had done this before, then they would know how extremely simple it would be to get this data into a newer version of the NAV executables. And if they have the tools for the newer versions they can still use them on older versions of Navision.

I must have miscommunicated.. they are not Navision Partners and don't have any Navision tools.. They are looking to receive the data in a "common format" like SQL, XLS, MDB, CSV, TXT... C/SIDE is not on their list, and i have been tasked with either extracting or converting the existing company data residing in the native Navision DB to something "accessible"

That makes sense.

Personally I would still go with just moving the whole thing to a SQL DB.

mpitcavage

I would too. How is that accomplished?

From what i've read, it seems like a pretty straight forward procedure if you're upgrading to NAV, otherwise it seems i'd have to hire a Navision Partner.

David_Singleton

mpitcavage wrote:

I would too. How is that accomplished?

From what i've read, it seems like a pretty straight forward procedure if you're upgrading to NAV, otherwise it seems i'd have to hire a Navision Partner.

Basically yes. You upgrade to SQL, then just throe Navision away and you are left with a SQL database.

Savatage

800mb Navision database isn't that big - what's your issues with dataports?

David_Singleton

Savatage wrote:

800mb Navision database isn't that big - what's your issues with dataports?

The issue comes down to who will do it. the key issue here is that the company working with the database doesn't know how to write Navision dataports. So someone would have to sit at both ends to say "what data do we need". And that means a cost in man hours. of course I guess you could just write dataports and export everything, but that would probably be more work than converting to SQL.

One the DB is in SQL they can just send to the new company and since that company already have experience with converting NAV SQL dbs, the learning curve should be less.

If they were having a Navision partner do this, then I agree dataports would be the way to go. But with non Navision people involved, SQL seems a safer (aka in the long run cheaper) solution.

Another option, is that most Navision end users ended out purchasing the Dataport designer, so maybe they have someone ( a power user) that could learn to write dataports and do it internally. But again it needs a NAV partner at least for training, and since they are leaving Navision, there is not a huge incentive for a partner to invest the time in a one of as small as this.

Savatage

I guess it also depends on what this new system actually just needs.

Are you looking to extract everything or just the main tables...??
like Item, Customer, Vendor, payment terms, salesperson/purchaser, etc.

That really simplifies the project
http://www.youtube.com/watch?v=0lo2ytGJiSE

David_Singleton

Savatage wrote:

I guess it also depends on what this new system actually just needs.

I was really working on the basis of this:

mpitcavage wrote:

The firm has done a number of Microsoft NAV data imports, and if my data resided in a newer more accessible MSSQL environment, i'd be able to shoot it over, they'd be familiar enough to convert, map, and do an initial import for our testing.

mpitcavage

David, that's a spot on summary of where i am.. If i hand them a SQL db, knowing NAV structure, they'll pull the few tables and discard the majority of the database... If i give them a few spreadsheets with "the right" information, they'll just use that.

I am (allegedly) the "power user" in our small group... I've written middleware using perl DBI connectors to sync Foxpro data with a MySQL blah blah blah.. but with this C/SIDE Proprietary Navision and little documentation, this group has given me more information than any research i've been able to do.

Christian_Buehl

Hi there,
2.01 is pretty old. With a conversion to SQL I would be carefull and would not recommend doing this for this old version.

As you need to convert your data I would recommend to use dataports for this and export into CSV files.
I remember about using a dataport creator which eased my life pretty much a long time ago.
I found the "Simple Dataport Creator" in the download section, but I'm not sure if this is the same tool I used several years ago.

viewtopic.php?f=7&t=39130

Hope that helps

Alex_Chow

This is especially true if the client is still using their Navision for something else.

mpitcavage

I've cross-posted this to the dynamicsuser.net forums and left this as the summary of what i've heard so far..

The methods of data extraction from a Navision 2.01 native database i am aware of are:

1)running the appropriate 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.



And like i said over there... thanks for all the valuable insights.. For a small non-profit, a resource like this forum is really invaluable.

Savatage

1)running the appropriate 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...

Can you see an Item List? This I would consider strange.

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....

What are you saving it as? Try saving it in the 50000+ range
50000
50001
50002
etc.

check you datbase login - you should just have
Role ID : SUPER & SUPER(DATA)

David_Singleton

mpitcavage wrote:

The methods of data extraction from a Navision 2.01 native database i am aware of are:
...

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.

robsmit

Dataports are not difficult, just they are a lot more work then C/ODBC.
From NAV to NAV it is all nice.
Migrating out of NAV is a problem.
Good old Foxpro never dies.
Converting to SQL won't bring anything just slow down.
And you will loose data.
Where are you going to, not NAV again I understand?

David Singleton wrote:

Savatage wrote:

800mb Navision database isn't that big - what's your issues with dataports?

The issue comes down to who will do it. the key issue here is that the company working with the database doesn't know how to write Navision dataports. , there is not a huge incentive for a partner to invest the time in a one of as small as this.

findreplace

The data extraction is the way to extract and to structure data from not structured and semi-structured electronic documents, as found on the web and in various data warehouses. Data extraction is extremely useful for the huge organizations which deal with considerable amounts of data, daily, which must be transformed into significant information and be stored for the use this later on.

For mlre details visit us at
Data Conversion \:D/

Parvardigar

I found this thread from from the early 2000s. We too have Navision 2. What I am asked is to "extract - capture the Navision email addresses into Excel. The user wants to use the emails for an advertising bulk email project. Is there a process to cultivate the client database into Excel?

Alex_Chow

Parvardigar wrote:

I found this thread from from the early 2000s. We too have Navision 2. What I am asked is to "extract - capture the Navision email addresses into Excel. The user wants to use the emails for an advertising bulk email project. Is there a process to cultivate the client database into Excel?

Run the customer table and copy and paste to Excel.

Parvardigar

I just had a talk with Accounting. Evidently back in 2000 no one in the company anticipated that 'email' would be of use.
Thus when the company customized they did not include email/email report. We can look at individual customers and copy out
the email however to pull out all emails, the email fields, is proving elusive.

David_Singleton

Parvardigar wrote:

I just had a talk with Accounting. Evidently back in 2000 no one in the company anticipated that 'email' would be of use.
Thus when the company customized they did not include email/email report. We can look at individual customers and copy out
the email however to pull out all emails, the email fields, is proving elusive.

Did you try Alex's suggestion. If emails are in the Customer table, then his suggestion should work for you.