|
DBToTextFiles.pl - Export data from database TableName(s) into CSV/TSV text files
DBTablesToTextFiles.pl TableName(s)...
DBTablesToTextFiles.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] [-o, --overwrite] [--outdelim comma | tab | semicolon] [-q, --quote yes | no] [-r, --root rootname] [--replacenullstr string] [-w --workingdir dirname] TableName(s)...
Export data from MySQL, Oracle or PostgreSQL database tables into CSV/TSV text files. Or perform describe on all tables and store its output into CSV/TSV text files. A text file is generated for each table.
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.
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>. Default new file file names: <Mode><TableName>.<Ext>. The csv and tsv <Ext> values are used for comma/semicolon, and tab delimited text files respectively.This option is ignored for multiple input table 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 all data in user and user_info tables from a MySQL server running on a local machine using username/password from DBI_USER and DBI_PASS environmental variables, type:
To describe user and user_info tables in a MySQL server running on a remote machine using explicit username/password and capturing the output into a DescribeTables.csv file, type:
To describe table all_tables in Oracle running on a remote machine using explicit username/password and capturing the output into a DescribeAllTable.tsv file, type:
To export all data in user and user_info tables from MySQL server running on a local machine using explicit username/password and capturing the data in ExportTables.tsv file with empty values substituted with NULL and no column labels, type:
DBSchemaTablesToTextFiles.pl, DBSQLToTextFiles.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.
