DSN - examples		AfinScript © AFIN 2016
		Start: [F5] or [Ctrl+Q]
AFIN.NET.IS Samples Library
Inserting values into sheets
DBF (table) -> sheet (table)	DSN=dbase files;DBQ=C:\Data	SELECT * FROM DbfFile
DBF (calculated value) -> sheet (cell)	DSN=dbase files;DBQ=C:\Data	SELECT MAX(sw) FROM DbfFile
Specifying the data source
OLE DB	Driver={Microsoft Excel Driver (*.xls)};DBQ=[AFIN.NET.Folder]\Samples\Data\ODBC\SalesInExcel.xls	SELECT * FROM table
DSN	DSN=excel files;DBQ=C:\sheets\SKOR1.xls	SELECT * FROM table
Getting the data via ODBC or OLE DB
from DBF	DSN=dbase files;DBQ=C:\Data	SELECT * FROM DbfFile
from XLS (MS Excel) - Source: named range	DSN=excel files;DBQ=C:\sheets\SKOR1.xls	SELECT * FROM range1
from XLS (MS Excel) - Source: sheet	DSN=excel files;DBQ=C:\sheets\SKOR1.xls	SELECT * FROM [Sheet1$]
from XLS (MS Excel) - Source: sheet range	DSN=excel files;DBQ=C:\sheets\SKOR1.xls	SELECT * FROM [Sheet1$A1:E10]
from TXT	DSN=text files;DBQ=C:\sheets\	SELECT * FROM TextFile.txt TextFile
from MDB (MS Access) /DSN	DSN=access files;DBQ=C:\sheets\DbName	SELECT * FROM table
from MDB (MS Access) /OLE DB	Driver={Microsoft Access Driver (*.mdb)};DBQ=[AFIN.NET.Folder]\Samples\Data\ODBC\SalesInAccess.mdb	SELECT * FROM table
from ACCDB (MS Access 2007 database)	Driver={Microsoft Access Driver (*.mdb, *.accdb)};ReadOnly=0;UID=admin;DBQ=[AFIN.NET.Folder]\Samples\Data\ODBC\SalesInAccess2007.accdb	SELECT * FROM table
from XLSX (MS Excel 2007 spreadsheet)	Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};ReadOnly=0;UID=admin;DBQ=D:\Path\File.xlsx;	SELECT * FROM [Sheet1$]
from SQLite	ODBC;DRIVER=SQLite3 ODBC Driver; Database=C:\test\TestSqlite3ForExcel.db3; LongNames=0;Timeout=1000;NoTXN=0;SyncPragma=NORMAL;StepAPI=0;	SELECT * FROM main.MyBigTable MyBigTable_0
Processing data via ODBC or OLE DB
Insert data into a sheet from Access	Driver={Microsoft Access Driver (*.mdb)};DBQ=[AFIN.NET.Folder]\Samples\Data\ODBC\SalesInAccess.mdb	SELECT * FROM faktura						21
Insert data into a sheet from Excel	Driver={Microsoft Excel Driver (*.xls)};DBQ=[AFIN.NET.Folder]\Samples\Data\ODBC\SalesInExcel.xls	SELECT * FROM odbiorca						22
Insert data into a sheet from DBF	Driver={Microsoft dBase Driver (*.dbf)};DBQ=[AFIN.NET.Folder]\Samples\Data\ODBC\SalesInDBF	SELECT * FROM towar						19
Insert data into a sheet from TXT	Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=[AFIN.NET.Folder]\Samples\Data\ODBC\SalesInTXT	SELECT * FROM pozycja.txt pozycja						33
Insert data into a sheet from HTM file (local)	URL;file:///[AFIN.NET.Folder]/Samples/Data/ODBC/SalesInHTML/Region.htm							0
Insert data into a sheet from HTM from Internet	URL;http://afin.net/samples/Data/SalesInHTML/Faktura.htm							0
Insert data into a sheet from HTM specified table	URL;http://nbp.pl/kursy/kursya.html	4						1
Insert data into a sheet from SQL Server	DRIVER=SQL Native Client;SERVER=AFIN5\SQLEXPRESS;UID=Wojciech G;Trusted_Connection=Yes;APP=Microsoft Office 2003;WSID=AFIN5;DATABASE=BazaDanych1;	SELECT FAKTURA.* FROM BazaDanych1.dbo.FAKTURA FAKTURA
More complicated query	Driver={Microsoft Excel Driver (*.xls)};DBQ=[AFIN.NET.Folder]\Samples\Data\ODBC\SalesInExcel.xls	SELECT region.*, odbiorca.*, faktura.* FROM region, odbiorca, faktura WHERE region.miasto=odbiorca.miasto AND odbiorca.nazwa=faktura.nazwa						138
Sample of using folder with '.' in names	Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\AccessFile.mdb	SELECT * INTO aaa IN 'C:\Folder.With.Points.In.The.Name\ExcelFile' [Excel 8.0;] FROM faktura
Sample of using folder with '.' in names	Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\AccessFile.mdb	SELECT * INTO aaa FROM `VER1$` `VER1$` IN '[AFIN.NET.Folder]\Samples\Data\Excel\SheetRanges\Model6\C01D01\Budget2007C01D01' [Excel 8.0;]
Getting, processing and inserting data via ODBC or OLE DB
from TXT to DBF 1. way	DSN=text files;DBQ=C:\Data\TxtFolder	SELECT * INTO DbfFile.dbf IN "C:\Data\DBF"[dbase IV;] FROM TextFile.txt TextFile
from TXT to DBF 2. way	DSN=dbase files;DBQ=C:\Data	SELECT * INTO CopyFile FROM TextFile.txt TextFile IN "C:\Data\TxtFolder" [text;]
from MDB to XLS	Driver={Microsoft Access Driver (*.mdb)};DBQ=[AFIN.NET.Folder]\Samples\Data\ODBC\SalesInAccess.mdb	SELECT nazwa AS ColA, wart_net AS ColB INTO [Excel 8.0;database=C:\TestXlsFile.xls;].MyNewTable FROM faktura
from DBF to XLS specyfied name	DSN=dbase files;DBQ=C:\Data	SELECT * INTO SheetRangeName IN "C:\Data\WorkbookName" [Excel 5.0;] FROM DbfFile
from DBF to XLS sheet	DSN=dbase files;DBQ=C:\Data	SELECT * INTO [SheetName$] IN "C:\Data\WorkbookName"[Excel 5.0;] FROM DbfFile
from TXT to XLS	DSN=text files;DBQ=C:\Data	SELECT * INTO RangeName IN "C:\Data\WorkbookName" [Excel 5.0;] FROM TextFile.txt TextFile
from DBF to MDB (MS Access)	DSN=dbase files;DBQ=C:\Data	SELECT * INTO table IN "C:\Data\NazwaBazy"[Access;] FROM DbfFile
from XLSX to MDB (!!!)	Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};ReadOnly=0;UID=admin;DBQ=D:\Folder\Book2.xlsx;	SELECT * INTO aaa IN 'D:\Folder\WorkBase.mdb' FROM NamedRange
from TXT to MDB (!!!)	DSN=text files;DBQ=C:\TxtFolder	SELECT * INTO testodb IN 'C:\Folder\WorkBase.mdb' FROM TextFile.txt TextFile
to the TXT file is possible too, but requires a special SHEMA.INI file definition
You can also use "Provider="	Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="DSN=Pliki programu Excel;DBQ=[AFIN.NET.Folder]\Samples\Data\ODBC\SalesInExcel.xls;DefaultDir=C:\KursExcela\Dane;DriverId=790;MaxBufferSize=2048;PageTimeout=5;"	SELECT * FROM [odbiorca$]
OLE DB SQL Server	Provider=SQLNCLI11;Server=ServerName;Database=DBName;Trusted_Connection=yes;	SELECT * FROM [dbo].[odbiorca]
Processing data (quasi data warehouse)
adding columns		ALTER TABLE tbl1 ADD COLUMN field1 SHORT,field2 TEXT(10),field3 TEXT(3),field4 DOUBLE
modyfying columns		ALTER TABLE tbl1 ALTER COLUMN col1 TEXT(20)
removing columns		ALTER TABLE tbl1 DROP COLUMN field1,field2
removing table		DROP TABLE tbl1
defining table		CREATE TABLE tbl1 (field1 INTEGER, field2 TEXT(50), field3 DOUBLE, field4 TEXT(20), field5 DATE)
connecting to other table	Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\Folder\WorkBase.mdb	SELECT _tbl1.*, _tbl2.* INTO _TestTable FROM `_tbl1` LEFT OUTER JOIN `_tbl2` ON `_tbl1`.DateRMD = `_tbl2`.Data WHERE `_tbl1`.Material NOT LIKE '%Txt1%' AND `_tbl1`.Material NOT LIKE '%Txt2%'
deleting records (or TRUNCATE)		DELETE FROM tbl1
Other
modyfying data		UPDATE tbl1 SET balance=0 WHERE balance IS NULL
making a local OLAP cube	DSN=Access files;DBQ=D:\Data\Sales.mdb	OQY=D:\Data\Sales.oqy
breaking for a given time [hh:mm:ss]	WAIT	0,000115741
breaking for about 1 second (default)	WAIT
stopping the program	STOP
The italic font in text in 'Source' makes the line not working	DSN=SampleDataSourceInItalicFont	This statement (command line) doesn't work
Calculates and publishes the workbook	BATCH=[AFIN.NET.Folder]\Temp\[ExcelFileName].xls	Calculate;Publish
Downloads data from Internet	DOWNLOAD	http://afin.net/samples/data/ODBC/SalesInExcel.xls
Converts to XLS format eg from XML format	CONVERT2XLS	[path]\file.xml
Converts to XLS format eg from ODC format	CONVERT2XLS	[path]\file.odc
Runs a specified macro in the current workbook	MACRO	MyMacroName
Runs THIS program again on a specfied time. It can be calculated by a formula.	RUN	41788,64042
Runs the another AFIN.NET.IS program (It CAN BE inserted as a HYPERLINK to a file of IS for better editing)	RUN IS	C:\Program files\AFIN.NET\Samples\IS.xlsm
IF the name [AFIN.NET.DB] is defined in this worksheet and has a value of the PROPER ConnctionString, you can use it as follows
Performs operation on the specified DB	[AFIN.NET.DB]	{Any SQL command}
Performs SELECT INTO or (if the table already exists and it has the same structure) INSERT INTO command. If [AFIN.NET.DB] is specified as an Access file, it is created automatically if it doesn't exist.	{Any ConnectionString}	{Any SQL command}
Performs SELECT INTO via the virtual recordset. Try it only if the above command fails.	{Any ConnectionString}	{Any SQL command}
Using the Shell (DOS) commands
deleting files	Shell (or "DOS")	del C:\Data\!*.dbf
copying files	Shell	copy C:\Data\*.dbf C:\Data2\*.dbf
It's better to use that with quotations…	Shell	copy "C:\Data\*.dbf" "C:\Data2\*.dbf"