|
DBSchemaTablesToTextFiles.pl - Export table data from database SchemaName(s) into CSV/TSV text files
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)...
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.
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.
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.
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.
Database user password. Default: none and value of environment variable DBI_PASS is used for connecting to database.
Database user name. Default: none and value of environment variable DBI_USER is used for connecting to database.
This option is mode specific and controls exporting of column data labels during exportdata mode. Possible values: yes or no. Default: yes.
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.
Print this help message.
Data selection criterion from database. Possible values: exportdata or describetable. Default value: exportdata.
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.
Overwrite existing files.
Output text file delimiter. Possible values: comma, tab, or semicolon. Default value: comma
Put quotes around column values in output text file. Possible values: yes or no. Default value: yes.
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.
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.
Location of working directory. Default: current directory.
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:
DBSQLToTextFiles.pl, DBTablesToTextFiles.pl
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.
