Simple command line tool for macOS which parses a tsv file and outputs MySQL CREATE and INSERT statements.
Unless the ignoreFirstLine option is passed, the first line of the file is assumed to be the header titles for all the columns.
The first line also indicates how many columns the table will possess.
tsvSQL <path/to/file> header
This will parse the file and list all possible types for each column.
By default, when generating the SQL statements, it will use the most encompassing type.
tsvSQL <path/to/file> sqlTable tableName=doggy
This will generate the CREATE statement.
CREATE TABLE doggy(id INT, breed VARCHAR(31));
Important: if any column name is a key word, the value of the keywordSuffix option will be appended to the name. The default is 'X'.
If the colType option is passed, you can set the type for each column. If the value in the tsv file cannot be converted to the type, the row-column value will be set to NULL.
tsvSQL <path/to/file> sqlCreate tableName=doggy
This will generate the SQL INSERT statement. This can be very long.
INSERT INTO doggy(id, breed) VALUES(1, 'Cockaopaniel'), (2, 'Doberman');
If the badlines option is passed, the tool will output lines whose count does not match the first line column count.
Format will be <line number>-<column count>:<line text>
tsvSQL <path/to/tsv/file> header | sqlTable | sqlCreate | sqlAll (null=<word>,...) (keywordSuffix=<string>) (ignoreFirstLine) (tableName=<name>) (colType=<name>:<type>) (badLines)
'null' is a list of words which are ignored by the tool when determining column type.
'header' outputs the possible types for each column. By default, when generating the CREATE statement, it will use the most encompassing type listed.
Least to most: boolean (TINYINT), INT, DECIMAL, DATE, STRING.
Decimal point count for SQL double type will be based on double with most decimal points.
INT will be one of TINYINT (UNSIGNED), SMALLINT (UNSIGNED), INT (UNSIGNED) or BIGINT depending on the largest integer value.
String will be on of VARCHAR(31), VARCHAR(127), VARCHAR(255) or TEXT depending on largest string length.
'keywordSuffix' is what is appended to first line fields if it matches an SQL keyword. Default is 'X'. Max length is 8 characters.
if 'ignoreFirstLine' is passed, the first line is not treated as the header and the column names will be col_N where N is an incrementing integer starting from zero.
If 'badLines' is passed, the tool will only output lines whose column count differs from the first line.
The output for each line will be <line number>-<column count>:<line text> Must be passed with 'sqlCreate' option.
'sqlTable' outputs possible CREATE TABLE command. Requires 'tableName' be passed.
Floating point values will use the DECIMAL type unless overriden by 'colType'.
'colType' sets the type for the column, not what the tool thinks it will be.
If a value is not valid for the type, the value will be set to NULL.
This option can be passed multiple times.
Only valid for sqlTable option.
Types allowed: text (TEXT), vc255 (VARCHAR(255)), vc127 (VARCHAR(127)), vc31 (VARCHAR(31)), decimal_NN_MM (DECIMAL(NN,MM)), i8 (TINYINT), ui8 (TINYINT UNSIGNED), i16 (SMALLINT), ui16 (SMALLINT UNSIGNED), i32 (INT), ui32 (INT UNSIGNED), i64 (BIGINT), ui64 (BIGINT UNSIGNED), bool (TINYINY), date (DATE)
'sqlCreate' outputs possible INSERT INTO command. Requires 'tableName' be passed.
'sqlAll' outputs both 'sqlTable' and 'sqlCreate'. badLines option is ignored,
Github link tsvSQL.
Binary is in Products directory.
Installer coming "soon".