Question Status

Verified
rschaefer asked a question on 18 Feb 2013 2:07 PM

I need to export our Sales Prices table.  I have tried using the Data Migration tool, but it times out every time.  It is a large file (591592 records).  Is there anything else I can use?  I've tried Jet Reports as well and I have the same problem.

Reply
Mohana Yadav responded on 19 Feb 2013 2:49 AM

Split the records by adding some filter and try

like yearly or 6 months..

Reply
Neville Foyn responded on 19 Feb 2013 3:03 PM

Hi

Your best option here would be to use a dataport to drop all the records into a CSV this would run in minutes where the migration tool would take hours to run

You will need someone who knows how to use the object designer in the classic client to do this though, if you have someone and this is still an issue let me know and i will send you instructions on how to do it

Cheers

Nev

Neville Foyn
NAV Developer, Consultant & Trainer
MCTS NAV, CRM, Sharepoint, SQL
MCSA SQL 2012
MCITP NAV
MCT 

Reply
rschaefer responded on 20 Feb 2013 10:19 AM

Hi Nev,

Thanks for the reply.  I've messed around a little bit with the Object Designer, but I have no idea how to create anything.  I would love to learn, but in the meantime I went the long way around and filtered by letter.    It was quite annoying and took forever, but I eventually got it finished.  I would still love to look at the instructions though.  :o)

Rachel

Reply
Verified Answer
Neville Foyn responded on 20 Feb 2013 4:28 PM

Hi

First off, only use this for exporting records, when importing you need to set up the validation and can miss something and cause yourself a ton of drama, but for exporting records it is a lifesaver

The next thing is that most NAV licences are sold with some extra objects available in the 50 000 range, and secondly you will need the dataport designer in your licence (Depending on your licence this could be in many places so just try and see if it complains but i would expect it to work)

This has a lot of steps but when you follow it you will see it is actually really simple so don't be put off by it looking complicated, we regularly teach this process to our clients to make them more self sufficient

So:

-Fire up the classic client and open the object designer

-Click on the dataport option on the right

-Have a look on the list for an available number (There is nothing there already) starting at 50 000 and going up (So 50 000 if available, otherwise 50001 if available / 50002 etc)

-Click NEW button on the bottom right

-In the window that opens look up the table you want to export (Sales Price for example - You can type it in in this case because we know the name exactly)

-Click on View -> Dataport Fields

-Dont do anything with the window that opens and immediately click View -> Field Menu

-On the little window that pops up click in the block in the top right of the window to highlight everything (Like you would do in Excel)

-Then click on the form that is open in the background and the system will ask "Do you want to add the fields"

-Say yes and the list will appear

-Keep pressing Escape on your keyboard until you get a message asking if you want to save

-Say yes,

-Type the available number that you got in the first step, for example 50000 and a name for you port "Export Sales Prices"

-You will now see it on the object designer

-Highlight the line and click run (Bottom Right)

-On the options Tab at the top specify "Export" and give it a file name

-Press the OK button and it will create a file

This file will have a TXT extension however it can be renames to CSV or just opened in Excel and it will display in columns

Let me know how it goes and if you run into any issues

Cheers

Nev

Neville Foyn
NAV Developer, Consultant & Trainer
MCTS NAV, CRM, Sharepoint, SQL
MCSA SQL 2012
MCITP NAV
MCT 

Reply
rschaefer responded on 21 Feb 2013 6:45 AM

Hi,

I tried it first thing this morning, but had one error:

The operating system cannot access to the file "Sales Prices 22113"

Verify that the file type and attributes are correct

I'm on Windows 7 Professional

Not sure what happened.

Any suggestions?

Reply
rschaefer responded on 21 Feb 2013 7:10 AM

Nev,

Okay, nevermind.  I just rebooted my whole PC and now it works perfect.  Weird.

Thank you so much!

Rachel

Reply
Verified Answer
Neville Foyn responded on 22 Feb 2013 4:33 PM

Great stuff, happy it works, it makes getting data out a heck of a lot faster

Neville Foyn
NAV Developer, Consultant & Trainer
MCTS NAV, CRM, Sharepoint, SQL
MCSA SQL 2012
MCITP NAV
MCT 

Reply
Verified Answer
Neville Foyn responded on 20 Feb 2013 4:28 PM

Hi

First off, only use this for exporting records, when importing you need to set up the validation and can miss something and cause yourself a ton of drama, but for exporting records it is a lifesaver

The next thing is that most NAV licences are sold with some extra objects available in the 50 000 range, and secondly you will need the dataport designer in your licence (Depending on your licence this could be in many places so just try and see if it complains but i would expect it to work)

This has a lot of steps but when you follow it you will see it is actually really simple so don't be put off by it looking complicated, we regularly teach this process to our clients to make them more self sufficient

So:

-Fire up the classic client and open the object designer

-Click on the dataport option on the right

-Have a look on the list for an available number (There is nothing there already) starting at 50 000 and going up (So 50 000 if available, otherwise 50001 if available / 50002 etc)

-Click NEW button on the bottom right

-In the window that opens look up the table you want to export (Sales Price for example - You can type it in in this case because we know the name exactly)

-Click on View -> Dataport Fields

-Dont do anything with the window that opens and immediately click View -> Field Menu

-On the little window that pops up click in the block in the top right of the window to highlight everything (Like you would do in Excel)

-Then click on the form that is open in the background and the system will ask "Do you want to add the fields"

-Say yes and the list will appear

-Keep pressing Escape on your keyboard until you get a message asking if you want to save

-Say yes,

-Type the available number that you got in the first step, for example 50000 and a name for you port "Export Sales Prices"

-You will now see it on the object designer

-Highlight the line and click run (Bottom Right)

-On the options Tab at the top specify "Export" and give it a file name

-Press the OK button and it will create a file

This file will have a TXT extension however it can be renames to CSV or just opened in Excel and it will display in columns

Let me know how it goes and if you run into any issues

Cheers

Nev

Neville Foyn
NAV Developer, Consultant & Trainer
MCTS NAV, CRM, Sharepoint, SQL
MCSA SQL 2012
MCITP NAV
MCT 

Reply
Verified Answer
Neville Foyn responded on 22 Feb 2013 4:33 PM

Great stuff, happy it works, it makes getting data out a heck of a lot faster

Neville Foyn
NAV Developer, Consultant & Trainer
MCTS NAV, CRM, Sharepoint, SQL
MCSA SQL 2012
MCITP NAV
MCT 

Reply