csv2sql

NAME

SYNOPSIS

csv2sql < file.csv

type file.csv | csv2sql

csv2sql [ -n tablename ] separator_opts column_opts output_opts < file.csv

type file.csv | csv2sql [ -n tablename ] separator_opts column_opts output_opts

---

separator_opts ::=

[ -t field_separator ] [ -m multi_separator ]

column_opts ::=

[ -c null_columns ] [ -s string_columns ]

output_opts ::=

[ -o ( record | csv | ansi | mariadb | mssql | oracle | postgresql | sqlite ) ]

AVAILABILITY

DESCRIPTION

Furthermore csv2sql skips empty lines and handles multi-line fields (fields that continue the following line(s)) in CSV data.

OPTIONS

-h

usage message.

-V

print program version.

-n tablename

the name of the table to produce the SQL insert statement for.

The default tablename is 'dummy'.

-t field_separator

field separator of CSV input data.

The default input field_separator is a comma (,).

Whenever the first line in the CSV data is 'sep=C' (example: sep=;) is present that character is used as field separator.

Beside common characters, \t, [:tab:], [:comma:], and [:semicolon:] can be specified.

-m multi_separator

change the multiline-field separator of output data.

This comes into effect, if a field continues on next line.

The multiline-field separator of CSV input data is always a newline (\n) as the 'RFC 4180' standard defines for CSV files.

The default output multi_separator is a newline (\n).

Beside common characters, \t, [:tab:], [:comma:], [:semicolon:], \n, [:newline:], " ", [:space:] and [:none:] can be specified.

-c null_columns

case-sensitive comma- or semicolon-separated (example: Id,Name,City) list of column names to insert empty fields as NULL values into the database; default is to insert an empty string.

-s string_columns

case-sensitive comma- or semicolon-separated (example: Id,Name,City) list of column names to forcefully insert as string values into the database; default is to insert numbers automatically as non-string values.

-o output_format

format of the data returned by the csv2sql command.

The default output format is mssql.

Supported output_formats are:

csv

CSV, comma (,) separated values file with header row containing the column names.

record

Key : Value record (mostly for verification purposes).

ansi

ANSI SQL (ISO/IEC 9075) standard compatible statements.

mariadb

SQL statements compatible with the MariaDB/MySQL database to insert the data into the related table.

mssql

SQL statements compatible with the Microsoft™-SQL database to insert the data into the related table.

oracle

SQL statements compatible with the Oracle™ database to insert the data into the related table.

postgresql

SQL statements compatible with the PostgreSQL database to insert the data into the related table.

sqlite

SQL statements compatible with the SQLite database to insert the data into the related table.

file.csv

CSV data provided thru stdin.

EXIT STATUS

0

no error.

1

error.

4

usage displayed.

5

version message displayed.

6, -1073741510

command aborted (using Ctrl+C).

FILES

EXAMPLES

1) Create SQL statements from a CSV file

This example creates SQL INSERT statements for a Microsoft™-SQL database from a CSV file that has line breaks in a field and sets empty fields of the STATE column to NULL:

  type cities.csv

  ZIP,CITY,STATE,CNT,COUNTRY
  8222,Beringen,SH,CH,Switzerland
  8005,"Cape
  Town",,RSA,South Africa
  93117,Goleta,CA,USA,United States of America

  csv2sql -c STATE < cities.csv

  INSERT INTO [dummy] ([ZIP], [CITY], [STATE], [CNT], [COUNTRY]) VALUES (
      8222, 'Beringen', 'SH', 'CH', 'Switzerland'
  );
  INSERT INTO [dummy] ([ZIP], [CITY], [STATE], [CNT], [COUNTRY]) VALUES (
      8005, 'Cape
  Town', NULL, 'RSA', 'South Africa'
  );
  INSERT INTO [dummy] ([ZIP], [CITY], [STATE], [CNT], [COUNTRY]) VALUES (
      93117, 'Goleta', 'CA', 'USA', 'United States of America'
  );

2) Flatten a CSV file that has multiline fields

To "flatten" a CSV file that has multiline fields (line breaks in fields), the '-m multi_separator' option can be used as follows:

  type cities.csv

  ZIP,CITY,STATE,CNT,COUNTRY
  8222,Beringen,SH,CH,Switzerland
  8005,"Cape
  Town",,RSA,South Africa
  93117,Goleta,CA,USA,United States of America

  csv2sql -o csv -m " " < cities.csv

  ZIP,CITY,STATE,CNT,COUNTRY
  8222,"Beringen","SH","CH","Switzerland"
  8005,"Cape Town",,"RSA","South Africa"
  93117,"Goleta","CA","USA","United States of America"

SEE ALSO

NOTES

BUGS

The formats ansi, mariadb, oracle and postgresql are not tested against a database. It is believed to be correct - however, nothing goes beyond testing. Therefore, real test results are highly appreciated.

VERSION

AUTHOR

COPYRIGHT

This is free software; see https://www.gnu.org/licenses/gpl-2.0.html for copying conditions. There is ABSOLUTELY NO WARRANTY; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.