Open LuisBL opened 8 months ago
Check if csvq:/out.csv
is correct. What happens if you connect using this DSN, can you create a table and insert records into it?
$ rm -f out.csv; touch out.csv
$ usql csvq:/out.csv
error: csvq: driver: bad connection
$ usql csvq://out.csv
error: csvq: driver: bad connection
$ usql csvq:out.csv
error: csvq: driver: bad connection
$
No way to to connect to this driver,
Go csvq is there:
$ csvq --version
csvq version 1.18.1
$
So no way to execute
-- test
DROP TABLE IF EXISTS out;
CREATE TABLE out(
CompanyName VARCHAR(255) NOT NULL,
Region VARCHAR(255),
Country VARCHAR(255) NOT NULL
);
When I was testing it, I found that you have to open a directory, and it'll have separate tables for every csv file in that directory, so try csvq://.
to open the current directory.
Note that this is not an issue with usql
, but just how csvq
works.
As soon as I succeed to have a way to create/wirte from usql a CSV file I will provide a MergeR to improve the usql documentation.
csql is a psql like with an exception for \copy
.
\copy
is the default way to create CSV files on psql, in psql with no need of csvq, it is possible to write csv files with full controle on csv-separator:
-- save my_table to output.csv with psql
COPY my_table TO '/path/to/output.csv' WITH CSV DELIMITER ';';
I'm looking a way to do this with usql
.
I would have preferred to have a native usql
way to do it without need to use csvq
Go driver, but if it is the only way to have write CSV with control on the delimiter, below my effort to make it work.
I understand from your note that I have to create a TABLE with the csvq driver.
$ rm out
$ usql csvq://.
Connected with driver csvq (CSVQ v1.18.1)
Type "help" for help.
cs:.=> CREATE TABLE out(aa, bb, cc);
CREATE TABLE
cs:.=> INSERT INTO out (aa, bb, cc) VALUES ('Acme Corp', 'West', 'United States');
INSERT 1
cs:.=> INSERT INTO out (aa, bb, cc) VALUES ('Bcme Corp', 'Rest', 'Znited States');
INSERT 1
cs:.=> \! cat out
aa,bb,cc
Acme Corp,West,United States
Bcme Corp,Rest,Znited States
cs:.=>
$ usql csvq://.
Connected with driver csvq (CSVQ v1.18.1)
Type "help" for help.
cs:.=> select * from out;
aa | bb | cc
-----------+------+---------------
Acme Corp | West | United States
Bcme Corp | Rest | Znited States
(2 rows)
cs:.=>
\copy
command to write in a CSV file ==> ko$ usql northwind.db
Connected with driver sqlite3 (SQLite3 3.42.0)
Type "help" for help.
sq:northwind.db=> \copy sqlite3:northwind.db csvq:/. 'SELECT CompanyName, Region, Country FROM Customers LIMIT 3' 'out(a, b, c)'
error: failed to exec insert: sql: converting argument $1 type: unsupported type: *sql.NullString
sq:northwind.db=>
even with type conversion ==> ko
sq:northwind.db=> \copy sqlite3:northwind.db csvq:/. "SELECT COALESCE(CompanyName, '') AS CompanyName, COALESCE(Region, '') AS Region, COALESCE(Country, '') AS Country FROM Customers LIMIT 3" 'out(aa, bb, cc)'
error: failed to exec insert: sql: converting argument $1 type: unsupported type: **interface {}
sq:northwind.db=>
@LuisBL DuckDB has been added to usql
, and it also supports CSV. You might want to try it, as it is likely a more robust database implementation than csvq
is.
I think PR #461 fixes this.
Playing with Northwind db:
I got csvq driver:
I try to create a 3 columns csv file with the content of Customers table:
Documentation to import (I try to export) https://github.com/xo/usql#importing-data-from-csv doesn't help ;(