1CSVSQL(1) csvkit CSVSQL(1)
2
3
4
6 csvsql - csvsql Documentation
7
9 Generate SQL statements for a CSV file or execute those statements
10 directly on a database. In the latter case supports both creating
11 tables and inserting data:
12
13 usage: csvsql [-h] [-d DELIMITER] [-t] [-q QUOTECHAR] [-u {0,1,2,3}] [-b]
14 [-p ESCAPECHAR] [-z FIELD_SIZE_LIMIT] [-e ENCODING] [-L LOCALE]
15 [-S] [--blanks] [--date-format DATE_FORMAT]
16 [--datetime-format DATETIME_FORMAT] [-H] [-K SKIP_LINES] [-v]
17 [-l] [--zero] [-V]
18 [-i {firebird,mssql,mysql,oracle,postgresql,sqlite,sybase}]
19 [--db CONNECTION_STRING] [--query QUERY] [--insert]
20 [--prefix PREFIX] [--tables TABLE_NAMES] [--no-constraints]
21 [--unique-constraint UNIQUE_CONSTRAINT] [--no-create]
22 [--create-if-not-exists] [--overwrite] [--db-schema DB_SCHEMA]
23 [-y SNIFF_LIMIT] [-I]
24 [FILE [FILE ...]]
25
26 Generate SQL statements for one or more CSV files, or execute those statements
27 directly on a database, and execute one or more SQL queries.
28
29 positional arguments:
30 FILE The CSV file(s) to operate on. If omitted, will accept
31 input on STDIN.
32
33 optional arguments:
34 -h, --help show this help message and exit
35 -i {firebird,mssql,mysql,oracle,postgresql,sqlite,sybase}, --dialect {firebird,mssql,mysql,oracle,postgresql,sqlite,sybase}
36 Dialect of SQL to generate. Only valid when --db is
37 not specified.
38 --db CONNECTION_STRING
39 If present, a sqlalchemy connection string to use to
40 directly execute generated SQL on a database.
41 --query QUERY Execute one or more SQL queries delimited by ";" and
42 output the result of the last query as CSV. QUERY may
43 be a filename.
44 --insert In addition to creating the table, also insert the
45 data into the table. Only valid when --db is
46 specified.
47 --prefix PREFIX Add an expression following the INSERT keyword, like
48 OR IGNORE or OR REPLACE.
49 --tables TABLE_NAMES A comma-separated list of names of tables to be
50 created. By default, the tables will be named after
51 the filenames without extensions or "stdin".
52 --no-constraints Generate a schema without length limits or null
53 checks. Useful when sampling big tables.
54 --unique-constraint UNIQUE_CONSTRAINT
55 A column-separated list of names of columns to include
56 in a UNIQUE constraint.
57 --no-create Skip creating a table. Only valid when --insert is
58 specified.
59 --create-if-not-exists
60 Create table if it does not exist, otherwise keep
61 going. Only valid when --insert is specified.
62 --overwrite Drop the table before creating. Only valid when
63 --insert is specified.
64 --db-schema DB_SCHEMA
65 Optional name of database schema to create table(s)
66 in.
67 -y SNIFF_LIMIT, --snifflimit SNIFF_LIMIT
68 Limit CSV dialect sniffing to the specified number of
69 bytes. Specify "0" to disable sniffing entirely.
70 -I, --no-inference Disable type inference when parsing the input.
71
72 See also: ../common_arguments.
73
74 For information on connection strings and supported dialects refer to
75 the SQLAlchemy documentation.
76
77 If you prefer not to enter your password in the connection string,
78 store the password securely in a PostgreSQL Password File, a MySQL
79 Options File or similar files for other systems.
80
81 NOTE:
82 Using the --query option may cause rounding (in Python 2) or intro‐
83 duce [Python floating point issues](‐
84 https://docs.python.org/3.4/tutorial/floatingpoint.html) (in Python
85 3).
86
88 Generate a statement in the PostgreSQL dialect:
89
90 csvsql -i postgresql examples/realdata/FY09_EDU_Recipients_by_State.csv
91
92 Create a table and import data from the CSV directly into PostgreSQL:
93
94 createdb test
95 csvsql --db postgresql:///test --tables fy09 --insert examples/realdata/FY09_EDU_Recipients_by_State.csv
96
97 For large tables it may not be practical to process the entire table.
98 One solution to this is to analyze a sample of the table. In this case
99 it can be useful to turn off length limits and null checks with the
100 no-constraints option:
101
102 head -n 20 examples/realdata/FY09_EDU_Recipients_by_State.csv | csvsql --no-constraints --tables fy09
103
104 Create tables for an entire folder of CSVs and import data from those
105 files directly into PostgreSQL:
106
107 createdb test
108 csvsql --db postgresql:///test --insert examples/*_converted.csv
109
110 If those CSVs have identical headers, you can import them into the same
111 table by using csvstack first:
112
113 createdb test
114 csvstack examples/dummy?.csv | csvsql --db postgresql:///test --insert
115
116 Group rows by one column:
117
118 csvsql --query "select * from 'dummy3' group by a" examples/dummy3.csv
119
120 You can also use CSVSQL to “directly” query one or more CSV files.
121 Please note that this will create an in-memory SQL database, so it
122 won’t be very fast:
123
124 csvsql --query "select m.usda_id, avg(i.sepal_length) as mean_sepal_length from iris as i join irismeta as m on (i.species = m.species) group by m.species" examples/iris.csv examples/irismeta.csv
125
126 Concatenate two columns:
127
128 csvsql --query "select a||b from 'dummy3'" examples/dummy3.csv
129
131 Christopher Groskopf
132
134 2016, Christopher Groskopf
135
136
137
138
1391.0.3 Feb 02, 2019 CSVSQL(1)