| Afin.SQL | AfinScript © AFIN 2016 | ||||||||
| Start: [F5] or [Ctrl+Q] | |||||||||
| <<< Parameters | Info: 5 columns (A:E = 'COCPI..T') from the #11 row are reserved for the AfinScript's program. | ||||||||
| e.g. Path = | C:\AnyPath | ||||||||
| e.g. Database = | |||||||||
| e.g. Period = | |||||||||
| (…) | |||||||||
| Class (Vendor) | Object | Command | Parameters | Info | The rest: Comments, calculations, row parameters | ||||
| Afin.SQL commands | . | ||||||||
| . | . | ||||||||
| Afin.SQL | Info | OK, but it stops the program. | |||||||
| Afin.SQL | C:\AnyPath\Testy\Sprzedaz2015_testy.xls | SELECT 1 | PasteTo=MySheet1!A5; Name=MyName | , | |||||
| Afin.SQL | C:\AnyPath\Testy\Sprzedaz2015_testy.xls | SELECT 1 AS [Jedynka] | PasteTo=MySheet1!A10 | . | |||||
| Afin.SQL | C:\AnyPath\Testy\Sprzedaz2015_testy.xlsx | SELECT 2 AS [Dwójka] FROM [Faktura$] | PasteTo=MySheet1!D10 | . | |||||
| Afin.SQL | C:\AnyPath\Testy\Sprzedaz2015_testy.mdb | SELECT 1 AS [Jedynka] | PasteTo=MySheet1!G10 | . | |||||
| Afin.SQL | C:\AnyPath\Testy\Sprzedaz2015_testy.accdb | SELECT 2 AS [Dwójka] FROM [Faktura] | PasteTo=MySheet1!J10 | . | |||||
| . | . | ||||||||
| Afin.SQL | C:\AnyPath\Testy\Sprzedaz2015_testy.xls | DROP TABLE [kopia] | . | ||||||
| Afin.SQL | DSN=Excel files;DBQ=C:\AnyPath\Testy\Sprzedaz2015_testy.xls | DROP TABLE [kopia] | . | ||||||
| Afin.SQL | DSN=Excel files;DBQ=C:\AnyPath\Testy\Sprzedaz2015_testy.xls | SELECT * INTO [kopia] FROM [faktura$] | . | ||||||
| Afin.SQL | DSN=MS Access Database;DBQ=C:\AnyPath\Testy\sprzedaz2015_testy.mdb | SELECT 1 AS [Jedynka2] | PasteTo=MySheet1 | . | |||||
| Afin.SQL | DSN=Excel files;DBQ=C:\AnyPath\Testy\Sprzedaz2015_testy.xls | SELECT 2 AS [Dwójka] FROM [Faktura$] | PasteTo=RemoveThisSheet!A4 | . | |||||
| Afin.SQL | C:\AnyPath\Testy\Sprzedaz2015_testy.xlsx | SELECT 2 AS [Dwójka] FROM [Faktura$] | PasteTo=MySheet2!A4 | . | |||||
| Afin.SQL | C:\AnyPath\Testy\Sprzedaz2015_testy.xlsx | SELECT 2 AS [Dwójka] FROM [Faktura$] | PasteTo=NewSheet; Name=MyData | . | |||||
| Afin.SQL | C:\AnyPath\Testy\Sprzedaz2015_testy.xlsx | SELECT 2 AS [Dwójka] FROM [Faktura$] | >=MyData | . | It specifies both parameters at once (the same as): PasteTo=MyData; Name=MyData | ||||
| AFIN.SQL | c:\AnyPath\AccessDatabase.accdb | SELECT * INTO [ODBC;DRIVER=SQL Server Native Client 11.0;SERVER=.\SQLEXPRESS;UID=WojciechG;Trusted_Connection=Yes;APP=Microsoft Office 2013;WSID=AFIN7; DATABASE=Test].[NewTable] FROM [Test.txt] IN 'c:\test'[Text;] |
. | Get data from a text file and insert it into a MS SQL Server database | |||||
| AFIN.SQL | … | … | Clear={examine 'Clear=' specification} | . | |||||
| . | |||||||||
| AFIN.VBA | GETDATAODBC | Arg1=c:\AnyPath\AccessDatabase.accdb; Arg2=SELECT * FROM table; PasteTo=…; Clear=…; Transpose=1; Name=… | . | If you wand to transpose or to unpivot the pasted date use Afin.VBA library and a function GETDATAODBC | |||||