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!

5.00 avg. rating (98% score) - 4 votes

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

9 comments

6 pings

Skip to comment form

    • Sebastiaan Lubbers on April 9, 2014 at 7:18 am
    • Reply

    So I assume this works for NAV2013 and NAV2013R2. As in your example it appears you are using NAV”Crete” 😉

      • waldo on April 9, 2014 at 8:31 am
        Author

      You’re totally right .. I’ll change the references :-). It works for NAV 2013 and further :-). Good remark!

    • Soren Klemmensen on April 10, 2014 at 8:33 pm
    • Reply

    Okay that was just too easy 🙂 I tested it on my current development and it worked like a charm. It was so easy that I am worried what I have missed! Nice work. Love it.

      • waldo on April 11, 2014 at 7:02 am
        Author

      Hey .. it’s all about simplicity, right :-).

    • moneim on April 11, 2014 at 2:17 pm
    • Reply

    Hi… When I run script I have this error message in log file :
    [22922070] The following SQL Server error or errors occurred: 18456,”28000″,[Microsoft][SQL Server Native Client 10.0][SQL Server]chec de l’ouverture de session de l’utilisateurÿ’XXX’.
    4060,”42000″,[Microsoft][SQL Server Native Client 10.0][SQL Server]Impossible d’ouvrir la base de donn‚es “Demo Database NAV (7-0)” demand‚e par la connexion. La connexion a ‚chou‚.

    Someone have an idea ??

      • waldo on April 11, 2014 at 2:37 pm
        Author

      You have to make sure that the user that is executing the script, is able to log in the database. Otherwise, change the script so that you can provide user credentials.. .

    • Rinkul on February 17, 2015 at 12:18 am
    • Reply

    is it import of FOB possible same way?

      • waldo on February 17, 2015 at 1:28 pm
        Author

      Yes it is, afaik.. . You might need to do a minor change to the script though..

  1. After getting inspiration from your work i also developed some code –

    https://pokhrandev.blogspot.com/2023/01/auto-import-of-objects-from-dev-to-test.html

  1. […] Continue reading » […]

  2. […] 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 […]

  3. […] 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 […]

  4. […] NAV 2013 R2: Import objects with PowerShell […]

  5. […] NAV 2013 R2: Import objects with PowerShell […]

Leave a Reply

Your email address will not be published.

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