Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.5K Microsoft Dynamics NAV
- 18.5K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 115 Navision DOS
- 854 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 615 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 267 Dynamics CRM
- 109 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 992 SQL General
- 384 SQL Performance
- 33 SQL Tips & Tricks
- 34 Design Patterns (General & Best Practices)
- Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.7K General
- 1.1K General Chat
- 1.6K Website
- 79 Testing
- 1.2K Download section
- 23 How Tos section
- 259 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
Options
Navision 2.01b data extraction
mpitcavage
Member Posts: 9
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?
From what I see, I have a Navision license file that doesn't allow c/odbc connections.
Any suggestions which direction to start?
0
Comments
AP Commerce, Inc. = where I work
Getting Started with Dynamics NAV 2013 Application Development = my book
Implementing Microsoft Dynamics NAV - 3rd Edition = my 2nd book
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.
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?".
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?
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.
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.
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.
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.
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.
http://www.BiloBeauty.com
http://www.autismspeaks.org
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.
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
http://www.BiloBeauty.com
http://www.autismspeaks.org
I was really working on the basis of this:
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.
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
AP Commerce, Inc. = where I work
Getting Started with Dynamics NAV 2013 Application Development = my book
Implementing Microsoft Dynamics NAV - 3rd Edition = my 2nd book
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.
Can you see an Item List? This I would consider strange.
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)
http://www.BiloBeauty.com
http://www.autismspeaks.org
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.
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?
For mlre details visit us at
Data Conversion \:D/
Run the customer table and copy and paste to Excel.
AP Commerce, Inc. = where I work
Getting Started with Dynamics NAV 2013 Application Development = my book
Implementing Microsoft Dynamics NAV - 3rd Edition = my 2nd book
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.