|
0
|
1 <html>
|
|
|
2 <head>
|
|
|
3 <title>MayaChemTools:Documentation:DBTablesToTextFiles.pl</title>
|
|
|
4 <meta http-equiv="content-type" content="text/html;charset=utf-8">
|
|
|
5 <link rel="stylesheet" type="text/css" href="../../css/MayaChemTools.css">
|
|
|
6 </head>
|
|
|
7 <body leftmargin="20" rightmargin="20" topmargin="10" bottommargin="10">
|
|
|
8 <br/>
|
|
|
9 <center>
|
|
|
10 <a href="http://www.mayachemtools.org" title="MayaChemTools Home"><img src="../../images/MayaChemToolsLogo.gif" border="0" alt="MayaChemTools"></a>
|
|
|
11 </center>
|
|
|
12 <br/>
|
|
|
13 <div class="DocNav">
|
|
|
14 <table width="100%" border=0 cellpadding=0 cellspacing=2>
|
|
|
15 <tr align="left" valign="top"><td width="33%" align="left"><a href="./DBSQLToTextFiles.html" title="DBSQLToTextFiles.html">Previous</a> <a href="./index.html" title="Table of Contents">TOC</a> <a href="./ElementalAnalysis.html" title="ElementalAnalysis.html">Next</a></td><td width="34%" align="middle"><strong>DBTablesToTextFiles.pl</strong></td><td width="33%" align="right"><a href="././code/DBTablesToTextFiles.html" title="View source code">Code</a> | <a href="./../pdf/DBTablesToTextFiles.pdf" title="PDF US Letter Size">PDF</a> | <a href="./../pdfgreen/DBTablesToTextFiles.pdf" title="PDF US Letter Size with narrow margins: www.changethemargins.com">PDFGreen</a> | <a href="./../pdfa4/DBTablesToTextFiles.pdf" title="PDF A4 Size">PDFA4</a> | <a href="./../pdfa4green/DBTablesToTextFiles.pdf" title="PDF A4 Size with narrow margins: www.changethemargins.com">PDFA4Green</a></td></tr>
|
|
|
16 </table>
|
|
|
17 </div>
|
|
|
18 <p>
|
|
|
19 </p>
|
|
|
20 <h2>NAME</h2>
|
|
|
21 <p>DBToTextFiles.pl - Export data from database TableName(s) into CSV/TSV text files</p>
|
|
|
22 <p>
|
|
|
23 </p>
|
|
|
24 <h2>SYNOPSIS</h2>
|
|
|
25 <p>DBTablesToTextFiles.pl TableName(s)...</p>
|
|
|
26 <p>DBTablesToTextFiles.pl [<strong>-d, --dbdriver</strong> mysql | Oracle | Postgres or Pg] [<strong>--dbhost </strong> hostname]
|
|
|
27 [<strong>--dbname</strong> databasename] [<strong>--dbpassword</strong> password] [<strong>--dbusername</strong> username]
|
|
|
28 [<strong>--exportdatalabels</strong> yes | no] [<strong>--exportlobs</strong> yes | no] [<strong>-h, --help</strong>]
|
|
|
29 [<strong>-m, --mode</strong> exportdata | describetable] [<strong>-o, --overwrite</strong>] [<strong>--outdelim</strong> comma | tab | semicolon]
|
|
|
30 [<strong>-q, --quote</strong> yes | no] [<strong>-r, --root</strong> rootname] [<strong>--replacenullstr string</strong>]
|
|
|
31 [<strong>-w --workingdir</strong> dirname] TableName(s)...</p>
|
|
|
32 <p>
|
|
|
33 </p>
|
|
|
34 <h2>DESCRIPTION</h2>
|
|
|
35 <p>Export data from MySQL, Oracle or PostgreSQL database tables into CSV/TSV text files. Or perform
|
|
|
36 describe on all tables and store its output into CSV/TSV text files. A text file is generated
|
|
|
37 for each table.</p>
|
|
|
38 <p>
|
|
|
39 </p>
|
|
|
40 <h2>OPTIONS</h2>
|
|
|
41 <dl>
|
|
|
42 <dt><strong><strong>-d, --dbdriver</strong> <em>mysql | Oracle | Postgres or Pg</em></strong></dt>
|
|
|
43 <dd>
|
|
|
44 <p>Database driver name. Possible values: <em>mysql, Oracle, Postgres or Pg</em>. Default: <em>MySQL</em> or value of
|
|
|
45 environment variable DBI_DRIVER. This script has only been tested with MySQL, Oracle
|
|
|
46 and PostgreSQL drivers.</p>
|
|
|
47 </dd>
|
|
|
48 <dt><strong><strong>--dbhost </strong> <em>hostname</em></strong></dt>
|
|
|
49 <dd>
|
|
|
50 <p>Database host name. Default: <em>127.0.0.1</em> for both MySQL and Oracle. For remote
|
|
|
51 databases, specify complete remote host domain: <em>dbhostname.org</em> or something
|
|
|
52 like it.</p>
|
|
|
53 </dd>
|
|
|
54 <dt><strong><strong>--dbname</strong> <em>databasename</em></strong></dt>
|
|
|
55 <dd>
|
|
|
56 <p>Database name. Default: mysql for MySQL, postgres for PostgreSQL and none for Oracle.
|
|
|
57 For connecting to local/remote Oracle databases, this value can be left undefined assuming
|
|
|
58 <strong>--dbhost</strong> is correctly specified.</p>
|
|
|
59 </dd>
|
|
|
60 <dt><strong><strong>--dbpassword</strong> <em>password</em></strong></dt>
|
|
|
61 <dd>
|
|
|
62 <p>Database user password. Default: <em>none</em> and value of environment variable DBI_PASS
|
|
|
63 is used for connecting to database.</p>
|
|
|
64 </dd>
|
|
|
65 <dt><strong><strong>--dbusername</strong> <em>username</em></strong></dt>
|
|
|
66 <dd>
|
|
|
67 <p>Database user name. Default: <em>none</em> and value of environment variable DBI_USER is
|
|
|
68 used for connecting to database.</p>
|
|
|
69 </dd>
|
|
|
70 <dt><strong><strong>--exportdatalabels</strong> <em>yes | no</em></strong></dt>
|
|
|
71 <dd>
|
|
|
72 <p>This option is mode specific and controls exporting of column data labels during
|
|
|
73 exportdata mode. Possible values: <em>yes or no</em>. Default: <em>yes</em>.</p>
|
|
|
74 </dd>
|
|
|
75 <dt><strong><strong>--exportlobs</strong> <em>yes | no</em></strong></dt>
|
|
|
76 <dd>
|
|
|
77 <p>This option is mode specific and controls exporting of CLOB/BLOB or BYTEA data columns during
|
|
|
78 exportdata mode. Possible values: <em>yes or no</em>. Default: <em>no</em>.</p>
|
|
|
79 </dd>
|
|
|
80 <dt><strong><strong>-h, --help</strong></strong></dt>
|
|
|
81 <dd>
|
|
|
82 <p>Print this help message.</p>
|
|
|
83 </dd>
|
|
|
84 <dt><strong><strong>-m, --mode</strong> <em>exportdata | describetable</em></strong></dt>
|
|
|
85 <dd>
|
|
|
86 <p>Data selection criterion from database. Possible values: <em>exportdata or describetable</em>.
|
|
|
87 Default value: <em>exportdata</em>.</p>
|
|
|
88 </dd>
|
|
|
89 <dt><strong><strong>-o, --overwrite</strong></strong></dt>
|
|
|
90 <dd>
|
|
|
91 <p>Overwrite existing files.</p>
|
|
|
92 </dd>
|
|
|
93 <dt><strong><strong>--outdelim</strong> <em>comma | tab | semicolon</em></strong></dt>
|
|
|
94 <dd>
|
|
|
95 <p>Output text file delimiter. Possible values: <em>comma, tab, or semicolon</em>
|
|
|
96 Default value: <em>comma</em>.</p>
|
|
|
97 </dd>
|
|
|
98 <dt><strong><strong>-q, --quote</strong> <em>yes | no</em></strong></dt>
|
|
|
99 <dd>
|
|
|
100 <p>Put quotes around column values in output text file. Possible values: <em>yes or
|
|
|
101 no</em>. Default value: <em>yes</em>.</p>
|
|
|
102 </dd>
|
|
|
103 <dt><strong><strong>-r, --root</strong> <em>rootname</em></strong></dt>
|
|
|
104 <dd>
|
|
|
105 <p>New file name is generated using the root:<Root>.<Ext>. Default new file
|
|
|
106 file names: <Mode><TableName>.<Ext>. The csv and tsv <Ext> values are used
|
|
|
107 for comma/semicolon, and tab delimited text files respectively.This option is
|
|
|
108 ignored for multiple input table names.</p>
|
|
|
109 </dd>
|
|
|
110 <dt><strong><strong>--replacenullstr</strong> <em>string</em></strong></dt>
|
|
|
111 <dd>
|
|
|
112 <p>Replace NULL or undefined row values with specified value. Default: <em>none</em></p>
|
|
|
113 <p>For importing output text files into MySQL database using "load data local infile '<tablename>.tsv'
|
|
|
114 into table <tablename>" command, use <em>--raplacenullstr "NULL"</em> in conjunction with <em>--exportdatalabels no</em>,
|
|
|
115 <em>--quote no</em>, and <em>--outdelim tab</em> options: it'll generate files for direct import into MySQL assuming
|
|
|
116 tables already exists.</p>
|
|
|
117 </dd>
|
|
|
118 <dt><strong><strong>-w --workingdir</strong> <em>dirname</em></strong></dt>
|
|
|
119 <dd>
|
|
|
120 <p>Location of working directory. Default: current directory.</p>
|
|
|
121 </dd>
|
|
|
122 </dl>
|
|
|
123 <p>
|
|
|
124 </p>
|
|
|
125 <h2>EXAMPLES</h2>
|
|
|
126 <p>To export all data in user and user_info tables from a MySQL server running on a local machine
|
|
|
127 using username/password from DBI_USER and DBI_PASS environmental variables, type:</p>
|
|
|
128 <div class="ExampleBox">
|
|
|
129 % DBTablesToTextFiles.pl -o user user_info</div>
|
|
|
130 <p>To describe user and user_info tables in a MySQL server running on a remote machine using explicit
|
|
|
131 username/password and capturing the output into a DescribeTables.csv file, type:</p>
|
|
|
132 <div class="ExampleBox">
|
|
|
133 % DBTablesToTextFiles.pl --dbdriver mysql --dbuser <name> --dbpassword
|
|
|
134 <pasword> --dbname mysql --dbhost <mysqlhostname.org>
|
|
|
135 -r DescribeTable -m describetable -o user user_info</div>
|
|
|
136 <p>To describe table all_tables in Oracle running on a remote machine using explicit
|
|
|
137 username/password and capturing the output into a DescribeAllTable.tsv file, type:</p>
|
|
|
138 <div class="ExampleBox">
|
|
|
139 % DBTablesToTextFiles.pl --dbdriver Oracle --dbuser <name> --dbpassword
|
|
|
140 <pasword> --dbhost <oraclehostname.com> -r DescribeAllTable
|
|
|
141 -m describetable --outdelim tab --quote no -o all_tables</div>
|
|
|
142 <p>To export all data in user and user_info tables from MySQL server running on a local machine
|
|
|
143 using explicit username/password and capturing the data in ExportTables.tsv file with empty
|
|
|
144 values substituted with NULL and no column labels, type:</p>
|
|
|
145 <div class="ExampleBox">
|
|
|
146 % DBTablesToTextFiles.pl --dbdriver Oracle --dbuser <name> --dbpassword
|
|
|
147 <pasword> -r ExportTables --outdelim tab --quote no --replacenullstr
|
|
|
148 "\N" -m exportdata --exportlobs no --exportdatalabels no -o
|
|
|
149 user user_info</div>
|
|
|
150 <p>
|
|
|
151 </p>
|
|
|
152 <h2>AUTHOR</h2>
|
|
|
153 <p><a href="mailto:msud@san.rr.com">Manish Sud</a></p>
|
|
|
154 <p>
|
|
|
155 </p>
|
|
|
156 <h2>SEE ALSO</h2>
|
|
|
157 <p><a href="./DBSchemaTablesToTextFiles.html">DBSchemaTablesToTextFiles.pl</a>, <a href="./DBSQLToTextFiles.html">DBSQLToTextFiles.pl</a>
|
|
|
158 </p>
|
|
|
159 <p>
|
|
|
160 </p>
|
|
|
161 <h2>COPYRIGHT</h2>
|
|
|
162 <p>Copyright (C) 2015 Manish Sud. All rights reserved.</p>
|
|
|
163 <p>This file is part of MayaChemTools.</p>
|
|
|
164 <p>MayaChemTools is free software; you can redistribute it and/or modify it under
|
|
|
165 the terms of the GNU Lesser General Public License as published by the Free
|
|
|
166 Software Foundation; either version 3 of the License, or (at your option)
|
|
|
167 any later version.</p>
|
|
|
168 <p> </p><p> </p><div class="DocNav">
|
|
|
169 <table width="100%" border=0 cellpadding=0 cellspacing=2>
|
|
|
170 <tr align="left" valign="top"><td width="33%" align="left"><a href="./DBSQLToTextFiles.html" title="DBSQLToTextFiles.html">Previous</a> <a href="./index.html" title="Table of Contents">TOC</a> <a href="./ElementalAnalysis.html" title="ElementalAnalysis.html">Next</a></td><td width="34%" align="middle"><strong>March 29, 2015</strong></td><td width="33%" align="right"><strong>DBTablesToTextFiles.pl</strong></td></tr>
|
|
|
171 </table>
|
|
|
172 </div>
|
|
|
173 <br />
|
|
|
174 <center>
|
|
|
175 <img src="../../images/h2o2.png">
|
|
|
176 </center>
|
|
|
177 </body>
|
|
|
178 </html>
|
