Home Documentation Download About

NAME

DBSchemaTablesToTextFiles.pl - Export table data from database SchemaName(s) into CSV/TSV text files

SYNOPSIS

DBSchemaTablesToTextFiles.pl SchemaName(s)...

DBSchemaTablesToTextFiles.pl [-d, --dbdriver mysql | Oracle| Postgres or Pg] [--dbhost hostname] [--dbname databasename] [--dbpassword password] [--dbusername username] [--exportdatalabels yes | no] [--exportlobs yes | no] [-h, --help] [-m, --mode exportdata | describetable] [-n, --numoutfilesmode single | multiple] [-o, --overwrite] [--outdelim comma | tab | semicolon] [-q, --quote yes | no] [-r, --root rootname] [--replacenullstr string] [-w --workingdir dirname] SchemaName(s)...

DESCRIPTION

Export table data from database SchemaName(s) into CSV/TSV text files. Use -n --numoutfiles option to control the number of text files generated for a database schema.

OPTIONS

-d, --dbdriver mysql | Oracle | Postgres or Pg

Database driver name. Possible values: mysql, Oracle, Postgres or Pg. Default: MySQL or value of environment variable DBI_DRIVER. This script has only been tested with MySQL, Oracle and PostgreSQL drivers.

--dbhost hostname

Database host name. Default: 127.0.0.1 for both MySQL and Oracle. For remote databases, specify complete remote host domain: dbhostname.org or something like it.

--dbname databasename

Database name. Default: mysql for MySQL, postgres for PostgreSQL and none for Oracle. For connecting to local/remote Oracle databases, this value can be left undefined assuming --dbhost is correctly specified.

--dbpassword password

Database user password. Default: none and value of environment variable DBI_PASS is used for connecting to database.

--dbusername username

Database user name. Default: none and value of environment variable DBI_USER is used for connecting to database.

--exportdatalabels yes | no

This option is mode specific and controls exporting of column data labels during exportdata mode. Possible values: yes or no. Default: yes.

--exportlobs yes | no

This option is mode specific and controls exporting of CLOB/BLOB or BYTEA data columns during exportdata mode. Possible values: yes or no. Default: no.

-h, --help

Print this help message.

-m, --mode exportdata | describetable

Data selection criterion from database. Possible values: exportdata or describetable. Default value: exportdata.

-n, --numoutfilesmode single | multiple

Number of CSV/TSV output files to generate: combine output into one file or generate a different file for each table in a schema. Possible values: single or multiple. Default: single.

In a single output file, data for different tables is separated by a blank line.

Single outfile option in exportdata mode is quite useful for exporting data from all tables in specifed schemas to one file which can be used for migrating data to another database or simply provide a backup of data; during describetable mode, it provides a means to collect information about columns of all schema tables which can help in creation of these tables on a different database server.

-o, --overwrite

Overwrite existing files.

--outdelim comma | tab | semicolon

Output text file delimiter. Possible values: comma, tab, or semicolon. Default value: comma

-q, --quote yes | no

Put quotes around column values in output text file. Possible values: yes or no. Default value: yes.

-r, --root rootname

New file name is generated using the root:<Root>.<Ext> and <Root><TableName>.<Ext> for single and multiple -n --numoutfiles option values. Default file name for single -n --numoutfiles option value: <Mode>SchemaTables.<Ext>. Default file names for multiple -n --numoutfiles value: <Mode><SchemaName><TableName>.<Ext>. Based on -m --mode option, Export or Describe <Mode> value is used. The csv and tsv <Ext> values are used for comma/semicolon, and tab delimited text files respectively. This option is ignored for multiple input schema names.

--replacenullstr string

Replace NULL or undefined row values with specified value. Default: none.

For importing output text files into MySQL database using "load data local infile '<tablename>.tsv' into table <tablename>" command, use --raplacenullstr "NULL" in conjunction with --exportdatalabels no, --quote no, and --outdelim tab options: it'll generate files for direct import into MySQL assuming tables already exists.

-w --workingdir dirname

Location of working directory. Default: current directory.

EXAMPLES

To export data in all tables from mysql schema on a MySQL server running on a local machine using username/password from DBI_USER and DBI_PASS environmental variables, type:

To describe all tables in mysql and test schemas on a MySQL server running on a remote machine using explicit username/password and capturing the ouput into a DescribeTables.csv file, type:

To describe all tables in SCOTT schema in Oracle running on a remote machine using explicit username/password and capturing the ouput into a DescribeAllTable.tsv file, type:

To export data in all tables in mysql and test schemas on a MySQL server running at a local machine using explicit username/password and capturing the data in TSV file for each table with empty values substitued with NULL and clob/blob data, type:

AUTHOR

Manish Sud

SEE ALSO

DBSQLToTextFiles.pl, DBTablesToTextFiles.pl

COPYRIGHT

Copyright (C) 2024 Manish Sud. All rights reserved.

This file is part of MayaChemTools.

MayaChemTools is free software; you can redistribute it and/or modify it under the terms of the GNU Lesser General Public License as published by the Free Software Foundation; either version 3 of the License, or (at your option) any later version.