Description

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.

Contents of Distribution Package

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 having the .udl extension
2. Right-click 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:

   DECLARE  @RSIFR INT = :rsifr  SELECT TOP 1000  [SIFR]  ,[CODE]  FROM [RK7Base].[dbo].[MENUITEMS]  where (CODE=2)

   
   

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 StoreHouse

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: