Retrieves all of the results from a previously executed query and places them in a file. The connection used must have already been established using the macro function SQL.OPEN. Also, a query must already have been executed using SQL.EXEC.QUERY and results must be pending.
If this function is not available, you must install the Microsoft ODBC add-in (XLODBC.XLA).
Syntax
SQL.RETRIEVE.TO.FILE(connection_num, destination, col_names_logical, column_delimiter)
Connection_num is the unique connection ID for a data source. The data source specified must have query results pending. Pending results were generated by a previous call to SQL.EXEC.QUERY on the same connection.
If there are no pending results on the connection SQL.RETRIEVE.TO.FILE returns the #N/A error value. The file is not affected.
If connection_num is not valid, SQL.RETRIEVE.TO.FILE returns the #VALUE! error value.
Destination specifies the name and path of the file where the results should be placed. SQL.RETRIEVE.TO.FILE will open the specified file and fill it with the entire result set.
The format of the data in the file will be compatible with the Microsoft Excel ".CSV" format. The overall format will be that columns will be separated by the value in column_delimiter (see below) and the individual rows will be separated by a linefeed/carriage-return.
If the file specified by destination cannot be opened then the error value #N/A will be returned by SQL.RETRIEVE.TO.FILE.
If the file already exists its previous contents will be overwritten by SQL.RETRIEVE.TO.FILE.
Col_names_logical is a logical value that, if TRUE, allows the column names to be returned as the first row of data. If FALSE or omitted, the column names will not be returned.
Column_delimiter is the value that will be used to separate the elements in each row. If column_delimiter is omitted then a tab will be used. If another value is desired then it should be enclosed in quotation marks. Possible values for column_delimiter might be: "," or ";" or " ". The string "tab" can also be used to specify a tab separator (even though this is redundant, since a tab is the default).
Remarks
If SQL.RETRIEVE.TO.FILE is unable to retrieve the results on the specified connection then an error value will be returned. In such a case SQL.RETRIEVE.TO.FILE will place error information in memory for the SQL.ERROR function, if such information is available.
If SQL.RETRIEVE.TO.FILE is able to successfully return rows of results on the specified connection and place them in a file it will return the number of rows that were actually written to the file. If there were no results pending on the connection then SQL.RETRIEVE.TO.FILE will return the #N/A error value and the file will not be created or modified.
Before calling SQL.RETRIEVE.TO.FILE a connection must be established with a data source using SQL.OPEN.
A successful call to SQL.OPEN returns a unique connection ID number, which can be used in a call to SQL.EXEC.QUERY to send a SQL language query. Following this call to SQL.EXEC.QUERY, SQL.RETRIEVE.TO.FILE uses the same connection ID number to retrieve query results and place them in a file.
Example
SQL.RETRIEVE.TO.FILE(conn1,"C:\MSQUERY\RESULTS1.QRY",TRUE,",") retrieves the results of a previously executed query and places them in the file RESULTS1.QRY, with column names that are comma delimited.
Related Functions
SQL.OPEN Establishes a connection with a data source
SQL.EXEC.QUERY Sends a query to a data source
SQL.BIND Specifies storage for a result column
SQL.RETRIEVE Retrieves query results
SQL.GET.SCHEMA Gets information about a connected data source.
SQL.CLOSE Closes a data source connection
SQL.ERROR Returns detailed error information
Return to index