NAV 2013 R2: Import objects with PowerShell

You know PowerShell is hot, right? And the fact that Dynamics NAV is using Powershell more and more, is good. Very good. I like it .. you must have figured that in previous post ;-).

We also are familiar with the finsql.exe .. and options we have with it. And if not, The Microsoft Team Blog have created a blog post about it. This was a great initiative .. but I did ask myself .. why not in PowerShell?? Well, there must be a legacy-reason behind it .. and it would even be understandable. But then again .. I don’t want to give up on the powershell .. . So,

Is there a way to import objects with powershell?

In extension to Mark’s blogpost from yesterday, it might come in handy when you’re getting started with e.g. Visual Studio Online. You will need a way to handle objects with Powershell .. if you want to get going with NAV and TFS through PowerShell, won’t you?

Well, this blog wouldn’t exist if there wasn’t a way, would it :-)? Here is a script, and I’ll explain about the scenario later.

function Import-NAVApplicationObjectFilesFromFolder
{
    [CmdletBinding()]
    param (
        [String]$SourceFolder,
        [String]$LogFolder,
        [String]$Database,
        [String]$TextFileFilter
          )

    $NAVFolder = 'C:\Program Files (x86)\Microsoft Dynamics NAV\71\RoleTailored Client'
    $importfinsqlcommand = """$NAVFolder\finsql.exe"" command=importobjects,servername=.,database=$Database,file="

    if ($TextFileFilter -ne "")
        {$TextFiles = gci "$SourceFolder\$TextFileFilter"} 
        else 
        {$TextFiles = gci "$SourceFolder\*.txt"}
    foreach ($TextFile in $TextFiles){

        $Command = $importfinsqlcommand + $TextFile
        $LogFile = "$LogFolder\$($TextFile.Basename).log"

        if ($LogFolder -ne ""){$Command = $Command + ',LogFile=' + "$LogFile"}
        Write-Debug $Command
        cmd /c $Command

        if (Test-Path "$LogFolder\navcommandresult.txt")
        {
            Write-Verbose "Processed $TextFile ."
            Remove-Item "$LogFolder\navcommandresult.txt"
        }
        else
        {
            Write-Warning "Crashed when importing $TextFile !"
        }

        If (Test-Path "$LogFile") {Write-Warning "Error when importing $TextFile"}
    }
}

In fact, the name “Import-NAVApplicationObjectFilesFromFolder” should describe what it is doing, but here is some more info:

• Import files from the Sourcefolder (parameter)
  
• Log the error-files into the LogFolder (parameter)
  
• Notify me, when the finsql crashed. Thanks to the fact that there is a navcommandresult.txt-file, when the process if finished, I could also track wether the finsql was crashed or not (if no file, and process finished, then it crashed.. ), which would mean something was really wrong with my file :-).
  
• Only do this for a certain TextFilter (like “TAB*.txt” – for only tables)
  

I created this script because I had an output of about 5000 textfiles from a merge. When I joined the files into one, the import was crashing .. . I wanted to know on which object. So in a way, i wanted to keep it as being 5000 files, try to import all of them, and list the ones that crashed, or errored out.

I know the script can be improved by many points .. but at least, it perfectly did what I was looking for :-). And it should get you going with the basics :-).

With special thanks to Bas Graaf of MSFT, who has a big share in this solution!