NAV 2013 R2: Export objects with PowerShell

A while ago, I create a blog about a way to import objects in NAV with PowerShell. Well, it’s time to do the opposite, and export objects. This is the function I came up with (pardon the formatting – copy/pasting really made it difficult for me…):

function Export-NAVApplicationObjectFile
{
    [CmdletBinding()]
    param (
        [String]$WorkingFolder,
        [String]$ExportFile,
        [String]$Database,
        [String]$Filter
          )
 
    $LogFile = "$WorkingFolder\Log_$ExportFile"
    $ExportFile = "$WorkingFolder\$ExportFile"
    if (Test-Path "$WorkingFolder\navcommandresult.txt") {Remove-Item "$WorkingFolder\navcommandresult.txt"}
    if (test-path $ExportFile) {remove-item $ExportFile}
 
    $NAVFolder = 'C:\Program Files (x86)\Microsoft Dynamics NAV\71\RoleTailored Client'
    $exportfinsqlcommand = """$NAVFolder\finsql.exe"" command=exportobjects,file=$ExportFile,servername=.,database=$Database,Logfile=$LogFile"
 
    if ($Filter -ne "")
        {$exportfinsqlcommand = "$exportfinsqlcommand,filter=$Filter"} 
 
    $Command = $exportfinsqlcommand
 
    Write-Debug $Command
    cmd /c $Command
 
    $ExportFileExists = Test-Path "$ExportFile"
    If (-not $ExportFileExists) 
    {
            write-error "Error on exporting to $ExportFile.  Look at the information below."
            if (Test-Path "$WorkingFolder\navcommandresult.txt"){Type "$WorkingFolder\navcommandresult.txt"}
            if (Test-Path $LogFile) {type $LogFile}
    }
    else
    {
 
        $NAVObjectFile = Get-ChildItem $ExportFile
        if ($NAVObjectFile.Length -eq 0)
        {
            Remove-Item $NAVObjectFile
        } 
 
        if (Test-Path "$WorkingFolder\navcommandresult.txt")
        {
            Type "$WorkingFolder\navcommandresult.txt"
        }
    }
} 

 

I agree .. things can be improved. But I think it’s more than enough to get you going. I think it’s very usable in some kind of automated script where you want to export stuff to for example TFS and back.

The function is going to create one file each time you call it. For example:

Export-NAVApplicationObjectFile `
    -WorkingFolder 'C:\_merge\Export\' `
    -ExportFile '700Objects.txt' `
    -Database 'NAV2013R2_BE' `
    -Filter 'Type=Query;ID=700..799'

 

 

This is going to create one file which contains the query-objects in the 700-range.

A way to create single files, is to play with the integers yourself, like this:

for ($i = 1; $i -lt 100; $i++)
{ 
  Export-NAVApplicationObjectFile `
    -WorkingFolder 'C:\_merge\Export\' `
    -ExportFile "TAB$i.txt" `
    -Database 'NAV2013R2_BE' `
    -Filter "Type=Table;ID=$i"
  $i
}

 

 

It is slow, but then again, it does its job, and there are ways to do it multithreaded in PowerShell as well ;-).

2.00 avg. rating (53% score) - 3 votes

Permanent link to this article: https://www.waldo.be/2014/06/04/nav-2013-r2-export-objects-with-powershell-3/

9 comments

3 pings

Skip to comment form

  1. Works nicely, but a ” seems to be missing in front of C:\Program Files in Line 18.
    I modified the function to include the servername as a fifth parameter and kept getting an error that the path C:\Progam does not exist (Win 7 OS) . After I inserted the ” it worked as planned.

      • waldo on March 17, 2015 at 4:35 pm
        Author

      It works on my end – but problems could arise when working with different versions of PowerShell. That might be what’s causing it .. .

      Thanks for the comment!

    • John on October 2, 2015 at 9:42 am
    • Reply

    It works perfectly when I call the function but when I just try to use a stripped down command like this:
    cmd /c “C:\Program Files (x86)\Microsoft Dynamics NAV\80\RoleTailored Client\finsql.exe” command=exportobjects,file=C:\asd\\TAB3.txt,servername=myservername,database=mydatabase,Logfile=C:\asd\\Log_TAB3.txt,filter=Type=Table;ID=3

    I get an error saying The text exportobjects,file=C:\asd\\TAB3.txt,servername=myservername,database=mydatabase,Logfile=C:\asd\\Log_TAB3.txt,filter=Type=Table is too long. Text can have a maximum length of 47 characters.

    I just copied the excact same command from the $Command variable when I ran your script. What’s the difference between the two methods?

      • waldo on October 30, 2015 at 4:14 pm
        Author

      Not sure if I understand correctly. but have you tried to put the c:\asd .. between quotes, like:
      cmd /c “C:\Program Files (x86)\Microsoft Dynamics NAV\80\RoleTailored Client\finsql.exe” command=exportobjects,file=”C:\asd\\TAB3.txt”,servername=myservername,database=mydatabase,Logfile=”C:\asd\\Log_TAB3.txt”,filter=”Type=Table;ID=3″

    • Jim on February 24, 2017 at 11:18 pm
    • Reply

    Hmmm… I’m fairly new to this Powershell stuff, but once I got the NAV cmdlets loaded, I believe I’ve got enough info to actually export the objects in the db to text. Here’s the command I am using:

    Export-NAVApplicationObject -DatabaseName “Demo Database NAV (9-0)” -Path “C:\Users\jim\Documents\SW Upgrade\Original\OldBaseVersion.txt”

    It thinks for a bit and comes back saying that I don’t have permission to run the File, Export, Text system. I am running the Windows Powershell ISE as an administrator, and when I load up the demo database in 2016, the license that’s automatically loaded is a development license where I have the ability to export objects myself into text. Any ideas what I may be missing? Anything you can come up with would be greatly appreciated!

      • waldo on February 27, 2017 at 4:53 pm
        Author

      I never had this – but then again, I’m always admin on my systems.. .

      Does the user that runs PowerShell have access to the database?

    • Jim on February 27, 2017 at 6:36 pm
    • Reply

    Boy is my face red! I’ve got 3 databases I’m trying to export for an upgrade, and 2 of the 3 are using the standard license, and the 3rd one has a developer’s license attached. The error was from one of the 2 without the right license. When I tried the one with the developer’s license attached, it did get further. But maybe you know the answer to this one. Is there a way in Powershell to ignore objects that aren’t readable with the license attached? For some reason there are Zetadocs objects in the db that the license doesn’t have permissions for.

      • waldo on February 27, 2017 at 11:00 pm
        Author

      Zetadocs is a pain for that matter indeed :). Well, in the newer version (don’t exactly remember the NAV version where it was introduced) there is a parameter that is called something like “-SkipUnlicensed” (or something like that ;-))

      • Jim on February 27, 2017 at 11:18 pm

      Okay, just to complete this thread, I found the option -ExportTxtSkipUnlicensed so other folks will have the complete story. Good to know it can be done! Thanks.

  1. […] Continue reading » […]

  2. […] /https://www.waldo.be/2014/06/04/nav-2013-r2-export-objects-with-powershell-3/ […]

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.