Tag: Data Transfer

Posted on

Reading a text file

To follow up the post about writing a text file with Automation I would also like to post about reading a text file with that same Automation.  Now we add the automation automation ‘Windows Script Host Object Model’.File.  First you need to create a file system object ‘Windows Script Host Object Model’.FileSystemObject. Then open the file in the File automation to get the size of the file. Finally open the textstream and start reading. This will give you correct progress dialog and the correct character set when reading.
[code htmlscript=”false”]IF FileName = ” THEN
ERROR(Text001);

CLEAR(SystemFilesSystem);
CREATE(SystemFilesSystem,TRUE,TRUE);

IF NOT SystemFilesSystem.FileExists(FileName) THEN
ERROR(Text002);

SystemFile := SystemFilesSystem.GetFile(FileName);
SystemTextStream := SystemFile.OpenAsTextStream;
FileSize := SystemFile.Size;
FilePos := 0;

Window.OPEN(Text003 + ‘\\@1@@@@@@@@@@@@@@@@@@@@’);
WindowLastUpdated := CURRENTDATETIME;
WHILE NOT SystemTextStream.AtEndOfStream DO BEGIN
Line := SystemTextStream.ReadLine;
FilePos := FilePos + STRLEN(Line) + 2;

‘Handle Line

IF (CURRENTDATETIME – WindowLastUpdated) > 100 THEN BEGIN
Window.UPDATE(1,ROUND(FilePos / FileSize * 10000,1));
WindowLastUpdated := CURRENTDATETIME;
END;
END;
Window.Close;
SystemTextStream.Close;
CLEAR(SystemFilesSystem);[/code]
 

Posted on

File Download via HTTP and FTP

I have been working on a solution for file download.  I am now able to download both binary and text files both via http and ftp and both with Classic Client and Role Tailored Client.

Attached is a codeunit that can download a text file via http, a zip file via http and a text file via ftp.  I use dotnet object for the Role Tailored Client to handle files on the client side.

FileDownload

Posted on

WSDL Code Generator

On several occasions I have needed to create a code to communicate with Soap Web Services. The manual labor in creating the functions and XML Ports is something that I would like to be rid of. On that node I created a Batch that does most of the job for me.

Execute this and you will be asked to save the “Web Service Objects.txt” file to your computer and from there you will be able to import the file and continue the work.

You will get XMP Ports for every method, both the request and response.  You will also get a Codeunit with a function for every method and the necessary functions to handle the web service communication.

What is left is for you to connect your data to the functions and the XML Ports.

This is not fully tested, so any updates would be appreciated.

Import WSDL (Updated 2012-12-08)

Posted on

Import a text to RTC Note in the Record Link Table

In the process of importing data from MSSQL into NAV I needed to import a comment text. I wanted to import the text into the Note BLOB field in the Record Link table. It was not obvious how to do this but after some work I successfully imported all my comments.

I first start to create the record link with the following code
[code htmlscript=”false”]IF Note <> ” THEN BEGIN
LinkID := DestTbl.ADDLINK(”,DestTbl.TABLECAPTION);
RecordLink.GET(LinkID);
RecordLink.Type := RecordLink.Type::Note;
RecordLink.Note.CREATEOUTSTREAM(OutStr);
OutStr.WRITE(Text2NoteText(Note));
RecordLink.MODIFY;
END;[/code]
After my first round I found out that I needed to include the length of the Note as a Char in the beginning of the note. Then I noticed that my Icelandic characters where not correctly imported and after some study found out that the content of the BLOB needs to be UFT-8 encoded. This led me to create the Text2NoteText function.
[code htmlscript=”false”]Text2NoteText(NoteToImport : Text[1024]) NAVNode : Text[1024]
NAVNode := UTF8Encode(NoteToImport);
NoteLength := STRLEN(NAVNode);
IF NoteLength <= 255 THEN BEGIN
Char1 := NoteLength;
NAVNode := FORMAT(Char1) + NAVNode;
Char2 := 1;
END ELSE BEGIN
Char1 := 128 + (NoteLength – 256) MOD 128;
Char2 := 2 + (NoteLength – 256) DIV 128;
NAVNode := FORMAT(Char1) + FORMAT(Char2) + NAVNode;
END;[/code]
And the UFT8Encode function
[code htmlscript=”false”]UTF8Encode(String2Encode : Text[1024]) EncodedString : Text[1024]
MakeVars;
String2Encode := CONVERTSTR(String2Encode,NavStr,AsciiStr);
FOR Index := 1 TO STRLEN(String2Encode) DO BEGIN
Char2 := String2Encode[Index];
IF Char2 <= 127 THEN
EncodedString := EncodedString + FORMAT(Char2)
ELSE IF Char2 <= 192 THEN BEGIN
Char1 := 194;
EncodedString := EncodedString + FORMAT(Char1) + FORMAT(Char2);
END ELSE BEGIN
Char1 := 195;
Char2 := Char2 – 64;
EncodedString := EncodedString + FORMAT(Char1) + FORMAT(Char2);
END;
END;[/code]
The MakeVars function creates the NavStr and AsciiStr that I need for the conversion. This is similar to function InitCharTables in Codeunit 424 except I am not using Excel to convert the strings.

Attached is a codeunit with these functions.

RTCNoteTool

 

Posted on

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

 

 

Posted on

Example on using NAV Web Service in NAV C/Side

Some of our clients are using LS Retail.That solution supports stand-alone POS terminals that are running Microsoft Dynamics NAV.  It is therefore easy to allow these terminals to be used also as a punch clock.

This punch clock starts up a background process to handle synchronization via web services.  Attached is a demo c/side code on how the web services are consumed.

SyncEmployees

Posted on

Data Transfer with XML Port

In my Payroll development I created a solution to import and export setup data from one company to another.  This is done via XMLPort.  I created the XMLPort code with a Report that is attached at the bottom of the post.

First I need a function to create the table list for the setup data
[code]SetupObjectNoList(VAR TempObject : Record Object)
TableIDArray[1] := DATABASE::"Payroll Setup";
TableIDArray[2] := DATABASE::"Payroll Tax Setup";
…
TableIDArray[57] := DATABASE::"Payroll Column Layout";

Object.SETRANGE(Type,Object.Type::Table);

FOR Index := 1 TO ARRAYLEN(TableIDArray) DO BEGIN
Object.SETRANGE(Object.ID,TableIDArray[Index]);
IF Object.FINDFIRST THEN BEGIN
TempObject := Object;
TempObject.INSERT;
END;
END;[/code]
And to be able to import new setup data I must clear all data from the setup tables
[code]DeleteSetupData()
IF NOT CONFIRM(Text005,FALSE) THEN EXIT;

SetupObjectNoList(TempObject);
DialogMgt.WindowOpen(‘@1@@@@@@@@@@@@@@@@@@@@@@’);
DialogMgt.WindowSetTotal(1,TempObject.COUNT);
TempObject.FINDSET;
REPEAT
RecRef.OPEN(TempObject.ID);
IF NOT RecRef.ISEMPTY THEN
RecRef.DELETEALL;
RecRef.CLOSE;
DefaultDim.SETRANGE("Table ID",TempObject.ID);
IF NOT DefaultDim.ISEMPTY THEN
DefaultDim.DELETEALL;
LineDim.SETRANGE("Table ID",TempObject.ID);
IF NOT LineDim.ISEMPTY THEN
LineDim.DELETEALL;
Translation.SETRANGE("Table ID",TempObject.ID);
IF NOT Translation.ISEMPTY THEN
Translation.DELETEALL;
DialogMgt.WindowProcess(1);
UNTIL TempObject.NEXT = 0;
DialogMgt.WindowClose;
COMMIT;[/code]
Then I have the Import and Export functions. In this code I am using a Log table BLOB field but could as well use the TempBlob table.
[code]ImportFile(FileName : Text[1024])
DialogMgt.WindowOpen(Text003);
Log.INIT;
Log."Entry No." := 0;
Log."Service Description" := Text001;
Log."Created by User ID" := USERID;
Log."Creation Date and Time" := CURRENTDATETIME;
IF Log.ImportIncomingXML(FileName,FALSE) &lt;&gt; ” THEN BEGIN
Log."Incoming Message".CREATEINSTREAM(InStr);
XML.SETSOURCE(InStr);
XML.IMPORT;
CLEAR(XML);
END;
Log.INSERT;
DialogMgt.WindowClose;

ExportFile(FileName : Text[1024])
DialogMgt.WindowOpen(Text004);
Log.INIT;
Log."Entry No." := 0;
Log."Service Description" := Text002;
Log."Created by User ID" := USERID;
Log."Creation Date and Time" := CURRENTDATETIME;
Log."Outgoing Message".CREATEOUTSTREAM(OutStr);
XML.SETDESTINATION(OutStr);
XML.EXPORT;
CLEAR(XML);
Log.INSERT;
Log.ExportOutgoingXML(FileName,FALSE);
DialogMgt.WindowClose;[/code]
Report to create XMLPort

Posted on

Transferring small amount of data between databases

Most of us have needed to copy data from one Dynamics NAV database to another.  For example the posting setup tables, payment terms, currency, etc.

If the databases are not identical you will not be able to copy and paste the data and you have to solve this problem with dataports or XMLPorts.

I offer a solution to this problem.  I have created a form that is able to export and import data based on table and field numbers.  It will import data to a table even if some fields are missing in the destination database.

Here is the source code