#438 Run query output to a file

[Mangesh]

I would like to run a query in squirrel and directly send the results to a file through the query.

For example MySQL allows me the below syntax to send the query result to a file on the local drive.

SELECT ... INTO OUTFILE 'file_name'

SELECT * from table INTO OUTFILE 'C:\file.csv'

The reason I would like such a feature is we run a lot of pre-defined queries everyday and save it in a csv file for further processing, and there's currently a manual process of saving the output (or the query result through SQL2File) to a csv file, which we would like to eliminate if we have the feature described above.

[Anonymous]

Well, have you tried it? Works for me (using Squirrel 3.3.0, MySQL 5.5.24, ArchLinux x86). An example from MySQL documentation

SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;

worked just great

[sfst]

As I am NOT on MySQL it would be an interesting generic feature, but with a very low priority (I am not going to upvote this yet, because I think other issues are more urgent).

Implementation would be difficult, because this functionality must not interfere with the SQL syntax - probably via some plugin and the extension being in SQL comments and enough parameters to offer flexibility e.g.

--SQUIRREL_META_COMMAND=EXPORT_NEXT_SQL2FILE('D:\DAILY_LOGS\2016-08-30 index_snap_shot.csv','CSV','\t','\r\n','x-windows-874')

But then some might want dynamic filenames, maybe via a query, e.g.

--SQUIRREL_META_COMMAND=EXPORT_NEXT_SQL2FILE(SELECT FILENAME FROM LOG_SCHEDULE WHERE TRUNC(SYSDATE)=START_DATE,'CSV','\t','\r\n','x-windows-874')

(This either needs some better thought out quoting, or the filename should be the last parameter to prevent that a comma in the SQl command messes up the meta-command parsing.)

I guess for XLS or XLSX exports a default formatting parameter would be needed as well.

For future extensions maybe the whole syntax should not be with a fixed parameter list, but like command line options, which only the FILENAME being mandatory and all others being optional (so there need to be defaults):

--SQUIRREL_META_COMMAND=EXPORT_NEXT_SQL2FILE(FILENAME=(SELECT FILENAME FROM LOG_SCHEDULE WHERE TRUNC(SYSDATE)=START_DATE) FILE_FORMAT='CSV' COLUMN_SEPARATOR='\t' LINE_SEPARATOR='\r\n' CHARSET='x-windows-874' OVERWRITE=APPEND EXIT_SQUIRREL=Y SAVE_EDITOR=N FEED_THE_CAT=Y)

Appending to XLS/XLSX/XML probably should be refused.

[Rou]

No need to overengineer this feature. I imagine a right-click menu item "Execute Selected Query to a CSV file" or something like that.

[sfst]

No need to overengineer this feature.

Ok, feeding the cat might be a bit over the top :-)

I imagine a right-click menu item "Execute Selected Query to a CSV file" or something like that.

That already exists in SQuirrel SQL under "Store result of SQL in file", but the OP wanted a feature that would NOT need manual intervention to save the data.

[Gerd Wagner]

Is committed to our GIT repository and will be available in future snapshots and versions.

The according excerpt from change.log:
Run query output to a file by defining the file as part of the SQL statement
Example:
@file '/tmp/customers.xlsx' SELECT * FROM customers
will write the result of 'SELECT * FROM customers' to '/tmp/customers.xlsx'
The settings for the output will be taken from the 'Store result of SQL to file' dialog.

[sfst]

Nice, but it silently overwrites existing files.

It also does not execute the command after the export.

This block:
SELECT '1' FROM tmp_id;
SELECT '2' FROM -tmp_id;
SELECT '3' FROM tmp_id;

will stop at the offending line with an error message in a tab.

But this:
@file '/tmp/test.csv' SELECT * FROM tmp_id;
@file '/tmp/test2.csv' SELECT * FROM -tmp_id;
@file '/tmp/test3.csv' SELECT * FROM tmp_id;

will run through the whole block, only with an error message in the message window at the bottom.

(Tested with Ubuntu 16.04 LTS and SQLLite)

Anyway, this new feature might be quite handy.

[Gerd Wagner]

The 'Run query output to a file'-feature does now respect the 'Abort on error ...'-property.
See menu File --> New Session Properties --> tab SQL

This was just committed to our GIT repository and will be available in future snapshots and versions.

[sfst]

FYI: With the Oracle plugin loaded this fails:

@file 'd:\test.csv' SELECT * FROM tmp_id;

It logged:

Logged by net.sourceforge.squirrel_sql.fw.sql.QueryTokenizer at 14/05/2020, 17:45:
Unexpected exception while reading lines from file (file 'd:\test.csv' SELECT * FROM tmp_id)

The stacktrace:
java.io.FileNotFoundException: file 'd:\test.csv' SELECT * FROM tmp_id (The filename, directory name, or volume label syntax is incorrect)

Using the "Unload" button for the Oracle plugin did not help; I had to uncheck the "Load at Startup", restart SQuirreL and then it worked again (even exporting from an Oracle database).

Existing files are still silently overwritten, though it logged " executeSQL: An ISQLExecutionListener veto'd execution of the following SQL: @file 'd:\test.csv' SELECT * FROM tmp_id;"

Tested with snapshot 2020-04-07 on Windows 8.1 and AdoptOpenJDK 11.0.5+10

Edit: The quoted logs had forward slash and backslash mixed up from different tests.
The "veto'd execution" seems always to be logged.

[Gerd Wagner]

The error described in the former comment is fixed in our GIT repository and will be available in future snapshots and versions.

Excerpt from change log:
Error in feature #438 (output query to file by @file) when Oracle Plugin was installed.
The error occurred because it ran into conflict with feature
(use @ to specify script file to be executed without loading to SQL Editor),
of the Oracle Plugin introduced in SQuirreL version 2.5.

[sfst]

The silent overwriting of existing files is a feature, right? Maybe the change log should mention that?

[Gerd Wagner]

Yes, it's a feature and is now mentioned in the change log, see about line 957.

[sfst]

Thank you. Please close this as fixed (it's getting long, I have stuffed this enough :-) and I'll open extra feature requests about remaining issues.

[Gerd Wagner]

Closed as requested.