Microsoft Dynamics™ NAV – Export your products to Magento by FTP using VBScript

Home Magento TopicsMicrosoft Dynamics™ NAV – Export your products to Magento by FTP using VBScript
Microsoft Dynamics™ NAV

Microsoft Dynamics™ NAV – Export your products to Magento by FTP using VBScript

Magento Topics, Microsoft Dynamics Nav Topics No Comments

Microsoft Dynamics™ NAV – Export your products, customers, etc. to Magento.

We show a powerful group of functions that will help you to export your data from Microsoft Dynamics™ NAV to a FTP Site.

We use this solution to synchronize products, translations, categories, etc. from Microsoft Dynamics™ NAV to Magento with our new Magento Extension Magboxes Product Synchronization (http://www.eboxesfactory.com/magboxes-product-synchro/).

We are sure that you will find this post very interesting!

The process, has the following steps:

1.- Delete local files.
2.- Export Microsoft Dynamics™ NAV data to a local file.
3.- Upload local file to a FTP Site.

In order to do this process, we need to create (for example) a codeunit where we will define all functions and variables necessary to execute the process. First, we will define the following global variables:

  • Name: WshShell
  • Data Type: Automation
  • Subtype: ‘Windows Script Host Object Model’.WshShell
  • Description: // EBOXESFACTORY – Automation VBScript.
  • Name: intWindowStyle
  • Data Type: Integer
  • Description: // EBOXESFACTORY – Used to define WshShell windows style.
  • Name: bWaitOnReturn
  • Data Type: Boolean
  • Description: // EBOXESFACTORY – Used to define WshShell type of execution.

1.- Delete Local Files

We will use function DeleteLocalfile with the following parameters:

  • Name: LocalCmdFile
  • Data Type: Text
  • Length: 120
  • Description: // EBOXESFACTORY – Path/file name .cmd used to inform delete commands.
  • Name: LocalFile
  • Data Type: Text
  • Length: 120
  • Description: // EBOXESFACTORY – Path/file name to delete.

Like local variables in function DeleteLocalfile we will found the following:

  • Name: DelCommandFile
  • Data Type: File
  • Description: // EBOXESFACTORY – File to keep delete commands.

Function DeleteLocalfile code:

//>>> EBOXESFACTORY: DeleteLocalfile. **
 
//>>> 1.- Create Command Execution File
DelCommandFile.CREATE(LocalCmdFile);
DelCommandFile.TEXTMODE(TRUE);
DelCommandFile.WRITE('del ' +LocalFile);
DelCommandFile.CLOSE;
 
//>>> 2.- Execute Command File
IF ISCLEAR(WshShell) THEN CREATE(WshShell);
intWindowStyle := 0;
bWaitOnReturn := TRUE;
WshShell.Run(LocalCmdFile,intWindowStyle,bWaitOnReturn);
CLEAR(WshShell);

2.- Export Microsoft Dynamics™ NAV to Local File

We will use a function with name ExportData used to call different functions of process.

In this example, we have added several configuration fields in a configuration table (311 – Sales & Receivables Setup, 313 – Inventory Setup, etc.), used to informe values that we will need to execute the process. Fields to include are the following:

  • Name: FTP User
  • Data Type: Text
  • Length: 30
  • Description: // EBOXESFACTORY – FTP connection user.
  • Name: FTP Password
  • Data Type: Text
  • Length: 30
  • Description: // EBOXESFACTORY – FTP connection password.
  • Name: IP FTP
  • Data Type: Text
  • Length: 30
  • Description: // EBOXESFACTORY – FTP Server IP.
  • Name: Folder FTP
  • Data Type: Text
  • Length: 120
  • Description: // EBOXESFACTORY – FTP Folder to keep the uploaded file.
  • Name: Export Local Path
  • Data Type: Text
  • Length: 120
  • Description: // EBOXESFACTORY – Local Path Local where we will save exported datafile.

Now, we are going to define the code to include in this function:

//>>> EBOXESFACTORY: ExportData. **
 
 
IF CustomTableSetup.GET() THEN
BEGIN
 
   <strong>//&gt;&gt;&gt; Delete Local File</strong>
   DeleteLocalFile(CustomTableSetup."Export Local Filename"+'\dele_command.cmd',
   CustomTableSetup."Export Local Filename"+'\products.csv');
 
   <strong>//&gt;&gt;&gt; Execute Export Data.</strong> In this example we are exporting product data.
   //&gt;&gt;&gt; You can execute a dataport, a XLMport, inform one variable type FILE, etc.
   ExportProducts; 
 
   <strong>//&gt;&gt;&gt; Upload File.</strong>
   FTPUploadFile(CustomTableSetup."FTP User",CustomTableSetup."FTP Password",
                 CustomTableSetup."Export Local Path"+'\ftp_script.txt',CustomTableSetup."Folder FTP",
                 CustomTableSetup."Export Local Path"+'\exec_command.cmd',CustomTableSetup."IP FTP",
                 CustomTableSetup."Export Local Path",'products.csv');
END

Finally, we define the function FTPUploadFile that will make the upload of local file to FTP Site.

3.- Upload local file to a FTP Site.

We use function FTPUploadFile with the following parameters:

  • Name: FTPUsername
  • Data Type: Text
  • Length: 90
  • Description: // EBOXESFACTORY – FTP User Name.
  • Name: FTPPassword
  • Data Type: Text
  • Length: 90
  • Description: // EBOXESFACTORY – FTP Password.
  • Name: FTPLocalScript
  • Data Type: Text
  • Length: 120
  • Description: // EBOXESFACTORY – Path/Local file name with Script commands.
  • Name: FTPRemoteFolder
  • Data Type: Text
  • Length: 90
  • Description: // EBOXESFACTORY – FTP Folder where we will keep the uploaded file.
  • Name: FTPLocalCmdFile
  • Data Type: Text
  • Length: 120
  • Description: // EBOXESFACTORY – Path/Local file name where we will keep the WshShell execution commands.
  • Name: FTPHostname
  • Data Type: Text
  • Length: 90
  • Description: // EBOXESFACTORY – IP/Host Name FTP.
  • Name: FTPLocalFolder
  • Data Type: Text
  • Length: 120
  • Description: // EBOXESFACTORY – Local folder where we will save the file to upload.
  • Name: FTPLocalFile
  • Data Type: Text
  • Length: 90
  • Description: // EBOXESFACTORY – local file name to upload to FTP Site.

The local variables to define in FTPUploadFile function, are the following:

  • Name: CommandFile
  • Data Type: File
  • Description: // EBOXESFACTORY – File to keep execution commands.
  • Name: ScriptFile
  • Data Type: File
  • Description: // EBOXESFACTORY – File to keep upload commands.

The FTPUploadFile function code:

//&gt;&gt;&gt; EBOXESFACTORY: FTPUploadFile. **
 
 
//&gt;&gt;&gt; 1.- Create Script File
 
//   binary --&gt; Set file transfer mode to binary.
//   cd FTPRemoteFolder --&gt; Select and place on remote FTP folder.
//   lcd FTPLocalFolder --&gt; Place on local folder.
//   mput *.* --&gt; Copy files from local folder to remote FTP folder.
//   quit -&gt;- Exit form FTP
 
ScriptFile.CREATE(FTPLocalScript);
ScriptFile.TEXTMODE(TRUE);
ScriptFile.WRITE(FTPUsername);
ScriptFile.WRITE(FTPPassword);
ScriptFile.WRITE('binary');
ScriptFile.WRITE('cd ' + FTPRemoteFolder);
ScriptFile.WRITE('lcd ' + FTPLocalFolder);
ScriptFile.WRITE('mput ' + FTPLocalFile);
ScriptFile.WRITE('quit');
ScriptFile.CLOSE;
 
//&gt;&gt;&gt; 2.- Create Command Execution File
 
//   -i --&gt; Turns off prompt.
//   -s:FileName --&gt; Text file that containf script ftp commands (without spaces!) ;
 
 
CommandFile.CREATE(FTPLocalCmdFile);
CommandFile.TEXTMODE(TRUE);
CommandFile.WRITE('ftp -i -s:' +FTPLocalScript+' '+FTPHostname);
CommandFile.CLOSE;
 
//&gt;&gt;&gt; 3.- Execute Command File.
 
//   intWindowStyle --&gt; Optional. Window style value. 0 = Hides the window and activates another window.
//   bWaitOnReturn --&gt;  Optional. If TRUE suspend script until the command execution has been finished.
 
 
IF ISCLEAR(WshShell) THEN CREATE(WshShell);
intWindowStyle := 0;
bWaitOnReturn := TRUE;
WshShell.Run(FTPLocalCmdFile,intWindowStyle,bWaitOnReturn);
CLEAR(WshShell);

To execute the process, we only need to call function ExportData.

Leave a Reply

Your email address will not be published. Required fields are marked *