PowerShell: Lokales Datumsformat im Objektpaket einsetzen

Objektpakete, die aus anderen Kulturkreisen (oder dem Nachbarland ) stammen, führen leider regelmäßig zu Abgleich- und Importverzögerungen, da NAV ja leider nach wie vor kein einheitliches ISO-Datumsformat wie YYYY-MM-DD verwendet.

Mit dieser Funktion kann die Formatierung des Objektdatums aller Objekte eines Objektpakets auf das lokale Datumsformat geändert werden.
Das Objektpaket wird dazu in einem Unterordner TempNAVObjects zerlegt und nach der Bearbeitung in der gleichen Objektgruppierung wie beim Export aus der Entwicklungsumgebung wieder zusammengesetzt, als neue Datei mit Namenssuffix "_LocalDateFormat" im gleichen Ordner wie die Quelldatei, damit die Pakete direkt verglichen und ggf. auch direkt importiert werden können.

Im Beispiel ein Ausschnitt der aktuellen Datei vom Object Manager (aus Holland), wo das Datum als dd-MM-YY formatiert ist, andere häufig anzutreffende Formate sind dd/MM/yy (z.B. England,Dänemark) oder MM/dd/yy (USA).

Links wie geliefert, rechts im DE-Format nach Anwendung des Skripts.

DatumsformatNL_DE.png

Verwendbar ist dies ab NAV 2013-Objekten. Bitte beachten, dass etwaige Sekunden bei der Uhrzeit von Set-NAVApplicationObjectProperty nicht verarbeitet und genullt werden, obwohl diese in den Zwischenvariablen durchaus noch vorhanden sind.

BugSeconds.png

Für andere Datumsformate diese Zeile anpassen:

Code:

 [datetime]$OldFormatDateTime = [datetime]::ParseExact(($OldDateString + ' ' + $OldTimeString), "dd-MM-yy HH:mm:ss",$null)

Code:

   function SetLocalObjectDateFormat
    {
    [CmdletBinding()]
    Param
    (
    [String][Parameter(Mandatory=$True)]
    $PathObjectPack
    )
    $argspath = resolve-path $PathObjectPack
    $WorkingFolder = split-path $argspath -Parent
    $ObjectPackFileBaseName = (Get-Item $PathObjectPack).Basename
    $NewObjectPackFullName = (Get-Item $PathObjectPack).DirectoryName + '\' + (Get-Item $PathObjectPack).Basename + '_LocalDateFormat' + (Get-Item $PathObjectPack).Extension
     
    New-Item -Path $WorkingFolder -type directory -Name TempNAVobjects -Force
    $WorkingSubFolder = "$WorkingFolder\TempNAVobjects\"
    remove-item $WorkingSubFolder\*.* -Recurse
    Split-NAVApplicationObjectFile -Source $argspath -Destination $WorkingFolder\TempNAVobjects -PreserveFormatting -Force
   
    foreach ($file in get-ChildItem -Path $WorkingSubFolder\ | Where {$_.extension -eq '.TXT'})
     {
   
          #Get-NAVApplicationObjectProperty -Source $File.FullName
          $OldDateString = (Get-NAVApplicationObjectProperty -Source $File.FullName).Date
          $OldTimeString = (Get-NAVApplicationObjectProperty -Source $File.FullName).Time
          # Write-host "$file.FullName $OldDateString $OldTimeString"
          # Source Format is set here
          [datetime]$OldFormatDateTime = [datetime]::ParseExact(($OldDateString + ' ' + $OldTimeString), "dd-MM-yy HH:mm:ss",$null)
          $OldDateTimeString = "$OldDateString $OldTimeString"
          #Write-host $OldDateTimeString
          # Set local format here
          $NewDateTimeString = $OldFormatDateTime.ToString("dd.MM.yy HH:mm:ss")
          #Write-host $NewDateTimeString

          Set-NAVApplicationObjectProperty -Target $file.FullName -DateTimeProperty (Get-Date $NewDateTimeString -Format g)
         
          }

         Write-Host "Deleting old object files…"
         IF (Test-Path $WorkingSubFolder\allobjects.txt) {Remove-Item $WorkingSubFolder\allobjects.txt}
         IF (Test-Path $WorkingSubFolder\alltables.txt) {Remove-Item $WorkingSubFolder\alltables.txt}
         IF (Test-Path $WorkingSubFolder\allreports.txt) {Remove-Item $WorkingSubFolder\allreports.txt}
         IF (Test-Path $WorkingSubFolder\allcodeunits.txt) {Remove-Item $WorkingSubFolder\allcodeunits.txt}
         IF (Test-Path $WorkingSubFolder\allxmlports.txt) {Remove-Item $WorkingSubFolder\allxmlports.txt}
         IF (Test-Path $WorkingSubFolder\allmenusuites.txt) {Remove-Item $WorkingSubFolder\allmenusuites.txt}
         IF (Test-Path $WorkingSubFolder\allpages.txt) {Remove-Item $WorkingSubFolder\allpages.txt}
         IF (Test-Path $WorkingSubFolder\allqueries.txt) {Remove-Item $WorkingSubFolder\allqueries.txt}
         
         Write-Host "Joining discrete NAV object files…"
         Join-NAVApplicationObjectFile -Source $WorkingSubFolder\TAB*.txt -Destination $WorkingSubFolder\alltables.txt
         Join-NAVApplicationObjectFile -Source $WorkingSubFolder\REP*.txt -Destination $WorkingSubFolder\allreports.txt
         Join-NAVApplicationObjectFile -Source $WorkingSubFolder\COD*.txt -Destination $WorkingSubFolder\allcodeunits.txt
         Join-NAVApplicationObjectFile -Source $WorkingSubFolder\XML*.txt -Destination $WorkingSubFolder\allxmlports.txt
         Join-NAVApplicationObjectFile -Source $WorkingSubFolder\MEN*.txt -Destination $WorkingSubFolder\allmenusuites.txt
         Join-NAVApplicationObjectFile -Source $WorkingSubFolder\PAG*.txt -Destination $WorkingSubFolder\allpages.txt
         Join-NAVApplicationObjectFile -Source $WorkingSubFolder\QUE*.txt -Destination $WorkingSubFolder\allqueries.txt
         
         Write-Host "Concatenating object files…"
         IF (Test-Path $WorkingSubFolder\alltables.txt) {Add-Content -path $WorkingSubFolder\AllObjects.txt -value(Get-Content $WorkingSubFolder\alltables.txt)}
          IF (Test-Path $WorkingSubFolder\allreports.txt) {Add-Content -path $WorkingSubFolder\AllObjects.txt -value(Get-Content $WorkingSubFolder\allreports.txt)}
         IF (Test-Path $WorkingSubFolder\allcodeunits.txt) {Add-Content -path $WorkingSubFolder\AllObjects.txt -value(Get-Content $WorkingSubFolder\allcodeunits.txt)}
          IF (Test-Path $WorkingSubFolder\allxmlports.txt) {Add-Content -path $WorkingSubFolder\AllObjects.txt -value(Get-Content $WorkingSubFolder\allxmlports.txt)}
          IF (Test-Path $WorkingSubFolder\allmenusuites.txt) {Add-Content -path $WorkingSubFolder\AllObjects.txt -value(Get-Content $WorkingSubFolder\allmenusuites.txt)}
            IF (Test-Path $WorkingSubFolder\allpages.txt) {Add-Content -path $WorkingSubFolder\AllObjects.txt -value(Get-Content $WorkingSubFolder\allpages.txt)}
         IF (Test-Path $WorkingSubFolder\allqueries.txt) {Add-Content -path $WorkingSubFolder\AllObjects.txt -value(Get-Content $WorkingSubFolder\allqueries.txt)}
         
         IF (Test-Path $WorkingSubFolder\alltables.txt) {Remove-Item $WorkingSubFolder\alltables.txt}
         IF (Test-Path $WorkingSubFolder\allreports.txt) {Remove-Item $WorkingSubFolder\allreports.txt}
         IF (Test-Path $WorkingSubFolder\allcodeunits.txt) {Remove-Item $WorkingSubFolder\allcodeunits.txt}
         IF (Test-Path $WorkingSubFolder\allxmlports.txt) {Remove-Item $WorkingSubFolder\allxmlports.txt}
         IF (Test-Path $WorkingSubFolder\allmenusuites.txt) {Remove-Item $WorkingSubFolder\allmenusuites.txt}
         IF (Test-Path $WorkingSubFolder\allpages.txt) {Remove-Item $WorkingSubFolder\allpages.txt}
         IF (Test-Path $WorkingSubFolder\allqueries.txt) {Remove-Item $WorkingSubFolder\allqueries.txt}
         Move-Item $WorkingSubFolder\AllObjects.txt $NewObjectPackFullName  -Force
     
     }

Du hast keine ausreichende Berechtigung, um die Dateianhänge dieses Beitrags anzusehen.

[…]da NAV ja leider nach wie vor kein einheitliches ISO-Datumsformat wie YYYY-MM-DD verwendet.

In dem Zwischenformat, welches man mit dem neuen finsql.exe-Befehl ExportToNewSyntax erhält (Info hier), gibt es nun einheitliche Formate für Datum und Uhrzeit.

NewCAL.png

Importieren darf man diese Dateien allerdings nicht, aber so hat es zumindest der nachfolgende Konverter leichter .

Du hast keine ausreichende Berechtigung, um die Dateianhänge dieses Beitrags anzusehen.

Aus gegebenem Anlass hier auch eine Skriptvariante mit Dialogfenster für die Quelldatei.

Code:

function SetLocalObjectDateFormatDlg
{

    Function Get-FileName($initialDirectory)
    {
        [System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms") | Out-Null
   
        $OpenFileDialog = New-Object System.Windows.Forms.OpenFileDialog
        $OpenFileDialog.initialDirectory = $initialDirectory
        $OpenFileDialog.filter = "NAV Object Files (*.txt)|*.txt"
        $OpenFileDialog.ShowDialog() | Out-Null
        $OpenFileDialog.filename
    }
       
    $inputfile = Get-FileName -initialdirectory "$env:HOMEDRIVE\temp"
    if ($inputfile -eq "") {throw 'Please select a file'}
   
    $argspath = resolve-path $inputfile
    $WorkingFolder = split-path $argspath -Parent
    $ObjectPackFileBaseName = (Get-Item $inputfile).Basename
    $NewObjectPackFullName = (Get-Item $inputfile).DirectoryName + '\' + (Get-Item $inputfile).Basename + '_LocalDateFormat' + (Get-Item $inputfile).Extension
     
    New-Item -Path $WorkingFolder -type directory -Name TempNAVobjects -Force
    $WorkingSubFolder = "$WorkingFolder\TempNAVobjects\"
    remove-item $WorkingSubFolder\*.* -Recurse
    Split-NAVApplicationObjectFile -Source $argspath -Destination $WorkingFolder\TempNAVobjects -PreserveFormatting -Force
   
    foreach ($file in get-ChildItem -Path $WorkingSubFolder\ | Where {$_.extension -eq '.TXT'})
    {
   
        #Get-NAVApplicationObjectProperty -Source $File.FullName
        $OldDateString = (Get-NAVApplicationObjectProperty -Source $File.FullName).Date
        $OldTimeString = (Get-NAVApplicationObjectProperty -Source $File.FullName).Time
        # Write-host "$file.FullName $OldDateString $OldTimeString"
        # Source Format is set here
        [datetime]$OldFormatDateTime = [datetime]::ParseExact(($OldDateString + ' ' + $OldTimeString), "dd-MM-yy HH:mm:ss",$null)
        $OldDateTimeString = "$OldDateString $OldTimeString"
        #Write-host $OldDateTimeString
        # Set local format here
        $NewDateTimeString = $OldFormatDateTime.ToString("dd.MM.yy HH:mm:ss")
        #Write-host $NewDateTimeString

        Set-NAVApplicationObjectProperty -Target $file.FullName -DateTimeProperty (Get-Date $NewDateTimeString -Format g)
         
    }

    Write-Host "Deleting old object files…"
    IF (Test-Path $WorkingSubFolder\allobjects.txt) {Remove-Item $WorkingSubFolder\allobjects.txt}
    IF (Test-Path $WorkingSubFolder\alltables.txt) {Remove-Item $WorkingSubFolder\alltables.txt}
    IF (Test-Path $WorkingSubFolder\allreports.txt) {Remove-Item $WorkingSubFolder\allreports.txt}
    IF (Test-Path $WorkingSubFolder\allcodeunits.txt) {Remove-Item $WorkingSubFolder\allcodeunits.txt}
    IF (Test-Path $WorkingSubFolder\allxmlports.txt) {Remove-Item $WorkingSubFolder\allxmlports.txt}
    IF (Test-Path $WorkingSubFolder\allmenusuites.txt) {Remove-Item $WorkingSubFolder\allmenusuites.txt}
    IF (Test-Path $WorkingSubFolder\allpages.txt) {Remove-Item $WorkingSubFolder\allpages.txt}
    IF (Test-Path $WorkingSubFolder\allqueries.txt) {Remove-Item $WorkingSubFolder\allqueries.txt}
         
    Write-Host "Joining discrete NAV object files…"
    Join-NAVApplicationObjectFile -Source $WorkingSubFolder\TAB*.txt -Destination $WorkingSubFolder\alltables.txt
    Join-NAVApplicationObjectFile -Source $WorkingSubFolder\REP*.txt -Destination $WorkingSubFolder\allreports.txt
    Join-NAVApplicationObjectFile -Source $WorkingSubFolder\COD*.txt -Destination $WorkingSubFolder\allcodeunits.txt
    Join-NAVApplicationObjectFile -Source $WorkingSubFolder\XML*.txt -Destination $WorkingSubFolder\allxmlports.txt
    Join-NAVApplicationObjectFile -Source $WorkingSubFolder\MEN*.txt -Destination $WorkingSubFolder\allmenusuites.txt
    Join-NAVApplicationObjectFile -Source $WorkingSubFolder\PAG*.txt -Destination $WorkingSubFolder\allpages.txt
    Join-NAVApplicationObjectFile -Source $WorkingSubFolder\QUE*.txt -Destination $WorkingSubFolder\allqueries.txt
         
    Write-Host "Concatenating object files…"
    IF (Test-Path $WorkingSubFolder\alltables.txt) {Add-Content -path $WorkingSubFolder\AllObjects.txt -value(Get-Content $WorkingSubFolder\alltables.txt)}
    IF (Test-Path $WorkingSubFolder\allreports.txt) {Add-Content -path $WorkingSubFolder\AllObjects.txt -value(Get-Content $WorkingSubFolder\allreports.txt)}
    IF (Test-Path $WorkingSubFolder\allcodeunits.txt) {Add-Content -path $WorkingSubFolder\AllObjects.txt -value(Get-Content $WorkingSubFolder\allcodeunits.txt)}
    IF (Test-Path $WorkingSubFolder\allxmlports.txt) {Add-Content -path $WorkingSubFolder\AllObjects.txt -value(Get-Content $WorkingSubFolder\allxmlports.txt)}
    IF (Test-Path $WorkingSubFolder\allmenusuites.txt) {Add-Content -path $WorkingSubFolder\AllObjects.txt -value(Get-Content $WorkingSubFolder\allmenusuites.txt)}
    IF (Test-Path $WorkingSubFolder\allpages.txt) {Add-Content -path $WorkingSubFolder\AllObjects.txt -value(Get-Content $WorkingSubFolder\allpages.txt)}
    IF (Test-Path $WorkingSubFolder\allqueries.txt) {Add-Content -path $WorkingSubFolder\AllObjects.txt -value(Get-Content $WorkingSubFolder\allqueries.txt)}
         
    IF (Test-Path $WorkingSubFolder\alltables.txt) {Remove-Item $WorkingSubFolder\alltables.txt}
    IF (Test-Path $WorkingSubFolder\allreports.txt) {Remove-Item $WorkingSubFolder\allreports.txt}
    IF (Test-Path $WorkingSubFolder\allcodeunits.txt) {Remove-Item $WorkingSubFolder\allcodeunits.txt}
    IF (Test-Path $WorkingSubFolder\allxmlports.txt) {Remove-Item $WorkingSubFolder\allxmlports.txt}
    IF (Test-Path $WorkingSubFolder\allmenusuites.txt) {Remove-Item $WorkingSubFolder\allmenusuites.txt}
    IF (Test-Path $WorkingSubFolder\allpages.txt) {Remove-Item $WorkingSubFolder\allpages.txt}
    IF (Test-Path $WorkingSubFolder\allqueries.txt) {Remove-Item $WorkingSubFolder\allqueries.txt}
    Move-Item $WorkingSubFolder\AllObjects.txt $NewObjectPackFullName  -Force
     
}