2 /* vim: set expandtab sw=4 ts=4 sts=4: */
4 * CSV import plugin for phpMyAdmin
6 * @todo add an option for handling NULL values
7 * @package PhpMyAdmin-Import
10 if (! defined('PHPMYADMIN')) {
16 if ($plugin_param !== 'table') {
20 if (isset($plugin_list)) {
21 $plugin_list['csv'] = array(
25 array('type' => 'begin_group', 'name' => 'general_opts'),
26 array('type' => 'bool', 'name' => 'replace', 'text' => __('Replace table data with file')),
27 array('type' => 'bool', 'name' => 'ignore', 'text' => __('Do not abort on INSERT error')),
28 array('type' => 'text', 'name' => 'terminated', 'text' => __('Columns separated with:'), 'size' => 2, 'len' => 2),
29 array('type' => 'text', 'name' => 'enclosed', 'text' => __('Columns enclosed with:'), 'size' => 2, 'len' => 2),
30 array('type' => 'text', 'name' => 'escaped', 'text' => __('Columns escaped with:'), 'size' => 2, 'len' => 2),
31 array('type' => 'text', 'name' => 'new_line', 'text' => __('Lines terminated with:'), 'size' => 2),
33 'options_text' => __('Options'),
36 if ($plugin_param !== 'table') {
37 $plugin_list['csv']['options'][]
38 = array('type' => 'bool', 'name' => 'col_names', 'text' => __('The first line of the file contains the table column names <i>(if this is unchecked, the first line will become part of the data)</i>'));
40 $hint = new PMA_Message(__('If the data in each row of the file is not in the same order as in the database, list the corresponding column names here. Column names must be separated by commas and not enclosed in quotations.'));
41 $plugin_list['csv']['options'][]
42 = array('type' => 'text', 'name' => 'columns', 'text' => __('Column names: ') . PMA_showHint($hint));
44 $plugin_list['csv']['options'][] = array('type' => 'end_group');
46 /* We do not define function when plugin is just queried for information above */
50 $replacements = array(
55 $csv_terminated = strtr($csv_terminated, $replacements);
56 $csv_enclosed = strtr($csv_enclosed, $replacements);
57 $csv_escaped = strtr($csv_escaped, $replacements);
58 $csv_new_line = strtr($csv_new_line, $replacements);
61 if (strlen($csv_terminated) != 1) {
62 $message = PMA_Message
::error(__('Invalid parameter for CSV import: %s'));
63 $message->addParam(__('Columns terminated by'), false);
66 // The default dialog of MS Excel when generating a CSV produces a
67 // semi-colon-separated file with no chance of specifying the
68 // enclosing character. Thus, users who want to import this file
69 // tend to remove the enclosing character on the Import dialog.
70 // I could not find a test case where having no enclosing characters
71 // confuses this script.
72 // But the parser won't work correctly with strings so we allow just
74 } elseif (strlen($csv_enclosed) > 1) {
75 $message = PMA_Message
::error(__('Invalid parameter for CSV import: %s'));
76 $message->addParam(__('Columns enclosed by'), false);
79 } elseif (strlen($csv_escaped) != 1) {
80 $message = PMA_Message
::error(__('Invalid parameter for CSV import: %s'));
81 $message->addParam(__('Columns escaped by'), false);
84 } elseif (strlen($csv_new_line) != 1 && $csv_new_line != 'auto') {
85 $message = PMA_Message
::error(__('Invalid parameter for CSV import: %s'));
86 $message->addParam(__('Lines terminated by'), false);
91 // If there is an error in the parameters entered, indicate that immediately.
93 PMA_mysqlDie($message->getMessage(), '', '', $err_url);
100 if (isset($csv_replace)) {
101 $sql_template = 'REPLACE';
103 $sql_template = 'INSERT';
104 if (isset($csv_ignore)) {
105 $sql_template .= ' IGNORE';
108 $sql_template .= ' INTO ' . PMA_backquote($table);
110 $tmp_fields = PMA_DBI_get_columns($db, $table);
112 if (empty($csv_columns)) {
113 $fields = $tmp_fields;
115 $sql_template .= ' (';
117 $tmp = preg_split('/,( ?)/', $csv_columns);
118 foreach ($tmp as $key => $val) {
119 if (count($fields) > 0) {
120 $sql_template .= ', ';
122 /* Trim also `, if user already included backquoted fields */
123 $val = trim($val, " \t\r\n\0\x0B`");
125 foreach ($tmp_fields as $id => $field) {
126 if ($field['Field'] == $val) {
132 $message = PMA_Message
::error(__('Invalid column (%s) specified! Ensure that columns names are spelled correctly, separated by commas, and not enclosed in quotes.'));
133 $message->addParam($val);
138 $sql_template .= PMA_backquote($val);
140 $sql_template .= ') ';
143 $required_fields = count($fields);
145 $sql_template .= ' VALUES (';
148 // Defaults for parser
158 $col_names = array();
164 while (!($finished && $i >= $len) && !$error && !$timeout_passed) {
165 $data = PMA_importGetNextChunk();
166 if ($data === false) {
167 // subtract data we didn't handle yet and stop processing
168 $offset -= strlen($buffer);
170 } elseif ($data === true) {
171 // Handle rest of buffer
173 // Append new data to buffer
176 // Do not parse string when we're not at the end and don't have new line inside
177 if (($csv_new_line == 'auto' && strpos($buffer, "\r") === false && strpos($buffer, "\n") === false)
178 ||
($csv_new_line != 'auto' && strpos($buffer, $csv_new_line) === false)
184 // Current length of our buffer
185 $len = strlen($buffer);
186 // Currently parsed char
189 // Deadlock protection
190 if ($lasti == $i && $lastlen == $len) {
191 $message = PMA_Message
::error(__('Invalid format of CSV input on line %d.'));
192 $message->addParam($line);
199 // This can happen with auto EOL and \r at the end of buffer
202 if ($ch == $csv_terminated) {
203 if ($i == $len - 1) {
214 if ($ch == $csv_enclosed) {
215 if ($i == $len - 1) {
226 while (($need_end && ($ch != $csv_enclosed ||
$csv_enclosed == $csv_escaped))
227 ||
(!$need_end && !($ch == $csv_terminated
228 ||
$ch == $csv_new_line ||
($csv_new_line == 'auto'
229 && ($ch == "\r" ||
$ch == "\n"))))) {
230 if ($ch == $csv_escaped) {
231 if ($i == $len - 1) {
237 if ($csv_enclosed == $csv_escaped && ($ch == $csv_terminated
238 ||
$ch == $csv_new_line ||
($csv_new_line == 'auto'
239 && ($ch == "\r" ||
$ch == "\n")))) {
244 if ($i == $len - 1) {
254 // unquoted NULL string
255 if (false === $need_end && $value === 'NULL') {
264 // Need to strip trailing enclosing char?
265 if ($need_end && $ch == $csv_enclosed) {
266 if ($finished && $i == $len - 1) {
268 } elseif ($i == $len - 1) {
277 // Are we at the end?
278 if ($ch == $csv_new_line
279 ||
($csv_new_line == 'auto' && ($ch == "\r" ||
$ch == "\n"))
280 ||
($finished && $i == $len - 1)
285 if ($ch == $csv_terminated) {
286 if ($i == $len - 1) {
294 // If everything went okay, store value
300 ||
$ch == $csv_new_line
301 ||
($csv_new_line == 'auto' && ($ch == "\r" ||
$ch == "\n"))
303 if ($csv_new_line == 'auto' && $ch == "\r") { // Handle "\r\n"
304 if ($i >= ($len - 2) && !$finished) {
305 break; // We need more data to decide new line
307 if ($buffer[$i +
1] == "\n") {
311 // We didn't parse value till the end of line, so there was empty one
317 foreach ($values as $ley => $val) {
322 if ($col_count > $max_cols) {
323 $max_cols = $col_count;
330 // Do we have correct count of values?
331 if (count($values) != $required_fields) {
334 if ($values[count($values) - 1] == ';') {
335 unset($values[count($values) - 1]);
337 $message = PMA_Message
::error(__('Invalid column count in CSV input on line %d.'));
338 $message->addParam($line);
345 $sql = $sql_template;
346 foreach ($values as $key => $val) {
353 $sql .= '\'' . PMA_sqlAddSlashes($val) . '\'';
361 * @todo maybe we could add original line to verbose SQL in comment
363 PMA_importRunQuery($sql, $sql);
369 $buffer = substr($buffer, $i +
1);
370 $len = strlen($buffer);
375 } // End of parser loop
376 } // End of import loop
379 /* Fill out all rows */
380 $num_rows = count($rows);
381 for ($i = 0; $i < $num_rows; ++
$i) {
382 for ($j = count($rows[$i]); $j < $max_cols; ++
$j) {
383 $rows[$i][] = 'NULL';
387 if (isset($_REQUEST['csv_col_names'])) {
388 $col_names = array_splice($rows, 0, 1);
389 $col_names = $col_names[0];
392 if ((isset($col_names) && count($col_names) != $max_cols)
393 ||
! isset($col_names)
395 // Fill out column names
396 for ($i = 0; $i < $max_cols; ++
$i) {
397 $col_names[] = 'COL '.($i+
1);
402 $result = PMA_DBI_fetch_result('SHOW TABLES');
403 $tbl_name = 'TABLE '.(count($result) +
1);
405 $tbl_name = 'TBL_NAME';
408 $tables[] = array($tbl_name, $col_names, $rows);
410 /* Obtain the best-fit MySQL types for each column */
412 $analyses[] = PMA_analyzeTable($tables[0]);
415 * string $db_name (no backquotes)
417 * array $table = array(table_name, array() column_names, array()() rows)
418 * array $tables = array of "$table"s
420 * array $analysis = array(array() column_types, array() column_sizes)
421 * array $analyses = array of "$analysis"s
423 * array $create = array of SQL strings
425 * array $options = an associative array of options
428 /* Set database name to the currently selected one, if applicable */
431 $options = array('create_db' => false);
437 /* Non-applicable parameters */
440 /* Created and execute necessary SQL statements from data */
441 PMA_buildSQL($db_name, $tables, $analyses, $create, $options);
447 // Commit any possible data in buffers
448 PMA_importRunQuery();
450 if (count($values) != 0 && !$error) {
451 $message = PMA_Message
::error(__('Invalid format of CSV input on line %d.'));
452 $message->addParam($line);