«

»

Print this Post

NAV 2013 R2: Export objects with PowerShell

NAV 2013 R2: Export objects with PowerShell
1 vote, 4.00 avg. rating (87% score)

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"}

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

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

Leave a Reply