Text files, reading, writing, converting and different code pages

Microsoft Dynamics NAV is still using the old DOS code page for files.  If you create a file with the file variable and write text to that file you will get a DOS file.  The same thing happens when writing to a BLOB and exporting to a file.  The Code example below handles the DOS code page.

[code]OBJECT Codeunit 50000 Read and Write DOS File
{
OBJECT-PROPERTIES
{
Date=30.05.13;
Time=09:16:44;
Modified=Yes;
Version List=Dynamics.is;
}
PROPERTIES
{
OnRun=VAR
LineRead@10000000 : Text[250];
CrLf@10000001 : Text[2];
BEGIN
CrLf[1] := 13;
CrLf[2] := 10;
DOSFileName := FileMgt.ServerTempFileName(‘txt’);
DOSFile.CREATE(DOSFileName);
DOSFile.CREATEOUTSTREAM(OutStr);
StandardText.FINDSET;
REPEAT
OutStr.WRITETEXT(STRSUBSTNO(‘%1,%2’,StandardText.Code,StandardText.Description) + CrLf);
UNTIL StandardText.NEXT = 0;
DOSFile.CLOSE;

DOSFile.OPEN(DOSFileName);
DOSFile.CREATEINSTREAM(InStr);
WHILE NOT InStr.EOS DO BEGIN
InStr.READTEXT(LineRead,MAXSTRLEN(LineRead));
TempStandardText.Code := SELECTSTR(1,LineRead);
TempStandardText.Description := SELECTSTR(2,LineRead);
TempStandardText.INSERT;
END;
DOSFile.CLOSE;

MESSAGE(Text001,DOSFileName);
PAGE.RUNMODAL(PAGE::"Standard Text Codes",TempStandardText);
END;

}
CODE
{
VAR
StandardText@10000007 : Record 7;
TempStandardText@10000006 : TEMPORARY Record 7;
FileMgt@10000003 : Codeunit 419;
DOSFile@10000000 : File;
DOSFileName@10000004 : Text[250];
InStr@10000001 : InStream;
OutStr@10000002 : OutStream;
Text001@10000005 : TextConst ‘ENU=Server File Name : %1;ISL=Skr�arnafn � �j�ni : %1’;

BEGIN
END.
}
}[/code]

Using DotNet for the same job as the below example shows, will create a file with the Windows code page.

[code]OBJECT Codeunit 50001 Read and Write Windows File
{
OBJECT-PROPERTIES
{
Date=30.05.13;
Time=09:26:03;
Modified=Yes;
Version List=Dynamics.is;
}
PROPERTIES
{
OnRun=VAR
LineRead@10000000 : Text[250];
CrLf@10000001 : Text[2];
Loop@10000002 : Integer;
BEGIN
CrLf[1] := 13;
CrLf[2] := 10;
ISOFileName := FileMgt.ServerTempFileName(‘txt’);
StandardText.FINDSET;
REPEAT
dotNetFile.AppendAllText(ISOFileName,STRSUBSTNO(‘%1,%2′,StandardText.Code,StandardText.Description) + CrLf);
UNTIL StandardText.NEXT = 0;

dotNetArray := dotNetFile.ReadAllLines(ISOFileName);
FOR Loop := 0 TO (dotNetArray.Length – 1) DO BEGIN
LineRead := dotNetArray.GetValue(Loop);
TempStandardText.Code := SELECTSTR(1,LineRead);
TempStandardText.Description := SELECTSTR(2,LineRead);
TempStandardText.INSERT;
END;

MESSAGE(Text001,ISOFileName);
PAGE.RUNMODAL(PAGE::"Standard Text Codes",TempStandardText);
END;

}
CODE
{
VAR
dotNetFile@10000011 : DotNet "’mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.IO.File";
dotNetArray@10000010 : DotNet "’mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’.System.Array";
StandardText@10000007 : Record 7;
TempStandardText@10000006 : TEMPORARY Record 7;
FileMgt@10000003 : Codeunit 419;
ISOFileName@10000004 : Text[250];
Text001@10000005 : TextConst ‘ENU=Server File Name : %1;ISL=Skr�arnafn � �j�ni : %1’;

BEGIN
END.
}
}
[/code]

And to write and read UTF-8 encoded file

[code]OBJECT Codeunit 50002 Read and Write UTF8 File
{
OBJECT-PROPERTIES
{
Date=30.05.13;
Time=09:26:51;
Modified=Yes;
Version List=Dynamics.is;
}
PROPERTIES
{
OnRun=VAR
LineRead@10000000 : Text[250];
CrLf@10000001 : Text[2];
Loop@10000002 : Integer;
BEGIN
CrLf[1] := 13;
CrLf[2] := 10;
ISOFileName := FileMgt.ServerTempFileName(‘txt’);
StandardText.FINDSET;
REPEAT
dotNetFile.AppendAllText(ISOFileName,STRSUBSTNO(‘%1,%2’,StandardText.Code,StandardText.Description) + CrLf,Encoding.GetEncoding(‘utf-8’));
UNTIL StandardText.NEXT = 0;

dotNetArray := dotNetFile.ReadAllLines(ISOFileName,Encoding.GetEncoding(‘utf-8′));
FOR Loop := 0 TO (dotNetArray.Length – 1) DO BEGIN
LineRead := dotNetArray.GetValue(Loop);
TempStandardText.Code := SELECTSTR(1,LineRead);
TempStandardText.Description := SELECTSTR(2,LineRead);
TempStandardText.INSERT;
END;

MESSAGE(Text001,ISOFileName);
PAGE.RUNMODAL(PAGE::"Standard Text Codes",TempStandardText);
END;

}
CODE
{
VAR
dotNetFile@10000011 : DotNet "’mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.IO.File";
dotNetArray@10000010 : DotNet "’mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Array";
Encoding@10000000 : DotNet "’mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’.System.Text.Encoding";
StandardText@10000007 : Record 7;
TempStandardText@10000006 : TEMPORARY Record 7;
FileMgt@10000003 : Codeunit 419;
ISOFileName@10000004 : Text[250];
Text001@10000005 : TextConst ‘ENU=Server File Name : %1;ISL=Skr�arnafn � �j�ni : %1’;

BEGIN
END.
}
}
[/code]

This also gives us an easy way to convert files from one code page to another. For example from the DOS format to the Windows format.

[code]
ServerISOFileName := FileMgt.ServerTempFileName(‘xml’);
dotNetFile.WriteAllText(
ServerISOFileName,
dotNetFile.ReadAllText(ServerDOSFileName,Encoding.GetEncoding(‘ibm850’)),
Encoding.GetEncoding(‘iso-8859-1’));[/code]

Also if you use the UTF-8 example and replace GetEncoding(‘utf-8’) with GetEncoding(‘ibm850’) you will get a DOS formatted file.  Microsoft offers a list of all supported encoding methods here.  The beauty with the DotNet methods is the possibility to use RunOnClient property to read and write files from the client computer.

Service to import files

The task is to import every file that is dropped into a specific folder on my local drive into NAV.  The solution is a windows service programmed in Visual Studio 2008 VB.NET.

The first step is to create a web service in Dynamics NAV that accepts a text line and a file name.  Another function to remove the file if the import fails and the third to process the file after it has been imported.

The vb.net code from Visual Studio

[code lang=”vb”]Imports System
Imports System.Timers
Imports System.Net
Imports System.IO

Public Class FileImportService
Dim Salvor1 As Salvor.SalvorWebService
Dim Timer1 As System.Timers.Timer
Dim User As New System.Net.NetworkCredential

Protected Overrides Sub OnStart(ByVal args() As String)
‘ Add code here to start your service. This method should set things
‘ in motion so your service can do its work.
Salvor1 = New Salvor.SalvorWebService
User.Domain = "<Domain>"
User.UserName = "<User>"
User.Password = "<Password>"
Salvor1.Credentials = User

Timer1 = New System.Timers.Timer(30000)
AddHandler Timer1.Elapsed, AddressOf OnTimedEvent

Timer1.Interval = 30000
Timer1.Enabled = True
Timer1.Start()
WriteDebug("Timer 1 Started")

‘ If the timer is declared in a long-running method, use
‘ KeepAlive to prevent garbage collection from occurring
‘ before the method ends.
GC.KeepAlive(Timer1)

End Sub

Protected Overrides Sub OnStop()
‘ Add code here to perform any tear-down necessary to stop your service.
End Sub

Protected Sub OnTimedEvent(ByVal source As Object, ByVal e As ElapsedEventArgs)
Timer1.Enabled = False
Timer1.Stop()
WriteDebug("File Event Started")
Try
If Salvor1.AboutCompany = "SAM" Then
ReadFolder()
End If
Catch ex As Exception
WriteToEventLog("Web Service unavailable:" & ex.ToString, EventLogEntryType.Error)
End Try
Timer1.Enabled = True
Timer1.Start()
End Sub

Protected Sub ReadFolder()
Dim dirInfo As New DirectoryInfo(My.Settings.ImportFolder)
Dim FileArray As FileInfo() = dirInfo.GetFiles()

For Each TextFile In FileArray
If ReadFile(TextFile) Then
WriteDebug("Check File: " & TextFile.Name)
If Salvor1.ProcessFile(TextFile.Name) Then
DeleteFile(TextFile)
Else
WriteDebug("Rollback File: " & TextFile.Name)
Salvor1.RemoveFile(TextFile.Name)
End If

Else
Salvor1.RemoveFile(TextFile.Name)
End If
Next

End Sub

Protected Function ReadFile(ByVal TextFile As FileInfo) As Boolean
Dim Success As Boolean
Try
If File.Exists(TextFile.FullName) Then
Dim ioFile As New StreamReader(TextFile.FullName)
Dim ioLine As String
Success = True

While Not ioFile.EndOfStream
ioLine = ioFile.ReadLine
Success = Success And Salvor1.InsertLine(TextFile.Name, ioLine)
End While
ioFile.Close()
End If
Catch ex As Exception
Success = False
WriteToEventLog("Import of file " & TextFile.FullName & " failed:" & ex.ToString, EventLogEntryType.Error)
End Try
Return Success
End Function

Protected Sub DeleteFile(ByVal TextFile As FileInfo)
Try
WriteDebug("Delete File: " & TextFile.Name)
TextFile.Delete()
Catch ex As Exception
WriteToEventLog("Failed to delete file " & TextFile.FullName & ":" & ex.ToString, EventLogEntryType.Error)
End Try
End Sub

Protected Sub WriteToEventLog(ByVal Message As String, ByVal EntryType As EventLogEntryType)
Dim MyLog As New EventLog()
‘ Check if the the Event Log Exists
If Not Diagnostics.EventLog.SourceExists(Me.ServiceName) Then
Diagnostics.EventLog.CreateEventSource(Me.ServiceName, Me.ServiceName & " Log")
‘ Create Log
End If
MyLog.Source = Me.ServiceName
‘ Write to the Log
Diagnostics.EventLog.WriteEntry(MyLog.Source, Message, EntryType)
End Sub

Protected Sub WriteDebug(ByVal Message As String)
If My.Settings.Debug Then
WriteToEventLog(Message, EventLogEntryType.Information)
End If
End Sub
End Class[/code]

Importing data from MS SQL via CSV File

I have a database with a number of tables that I need to import into NAV in order to create a NAV solution to replace the old outdated solution.

The first step was to build identical tables in NAV.  In my case this was done manually, but there should not be difficult to convert a “CREATE TABLE” command into a NAV object text file.

I then got the name of all the tables in the database with

select name from sys.tables;

and copied the result to Excel. In Excel I first created the select statement in column C

="SELECT * FROM ["&A2&"];"

and then created the sqlcmd function in column G

="sqlcmd -S ServerName -E -d DatabaseName -Q " &
  CHAR(34)&C2&CHAR(34)&" -o "&A2&".csv -s ;"

Then I fill down column C and column G and have all the lines for my command file ready. Note that I use “;” as a decimal separator.

I create a folder for the data and within the folder I create a file called ExtractData.cmd and copy all the lines from column G to that file. Then this command file is executed and that folder fills up with CSV files for each table.

The final step is to import the data with the attached NAV Report.

MSSQL-CSV Import