Introduction

Using the ScriptExecuter utility, you can export data from an SQL database complying with relevant criteria to the .csv file. For example, the data by dishes, prices, sales (cash and non-cash payments, returns) over a specified period.

For proper work of the utility, installed MS SQL is needed.

To export the data, download the ScriptExecuter distributive. The distribution package contains:

  • The ScriptExecuter.exe app
  • The Settings.ini configuration file
  • The readme.txt information file with command and request description.

Utility Settings

To configure export, first, create a link to the SQL database (web.udl).

  1. Create a file with the .udl extension for configuring connection with MS SQL

    For correct work of ScriptExecuter.exe, create all three files — the parameters file, the request file and the .udl file — in the UTF-16 LE coding.

  2. Right-click on it and open its properties
  3. On the Provider tab, select Microsoft OLE DB Provider for SQL Server
  4. On the Connection tab, fill in the data for SQL server connection, and press Test Connection.

  5. Now, create an SQL request (SQLQuery3.sql). Below is an example of a simplest request:

    SELECT COUNT(*)
  FROM [RK169].[dbo].[MENUITEMS]

  6. Create a list.txt file. In this file, specify the parameters from the readme.txt file that will define the export from the SQL database.

Example:

Below are command variants from the readme.txt file:

  • tocsv: the file's path and name format for saving in the CSV format
  • mail: the addressee's email, used with -tofile
  • frtemplare:<file.fr3> is the fr3 template used for emailing instead of files
  • fields: FIELD1=DATEFORMAT;FIELD2
  • params: "PARAM1=VALUE1;PARAM2=VALUE 2"
  • fdf: a date format in the file name
  • ftf: a time formate in the file name
  • setmailpwd: a new email password
  • toxml: a save path
  • xmltmpl: an xml template file
  • http: to send via ftp using the current settings from settings.ini
  • ftp: to send via ftp using the current settings from settings.ini
  • ftpdir: path to ftp —  to send via ftp (used without -ftp!) using the indicated path
  • postrecsql: an sql-file with a request for each individual result entry once operations are finished
  • postsql: an sql-file with a global request that will be executed once operations are finished
  • setsftppwd: a new password
  • sethttppwd: a new password
  • sethttpproxypwd: a new password
  • setftppwd: a new password
  • clear: not to inherit parameters from previous operations
  • csvformat: "<value separator symbol><value isolation symbol, can be empty>", CSV format
  • csvcp: <0,1,2,...> — csv file coding, 1 is UNICODE, otherwise ANSI, by default = 1

Running Utility

Before running the utility, create a ScriptExecuter shortcut and indicate the parameter -list:list.txt in the shortcut properties.

Once the shortcut is run, a folder (in our case this is the 73905 folder) containing the TEST.csv should appear in the root directory. A folder containing logs (Log folder) is also created. Here you can track the operations, their success, and errors.

Export from SQL to sFTP and from Store House

There is an adjustment allowing to export data from StoreHouse.

The article has an attachment containing two files.

  • start.cmd
  • 105829.sql. 105829 — a request number where you can see more details.

MS SQL must be installed on your computer.

Usage: Run the 105829 script or open it as a text file and copy-paste it to the MS SQL console

Attachments:

scriptexecuter.rar

sqlexport.zip

  • No labels