The SQL Developer creates SQL code and stores them in spreadsheets, by allocating one column for one SQL code. The column has 3 sections:

1. TITLE SECTION - consist of a title in row 1 enclosed as comments, e.g., /* TITLE */

2. DRIVER SECTION - consist of multi-line comments enclosed by 1 row for "/* DRIVER:" at the beginning and 1 row for "*/" as the end

3. CODE SECTION - consist of a valid and tested SQL code.


The sheet name for SQL codes, can be any name (e.g., SALES, CUSTOMER, GL) so long as it does not begin with menu*. docs*, Sheet*, Macro* which are reserved for other components in D4E.


General Format Example:

/* SQL TITLE */

/* Driver:

connections strings

*/

SELECT * FROM TABLE


Depending on the DBMS or SQL source type, the DRIVER SECTION will configured correspondingly.  Within the DRIVER SECTION, you can have optional variables (with format $variable=value,  or $variable=value=$variable,) to be used depending on the DBMS:

$type=value,     (where value can be ODBC, Python, Excel, CSV  default is ODBC)
$dbms=value,      (where value can be BigQuery, PostgreSQL,MySQL, SQLite, SQLServer, Oracle)
$file_path=value,     (paths and filenames are used in reading output results)

$filetables=table1;table2,
$results=keep,      (write output results to tempdir C:\temp\d4e\)
$showresults=yes,     (display results in Excel, default if no tempdir outputs)

$conn=value=$conn,

$your_host=value,     ("your" variables are used in Python DBMSs)
$your_port=value,
$your_service_name=value,
$your_user=value,
$your_password=value,




Below are sample ways to create SQL code in SQL Files for various DBMS types


1.  ODBC connection and SQL to read an MS ACCESS database:


/* Title Sample ODBC */
/* Driver:
$type=ODBC,
$conn=
Provider=Microsoft.ACE.OLEDB.12.0;
Data Source=C:\temp\d4e\FinancialData.accdb;
Persist Security Info=False;
=$conn,
*/
SELECT * FROM Budgets


EXPLANATION: In the DRIVER SECTION, $type=ODBC indicates that this is an ODBC connection, and the connection string is indicated between "$conn=" and "=conn,"


2. Python method to read a CSV file:


/* GL Bals SYD CY */
/* Driver: 
$type=Python,
$dbms=CSV,
$file_path=<<#td#>>gl_balances_data.csv,
$results=keep,
$showresults=yes,
*/
SELECT 
Company as Company_key,
Branch as Branch_key,
Account as Branch_key,
Premium_Class,
RI_CC,
Source_Employee,
UW_Year,
Intercompany,
Spare1,
Spare2,
Currency,
DR,
CR,
Net_Amt,
Year as Year_key,
Period as Period_key,
Account_Description
FROM data WHERE Year = 2025


EXPLANATION: Because of $results=keep and $showresults=yes, D4E shows the results in Excel as well as write the results in the TEMP folder (C:\TEMP\D4E\). So this result will be found in file <SQL_sheetname>__<column_char of SQL code)>.csv  (e.g.,SALES__F.csv)



3. Python method of reading results files or previous extracts using D4E:  In the examples below, the 3 queries are in column A, B and C in sheet SALES

Sheet SALES Column A:
/* Ver F  Python-CSV*/
/* Driver:
$type=Python,
$dbms=CSV,
$file_path=C:\temp\sample_data.csv,
$results=keep,
$showresults=yes,

*/

SELECT * FROM data WHERE Age > 30


EXPLANATION: With $results=keep, the output will be written to the temp foider as SALES__A.csv


Sheet SALES Column B:

/* Ver G  Python-CSV*/
/* Driver:
$type=Python,
$dbms=CSV,
$file_path=C:\temp\sample_data.csv,
$results=keep,
$showresults=yes,
*/

SELECT * FROM data WHERE Age > 30


EXPLANATION: With $results=keep, the output will be written to the temp foider as SALES__B.csv


Sheet SALES Column C:
/* Ver K  Python-CSV*/
/* Driver:
$type=Python,
$dbms=Results,
$filefolder=c:\temp\d4e,
$results=keep,
$showresults=yes,
$filetables=Sales__A;Sales__B,
*/
SELECT * FROM Sales__A
UNION ALL
SELECT * FROM  Sales__B


EXPLANATION: In the DRIVER SECTION, $fileholder and $filetables, refer to the location and names of previously created results in D4E.  By default D4E write output results to the C:\Temp\D4E\ folder and names the files as <SQL sheet>__<column char>.CSV


4. Python extract for a Google BigQuery database:


/* Ver E  Python-BigQuery */
/* Driver: 
$type=Python,
$dbms=BigQuery,
*/
SELECT * FROM `bigquery-public-data.samples.shakespeare` LIMIT 10


EXPLANATION:  There is no connection string as BigQuery may automatically check your Windows credential and so only need to give correct SQL statement.  Please check with your local support.


5. Python extract for a Oracle database:

/* Sample Oracle DB */
/* Driver: 
$type=Python,
$dbms=Oracle,

$your_host=<yourhost>,

$your_port=<yourport>,

$your_service_name=<servicename>,

$your_user=<userid>,

$your_password=<password>,
*/
SELECT * FROM TABLE LIMIT 10


EXPLANATION: Based on the inputs above, D4E will construct and submit a Python script like:
           import cx_Oracle
           dsn = cx_Oracle.makedsn('<your_host>', <your_port>, service_name='<your_service_name>')
           conn = cx_Oracle.connect(user='<your_user>', password='<your_password>', dsn=dsn)

6. ODBC extract for MySQL database:

/* Sample MySQL ODBC public.opendatasoft.com */
/* Driver:
$type=ODBC,
$results=keep,
$conn=
Driver={MySQL ODBC 8.0 Unicode Driver};

Server=public.opendatasoft.com;Port=3306;Database=dataset_name;User=guest;Password=guest;
=$conn,
*/
SELECT * FROM cities LIMIT 10;


EXPLANATION: With ODBC, the connection string already defines the type of DBMS, so only need ot give a correct connection string in $conn


7. ODBC Extract for IBM DB2 Database:

/* Sample IBM DB2 */
/* Driver:
$type=ODBC,
$conn=
Driver={IBM DB2 ODBC DRIVER};
Database=mydatabase;
Hostname=mydbhost;
Port=50000;
Protocol=TCPIP;
Uid=myuser;
Pwd=mypassword;
=$conn,
*/
SELECT * FROM MYTABLE
  













DEMONSTRATION - HOW TO CREATE APPS THAT EXTRACT FROM DBMS

A DATA DISCOVERY TOOL WITH EXCELLENT MS 365 INTEGRATION