Debian Codegraph @

Table.html

raw
1 <?xml version="1.0" ?>
2 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
3 <html xmlns="http://www.w3.org/1999/xhtml">
4 <head>
5 <title>Data::Table - Data type related to database tables, spreadsheets, CSV/TSV files, HTML table displays, etc.</title>
6 <meta http-equiv="content-type" content="text/html; charset=utf-8" />
7 <link rev="made" href="mailto:[email protected]" />
8 </head>
9
10 <body>
11
12
13
14 <ul id="index">
15 <li><a href="#NAME">NAME</a></li>
16 <li><a href="#SYNOPSIS">SYNOPSIS</a></li>
17 <li><a href="#ABSTRACT">ABSTRACT</a></li>
18 <li><a href="#INTRODUCTION">INTRODUCTION</a></li>
19 <li><a href="#DESCRIPTION">DESCRIPTION</a>
20 <ul>
21 <li><a href="#Field-Summary">Field Summary</a></li>
22 <li><a href="#Package-Variables">Package Variables</a></li>
23 <li><a href="#Class-Methods">Class Methods</a></li>
24 <li><a href="#Table-Creation">Table Creation</a></li>
25 <li><a href="#Table-Access-and-Properties">Table Access and Properties</a></li>
26 <li><a href="#Table-Formatting">Table Formatting</a></li>
27 <li><a href="#Table-Operations">Table Operations</a></li>
28 <li><a href="#Table-Table-Manipulations">Table-Table Manipulations</a></li>
29 <li><a href="#Internal-Methods">Internal Methods</a></li>
30 </ul>
31 </li>
32 <li><a href="#TSV-FORMAT">TSV FORMAT</a></li>
33 <li><a href="#INTERFACE-TO-OTHER-SOFTWARES">INTERFACE TO OTHER SOFTWARES</a>
34 <ul>
35 <li><a href="#Interface-to-Database-and-Web">Interface to Database and Web</a></li>
36 <li><a href="#Interface-to-CSV-TSV">Interface to CSV/TSV</a></li>
37 <li><a href="#Interface-to-Excel-XLS-XLSX">Interface to Excel XLS/XLSX</a></li>
38 <li><a href="#Interface-to-Graphics-Package">Interface to Graphics Package</a></li>
39 </ul>
40 </li>
41 <li><a href="#AUTHOR">AUTHOR</a></li>
42 <li><a href="#SEE-ALSO">SEE ALSO</a></li>
43 </ul>
44
45 <h1 id="NAME">NAME</h1>
46
47 <p>Data::Table - Data type related to database tables, spreadsheets, CSV/TSV files, HTML table displays, etc.</p>
48
49 <h1 id="SYNOPSIS">SYNOPSIS</h1>
50
51 <pre><code> News: The package now includes &quot;Perl Data::Table Cookbook&quot; (PDF), which may serve as a better learning material.
52 To download the free Cookbook, visit https://sites.google.com/site/easydatabase/
53
54 # some cool ways to use Table.pm
55 use Data::Table;
56
57 $header = [&quot;name&quot;, &quot;age&quot;];
58 $data = [
59 [&quot;John&quot;, 20],
60 [&quot;Kate&quot;, 18],
61 [&quot;Mike&quot;, 23]
62 ];
63 $t = Data::Table-&gt;new($data, $header, 0); # Construct a table object with
64 # $data, $header, $type=0 (consider
65 # $data as the rows of the table).
66 print $t-&gt;csv; # Print out the table as a csv file.
67
68 $t = Data::Table::fromCSV(&quot;aaa.csv&quot;); # Read a csv file into a table object
69 ### Since version 1.51, a new method fromFile can automatically guess the correct file format
70 # either CSV or TSV file, file with or without a column header line
71 # e.g.
72 # $t = Data::Table::fromFile(&quot;aaa.csv&quot;);
73 # is equivalent.
74 print $t-&gt;html; # Display a &#39;portrait&#39; HTML TABLE on web.
75
76 use DBI;
77 $dbh= DBI-&gt;connect(&quot;DBI:mysql:test&quot;, &quot;test&quot;, &quot;&quot;) or die $DBI::errstr;
78 my $minAge = 10;
79 $t = Data::Table::fromSQL($dbh, &quot;select * from mytable where age &gt;= ?&quot;, [$minAge]);
80 # Construct a table form an SQL
81 # database query.
82
83 $t-&gt;sort(&quot;age&quot;, 0, 0); # Sort by col &#39;age&#39;,numerical,ascending
84 print $t-&gt;html2; # Print out a &#39;landscape&#39; HTML Table.
85
86 $row = $t-&gt;delRow(2); # Delete the third row (index=2).
87 $t-&gt;addRow($row, 4); # Add the deleted row back as fifth row.
88 @rows = $t-&gt;delRows([0..2]); # Delete three rows (row 0 to 2).
89 $col = $t-&gt;delCol(&quot;age&quot;); # Delete column &#39;age&#39;.
90 $t-&gt;addCol($col, &quot;age&quot;,2); # Add column &#39;age&#39; as the third column
91 @cols = $t-&gt;delCols([&quot;name&quot;,&quot;phone&quot;,&quot;ssn&quot;]);
92 # Delete 3 columns at the same time.
93
94 $name = $t-&gt;elm(2,&quot;name&quot;); # Element access
95 $t2=$t-&gt;subTable([1, 3..4],[&#39;age&#39;, &#39;name&#39;]);
96 # Extract a sub-table
97
98 $t-&gt;rename(&quot;Entry&quot;, &quot;New Entry&quot;); # Rename column &#39;Entry&#39; by &#39;New Entry&#39;
99 $t-&gt;replace(&quot;Entry&quot;, [1..$t-&gt;nofRow()], &quot;New Entry&quot;);
100 # Replace column &#39;Entry&#39; by an array of
101 # numbers and rename it as &#39;New Entry&#39;
102 $t-&gt;swap(&quot;age&quot;,&quot;ssn&quot;); # Swap the positions of column &#39;age&#39;
103 # with column &#39;ssn&#39; in the table.
104
105 $t-&gt;colMap(&#39;name&#39;, sub {return uc}); # Map a function to a column
106 $t-&gt;sort(&#39;age&#39;,0,0,&#39;name&#39;,1,0); # Sort table first by the numerical
107 # column &#39;age&#39; and then by the
108 # string column &#39;name&#39; in ascending
109 # order
110 $t2=$t-&gt;match_pattern(&#39;$_-&gt;[0] =~ /^L/ &amp;&amp; $_-&gt;[3]&lt;0.2&#39;);
111 # Select the rows that matched the
112 # pattern specified
113 $t2=$t-&gt;match_pattern_hash(&#39;$_{&quot;Amino acid&quot;} =~ /^L-a/ &amp;&amp; $_{&quot;Grams \&quot;(a.a.)\&quot;&quot;}&lt;0.2&#39;));
114 # use column name in the pattern, method added in 1.62
115 $t2=$t-&gt;match_string(&#39;John&#39;); # Select the rows that matches &#39;John&#39;
116 # in any column
117
118 $t2=$t-&gt;clone(); # Make a copy of the table.
119 $t-&gt;rowMerge($t2); # Merge two tables
120 $t-&gt;colMerge($t2);
121
122 $t = Data::Table-&gt;new( # create an employ salary table
123 [
124 [&#39;Tom&#39;, &#39;male&#39;, &#39;IT&#39;, 65000],
125 [&#39;John&#39;, &#39;male&#39;, &#39;IT&#39;, 75000],
126 [&#39;Tom&#39;, &#39;male&#39;, &#39;IT&#39;, 65000],
127 [&#39;John&#39;, &#39;male&#39;, &#39;IT&#39;, 75000],
128 [&#39;Peter&#39;, &#39;male&#39;, &#39;HR&#39;, 85000],
129 [&#39;Mary&#39;, &#39;female&#39;, &#39;HR&#39;, 80000],
130 [&#39;Nancy&#39;, &#39;female&#39;, &#39;IT&#39;, 55000],
131 [&#39;Jack&#39;, &#39;male&#39;, &#39;IT&#39;, 88000],
132 [&#39;Susan&#39;, &#39;female&#39;, &#39;HR&#39;, 92000]
133 ],
134 [&#39;Name&#39;, &#39;Sex&#39;, &#39;Department&#39;, &#39;Salary&#39;], 0);
135
136 sub average { # this is an subroutine calculate mathematical average, ignore NULL
137 my @data = @_;
138 my ($sum, $n) = (0, 0);
139 foreach $x (@data) {
140 next unless $x;
141 $sum += $x; $n++;
142 }
143 return ($n&gt;0)?$sum/$n:undef;
144 }
145
146 $t2 = $t-&gt;group([&quot;Department&quot;,&quot;Sex&quot;],[&quot;Name&quot;, &quot;Salary&quot;], [sub {scalar @_}, \&amp;average], [&quot;Nof Employee&quot;, &quot;Average Salary&quot;]);
147 # For each (Department,Sex) pair, calculate the number of employees and average salary
148 $t2 = $t2-&gt;pivot(&quot;Sex&quot;, 0, &quot;Average Salary&quot;, [&quot;Department&quot;]);
149 # Show average salary information in a Department by Sex spreadsheet</code></pre>
150
151 <h1 id="ABSTRACT">ABSTRACT</h1>
152
153 <p>This perl package uses perl5 objects to make it easy for manipulating spreadsheet data among disk files, database, and Web publishing.</p>
154
155 <p>A table object contains a header and a two-dimensional array of scalars. Four class methods Data::fromFile, Data::Table::fromCSV, Data::Table::fromTSV, and Data::Table::fromSQL allow users to create a table object from a CSV/TSV file or a database SQL selection in a snap.</p>
156
157 <p>Table methods provide basic access, add, delete row(s) or column(s) operations, as well as more advanced sub-table extraction, table sorting, record matching via keywords or patterns, table merging, and web publishing. Data::Table class also provides a straightforward interface to other popular Perl modules such as DBI and GD::Graph.</p>
158
159 <p>The most updated version of the Perl Data::Table Cookbook is available at https://sites.google.com/site/easydatabase/</p>
160
161 <p>We use Data::Table instead of Table, because Table.pm has already been used inside PerlQt module in CPAN.</p>
162
163 <h1 id="INTRODUCTION">INTRODUCTION</h1>
164
165 <p>A table object has three data members:</p>
166
167 <dl>
168
169 <dt id="data">1. $data:</dt>
170 <dd>
171
172 <p>a reference to an array of array-references. It&#39;s basically a reference to a two-dimensional array.</p>
173
174 </dd>
175 <dt id="header">2. $header:</dt>
176 <dd>
177
178 <p>a reference to a string array. The array contains all the column names.</p>
179
180 </dd>
181 <dt id="type-1-or-0">3. $type = 1 or 0.</dt>
182 <dd>
183
184 <p>1 means that @$data is an array of table columns (fields) (column-based); 0 means that @$data is an array of table rows (records) (row-based);</p>
185
186 </dd>
187 </dl>
188
189 <p>Row-based/Column-based are two internal implementations for a table object. E.g., if a spreadsheet consists of two columns lastname and age. In a row-based table, $data = [ [&#39;Smith&#39;, 29], [&#39;Dole&#39;, 32] ]. In a column-based table, $data = [ [&#39;Smith&#39;, &#39;Dole&#39;], [29, 32] ].</p>
190
191 <p>Two implementations have their pros and cons for different operations. Row-based implementation is better for sorting and pattern matching, while column-based one is better for adding/deleting/swapping columns.</p>
192
193 <p>Users only need to specify the implementation type of the table upon its creation via Data::Table::new, and can forget about it afterwards. Implementation type of a table should be considered volatile, because methods switch table objects from one type into another internally. Be advised that row/column/element references gained via table::rowRef, table::rowRefs, table::colRef, table::colRefs, or table::elmRef may become stale after other method calls afterwards.</p>
194
195 <p>For those who want to inherit from the Data::Table class, internal method table::rotate is used to switch from one implementation type into another. There is an additional internal assistant data structure called colHash in our current implementation. This hash table stores all column names and their corresponding column index number as key-value pairs for fast conversion. This gives users an option to use column name wherever a column ID is expected, so that user don&#39;t have to use table::colIndex all the time. E.g., you may say $t-&gt;rename(&#39;oldColName&#39;, &#39;newColName&#39;) instead of $t-&gt;rename($t-&gt;colIndex(&#39;oldColName&#39;), &#39;newColIdx&#39;).</p>
196
197 <h1 id="DESCRIPTION">DESCRIPTION</h1>
198
199 <h2 id="Field-Summary">Field Summary</h2>
200
201 <dl>
202
203 <dt id="data-refto_arrayof_refto_array">data refto_arrayof_refto_array</dt>
204 <dd>
205
206 <p>contains a two-dimensional spreadsheet data.</p>
207
208 </dd>
209 <dt id="header-refto_array">header refto_array</dt>
210 <dd>
211
212 <p>contains all column names.</p>
213
214 </dd>
215 <dt id="type-0-1">type 0/1</dt>
216 <dd>
217
218 <p>0 is row-based, 1 is column-based, describe the orientation of @$data.</p>
219
220 </dd>
221 </dl>
222
223 <h2 id="Package-Variables">Package Variables</h2>
224
225 <dl>
226
227 <dt id="Data::Table::VERSION">$Data::Table::VERSION</dt>
228 <dd>
229
230 </dd>
231 <dt id="Data::Table::OK">@Data::Table::OK</dt>
232 <dd>
233
234 <p>see table::match_string, table::match_pattern, and table::match_pattern_hash Since 1.62, we recommend you to use $table-&gt;{OK} instead, which is a local array reference.</p>
235
236 </dd>
237 <dt id="Data::Table::MATCH">@Data::Table::MATCH</dt>
238 <dd>
239
240 <p>see table::match_string, table::match_pattern, and table::match_pattern_hash Since 1.67, we return the matched row indices in an array. Data::Table::MATCH is this array reference. Here is an example of setting a max price of 20 to all items with UnitPrice &gt; 20.</p>
241
242 <pre><code> $t_product-&gt;match_pattern_hash(&#39;$_{UnitPrice} &gt; 20&#39;);
243 $t_product-&gt;setElm($t_product-&gt;{MATCH}, &#39;UnitPrice&#39;, 20);</code></pre>
244
245 </dd>
246 <dt id="Data::Table::DEFAULTS">%Data::Table::DEFAULTS</dt>
247 <dd>
248
249 <p>Store default settings, currently it contains CSV_DELIMITER (set to &#39;,&#39;), CSV_QUALIFER (set to &#39;&quot;&#39;), and OS (set to 0). see table::fromCSV, table::csv, table::fromTSV, table::tsv for details.</p>
250
251 </dd>
252 </dl>
253
254 <h2 id="Class-Methods">Class Methods</h2>
255
256 <p>Syntax: return_type method_name ( [ parameter [ = default_value ]] [, parameter [ = default_value ]] )</p>
257
258 <p>If method_name starts with table::, this is an instance method, it can be used as $t-&gt;method( parameters ), where $t is a table reference.</p>
259
260 <p>If method_name starts with Data::Table::, this is a class method, it should be called as Data::Table::method, e.g., $t = Data::Table::fromCSV(&quot;filename.csv&quot;).</p>
261
262 <p>Conventions for local variables:</p>
263
264 <pre><code> colID: either a numerical column index or a column name;
265 rowIdx: numerical row index;
266 rowIDsRef: reference to an array of column IDs;
267 rowIdcsRef: reference to an array of row indices;
268 rowRef, colRef: reference to an array of scalars;
269 data: ref_to_array_of_ref_to_array of data values;
270 header: ref to array of column headers;
271 table: a table object, a blessed reference.</code></pre>
272
273 <h2 id="Table-Creation">Table Creation</h2>
274
275 <dl>
276
277 <dt id="table-Data::Table::new-data-header-type-0-enforceCheck-1">table Data::Table::new ( $data = [], $header = [], $type = 0, $enforceCheck = 1)</dt>
278 <dd>
279
280 <p>create a new table. It returns a table object upon success, undef otherwise. $data: points to the spreadsheet data. $header: points to an array of column names. Before version 1.69, a column name must have at least one non-digit character. Since version 1.69, this is relaxed. Although integer and numeric column names can now be accepted, when accessing a column by integer, it is first interpreted as a column name. $type: 0 or 1 for row-based/column-based spreadsheet. $enforceCheck: 1/0 to turn on/off initial checking on the size of each row/column to make sure the data argument indeed points to a valid structure. In 1.63, we introduce constants Data::Table::ROW_BASED and Data::Table::COL_BASED as synonyms for $type. To create an empty Data::Table, use new Data::Table([], [], Data::Table::ROW_BASED);</p>
281
282 </dd>
283 <dt id="table-table::subTable-rowIdcsRef-colIDsRef-arg_ref">table table::subTable ($rowIdcsRef, $colIDsRef, $arg_ref)</dt>
284 <dd>
285
286 <p>create a new table, which is a subset of the original. It returns a table object. $rowIdcsRef: points to an array of row indices (or a true/false row mask array). $colIDsRef: points to an array of column IDs. The function make a copy of selected elements from the original table. Undefined $rowIdcsRef or $colIDsRef is interpreted as all rows or all columns. The elements in $colIDsRef may be modified as a side effect before version 1.62, fixed in 1.62. If $arg_ref-&gt;{useRowMask} is set to 1, $rowIdcsRef is a true/false row mask array, where rows marked as TRUE will be returned. Row mask array is typically the Data::Table::OK set by match_string/match_pattern/match_pattern_hash methods.</p>
287
288 </dd>
289 <dt id="table-table::clone">table table::clone</dt>
290 <dd>
291
292 <p>make a clone of the original. It return a table object, equivalent to table::subTable(undef,undef).</p>
293
294 </dd>
295 <dt id="table-Data::Table::fromCSV-name_or_handler-includeHeader-1-header-col1-...-OS-Data::Table::DEFAULTS-OS-delimiter-Data::Table::DEFAULTS-CSV_DELIMITER-qualifier-Data::Table::DEFAULTS-CSV_QUALIFIER-skip_lines-0-skip_pattern-undef-encoding-Data::Table::DEFAULTS-ENCODING">table Data::Table::fromCSV ($name_or_handler, $includeHeader = 1, $header = [&quot;col1&quot;, ... ], {OS=&gt;$Data::Table::DEFAULTS{&#39;OS&#39;}, delimiter=&gt;$Data::Table::DEFAULTS{&#39;CSV_DELIMITER&#39;}, qualifier=&gt;$Data::Table::DEFAULTS{&#39;CSV_QUALIFIER&#39;}, skip_lines=&gt;0, skip_pattern=&gt;undef, encoding=&gt;$Data::Table::DEFAULTS{&#39;ENCODING&#39;}})</dt>
296 <dd>
297
298 <p>create a table from a CSV file. return a table object. $name_or_handler: the CSV file name or an already opened file handler. If a handler is used, it&#39;s not closed upon return. To read from STDIN, use Data::Table::fromCSV(\*STDIN, 1). $includeHeader: 0 or 1 to ignore/interpret the first line in the file as column names, If it is set to 0, the array in $header is used. If $header is not supplied, the default column names are &quot;col1&quot;, &quot;col2&quot;, ... optional named argument OS specifies under which operating system the CSV file was generated. 0 for UNIX, 1 for PC and 2 for MAC. If not specified, $Data::Table::DEFAULTS{&#39;OS&#39;} is used, which defaults to UNIX. Basically linebreak is defined as &quot;\n&quot;, &quot;\r\n&quot; and &quot;\r&quot; for three systems, respectively.</p>
299
300 <p>optional name argument delimiter and qualifier let user replace comma and double-quote by other meaningful single characters. &lt;b&gt;Exception&lt;/b&gt;: if the delimiter or the qualifier is a special symbol in regular expression, you must escape it by &#39;\&#39;. For example, in order to use pipe symbol as the delimiter, you must specify the delimiter as &#39;\|&#39;.</p>
301
302 <p>optional name argument skip_lines let you specify how many lines in the csv file should be skipped, before the data are interpretted.</p>
303
304 <p>optional name argument skip_pattern let you specify a regular expression. Lines that match the regular expression will be skipped.</p>
305
306 <p>optional name argument encoding let you specify an encoding method of the csv file. This option is added to fromCSV, fromTSV, fromFile since version 1.69.</p>
307
308 <p>The following example reads a DOS format CSV file and writes a MAC format:</p>
309
310 <pre><code> $t = Data::Table:fromCSV(&#39;A_DOS_CSV_FILE.csv&#39;, 1, undef, {OS=&gt;1});
311 $t-&gt;csv(1, {OS=&gt;2, file=&gt;&#39;A_MAC_CSV_FILE.csv&#39;});
312 open(SRC, &#39;A_DOS_CSV_FILE.csv&#39;) or die &quot;Cannot open A_DOS_CSV_FILE.csv to read!&quot;;
313 $t = Data::Table::fromCSV(\*SRC, 1);
314 close(SRC);</code></pre>
315
316 <p>The following example reads a non-standard CSV file with : as the delimiter, &#39; as the qaulifier</p>
317
318 <pre><code> my $s=&quot;col_A:col_B:col_C\n1:2, 3 or 5:3.5\none:&#39;one:two&#39;:&#39;double\&quot;, single&#39;&#39;&#39;&quot;;
319 open my $fh, &quot;&lt;&quot;, \$s or die &quot;Cannot open in-memory file\n&quot;;
320 my $t_fh=Data::Table::fromCSV($fh, 1, undef, {delimiter=&gt;&#39;:&#39;, qualifier=&gt;&quot;&#39;&quot;});
321 close($fh);
322 print $t_fh-&gt;csv;
323 # convert to the standard CSV (comma as the delimiter, double quote as the qualifier)
324 # col_A,col_B,col_C
325 # 1,&quot;2, 3 or 5&quot;,3.5
326 # one,one:two,&quot;double&quot;&quot;, single&#39;&quot;
327 print $t-&gt;csv(1, {delimiter=&gt;&#39;:&#39;, qualifier=&gt;&quot;&#39;&quot;}); # prints the csv file use the original definition</code></pre>
328
329 <p>The following example reads bbb.csv file (included in the package) by skipping the first line (skip_lines=&gt;1), then treats any line that starts with &#39;#&#39; (or space comma) as comments (skip_pattern=&gt;&#39;^\s*#&#39;), use &#39;:&#39; as the delimiter.</p>
330
331 <pre><code> $t = Data::Table::fromCSV(&quot;bbb.csv&quot;, 1, undef, {skip_lines=&gt;1, delimiter=&gt;&#39;:&#39;, skip_pattern=&gt;&#39;^\s*#&#39;});</code></pre>
332
333 <p>Use the optional name argument encoding to specify file encoding method. $t = Data::Table::fromCSV(&quot;bbb.csv&quot;, 1, undef, {encoding=&gt;&#39;UTF-8&#39;});</p>
334
335 </dd>
336 <dt id="table-table::fromCSVi-name-includeHeader-1-header-col1">table table::fromCSVi ($name, $includeHeader = 1, $header = [&quot;col1&quot;, ... ])</dt>
337 <dd>
338
339 <p>Same as Data::Table::fromCSV. However, this is an instant method (that&#39;s what &#39;i&#39; stands for), which can be inherited.</p>
340
341 </dd>
342 <dt id="table-Data::Table::fromTSV-name-includeHeader-1-header-col1-...-OS-Data::Table::DEFAULTS-OS-skip_lines-0-skip_pattern-undef-transform_element-1-encoding-Data::Table::DEFAULTS-ENCODING">table Data::Table::fromTSV ($name, $includeHeader = 1, $header = [&quot;col1&quot;, ... ], {OS=&gt;$Data::Table::DEFAULTS{&#39;OS&#39;}, skip_lines=&gt;0, skip_pattern=&gt;undef, transform_element=&gt;1, encoding=&gt;$Data::Table::DEFAULTS{&#39;ENCODING&#39;}})</dt>
343 <dd>
344
345 <p>create a table from a TSV file. return a table object. $name: the TSV file name or an already opened file handler. If a handler is used, it&#39;s not closed upon return. To read from STDIN, use Data::Table::fromTSV(\*STDIN, 1). $includeHeader: 0 or 1 to ignore/interpret the first line in the file as column names, If it is set to 0, the array in $header is used. If $header is not supplied, the default column names are &quot;col1&quot;, &quot;col2&quot;, ... optional named argument OS specifies under which operating system the TSV file was generated. 0 for UNIX, 1 for P C and 2 for MAC. If not specified, $Data::Table::DEFAULTS{&#39;OS&#39;} is used, which defaults to UNIX. Basically linebreak is defined as &quot;\n&quot;, &quot;\r\n&quot; and &quot;\r&quot; for three systems, respectively. &lt;b&gt;Exception&lt;/b&gt;: if the delimiter or the qualifier is a special symbol in regular expression, you must escape it by &#39;\&#39;. For example, in order to use pipe symbol as the delimiter, you must specify the delimiter as &#39;\|&#39;.</p>
346
347 <p>optional name argument skip_lines let you specify how many lines in the csv file should be skipped, before the data are interpretted.</p>
348
349 <p>optional name argument skip_pattern let you specify a regular expression. Lines that match the regular expression will be skipped.</p>
350
351 <p>optional name argument transform_element let you switch on/off \t to tab, \N to undef (etc.) transformation. See TSV FORMAT for details. However, elements are always transformed when export table to tsv format, because not escaping an element containing a tab will be disasterous.</p>
352
353 <p>optional name argument encoding enables one to provide an encoding method when open the tsv file.</p>
354
355 <p>See similar examples under Data::Table::fromCSV;</p>
356
357 <p>Note: read &quot;TSV FORMAT&quot; section for details.</p>
358
359 </dd>
360 <dt id="table-table::fromTSVi-name-includeHeader-1-header-col1">table table::fromTSVi ($name, $includeHeader = 1, $header = [&quot;col1&quot;, ... ])</dt>
361 <dd>
362
363 <p>Same as Data::Table::fromTSV. However, this is an instant method (that&#39;s what &#39;i&#39; stands for), which can be inherited.</p>
364
365 </dd>
366 <dt id="table-Data::Table::fromFile-file_name-arg_ref-linesChecked-2-allowNumericHeader-0-encoding-Data::Table::DEFAULTS-ENCODING">table Data::Table::fromFile ($file_name, $arg_ref = {linesChecked=&gt;2, allowNumericHeader=&gt;0, encoding=&gt;$Data::Table::DEFAULTS{&#39;ENCODING&#39;}})</dt>
367 <dd>
368
369 <p>create a table from a text file. return a table object. $file_name: the file name (cannot take a file handler). linesChecked: the first number of lines used for guessing the input format. The delimiter will have to produce the same number of columns for these lines. By default only check the first 2 lines, 0 means all lines in the file. $arg_ref can take additional parameters, such as OS, has_header, delimiter, transform_element, etc. Encoding allows one to specify encoding methods used to open the file, which defaults to UTF-8.</p>
370
371 <p>fromFile is added after version 1.51. It relies on the following new methods to automatically figure out the correct file format in order to call fromCSV or fromTSV internally:</p>
372
373 <pre><code> fromFileGuessOS($file_name, {encoding=&gt;&#39;UTF-8&#39;})
374 returns integer, 0 for UNIX, 1 for PC, 2 for MAC
375 fromFileGetTopLines($file_name, $os, $lineNumber, {encoding=&gt;&#39;UTF-8&#39;}) # $os defaults to fromFileGuessOS($file_name), if not specified
376 returns an array of strings, each string represents each row with linebreak removed.
377 fromFileGuessDelimiter($lineArrayRef) # guess delimiter from &quot;,&quot;, &quot;\t&quot;, &quot;:&quot;;
378 returns the guessed delimiter string.
379 fromFileIsHeader($line_concent, $delimiter, $allowNumericHeader) # $delimiter defaults to $Data::Table::DEFAULTS{&#39;CSV_DELIMITER&#39;}
380 returns 1 or 0.</code></pre>
381
382 <p>It first ask fromFileGuessOS to figure out which OS (UNIX, PC or MAC) generated the input file. The fetch the first linesChecked lines using fromFileGetTopLines. It then guesses the best delimiter using fromFileGuessDelimiter, then it checks if the first line looks like a column header row using fromFileIsHeader. Since fromFileGuessOS and fromFileGetTopLines needs to open/close the input file, these methods can only take file name, not file handler. If user specify formatting parameters in $arg_ref, the routine will skip the corresponding guess work. At the end, fromFile simply calls either fromCSV or fromTSV with $arg_ref forwarded. So if you call fromFile({transform_element=&gt;0}) on a TSV file, transform_elment will be passed onto fromTSV calls internally.</p>
383
384 <p>fromFileGuessOS finds the linebreak that gives shortest first line (in the priority of UNIX, PC, MAC upon tie). fromFileGuessDelimiter works based on the assumption that the correct delimiter will produce equal number of columns for the given rows. If multiple matches, it chooses the delimiter that gives maximum number of columns. If none matches, it returns the default delimiter. fromFileIsHeader works based on the assumption that no column header can be empty or numeric values. However, if we allow numeric column names (especially integer column names), set {allowNumericHeader =&gt; 1}</p>
385
386 </dd>
387 <dt id="table-Data::Table::fromSQL-dbh-sql-vars">table Data::Table::fromSQL ($dbh, $sql, $vars)</dt>
388 <dd>
389
390 <p>create a table from the result of an SQL selection query. It returns a table object upon success or undef otherwise. $dbh: a valid database handler. Typically $dbh is obtained from DBI-&gt;connect, see &quot;Interface to Database&quot; or DBI.pm. $sql: an SQL query string or a DBI::st object (starting in version 1.61). $vars: optional reference to an array of variable values, required if $sql contains &#39;?&#39;s which need to be replaced by the corresponding variable values upon execution, see DBI.pm for details. Hint: in MySQL, Data::Table::fromSQL($dbh, &#39;show tables from test&#39;) will also create a valid table object.</p>
391
392 <p>Data::Table::fromSQL now can take DBI::st instead of a SQL string. This is introduced, so that variable binding (such as CLOB/BLOB) can be done outside the method, for example:</p>
393
394 <pre><code> $sql = &#39;insert into test_table (id, blob_data) values (1, :val)&#39;;
395 $sth = $dbh-&gt;prepare($sql);
396 $sth-&gt;bind_param(&#39;:val&#39;, $blob, {ora_type =&gt; SQLT_BIN});
397 Data::Table::fromSQL($dbh, $sth);</code></pre>
398
399 </dd>
400 <dt id="table-Data::Table::fromSQLi-dbh-sql-vars">table Data::Table::fromSQLi ($dbh, $sql, $vars)</dt>
401 <dd>
402
403 <p>Same as Data::Table::fromSQL. However, this is an instant method (that&#39;s what &#39;i&#39; stands for), whic h can be inherited.</p>
404
405 </dd>
406 </dl>
407
408 <h2 id="Table-Access-and-Properties">Table Access and Properties</h2>
409
410 <dl>
411
412 <dt id="int-table::colIndex-colID">int table::colIndex ($colID)</dt>
413 <dd>
414
415 <p>translate a column name into its numerical position, the first column has index 0 as in as any perl array. return -1 for invalid column names.</p>
416
417 <p>Since 1.69, we allow integer to be used as a column header. The integer $colID will first be checked against column names, if matched, the corresponding column index is returned. E.g., if column name for the 3rd column is &quot;1&quot;, colIndex(1) will return 2 instead of 1! In such case, if one need to access the second column, one has to access it by column name, i.e., $t-&gt;col(($t-&gt;header)[1]).</p>
418
419 </dd>
420 <dt id="int-table::nofCol">int table::nofCol</dt>
421 <dd>
422
423 <p>return number of columns.</p>
424
425 </dd>
426 <dt id="int-table::nofRow">int table::nofRow</dt>
427 <dd>
428
429 <p>return number of rows.</p>
430
431 </dd>
432 <dt id="int-table::lastCol">int table::lastCol</dt>
433 <dd>
434
435 <p>return the index of the last columns, i.e., nofCol - 1.</p>
436
437 </dd>
438 <dt id="int-table::lastRow">int table::lastRow</dt>
439 <dd>
440
441 <p>return the index of the last rows, i.e., nofRow - 1; This is syntax sugar.</p>
442
443 <pre><code> # these two are equivalent
444 foreach my $i (0 .. $t-&gt;lastRow)
445 foreach my $i (0 .. $t-&gt;nofRow - 1)</code></pre>
446
447 </dd>
448 <dt id="bool-table::isEmpty">bool table::isEmpty</dt>
449 <dd>
450
451 <p>return whether the table has any column, introduced in 1.63.</p>
452
453 </dd>
454 <dt id="bool-table::hasCol-colID">bool table::hasCol($colID)</dt>
455 <dd>
456
457 <p>returns whether the colID is a table column, introduced in 1.63.</p>
458
459 </dd>
460 <dt id="bool-table::colName-colNumericIndex">bool table::colName($colNumericIndex)</dt>
461 <dd>
462
463 <p>returns the column name for a numeric column index, notice the first column has an index of 0. Introduced in 1.68.</p>
464
465 </dd>
466 <dt id="scalar-table::elm-rowIdx-colID">scalar table::elm ($rowIdx, $colID)</dt>
467 <dd>
468
469 <p>return the value of a table element at [$rowIdx, $colID], undef if $rowIdx or $colID is invalid.</p>
470
471 </dd>
472 <dt id="refto_scalar-table::elmRef-rowIdx-colID">refto_scalar table::elmRef ($rowIdx, $colID)</dt>
473 <dd>
474
475 <p>return the reference to a table element at [$rowIdx, $colID], to allow possible modification. It returns undef for invalid $rowIdx or $colID.</p>
476
477 </dd>
478 <dt id="array-table::header-header">array table::header ($header)</dt>
479 <dd>
480
481 <p>Without argument, it returns an array of column names. Otherwise, use the new header.</p>
482
483 </dd>
484 <dt id="int-table::type">int table::type</dt>
485 <dd>
486
487 <p>return the implementation type of the table (row-based/column-based) at the time, be aware that the type of a table should be considered as volatile during method calls.</p>
488
489 </dd>
490 </dl>
491
492 <h2 id="Table-Formatting">Table Formatting</h2>
493
494 <dl>
495
496 <dt id="string-table::csv-header-OS-Data::Table::DEFAULTS-OS-file-undef-delimiter-Data::Table::DEFAULTS-CSV_DELIMITER-qualifier-Data::Table::DEFAULTS-CSV_QAULIFIER">string table::csv ($header, {OS=&gt;$Data::Table::DEFAULTS{&#39;OS&#39;}, file=&gt;undef, delimiter=&gt;$Data::Table::DEFAULTS{&#39;CSV_DELIMITER&#39;}, qualifier=&gt;$Data::Table::DEFAULTS{&#39;CSV_QAULIFIER&#39;}})</dt>
497 <dd>
498
499 <p>return a string corresponding to the CSV representation of the table. $header controls whether to print the header line, 1 for yes, 0 for no. optional named argument OS specifies for which operating system the CSV file is generated. 0 for UNIX, 1 for P C and 2 for MAC. If not specified, $Data::Table::DEFAULTS{&#39;OS&#39;} is used. Basically linebreak is defined as &quot;\n&quot;, &quot;\r\n&quot; and &quot;\r&quot; for three systems, respectively. if &#39;file&#39; is given, the csv content will be written into it, besides returning the string. One may specify custom delimiter and qualifier if the other than default are desired.</p>
500
501 </dd>
502 <dt id="string-table::tsv">string table::tsv</dt>
503 <dd>
504
505 <p>return a string corresponding to the TSV representation of the table. $header controls whether to print the header line, 1 for yes, 0 for no. optional named argument OS specifies for which operating system the TSV file is generated. 0 for UNIX, 1 for P C and 2 for MAC. If not specified, $Data::Table::DEFAULTS{&#39;OS&#39;} is used. Basically linebreak is defined as &quot;\n&quot;, &quot;\r\n&quot; and &quot;\r&quot; for three systems, respectively. if &#39;file&#39; is given, the tsv content will be written into it, besides returning the string.</p>
506
507 <p>Note: read &quot;TSV FORMAT&quot; section for details.</p>
508
509 </dd>
510 <dt id="string-table::html-colorArrayRef_or_colorHashRef-D4D4BF-ECECE4-CCCC99-tag_tbl-border-1-tag_tr-align-left-tag_th-align-center-tag_td-col3-align-right-valign-bottom-4-align-left-l_portrait-1-callback-undef">string table::html ($colorArrayRef_or_colorHashRef = [&quot;#D4D4BF&quot;,&quot;#ECECE4&quot;,&quot;#CCCC99&quot;], $tag_tbl = {border =&gt; &#39;1&#39;}, $tag_tr = {align =&gt; &#39;left&#39;}, $tag_th = {align =&gt; &#39;center&#39;}, $tag_td = {col3 =&gt; &#39;align=&quot;right&quot; valign=&quot;bottom&quot;&#39;, 4 =&gt; &#39;align=&quot;left&quot;&#39;}, $l_portrait = 1, $callback = undef )</dt>
511 <dd>
512
513 <p>return a string corresponding to a &#39;Portrait/Landscape&#39;-style html-tagged table. $colorArrayRef_or_colorHashRef: If a hash reference is provided, it will take three CSS class names for odd data rows, even data rows and for the header row. The default hash is {even=&gt;&quot;data_table_even&quot;, odd=&gt;&quot;data_table_odd&quot;, header=&gt;&quot;data_table_header&quot;). If a hash reference is not found, a reference to an array of three color strings is expected to provided for backgrounds for even-row records, odd-row records, and -der row, respectively. A default color array (&quot;#D4D4BF&quot;,&quot;#ECECE4&quot;,&quot;#CCCC99&quot;) will be used if $colors isn&#39;t defined.</p>
514
515 <p>Since version 1.74, users can prevent default coloring by passing in a color array reference [&quot;&quot;, &quot;&quot;, &quot;&quot;].</p>
516
517 <p>Before version 1.59, the parameter can only accept an array reference.</p>
518
519 <p>$tag_tbl: a reference to a hash that specifies any legal attributes such as name, border, id, class, etc. for the TABLE tag.</p>
520
521 <p>$tag_tr: a reference to a hash that specifies any legal attributes for the TR tag.</p>
522
523 <p>$tag_th: a reference to a hash that specifies any legal attributes for the TH tag.</p>
524
525 <p>$tag_td: a reference to a hash that specifies any legal attributes for the TD tag.</p>
526
527 <p>Notice $tag_tr and $tag_th controls all the rows and columns of the whole table. The keys of the hash are the attribute names in these cases. However, $tag_td is column specific, i.e., you should specify TD attributes for every column separately. The key of %$tag_td are either column names or column indices, the value is a reference to a hash. E.g., $tag_td = {col3 =&gt; {&#39;style&#39;=&gt;&#39;background-color:#cccc99;&#39;}}. However, before version 1.74, the value is the full string to be inserted into the TD tag. E.g., $tag_td = {col3 =&gt; &#39;align=right valign=bottom} only change the TD tag in &quot;col3&quot; to be &lt;TD align=right valign=bottom&gt;;. This format is still supported for backward compatibility.</p>
528
529 <p>$portrait controls the layout of the table. The default is 1, i.e., the table is shown in the &quot;Portrait&quot; style, like in Excel. 0 means &quot;Landscape&quot;. Since version 1.59, tbody and thead tags are added to the portrait mode output.</p>
530
531 <p>Since version 1.74, $callback is introduced to give users fine control on the tag for each cell, i.e., for each th/td cells. $callback is a subroutine reference, where the sub is expected to take parameters ($tag, $row_index, $col_index, $col_name, $table), $tag is reference to a hash containing existing TH/TD tags, the sub will return a new tag. The rest of the parameters give sub access to the identity of the table cell, as well as the table itself.</p>
532
533 <p>If the following example, the callback function colors each UnitPrice cell based on whether its value is &gt;=20 or &lt;20. It colors each Discontinued cell based on whether its value is TRUE or FALSE. One can also control the column header cells, which has row index of -1. That is the reason we use &quot;$row &gt;=0 &quot; within callback to make sure it cell is not a column header.</p>
534
535 <pre><code> $t=Data::Table::fromCSV(&quot;Data-Table-1.74/Product.csv&quot;,1,undef, {&#39;OS&#39;=&gt;1});
536 my $callback = sub {
537 my ($tag, $row, $col, $colName, $table) = @_;
538 if ($row &gt;=0 &amp;&amp; $colName eq &#39;UnitPrice&#39;) {
539 $tag-&gt;{&#39;style&#39;} = &#39;background-color:&#39;. (($table-&gt;elm($row, $col)&gt;=20) ? &#39;#fc8d59&#39;:&#39;#91bfdb&#39;) . &#39;;&#39;;
540 }
541 if ($row &gt;=0 &amp;&amp; $colName eq &#39;Discontinued&#39;) {
542 $tag-&gt;{&#39;style&#39;} = &#39;background-color:&#39;. (($table-&gt;elm($row, $col) eq &#39;TRUE&#39;) ? &#39;#999999&#39;:&#39;#af8dc3&#39;) .&#39;;&#39;;
543 }
544 return $tag;
545 };
546
547 print $t-&gt;html(undef, undef, undef, undef, undef, undef, $callback);</code></pre>
548
549 <p>Attention: You will have to escape HTML-Entities yourself (for example &#39;&lt;&#39; as &#39;&amp;lt;&#39;), if you have characters in you table which need to be escaped. You can do this for example with the escapeHTML-function from CGI.pm (or the HTML::Entities module).</p>
550
551 <pre><code> use CGI qw(escapeHTML);
552 [...]
553 $t-&gt;colMap($columnname, sub{escapeHTML($_)}); # for every column, where HTML-Entities occur.</code></pre>
554
555 </dd>
556 <dt id="string-table::html2-colors-D4D4BF-ECECE4-CCCC99-specs-name-border-1">string table::html2 ($colors = [&quot;#D4D4BF&quot;,&quot;#ECECE4&quot;,&quot;#CCCC99&quot;], $specs = {&#39;name&#39; =&gt; &#39;&#39;, &#39;border&#39; =&gt; &#39;1&#39;, ...})</dt>
557 <dd>
558
559 <p>This method is deprecated. It&#39;s here for compatibility. It now simple call html method with $portrait = 0, see previous description.</p>
560
561 <p>return a string corresponding to a &quot;Landscape&quot; html-tagged table. This is useful to present a table with many columns, but very few entries. Check the above table::html for parameter descriptions.</p>
562
563 </dd>
564 <dt id="string-table::wiki">string table::wiki(...)</dt>
565 <dd>
566
567 <p>This method accepts the same parameters as table::html, returns a wikitable instead.</p>
568
569 </dd>
570 <dt id="string-table::wiki2">string table::wiki2(...)</dt>
571 <dd>
572
573 <p>This method accepts the same parameters as table::html2, returns a wikitable instead in landscape orientation.</p>
574
575 </dd>
576 </dl>
577
578 <h2 id="Table-Operations">Table Operations</h2>
579
580 <dl>
581
582 <dt id="int-table::setElm-rowIdx-colID-val">int table::setElm ($rowIdx, $colID, $val)</dt>
583 <dd>
584
585 <p>modify the value of a table element at [$rowIdx, $colID] to a new value $val. It returns 1 upon success, undef otherwise. In 1.68, setElm can manipulate multiple elements, i.e., $rowIdx and $colIdx can be references to an index array, and setElm() will modifies all cells defined by the grid.</p>
586
587 <pre><code> $t-&gt;setElm([0..2], [&#39;ColA&#39;, &#39;ColB&#39;], &#39;new value&#39;);
588 $t-&gt;setElm(0, [1..2], &#39;new value&#39;);
589
590 # puts a limit on the price of all expensive items
591 $t_product-&gt;match_pattern_hash(&#39;$_{UnitPrice} &gt; 20&#39;);
592 $t_product-&gt;setElm($t_product-&gt;{MATCH}, &#39;UnitPrice&#39;, 20);</code></pre>
593
594 </dd>
595 <dt id="int-table::addRow-rowRef-rowIdx-table::nofRow-arg_ref-addNewCol-0">int table::addRow ($rowRef, $rowIdx = table::nofRow, $arg_ref = {addNewCol =&gt; 0})</dt>
596 <dd>
597
598 <p>add a new row ($rowRef may point to the actual list of scalars, or it can be a hash_ref (supported since version 1.60)). If $rowRef points to a hash, the method will lookup the value of a field by ts column name: $rowRef-&gt;{colName}, if not found, undef is used for that field. The new row will be referred as $rowIdx as the result. E.g., addRow($aRow, 0) will put the new row as the very first row. By default, it appends a row to the end. In 1.67, we support {addNewCol =&gt; 1}, if specified, a new column will be automatically created for each new element encountered in the $rowRef.</p>
599
600 <pre><code> # automatically add a new column &quot;aNewColumn&quot; to $t, in order to hold the new value
601 $t-&gt;addRow({anExistingColumn =&gt; 123, aNewColumn =&gt; &quot;XYZ&quot;}, undef, {addNewCol =&gt; 1});
602 # $t only had one column, after this call, it will contain a new column &#39;col2&#39;, in order to hold the new value
603 $t-&gt;addRow([123, &quot;XYZ&quot;], undef, {addNewCol =&gt; 1});</code></pre>
604
605 <p>It returns 1 upon success, undef otherwise.</p>
606
607 </dd>
608 <dt id="refto_array-table::delRow-rowIdx">refto_array table::delRow ( $rowIdx )</dt>
609 <dd>
610
611 <p>delete a row at $rowIdx. It will the reference to the deleted row.</p>
612
613 </dd>
614 <dt id="refto_array-table::delRows-rowIdcsRef">refto_array table::delRows ( $rowIdcsRef )</dt>
615 <dd>
616
617 <p>delete rows in @$rowIdcsRef. It will return an array of deleted rows in the same order of $rowIdcsRef upon success. upon success.</p>
618
619 </dd>
620 <dt id="int-table::addCol-colRef-colName-colIdx-numCol">int table::addCol ($colRef, $colName, $colIdx = numCol)</dt>
621 <dd>
622
623 <p>add a new column ($colRef points to the actual data), the new column will be referred as $colName or $colIdx as the result. E.g., addCol($aCol, &#39;newCol&#39;, 0) will put the new column as the very first column. By default, append a column to the end. It will return 1 upon success or undef otherwise. In 1.68, $colRef can be a scalar, which is the default value that can be used to create the new column. E.g., to create a new column with default value of undef, 0, &#39;default&#39;, respectively, one can do:</p>
624
625 <pre><code> $t-&gt;addCol(undef, &#39;NewCol&#39;);
626 $t-&gt;addCol(0, &#39;NewIntCol&#39;);
627 $t-&gt;addCol(&#39;default&#39;, &#39;NewStringCol&#39;);</code></pre>
628
629 </dd>
630 <dt id="refto_array-table::delCol-colID">refto_array table::delCol ($colID)</dt>
631 <dd>
632
633 <p>delete a column at $colID return the reference to the deleted column.</p>
634
635 </dd>
636 <dt id="arrayof_refto_array-table::delCols-colIDsRef">arrayof_refto_array table::delCols ($colIDsRef)</dt>
637 <dd>
638
639 <p>delete a list of columns, pointed by $colIDsRef. It will return an array of deleted columns in the same order of $colIDsRef upon success.</p>
640
641 </dd>
642 <dt id="refto_array-table::rowRef-rowIdx">refto_array table::rowRef ($rowIdx)</dt>
643 <dd>
644
645 <p>return a reference to the row at $rowIdx upon success or undef otherwise.</p>
646
647 </dd>
648 <dt id="refto_arrayof_refto_array-table::rowRefs-rowIdcsRef">refto_arrayof_refto_array table::rowRefs ($rowIdcsRef)</dt>
649 <dd>
650
651 <p>return a reference to array of row references upon success, undef otherwise.</p>
652
653 </dd>
654 <dt id="array-table::row-rowIdx">array table::row ($rowIdx)</dt>
655 <dd>
656
657 <p>return a copy of the row at $rowIdx upon success or undef otherwise.</p>
658
659 </dd>
660 <dt id="refto_hash-table::rowHashRef-rowIdx">refto_hash table::rowHashRef ($rowIdx)</dt>
661 <dd>
662
663 <p>return a reference to a hash, which contains a copy of the row at $rowIdx, upon success or undef otherwise. The keys in the hash are column names, and the values are corresponding elements in that row. The hash is a copy, therefore modifying the hash values doesn&#39;t change the original table.</p>
664
665 </dd>
666 <dt id="refto_array-table::colRef-colID">refto_array table::colRef ($colID)</dt>
667 <dd>
668
669 <p>return a reference to the column at $colID upon success.</p>
670
671 </dd>
672 <dt id="refto_arrayof_refto_array-table::colRefs-colIDsRef">refto_arrayof_refto_array table::colRefs ($colIDsRef)</dt>
673 <dd>
674
675 <p>return a reference to array of column references upon success.</p>
676
677 </dd>
678 <dt id="array-table::col-colID">array table::col ($colID)</dt>
679 <dd>
680
681 <p>return a copy to the column at $colID upon success or undef otherwise.</p>
682
683 </dd>
684 <dt id="int-table::rename-colID-newName">int table::rename ($colID, $newName)</dt>
685 <dd>
686
687 <p>rename the column at $colID to a $newName (the newName must be valid, and should not be identical to any other existing column names). It returns 1 upon success or undef otherwise.</p>
688
689 </dd>
690 <dt id="refto_array-table::replace-oldColID-newColRef-newName">refto_array table::replace ($oldColID, $newColRef, $newName)</dt>
691 <dd>
692
693 <p>replace the column at $oldColID by the array pointed by $newColRef, and renamed it to $newName. $newName is optional if you don&#39;t want to rename the column. It returns 1 upon success or undef otherwise.</p>
694
695 </dd>
696 <dt id="int-table::swap-colID1-colID2">int table::swap ($colID1, $colID2)</dt>
697 <dd>
698
699 <p>swap two columns referred by $colID1 and $colID2. It returns 1 upon success or undef otherwise.</p>
700
701 </dd>
702 <dt id="int-table::moveCol-colID-colIdx-newColName">int table::moveCol($colID, $colIdx, $newColName)</dt>
703 <dd>
704
705 <p>move column referred by $colID to a new location $colIdx. If $newColName is specified, the column will be renamed as well. It returns 1 upon success or undef otherwise.</p>
706
707 </dd>
708 <dt id="int-table::reorder-colIDRefs-arg_ref">int table::reorder($colIDRefs, $arg_ref)</dt>
709 <dd>
710
711 <p>Rearrange the columns according to the order specified in $colIDRef. Columns not specified in the reference array will be appended to the end! If one would like to drop columns not specified, set $arg_ref to {keepRest =&gt; 0}. reorder() changes the table itself, while subTable(undef, $colIDRefs) will return a new table. reorder() might also runs faster than subTable, as elements may not need to be copied.</p>
712
713 </dd>
714 <dt id="int-table::colMap-colID-fun">int table::colMap ($colID, $fun)</dt>
715 <dd>
716
717 <p>foreach element in column $colID, map a function $fun to it. It returns 1 upon success or undef otherwise. This is a handy way to format a column. E.g. if a column named URL contains URL strings, colMap(&quot;URL&quot;, sub {&quot;&lt;a href=&#39;$_&#39;&gt;$_&lt;/a&gt;&quot;}) before html() will change each URL into a clickable hyper link while displayed in a web browser.</p>
718
719 </dd>
720 <dt id="int-table::colsMap-fun">int table::colsMap ($fun)</dt>
721 <dd>
722
723 <p>foreach row in the table, map a function $fun to it. It can do whatever colMap can do and more. It returns 1 upon success or undef otherwise. colMap function only give $fun access to the particular element per row, while colsMap give $fun full access to all elements per row. E.g. if two columns named duration and unit ([&quot;2&quot;, &quot;hrs&quot;], [&quot;30&quot;, &quot;sec&quot;]). colsMap(sub {$_-&gt;[0] .= &quot; (&quot;.$_-&gt;[1].&quot;)&quot;; } will change each row into ([&quot;2 hrs&quot;, &quot;hrs&quot;], [&quot;30 sec&quot;, &quot;sec&quot;]). As show, in the $func, a column element should be referred as $_-&gt;[$colIndex].</p>
724
725 </dd>
726 <dt id="int-table::sort-colID1-type1-order1-colID2-type2-order2">int table::sort($colID1, $type1, $order1, $colID2, $type2, $order2, ... )</dt>
727 <dd>
728
729 <p>sort a table in place. First sort by column $colID1 in $order1 as $type1, then sort by $colID2 in $order2 as $type2, ... $type is 0 for numerical and 1 for others; $order is 0 for ascending and 1 for descending;</p>
730
731 <p>In 1.62, instead of memorize these numbers, you can use constants instead (notice constants do not start with &#39;$&#39;). Data::Table::NUMBER Data::Table::STRING Data::Table::ASC Data::Table::DESC</p>
732
733 <p>Sorting is done in the priority of colID1, colID2, ... It returns 1 upon success or undef otherwise. Notice the table is rearranged as a result! This is different from perl&#39;s list sort, which returns a sorted copy while leave the original list untouched, the authors feel inplace sorting is more natural.</p>
734
735 <p>table::sort can take a user supplied operator, this is useful when neither numerical nor alphabetic order is correct.</p>
736
737 <pre><code> $Well=[&quot;A_1&quot;, &quot;A_2&quot;, &quot;A_11&quot;, &quot;A_12&quot;, &quot;B_1&quot;, &quot;B_2&quot;, &quot;B_11&quot;, &quot;B_12&quot;];
738 $t = Data::Table-&gt;new([$Well], [&quot;PlateWell&quot;], 1);
739 $t-&gt;sort(&quot;PlateWell&quot;, 1, 0);
740 print join(&quot; &quot;, $t-&gt;col(&quot;PlateWell&quot;));
741 # prints: A_1 A_11 A_12 A_2 B_1 B_11 B_12 B_2
742 # in string sorting, &quot;A_11&quot; and &quot;A_12&quot; appears before &quot;A_2&quot;;
743 my $my_sort_func = sub {
744 my @a = split /_/, $_[0];
745 my @b = split /_/, $_[1];
746 my $res = ($a[0] cmp $b[0]) || (int($a[1]) &lt;=&gt; int($b[1]));
747 };
748 $t-&gt;sort(&quot;PlateWell&quot;, $my_sort_func, 0);
749 print join(&quot; &quot;, $t-&gt;col(&quot;PlateWell&quot;));
750 # prints the correct order: A_1 A_2 A_11 A_12 B_1 B_2 B_11 B_12</code></pre>
751
752 </dd>
753 <dt id="table-table::match_pattern-pattern-countOnly">table table::match_pattern ($pattern, $countOnly)</dt>
754 <dd>
755
756 <p>return a new table consisting those rows evaluated to be true by $pattern upon success or undef otherwise. If $countOnly is set to 1, it simply returns the number of rows that matches the string without making a new copy of table. $countOnly is 0 by default.</p>
757
758 <p>Side effect: @Data::Table::OK (should use $t-&gt;{OK} after 1.62) stores a true/false array for the original table rows. Using it, users can find out what are the rows being selected/unselected. Side effect: @Data::Table::MATCH stores a reference to an array containing all row indices for matched rows.</p>
759
760 <p>In the $pattern string, a column element should be referred as $_-&gt;[$colIndex]. E.g., match_pattern(&#39;$_-&gt;[0]&gt;3 &amp;&amp; $_-&gt;[1]=~/^L&#39;) retrieve all the rows where its first column is greater than 3 and second column starts with letter &#39;L&#39;. Notice it only takes colIndex, column names are not acceptable here!</p>
761
762 </dd>
763 <dt id="table-table::match_pattern_hash-pattern-countOnly">table table::match_pattern_hash ($pattern, $countOnly)</dt>
764 <dd>
765
766 <p>return a new table consisting those rows evaluated to be true by $pattern upon success or undef otherwise. If $countOnly is set to 1, it simply returns the number of rows that matches the string without making a new copy of table. $countOnly is 0 by default.</p>
767
768 <p>Side effect: @Data::Table::OK stores a reference to a true/false array for the original table rows. Using it, users can find out what are the rows being selected/unselected. Side effect: @Data::Table::MATCH stores a reference to an array containing all row indices for matched rows.</p>
769
770 <p>In the $pattern string, a column element should be referred as ${column_name}. match_pattern_hash() is added in 1.62. The difference between this method and match_pattern is each row is fed to the pattern as a hash %_. In the case of match_pattern, each row is fed as an array ref $_. The pattern for match_pattern_hash() becomes much cleaner.</p>
771
772 <p>If a table has two columns: Col_A as the 1st column and Col_B as the 2nd column, a filter &quot;Col_A &gt; 2 AND Col_B &lt; 2&quot; is written before as $t-&gt;match_pattern(&#39;$_-&gt;[0] &gt; 2 &amp;&amp; $_-&gt;[1] &lt;2&#39;); where we need to figure out $t-&gt;colIndex(&#39;Col_A&#39;) is 0 and $t-&gt;colIndex(&#39;Col_B&#39;) is 1, in order to build the pattern. Now you can use column name directly in the pattern: $t-&gt;match_pattern_hash(&#39;$_{Col_A} &gt;2 &amp;&amp; $_{Col_B} &lt;2&#39;); This method creates $t-&gt;{OK}, as well as @Data::Table::OK, same as match_pattern().</p>
773
774 <p>Simple boolean operators such as and/or can be directly put into the pattern string. More complex logic can also be supported in the example below:</p>
775
776 <pre><code> my $t= Data::Table-&gt;new([[2,5,&#39;Jan&#39;], [1,6,&#39;Feb&#39;], [-3,2,&#39;Apr&#39;], [6,-4,&#39;Dec&#39;]], [&#39;X&#39;,&#39;Y&#39;,&#39;Month&#39;], 0);
777 # we need to use our instead of my, so that %Q1 is accessible within match_pattern_hash
778 our %Q1 = (&#39;Jan&#39;=&gt;1, &#39;Feb&#39;=&gt;1, &#39;Mar&#39;=&gt;1);
779 # find records belongin to Q1 months, we need to use %::Q1 to access the Q1 defined outside Data::Table
780 $t2=$t-&gt;match_pattern_hash(&#39;exists $::Q1{$_{Month}}&#39;);</code></pre>
781
782 <p>similarly, subroutines can be accessed inside match_pattern_hash using &quot;::&quot;:</p>
783
784 <pre><code> sub in_Q1 {
785 my $x = shift;
786 return ($x eq &#39;Jan&#39; or $x eq &#39;Feb&#39; or $x eq &#39;Mar&#39;);
787 }
788 $t2=$t-&gt;match_pattern_hash(&#39;::in_Q1($_{Month})&#39;);</code></pre>
789
790 <p>However, such usage is discouraged, as match_pattern_hash() does not throw errors when the pattern is invalid. For complex filtering logic, we strongly recommend you stick to row-based looping.</p>
791
792 </dd>
793 <dt id="table-table::match_string-s-caseIgnore-countOnly">table table::match_string ($s, $caseIgnore, $countOnly)</dt>
794 <dd>
795
796 <p>return a new table consisting those rows contains string $s in any of its fields upon success, undef otherwise. if $caseIgnore evaluated to true, case will is be ignored (s/$s/i). If $countOnly is set to 1, it simply returns the number of rows that matches the string without making a new copy of table. $countOnly is 0 by default.</p>
797
798 <p>Side effect: @Data::Table::OK stores a reference to a true/false array for the original table rows. Side effect: @Data::Table::MATCH stores a reference to an array containing all row indices for matched rows. Using it, users can find out what are the rows being selected/unselected. The $s string is actually treated as a regular expression and applied to each row element, therefore one can actually specify several keywords by saying, for instance, match_string(&#39;One|Other&#39;).</p>
799
800 </dd>
801 <dt id="table-table::rowMask-mask-complement">table table::rowMask($mask, $complement)</dt>
802 <dd>
803
804 <p>mask is reference to an array, where elements are evaluated to be true or false. The size of the mask must be equal to the nofRow of the table. return a new table consisting those rows where the corresponding mask element is true (or false, when complement is set to true).</p>
805
806 <p>E.g., $t1=$tbl-&gt;match_string(&#39;keyword&#39;); $t2=$tbl-&gt;rowMask(\@Data::Table::OK, 1) creates two new tables. $t1 contains all rows match &#39;keyword&#39;, while $t2 contains all other rows.</p>
807
808 <p>mask is reference to an array, where elements are evaluated to be true or false. The size of the mask must be equal to the nofRow of the table. return a new table consisting those rows where the corresponding mask element is true (or false, when complement is set to true).</p>
809
810 <p>E.g., $t1=$tbl-&gt;match_string(&#39;keyword&#39;); $t2=$tbl-&gt;rowMask(\@Data::Table::OK, 1) creates two new tables. $t1 contains all rows match &#39;keyword&#39;, while $t2 contains all other rows.</p>
811
812 </dd>
813 <dt id="table-table::iterator-reverse-0">table table::iterator({$reverse =&gt; 0})</dt>
814 <dd>
815
816 <p>Returns a reference to a enumerator routine, which enables one to loop through each table row. If $reverse is set to 1, it will enumerate backward. The convenience here is each row is fetch as a rowHashRef, so one can easily access row elements by name.</p>
817
818 <pre><code> my $next = $t_product-&gt;iterator();
819 while (my $row = $next-&gt;()) {
820 # have access to a row as a hash reference, access row number by &amp;$next(1);
821 $t_product-&gt;setElm($next-&gt;(1), &#39;ProductName&#39;, &#39;New! &#39;.$row-&gt;{ProductName});
822 }</code></pre>
823
824 <p>In this example, each $row is fetched as a hash reference, so one can access the elements by $row-&gt;{colName}. Be aware that the elements in the hash is a copy of the original table elements, so modifying $row-&gt;{colName} does not modify the original table. If table modification is intended, one needs to obtain the row index of the returned row. $next-&gt;(1) call with a non-empty argument returns the row index of the record that was previously fetched with $next-&gt;(). In this example, one uses the row index to modify the original table.</p>
825
826 </dd>
827 <dt id="table-table::each_group-colsToGroupBy-funsToApply">table table::each_group($colsToGroupBy, $funsToApply)</dt>
828 <dd>
829
830 <p>Primary key columns are specified in $colsToGroupBy. All rows are grouped by primary keys first (keys sorted as string). Then for each group, subroutines $funToAppy is applied to corresponding rows. $funToApply are passed with two parameters ($tableRef, $rowIDsRef). All rows sharing the key are passed in as a Data::Table object (with all columns and in the order of ascending row index) in the first parameter. The second optional parameter contains an array of row indices of the group members. Since all rows in the passed-in table contains the same keys, the key value can be obtained from its first table row.</p>
831
832 </dd>
833 <dt id="table-table::group-colsToGroupBy-colsToCalculate-funsToApply-newColNames-keepRestCols">table table::group($colsToGroupBy, $colsToCalculate, $funsToApply, $newColNames, $keepRestCols)</dt>
834 <dd>
835
836 <p>Primary key columns are specified in $colsToGroupBy. All rows are grouped by primary keys first. Then for each group, an array of subroutines (in $funsToAppy) are applied to corresponding columns and yield a list of new columns (specified in $newColNames).</p>
837
838 <p>$colsToGroupBy, $colsToCalculate are references to array of colIDs. $funsToApply is a reference to array of subroutine references. $newColNames are a reference to array of new column name strings. If specified, the size of arrays pointed by $colsToCalculate, $funsToApply and $newColNames should be i dentical. A column may be used more than once in $colsToCalculate.</p>
839
840 <p>$keepRestCols is default to 1 (was introduced as 0 in 1.64, changed to 1 in 1.66 for backward compatibility) introduced in 1.64), otherwise, the remaining columns are returned with the first encountered value of that group.</p>
841
842 <p>E.g., an employee salary table $t contains the following columns: Name, Sex, Department, Salary. (see examples in the SYNOPSIS)</p>
843
844 <pre><code> $t2 = $t-&gt;group([&quot;Department&quot;,&quot;Sex&quot;],[&quot;Name&quot;, &quot;Salary&quot;], [sub {scalar @_}, \&amp;average], [&quot;Nof Employee&quot;, &quot;Average Salary&quot;], 0);</code></pre>
845
846 <p>Department, Sex are used together as the primary key columns, a new column &quot;Nof Employee&quot; is created by counting the number of employee names in each group, a new column &quot;Average Salary&quot; is created by averaging the Salary data falled into each group. As the result, we have the head count and average salary information for each (Department, Sex) pair. With your own functions (such as sum, product, average, standard deviation, etc), group method is very handy for accounting purpose. If primary key columns are not defined, all records will be treated as one group.</p>
847
848 <pre><code> $t2 = $t-&gt;group(undef,[&quot;Name&quot;, &quot;Salary&quot;], [sub {scalar @_}, \&amp;average], [&quot;Nof Employee&quot;, &quot;Average Salary&quot;], 0);</code></pre>
849
850 <p>The above statement will output the total number of employees and their average salary as one line.</p>
851
852 </dd>
853 <dt id="table-table::pivot-colToSplit-colToSplitIsStringOrNumeric-colToFill-colsToGroupBy-keepRestCols">table table::pivot($colToSplit, $colToSplitIsStringOrNumeric, $colToFill, $colsToGroupBy, $keepRestCols)</dt>
854 <dd>
855
856 <p>Every unique values in a column (specified by $colToSplit) become a new column. undef value become &quot;NULL&quot;. $colToSplitIsStringOrNumeric is set to numeric (0 or Data::Table:NUMBER), the new column names are prefixed by &quot;oldColumnName=&quot;. The new cell element is filled by the value specified by $colToFill (was 1/0 before version 1.63).</p>
857
858 <p>Note: yes, it seems I made an incompatible change in version 1.64, where $colToSplitIsStringOrNumber used to be $colToSplitIsNumeric, where 0 meant STRING and 1 meant NUMBER. Now it is opposite. However, I also added auto-type detection code, that this parameter essentially is auto-guessed and most old code should behave the same as before.</p>
859
860 <p>When primary key columns are specified by $colsToGroupBy, all records sharing the same primary key collapse into one row, with values in $colToFill filling the corresponding new columns. If $colToFill is not specified, a cell is filled with the number of records fall into that cell.</p>
861
862 <p>$colToSplit and $colToFill are colIDs. $colToSplitIsNumeric is 1/0. $colsToGroupBy is a reference to array of colIDs. $keepRestCols is 1/0, by default is 0. If $keepRestCols is off, only primary key columns and new columns are exported, otherwise, all the rest columns are exported as well.</p>
863
864 <p>E.g., applying pivot method to the resultant table of the example of the group method.</p>
865
866 <pre><code> $t2-&gt;pivot(&quot;Sex&quot;, 0, &quot;Average Salary&quot;,[&quot;Department&quot;]);</code></pre>
867
868 <p>This creates a 2x3 table, where Departments are use as row keys, Sex (female and male) become two new columns. &quot;Average Salary&quot; values are used to fill the new table elements. Used together with group method, pivot method is very handy for accounting type of analysis. If $colsToGroupBy is left as undef, all rows are treated as one group. If $colToSplit is left as undef, the method will generate a column named &quot;(all)&quot; that matches all records share the corresponding primary key.</p>
869
870 </dd>
871 <dt id="table-table::melt-keyCols-variableCols-arg_ref">table table::melt($keyCols, $variableCols, $arg_ref)</dt>
872 <dd>
873
874 <p>The idea of melt() and cast() are taken from Hadley Wickham&#39;s Reshape package in R language. A table is first melt() into a tall-skiny format, where measurements are stored in the format of a variable-value pair per row. Such a format can then be easily cast() into various contingency tables.</p>
875
876 <p>One needs to specify the columns consisting of primary keys, columns that are consider as variable columns. The output variable column is named &#39;variable&#39; unless specified by $arg_ref{variableColName}. The output value column is named &#39;value&#39;, unless specified in $arg_ref{valueColName}. By default NULL values are not output, unless $arg_ref{skip_NULL} is set to false. By default empty string values are kept, unless one sets skip_empty to `.</p>
877
878 <pre><code> For each object (id), we measure variable x1 and x2 at two time points
879 $t = new Data::Table([[1,1,5,6], [1,2,3,5], [2,1,6,1], [2,2,2,4]], [&#39;id&#39;,&#39;time&#39;,&#39;x1&#39;,&#39;x2&#39;], Data::Table::ROW_BASED);
880 # id time x1 x2
881 # 1 1 5 6
882 # 1 2 3 5
883 # 2 1 6 1
884 # 2 2 2 4
885
886 # melting a table into a tall-and-skinny table
887 $t2 = $t-&gt;melt([&#39;id&#39;,&#39;time&#39;]);
888 #id time variable value
889 # 1 1 x1 5
890 # 1 1 x2 6
891 # 1 2 x1 3
892 # 1 2 x2 5
893 # 2 1 x1 6
894 # 2 1 x2 1
895 # 2 2 x1 2
896 # 2 2 x2 4
897
898 # casting the table, &amp;average is a method to calculate mean
899 # for each object (id), we calculate average value of x1 and x2 over time
900 $t3 = $t2-&gt;cast([&#39;id&#39;],&#39;variable&#39;,Data::Table::STRING,&#39;value&#39;, \&amp;average);
901 # id x1 x2
902 # 1 4 5.5
903 # 2 4 2.5
904 </code></pre>
905
906 </dd>
907 <dt id="table-table::cast-colsToGroupBy-colToSplit-colToSplitIsStringOrNumeric-colToCalculate-funToApply">table table::cast($colsToGroupBy, $colToSplit, $colToSplitIsStringOrNumeric, $colToCalculate, $funToApply)</dt>
908 <dd>
909
910 <p>see melt(), as melt() and cast() are meant to use together.</p>
911
912 <p>The table has been melten before. cast() group the table according to primary keys specified in $colsToGroupBy. For each group of objects sharing the same id, it further groups values (specified by $colToCalculate) according to unique variable names (specified by $colToSplit). Then it applies subroutine $funToApply to obtain an aggregate value. For the output, each unique primary key will be a row, each unique variable name will become a column, the cells are the calculated aggregated value.</p>
913
914 <p>If $colsToGroupBy is undef, all rows are treated as within the same group. If $colToSplit is undef, a new column &quot;(all)&quot; is used to hold the results.</p>
915
916 <pre><code> $t = Data::Table-&gt;new( # create an employ salary table
917 [
918 [&#39;Tom&#39;, &#39;male&#39;, &#39;IT&#39;, 65000],
919 [&#39;John&#39;, &#39;male&#39;, &#39;IT&#39;, 75000],
920 [&#39;Tom&#39;, &#39;male&#39;, &#39;IT&#39;, 65000],
921 [&#39;John&#39;, &#39;male&#39;, &#39;IT&#39;, 75000],
922 [&#39;Peter&#39;, &#39;male&#39;, &#39;HR&#39;, 85000],
923 [&#39;Mary&#39;, &#39;female&#39;, &#39;HR&#39;, 80000],
924 [&#39;Nancy&#39;, &#39;female&#39;, &#39;IT&#39;, 55000],
925 [&#39;Jack&#39;, &#39;male&#39;, &#39;IT&#39;, 88000],
926 [&#39;Susan&#39;, &#39;female&#39;, &#39;HR&#39;, 92000]
927 ],
928 [&#39;Name&#39;, &#39;Sex&#39;, &#39;Department&#39;, &#39;Salary&#39;], Data::Table::ROW_BASED);
929
930 # get a Department x Sex contingency table, get average salary across all four groups
931 print $t-&gt;cast([&#39;Department&#39;], &#39;Sex&#39;, Data::Table::STRING, &#39;Salary&#39;, \&amp;average)-&gt;csv(1);
932 Department,female,male
933 IT,55000,73600
934 HR,86000,85000
935 # get average salary for each department
936 print $t-&gt;cast([&#39;Department&#39;], undef, Data::Table::STRING, &#39;Salary&#39;, \&amp;average)-&gt;csv(1);
937 Department,(all)
938 IT,70500
939 HR,85666.6666666667
940
941 # get average salary for each gender
942 print $t-&gt;cast([&#39;Sex&#39;], undef, Data::Table::STRING, &#39;Salary&#39;, \&amp;average)-&gt;csv(1);
943 Sex,(all)
944 male,75500
945 female,75666.6666666667
946
947 # get average salary for all records
948 print $t-&gt;cast(undef, undef, Data::Table::STRING, &#39;Salary&#39;, \&amp;average)-&gt;csv(1);
949 (all)
950 75555.5555555556</code></pre>
951
952 </dd>
953 </dl>
954
955 <h2 id="Table-Table-Manipulations">Table-Table Manipulations</h2>
956
957 <dl>
958
959 <dt id="int-table::rowMerge-tbl-argRef">int table::rowMerge ($tbl, $argRef)</dt>
960 <dd>
961
962 <p>Append all the rows in the table object $tbl to the original rows. Before 1.62, the merging table $tbl must have the same number of columns as the original, as well as the columns are in exactly the same order. It returns 1 upon success, undef otherwise. The table object $tbl should not be used afterwards, since it becomes part of the new table.</p>
963
964 <p>Since 1.62, you may provide {byName =&gt;1, addNewCol=&gt;1} as $argRef. If byName is set to 1, the columns in in $tbl do not need to be in the same order as they are in the first table, instead the column name is used for the matching. If addNewCol is set to 1, if $tbl contains a new column name that does not already exist in the first table, this new column will be automatically added to the resultant table. Typically, you want to specify there two options simultaneously.</p>
965
966 </dd>
967 <dt id="int-table::colMerge-tbl-argRef">int table::colMerge ($tbl, $argRef)</dt>
968 <dd>
969
970 <p>Append all the columns in table object $tbl to the original columns. Table $tbl must have the same number of rows as the original. It returns 1 upon success, undef otherwise. Table $tbl should not be used afterwards, since it becomes part of the new table.</p>
971
972 <p>Since 1.62, you can specify {renameCol =&gt; 1} as $argRef. This is to auto fix any column name collision. If $tbl contains a column that already exists in the first table, it will be renamed (by a suffix _2) to avoid the collision.</p>
973
974 </dd>
975 <dt id="table-table::join-tbl-type-cols1-cols2-argRef">table table::join ($tbl, $type, $cols1, $cols2, $argRef)</dt>
976 <dd>
977
978 <p>Join two tables. The following join types are supported (defined by $type):</p>
979
980 <p>0: inner join 1: left outer join 2: right outer join 3: full outer join</p>
981
982 <p>In 1.62, instead of memorize these numbers, you can use constants instead (notice constants do not start with &#39;$&#39;). Data::Table::INNER_JOIN Data::Table::LEFT_JOIN Data::Table::RIGHT_JOIN Data::Table::FULL_JOIN</p>
983
984 <p>$cols1 and $cols2 are references to array of colIDs, where rows with the same elements in all listed columns are merged. As the result table, columns listed in $cols2 are deleted, before a new table is returned.</p>
985
986 <p>The implementation is hash-join, the running time should be linear with respect to the sum of number of rows in the two tables (assume both tables fit in memory).</p>
987
988 <p>If the non-key columns of the two tables share the same name, the routine will fail, as the result table cannot contain two columns of the same name. In 1.62, one can specify {renameCol=&gt;1} as $argRef, so that the second column will be automatically renamed (with suffix _2) to avoid collision.</p>
989
990 <p>If you would like to treat the NULLs in the key columns as empty string, set {NULLasEmpty =&gt; 1}. If you do not want to treat NULLs as empty strings, but you still like the NULLs in two tables to be considered as equal (but not equal to &#39;&#39;), set {matchNULL =&gt; 1}. Obviously if NULLasEmpty is set to 1, matchNULL will have no effect.</p>
991
992 </dd>
993 </dl>
994
995 <h2 id="Internal-Methods">Internal Methods</h2>
996
997 <p>All internal methods are mainly implemented for used by other methods in the Table class. Users should avoid using them. Nevertheless, they are listed here for developers who would like to understand the code and may derive a new class from Data::Table.</p>
998
999 <dl>
1000
1001 <dt id="int-table::rotate">int table::rotate</dt>
1002 <dd>
1003
1004 <p>convert the internal structure of a table between row-based and column-based. return 1 upon success, undef otherwise.</p>
1005
1006 </dd>
1007 <dt id="string-csvEscape-string-delimiter-qualifier">string csvEscape($string, {delimiter=&gt;, qualifier})</dt>
1008 <dd>
1009
1010 <p>Encode a scalar into a CSV-formatted field.</p>
1011
1012 <p>optional named arguments: delimiter and qualifier, in case user wants to use characters other than the defaults. The default delimiter and qualifier is taken from $Data::Table::DEFAULTS{&#39;CSV_DELIMITER&#39;} (defaults to &#39;,&#39;) and $Data::Table::DEFAULTS{&#39;CSV_QUALIFIER&#39;} (defaults to &#39;&quot;&#39;), respectively.</p>
1013
1014 <p>Please note that this function only escape one element in a table. To escape the whole table row, you need to join($delimiter, map {csvEscape($_)} @row . $endl; $endl refers to End-of-Line, which you may or may not want to add, and it is OS-dependent. Therefore, csvEscape method is kept to the simplest form as an element transformer.</p>
1015
1016 </dd>
1017 <dt id="refto_array-parseCSV-string">refto_array parseCSV($string)</dt>
1018 <dd>
1019
1020 <p>Break a CSV encoded string to an array of scalars (check it out, we did it the cool way).</p>
1021
1022 <p>optional argument size: specify the expected number of fields after csv-split. optional named arguments: delimiter and qualifier, in case user wants to use characters other than the defaults. respectively. The default delimiter and qualifier is taken from $Data::Table::DEFAULTS{&#39;CSV_DELIMITER&#39;} (defaults to &#39;,&#39;) and $Data::Table::DEFAULTS{&#39;CSV_QUALIFIER&#39;} (defaults to &#39;&quot;&#39;), respectively.</p>
1023
1024 </dd>
1025 <dt id="string-tsvEscape-rowRef">string tsvEscape($rowRef)</dt>
1026 <dd>
1027
1028 <p>Encode a scalar into a TSV-formatted string.</p>
1029
1030 </dd>
1031 </dl>
1032
1033 <h1 id="TSV-FORMAT">TSV FORMAT</h1>
1034
1035 <p>There is no standard for TSV format as far as we know. CSV format can&#39;t handle binary data very well, therefore, we choose the TSV format to overcome this limitation.</p>
1036
1037 <p>We define TSV based on MySQL convention.</p>
1038
1039 <pre><code> &quot;\0&quot;, &quot;\n&quot;, &quot;\t&quot;, &quot;\r&quot;, &quot;\b&quot;, &quot;&#39;&quot;, &quot;\&quot;&quot;, and &quot;\\&quot; are all escaped by &#39;\&#39; in the TSV file.
1040 (Warning: MySQL treats &#39;\f&#39; as &#39;f&#39;, and it&#39;s not escaped here)
1041 Undefined values are represented as &#39;\N&#39;.</code></pre>
1042
1043 <p>However, you can switch off this transformation by setting {transform_element =&gt; 0} in the fromTSV or tsv method. Before if a cell reads &#39;A line break is \n&#39;, it is read in as &#39;A link break is [return]&#39; in memory. When use tsv method to export, it is transformed back to &#39;A line break is \n&#39;. However, if it is exported as a csv, the [return] will break the format. Now if transform_element is set to 0, the cell is stored as &#39;A line break is \n&#39; in memory, so that csv export will be correct. However, do remember to set {transform_element =&gt; 0} in tsv export method, otherwise, the cell will become &#39;A line break is \\n&#39;. Be aware that trasform_element controls column headers as well.</p>
1044
1045 <h1 id="INTERFACE-TO-OTHER-SOFTWARES">INTERFACE TO OTHER SOFTWARES</h1>
1046
1047 <p>Spreadsheet is a very generic type, therefore Data::Table class provides an easy interface between databases, web pages, CSV/TSV files, graphics packages, etc.</p>
1048
1049 <p>Here is a summary (partially repeat) of some classic usages of Data::Table.</p>
1050
1051 <h2 id="Interface-to-Database-and-Web">Interface to Database and Web</h2>
1052
1053 <pre><code> use DBI;
1054
1055 $dbh= DBI-&gt;connect(&quot;DBI:mysql:test&quot;, &quot;test&quot;, &quot;&quot;) or die $DBI::errstr;
1056 my $minAge = 10;
1057 $t = Data::Table::fromSQL($dbh, &quot;select * from mytable where age &gt;= ?&quot;, [$minAge]);
1058 print $t-&gt;html;</code></pre>
1059
1060 <h2 id="Interface-to-CSV-TSV">Interface to CSV/TSV</h2>
1061
1062 <pre><code> $t = fromFile(&quot;mydata.csv&quot;); # after version 1.51
1063 $t = fromFile(&quot;mydata.tsv&quot;); # after version 1.51
1064
1065 $t = fromCSV(&quot;mydata.csv&quot;);
1066 $t-&gt;sort(1,1,0);
1067 print $t-&gt;csv;
1068
1069 Same for TSV</code></pre>
1070
1071 <h2 id="Interface-to-Excel-XLS-XLSX">Interface to Excel XLS/XLSX</h2>
1072
1073 <p>Read in two tables from NorthWind.xls file, writes them out to XLSX format. See Data::Table::Excel module for details.</p>
1074
1075 <pre><code> use Data::Table::Excel;
1076
1077 my ($tableObjects, $tableNames)=xls2tables(&quot;NorthWind.xls&quot;);
1078 $t_category = $tableObjects[0];
1079 $t_product = $tableObjects[1];
1080
1081 tables2xlsx(&quot;NorthWind.xlsx&quot;, [$t_category, $t_product]);</code></pre>
1082
1083 <h2 id="Interface-to-Graphics-Package">Interface to Graphics Package</h2>
1084
1085 <pre><code> use GD::Graph::points;
1086
1087 $graph = GD::Graph::points-&gt;new(400, 300);
1088 $t2 = $t-&gt;match(&#39;$_-&gt;[1] &gt; 20 &amp;&amp; $_-&gt;[3] &lt; 35.7&#39;);
1089 my $gd = $graph-&gt;plot($t-&gt;colRefs([0,2]));
1090 open(IMG, &#39;&gt;mygraph.png&#39;) or die $!;
1091 binmode IMG;
1092 print IMG $gd-&gt;png;
1093 close IMG;</code></pre>
1094
1095 <h1 id="AUTHOR">AUTHOR</h1>
1096
1097 <p>Copyright 1998-2008, Yingyao Zhou &amp; Guangzhou Zou. All rights reserved.</p>
1098
1099 <p>It was first written by Zhou in 1998, significantly improved and maintained by Zou since 1999. The authors thank Tong Peng and Yongchuang Tao for valuable suggestions. We also thank those who kindly reported bugs, some of them are acknowledged in the &quot;Changes&quot; file.</p>
1100
1101 <p>This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself.</p>
1102
1103 <p>Please send bug reports and comments to: easydatabase at gmail dot com. When sending bug reports, please provide the version of Table.pm, the version of Perl.</p>
1104
1105 <h1 id="SEE-ALSO">SEE ALSO</h1>
1106
1107 <pre><code> DBI, GD::Graph, Data::Table::Excel.</code></pre>
1108
1109
1110 </body>
1111
1112 </html>
1113
1114