The sql_insert command generates SQL INSERT statements from CSV data. Once generated, you can use your favourite SQL tool (or isql) to run the statements against your database. The sql_insert command handles quoting of the apostrophe in names like O'Brien automatically.

See also: sql_delete, sql_update

Flag

Req'd?

Description

-t table

Yes

Specifies the name of the SQL table to use in the INSERT statement.

-f fields

Yes

Specifies a list of field index/field name pairs to use to generate the SQL statement. The list is comma-separated, with each pair being colon-separated. For example:

-f 1:name,2:rank,5:serialno

specifies that field 1 will be called 'name', field 2 will be called 'rank' and field 5 will be called 'serialno'. Fields 3 and 4, which are not mentioned in the list, will be excluded from the generated INSERT statements.

-s separator

No

Specifies the separator that will be appended to the end of each statement. By default this is a new line followed by a semicolon. If your database requires COMMITs after each insert, you could use something like this:

-s '\n;\nCOMMIT\n\;\n'

-nq fields

No

By default, CSVfix wraps all SQL values in single quotes. This works well in most circumstances as SQL can implicitly convert the quoted strings to the actual data types. However, some types (particularly dates and times) are not (depending on your SQL implementation) convertible and so must not be quoted. The -nq flag specifies a list of fields in the CSV input which will not be quoted in the SQL output. Note that the special NULL value is not normally quoted.

-qn

No

Specifies that the special value NULL should be quoted. By default CSVfix does not quote the NULL string (in whatever case).

-en

No

Convert empty CSV fields to NULL



The following example generates INSERT statements from the names.csv file:

csvfix sql_insert -t people -f 1:fname,2:sname data/names.csv

which produces:

INSERT INTO people ( fname, sname ) VALUES( 'Charles', 'Dickens')
;
INSERT INTO people ( fname, sname ) VALUES( 'Jane', 'Austen')
;
INSERT INTO people ( fname, sname ) VALUES( 'Herman', 'Melville')
;
INSERT INTO people ( fname, sname ) VALUES( 'Flann', 'O''Brien')
;
INSERT INTO people ( fname, sname ) VALUES( 'George', 'Elliot')
;
INSERT INTO people ( fname, sname ) VALUES( 'Virginia', 'Woolf')
;
INSERT INTO people ( fname, sname ) VALUES( 'Oscar', 'Wilde')
;

Created with the Personal Edition of HelpNDoc: Full-featured Documentation generator