{"id":147,"date":"2017-01-04T20:14:03","date_gmt":"2017-01-04T20:14:03","guid":{"rendered":"https:\/\/reldb.org\/c\/?page_id=147"},"modified":"2017-01-04T23:48:46","modified_gmt":"2017-01-04T23:48:46","slug":"accessing-external-data-sources","status":"publish","type":"page","link":"https:\/\/reldb.org\/c\/index.php\/read\/accessing-external-data-sources\/","title":{"rendered":"Accessing External Data Sources"},"content":{"rendered":"

As of\u00a0Rel<\/em> version 3.003, it’s easy to read external data sources like CSV files, XLS\/XLSX spreadsheets, tables in Microsoft Access databases, and tables in external databases accessible via JDBC. You can also write to CSV files and XLS\/XLSX spreadsheet files.<\/p>\n

Reading External Data Sources<\/h2>\n

Data sources are read by defining\u00a0them as\u00a0external<\/em> relation-valued variables.<\/p>\n

External relation-valued variables, or external relvars, are defined via a\u00a0Rel<\/em> extension to\u00a0Tutorial D<\/strong>. The VAR variable declaration supports the following syntax:<\/p>\n

VAR <varname> EXTERNAL <kind> <connection_string> [ DUP_REMOVE | DUP_COUNT | AUTOKEY ];<\/p><\/blockquote>\n

<kind><\/em> is the kind of external relvar. Built-in are CSV, XLS, JDBC and ACCDB. (Additional kinds, and additional JDBC drivers, can be put in the Extensions directory of a Rel database but are only recognised by the standalone Rel DBMS.)<\/p>\n

<connection_string><\/em> is a CHAR specific to the kind of external relvar that describes the path or connection to the data source.<\/p>\n

DUP_REMOVE<\/em> silently removes duplicate tuples.<\/p>\n

DUP_COUNT<\/em> adds an INTEGER attribute named _DUP_COUNT that indicates a count of duplicate tuples.<\/p>\n

AUTOKEY<\/em> adds an INTEGER attribute named _AUTOKEY that is serially numbered. In the absence of DUP_REMOVE, DUP_COUNT, or AUTOKEY, the default is AUTOKEY.<\/p>\n

Examples:<\/p>\n

VAR myvar EXTERNAL CSV “\/home\/dave\/test.csv”;<\/p>\n

VAR myvar EXTERNAL CSV “\/home\/dave\/test.csv,HOHEADING” DUP_REMOVE;<\/p>\n

VAR myvar EXTERNAL XLS “\/home\/dave\/test.xls” DUP_REMOVE;<\/p>\n

VAR myvar EXTERNAL XLS “\/home\/dave\/test.xlsx” DUP_COUNT;<\/p>\n

VAR myvar EXTERNAL XLS “\/home\/dave\/test.xlsx,1” DUP_COUNT;<\/p>\n

VAR myvar EXTERNAL XLS “\/home\/dave\/test.xls,2,NOHEADING” DUP_REMOVE;<\/p>\n

VAR myvar EXTERNAL JDBC “jdbc:postgresql:\/\/localhost\/database,sqluser,sqluserpw,MyTable” AUTOKEY;<\/p>\n

VAR myvar EXTERNAL ACCDB “c:\\\\users\\\\me\\\\mydb.accdb,mytable”;<\/p>\n

VAR myvar EXTERNAL ACCDB “c:\\\\users\\\\me\\\\mydb.accdb,mytable” DUP_REMOVE;<\/p><\/blockquote>\n

NOTE: In the XLS connection string, the number after the optional ‘,’ indicates the zero-based tab number.<\/p>\n

NOTE: In the XLS & CSV connection string, NOHEADING indicates that the spreadsheet does not have a heading row.<\/p>\n

The\u00a0Rel<\/em> command-line interface can help you specify the appropriate file name. Use the “Get file path” toolbar item to find and insert the file name. The button is shown, in a highlight box with a red circle, here:<\/p>\n

\"\"<\/p>\n

Built-in JDBC support is provided for MySQL, MariaDB, PostgreSQL, Oracle Database and Microsoft SQL Server. E.g.:<\/p>\n

VAR myvar EXTERNAL JDBC “jdbc:mysql:\/\/localhost\/database,sqluser,sqluserpw,MyTable”;<\/p>\n

VAR myvar EXTERNAL JDBC “jdbc:mariadb:\/\/localhost\/database,sqluser,sqluserpw,MyTable”;<\/p>\n

VAR myvar EXTERNAL JDBC “jdbc:postgresql:\/\/localhost\/database,sqluser,sqluserpw,MyTable”;<\/p>\n

VAR myvar EXTERNAL JDBC “jdbc:oracle:thin:@localhost:1521:database,sqluser,sqluserpw,MyTable”;<\/p>\n

VAR myvar EXTERNAL JDBC “jdbc:sqlserver:\/\/localhost:1433;databaseName=database,sqluser,sqluserpw,MyTable”;<\/p><\/blockquote>\n

Writing to CSV and Spreadsheet Files<\/h2>\n

The\u00a0Rel<\/em> user interface supports exporting relations from variables (relvars), views, and queries to CSV (comma separated value) and XLS or XLSX spreadsheet files.<\/p>\n

To export from variables and views, right-click on the variable or view name to bring up a menu, as shown here:<\/p>\n

\"\"<\/p>\n

Select the Export menu item. The following dialog box will appear:<\/p>\n

\"\"<\/p>\n

Once you’ve chosen the appropriate file format, press the Export button to choose a filename and export the file.<\/p>\n

You can also export CSV and XLS\/XLSX files from the Rev Visual Query Language. Select the “Rev” mode (note that the Rev extensions must be installed) and load or define a query like the following and click on the ‘?’ of any query node, as shown with a red circle here:<\/p>\n

\"\"<\/p>\n

That will pop up a dialog box that shows you the\u00a0Tutorial D<\/strong> code for the query, along with four option buttons, as shown here:<\/p>\n

\"\"<\/p>\n

You can then click the “Export query results to file” button…<\/p>\n

\"\"<\/p>\n

…to choose a file format and export the results of evaluating the query.<\/p>\n","protected":false},"excerpt":{"rendered":"

As of\u00a0Rel version 3.003, it’s easy to read external data sources like CSV files, XLS\/XLSX spreadsheets, tables in Microsoft Access databases, and tables in external databases accessible via JDBC. You can also write to CSV files and XLS\/XLSX spreadsheet files. Reading External Data Sources Data sources are read by defining\u00a0them as\u00a0external relation-valued variables. External relation-valued Read more about Accessing External Data Sources<\/span>[…]<\/a><\/p>\n","protected":false},"author":1,"featured_media":149,"parent":17,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":"","_links_to":"","_links_to_target":""},"class_list":["post-147","page","type-page","status-publish","has-post-thumbnail","hentry"],"_links":{"self":[{"href":"https:\/\/reldb.org\/c\/index.php\/wp-json\/wp\/v2\/pages\/147","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/reldb.org\/c\/index.php\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/reldb.org\/c\/index.php\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/reldb.org\/c\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/reldb.org\/c\/index.php\/wp-json\/wp\/v2\/comments?post=147"}],"version-history":[{"count":7,"href":"https:\/\/reldb.org\/c\/index.php\/wp-json\/wp\/v2\/pages\/147\/revisions"}],"predecessor-version":[{"id":238,"href":"https:\/\/reldb.org\/c\/index.php\/wp-json\/wp\/v2\/pages\/147\/revisions\/238"}],"up":[{"embeddable":true,"href":"https:\/\/reldb.org\/c\/index.php\/wp-json\/wp\/v2\/pages\/17"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/reldb.org\/c\/index.php\/wp-json\/wp\/v2\/media\/149"}],"wp:attachment":[{"href":"https:\/\/reldb.org\/c\/index.php\/wp-json\/wp\/v2\/media?parent=147"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}