comparison docs/scripts/txt/DBTablesToTextFiles.txt @ 0:4816e4a8ae95 draft default tip

Uploaded
author deepakjadmin
date Wed, 20 Jan 2016 09:23:18 -0500
parents
children
comparison
equal deleted inserted replaced
-1:000000000000 0:4816e4a8ae95
1 NAME
2 DBToTextFiles.pl - Export data from database TableName(s) into CSV/TSV
3 text files
4
5 SYNOPSIS
6 DBTablesToTextFiles.pl TableName(s)...
7
8 DBTablesToTextFiles.pl [-d, --dbdriver mysql | Oracle | Postgres or Pg]
9 [--dbhost hostname] [--dbname databasename] [--dbpassword password]
10 [--dbusername username] [--exportdatalabels yes | no] [--exportlobs yes
11 | no] [-h, --help] [-m, --mode exportdata | describetable] [-o,
12 --overwrite] [--outdelim comma | tab | semicolon] [-q, --quote yes | no]
13 [-r, --root rootname] [--replacenullstr string] [-w --workingdir
14 dirname] TableName(s)...
15
16 DESCRIPTION
17 Export data from MySQL, Oracle or PostgreSQL database tables into
18 CSV/TSV text files. Or perform describe on all tables and store its
19 output into CSV/TSV text files. A text file is generated for each table.
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 -o, --overwrite
65 Overwrite existing files.
66
67 --outdelim *comma | tab | semicolon*
68 Output text file delimiter. Possible values: *comma, tab, or
69 semicolon* Default value: *comma*.
70
71 -q, --quote *yes | no*
72 Put quotes around column values in output text file. Possible
73 values: *yes or no*. Default value: *yes*.
74
75 -r, --root *rootname*
76 New file name is generated using the root:<Root>.<Ext>. Default new
77 file file names: <Mode><TableName>.<Ext>. The csv and tsv <Ext>
78 values are used for comma/semicolon, and tab delimited text files
79 respectively.This option is ignored for multiple input table names.
80
81 --replacenullstr *string*
82 Replace NULL or undefined row values with specified value. Default:
83 *none*
84
85 For importing output text files into MySQL database using "load data
86 local infile '<tablename>.tsv' into table <tablename>" command, use
87 *--raplacenullstr "NULL"* in conjunction with *--exportdatalabels
88 no*, *--quote no*, and *--outdelim tab* options: it'll generate
89 files for direct import into MySQL assuming tables already exists.
90
91 -w --workingdir *dirname*
92 Location of working directory. Default: current directory.
93
94 EXAMPLES
95 To export all data in user and user_info tables from a MySQL server
96 running on a local machine using username/password from DBI_USER and
97 DBI_PASS environmental variables, type:
98
99 % DBTablesToTextFiles.pl -o user user_info
100
101 To describe user and user_info tables in a MySQL server running on a
102 remote machine using explicit username/password and capturing the output
103 into a DescribeTables.csv file, type:
104
105 % DBTablesToTextFiles.pl --dbdriver mysql --dbuser <name> --dbpassword
106 <pasword> --dbname mysql --dbhost <mysqlhostname.org>
107 -r DescribeTable -m describetable -o user user_info
108
109 To describe table all_tables in Oracle running on a remote machine using
110 explicit username/password and capturing the output into a
111 DescribeAllTable.tsv file, type:
112
113 % DBTablesToTextFiles.pl --dbdriver Oracle --dbuser <name> --dbpassword
114 <pasword> --dbhost <oraclehostname.com> -r DescribeAllTable
115 -m describetable --outdelim tab --quote no -o all_tables
116
117 To export all data in user and user_info tables from MySQL server
118 running on a local machine using explicit username/password and
119 capturing the data in ExportTables.tsv file with empty values
120 substituted with NULL and no column labels, type:
121
122 % DBTablesToTextFiles.pl --dbdriver Oracle --dbuser <name> --dbpassword
123 <pasword> -r ExportTables --outdelim tab --quote no --replacenullstr
124 "\N" -m exportdata --exportlobs no --exportdatalabels no -o
125 user user_info
126
127 AUTHOR
128 Manish Sud <msud@san.rr.com>
129
130 SEE ALSO
131 DBSchemaTablesToTextFiles.pl, DBSQLToTextFiles.pl
132
133 COPYRIGHT
134 Copyright (C) 2015 Manish Sud. All rights reserved.
135
136 This file is part of MayaChemTools.
137
138 MayaChemTools is free software; you can redistribute it and/or modify it
139 under the terms of the GNU Lesser General Public License as published by
140 the Free Software Foundation; either version 3 of the License, or (at
141 your option) any later version.
142