|
0
|
1 NAME
|
|
|
2 DBSchemaTablesToTextFiles.pl - Export table data from database
|
|
|
3 SchemaName(s) into CSV/TSV text files
|
|
|
4
|
|
|
5 SYNOPSIS
|
|
|
6 DBSchemaTablesToTextFiles.pl SchemaName(s)...
|
|
|
7
|
|
|
8 DBSchemaTablesToTextFiles.pl [-d, --dbdriver mysql | Oracle| Postgres or
|
|
|
9 Pg] [--dbhost hostname] [--dbname databasename] [--dbpassword password]
|
|
|
10 [--dbusername username] [--exportdatalabels yes | no] [--exportlobs yes
|
|
|
11 | no] [-h, --help] [-m, --mode exportdata | describetable] [-n,
|
|
|
12 --numoutfilesmode single | multiple] [-o, --overwrite] [--outdelim comma
|
|
|
13 | tab | semicolon] [-q, --quote yes | no] [-r, --root rootname]
|
|
|
14 [--replacenullstr string] [-w --workingdir dirname] SchemaName(s)...
|
|
|
15
|
|
|
16 DESCRIPTION
|
|
|
17 Export table data from database SchemaName(s) into CSV/TSV text files.
|
|
|
18 Use -n --numoutfiles option to control the number of text files
|
|
|
19 generated for a database schema.
|
|
|
20
|
|
|
21 OPTIONS
|
|
|
22 -d, --dbdriver *mysql | Oracle | Postgres or Pg*
|
|
|
23 Database driver name. Possible values: *mysql, Oracle, Postgres or
|
|
|
24 Pg*. Default: *MySQL* or value of environment variable DBI_DRIVER.
|
|
|
25 This script has only been tested with MySQL, Oracle and PostgreSQL
|
|
|
26 drivers.
|
|
|
27
|
|
|
28 --dbhost *hostname*
|
|
|
29 Database host name. Default: *127.0.0.1* for both MySQL and Oracle.
|
|
|
30 For remote databases, specify complete remote host domain:
|
|
|
31 *dbhostname.org* or something like it.
|
|
|
32
|
|
|
33 --dbname *databasename*
|
|
|
34 Database name. Default: mysql for MySQL, postgres for PostgreSQL and
|
|
|
35 none for Oracle. For connecting to local/remote Oracle databases,
|
|
|
36 this value can be left undefined assuming --dbhost is correctly
|
|
|
37 specified.
|
|
|
38
|
|
|
39 --dbpassword *password*
|
|
|
40 Database user password. Default: *none* and value of environment
|
|
|
41 variable DBI_PASS is used for connecting to database.
|
|
|
42
|
|
|
43 --dbusername *username*
|
|
|
44 Database user name. Default: *none* and value of environment
|
|
|
45 variable DBI_USER is used for connecting to database.
|
|
|
46
|
|
|
47 --exportdatalabels *yes | no*
|
|
|
48 This option is mode specific and controls exporting of column data
|
|
|
49 labels during exportdata mode. Possible values: *yes or no*.
|
|
|
50 Default: *yes*.
|
|
|
51
|
|
|
52 --exportlobs *yes | no*
|
|
|
53 This option is mode specific and controls exporting of CLOB/BLOB or
|
|
|
54 BYTEA data columns during exportdata mode. Possible values: *yes or
|
|
|
55 no*. Default: *no*.
|
|
|
56
|
|
|
57 -h, --help
|
|
|
58 Print this help message.
|
|
|
59
|
|
|
60 -m, --mode *exportdata | describetable*
|
|
|
61 Data selection criterion from database. Possible values: *exportdata
|
|
|
62 or describetable*. Default value: *exportdata*.
|
|
|
63
|
|
|
64 -n, --numoutfilesmode *single | multiple*
|
|
|
65 Number of CSV/TSV output files to generate: combine output into one
|
|
|
66 file or generate a different file for each table in a schema.
|
|
|
67 Possible values: *single or multiple*. Default: *single*.
|
|
|
68
|
|
|
69 In a single output file, data for different tables is separated by a
|
|
|
70 blank line.
|
|
|
71
|
|
|
72 Single outfile option in *exportdata* mode is quite useful for
|
|
|
73 exporting data from all tables in specifed schemas to one file which
|
|
|
74 can be used for migrating data to another database or simply provide
|
|
|
75 a backup of data; during *describetable* mode, it provides a means
|
|
|
76 to collect information about columns of all schema tables which can
|
|
|
77 help in creation of these tables on a different database server.
|
|
|
78
|
|
|
79 -o, --overwrite
|
|
|
80 Overwrite existing files.
|
|
|
81
|
|
|
82 --outdelim *comma | tab | semicolon*
|
|
|
83 Output text file delimiter. Possible values: *comma, tab, or
|
|
|
84 semicolon*. Default value: *comma*
|
|
|
85
|
|
|
86 -q, --quote *yes | no*
|
|
|
87 Put quotes around column values in output text file. Possible
|
|
|
88 values: *yes or no*. Default value: *yes*.
|
|
|
89
|
|
|
90 -r, --root *rootname*
|
|
|
91 New file name is generated using the root:<Root>.<Ext> and
|
|
|
92 <Root><TableName>.<Ext> for *single* and *multiple* -n --numoutfiles
|
|
|
93 option values. Default file name for *single* -n --numoutfiles
|
|
|
94 option value: <Mode>SchemaTables.<Ext>. Default file names for
|
|
|
95 *multiple* -n --numoutfiles value:
|
|
|
96 <Mode><SchemaName><TableName>.<Ext>. Based on -m --mode option,
|
|
|
97 *Export* or *Describe* <Mode> value is used. The csv and tsv <Ext>
|
|
|
98 values are used for comma/semicolon, and tab delimited text files
|
|
|
99 respectively. This option is ignored for multiple input schema
|
|
|
100 names.
|
|
|
101
|
|
|
102 --replacenullstr *string*
|
|
|
103 Replace NULL or undefined row values with specified value. Default:
|
|
|
104 *none*.
|
|
|
105
|
|
|
106 For importing output text files into MySQL database using "load data
|
|
|
107 local infile '<tablename>.tsv' into table <tablename>" command, use
|
|
|
108 *--raplacenullstr "NULL"* in conjunction with *--exportdatalabels
|
|
|
109 no*, *--quote no*, and *--outdelim tab* options: it'll generate
|
|
|
110 files for direct import into MySQL assuming tables already exists.
|
|
|
111
|
|
|
112 -w --workingdir *dirname*
|
|
|
113 Location of working directory. Default: current directory.
|
|
|
114
|
|
|
115 EXAMPLES
|
|
|
116 To export data in all tables from mysql schema on a MySQL server running
|
|
|
117 on a local machine using username/password from DBI_USER and DBI_PASS
|
|
|
118 environmental variables, type:
|
|
|
119
|
|
|
120 % DBSchemaTablesToTextFiles.pl mysql
|
|
|
121
|
|
|
122 To describe all tables in mysql and test schemas on a MySQL server
|
|
|
123 running on a remote machine using explicit username/password and
|
|
|
124 capturing the ouput into a DescribeTables.csv file, type:
|
|
|
125
|
|
|
126 % DBSchemaTablesToTextFiles.pl --dbdriver mysql --dbuser <name>
|
|
|
127 --dbpassword <pasword> --dbname mysql --dbhost
|
|
|
128 <mysqlhostname.org> -r DescribeTable -m describetable
|
|
|
129 -o mysql test
|
|
|
130
|
|
|
131 To describe all tables in SCOTT schema in Oracle running on a remote
|
|
|
132 machine using explicit username/password and capturing the ouput into a
|
|
|
133 DescribeAllTable.tsv file, type:
|
|
|
134
|
|
|
135 % DBSchemaTablesToTextFiles.pl --dbdriver Oracle --dbuser <name>
|
|
|
136 --dbpassword <pasword> --dbhost <oraclehostname.com>
|
|
|
137 -r DescribeAllTable -m describetable --outdelim tab --quote no
|
|
|
138 -o SCOTT
|
|
|
139
|
|
|
140 To export data in all tables in mysql and test schemas on a MySQL server
|
|
|
141 running at a local machine using explicit username/password and
|
|
|
142 capturing the data in TSV file for each table with empty values
|
|
|
143 substitued with NULL and clob/blob data, type:
|
|
|
144
|
|
|
145 % DBSchemaTablesToTextFiles.pl --dbdriver Oracle --dbuser <name>
|
|
|
146 --dbpassword <pasword> -r ExportTables --outdelim tab --quote no
|
|
|
147 --replacenullstr "NULL" -m exportdata --exportlobs no --numoutfiles
|
|
|
148 multiple -o user user_info
|
|
|
149
|
|
|
150 AUTHOR
|
|
|
151 Manish Sud <msud@san.rr.com>
|
|
|
152
|
|
|
153 SEE ALSO
|
|
|
154 DBSQLToTextFiles.pl, DBTablesToTextFiles.pl
|
|
|
155
|
|
|
156 COPYRIGHT
|
|
|
157 Copyright (C) 2015 Manish Sud. All rights reserved.
|
|
|
158
|
|
|
159 This file is part of MayaChemTools.
|
|
|
160
|
|
|
161 MayaChemTools is free software; you can redistribute it and/or modify it
|
|
|
162 under the terms of the GNU Lesser General Public License as published by
|
|
|
163 the Free Software Foundation; either version 3 of the License, or (at
|
|
|
164 your option) any later version.
|
|
|
165
|
