Export table to Excel...

This question is answered

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.

Verified Answer
  • 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 (Since 2001)
    MCTS NAV, CRM, Sharepoint, SQL
    MCITP NAV
    MCT 

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

    Neville Foyn
    NAV Developer, Consultant & Trainer (Since 2001)
    MCTS NAV, CRM, Sharepoint, SQL
    MCITP NAV
    MCT 

All Replies
  • Split the records by adding some filter and try

    like yearly or 6 months..

  • 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 (Since 2001)
    MCTS NAV, CRM, Sharepoint, SQL
    MCITP NAV
    MCT 

  • 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

  • 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 (Since 2001)
    MCTS NAV, CRM, Sharepoint, SQL
    MCITP NAV
    MCT 

  • 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?

  • Nev,

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

    Thank you so much!

    Rachel

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

    Neville Foyn
    NAV Developer, Consultant & Trainer (Since 2001)
    MCTS NAV, CRM, Sharepoint, SQL
    MCITP NAV
    MCT