Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP
tree: 35b18e57a2
Fetching contributors…

Cannot retrieve contributors at this time

442 lines (396 sloc) 14.583 kb
<?php
/* vim: set expandtab sw=4 ts=4 sts=4: */
/**
* CSV import plugin for phpMyAdmin
*
* @todo add an option for handling NULL values
* @package PhpMyAdmin-Import
* @subpackage CSV
*/
if (! defined('PHPMYADMIN')) {
exit;
}
$analyze = false;
if ($plugin_param !== 'table') {
$analyze = true;
}
if (isset($plugin_list)) {
$plugin_list['csv'] = array(
'text' => __('CSV'),
'extension' => 'csv',
'options' => array(
array('type' => 'begin_group', 'name' => 'general_opts'),
array('type' => 'bool', 'name' => 'replace', 'text' => __('Replace table data with file')),
array('type' => 'bool', 'name' => 'ignore', 'text' => __('Do not abort on INSERT error')),
array('type' => 'text', 'name' => 'terminated', 'text' => __('Columns separated with:'), 'size' => 2, 'len' => 2),
array('type' => 'text', 'name' => 'enclosed', 'text' => __('Columns enclosed with:'), 'size' => 2, 'len' => 2),
array('type' => 'text', 'name' => 'escaped', 'text' => __('Columns escaped with:'), 'size' => 2, 'len' => 2),
array('type' => 'text', 'name' => 'new_line', 'text' => __('Lines terminated with:'), 'size' => 2),
),
'options_text' => __('Options'),
);
if ($plugin_param !== 'table') {
$plugin_list['csv']['options'][]
= 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>'));
} else {
$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.'));
$plugin_list['csv']['options'][]
= array('type' => 'text', 'name' => 'columns', 'text' => __('Column names: ') . PMA_showHint($hint));
}
$plugin_list['csv']['options'][] = array('type' => 'end_group');
/* We do not define function when plugin is just queried for information above */
return;
}
$replacements = array(
'\\n' => "\n",
'\\t' => "\t",
'\\r' => "\r",
);
$csv_terminated = strtr($csv_terminated, $replacements);
$csv_enclosed = strtr($csv_enclosed, $replacements);
$csv_escaped = strtr($csv_escaped, $replacements);
$csv_new_line = strtr($csv_new_line, $replacements);
$param_error = false;
if (strlen($csv_terminated) != 1) {
$message = PMA_Message::error(__('Invalid parameter for CSV import: %s'));
$message->addParam(__('Columns terminated by'), false);
$error = true;
$param_error = true;
// The default dialog of MS Excel when generating a CSV produces a
// semi-colon-separated file with no chance of specifying the
// enclosing character. Thus, users who want to import this file
// tend to remove the enclosing character on the Import dialog.
// I could not find a test case where having no enclosing characters
// confuses this script.
// But the parser won't work correctly with strings so we allow just
// one character.
} elseif (strlen($csv_enclosed) > 1) {
$message = PMA_Message::error(__('Invalid parameter for CSV import: %s'));
$message->addParam(__('Columns enclosed by'), false);
$error = true;
$param_error = true;
} elseif (strlen($csv_escaped) != 1) {
$message = PMA_Message::error(__('Invalid parameter for CSV import: %s'));
$message->addParam(__('Columns escaped by'), false);
$error = true;
$param_error = true;
} elseif (strlen($csv_new_line) != 1 && $csv_new_line != 'auto') {
$message = PMA_Message::error(__('Invalid parameter for CSV import: %s'));
$message->addParam(__('Lines terminated by'), false);
$error = true;
$param_error = true;
}
// If there is an error in the parameters entered, indicate that immediately.
if ($param_error) {
PMA_mysqlDie($message->getMessage(), '', '', $err_url);
}
$buffer = '';
$required_fields = 0;
if (!$analyze) {
if (isset($csv_replace)) {
$sql_template = 'REPLACE';
} else {
$sql_template = 'INSERT';
if (isset($csv_ignore)) {
$sql_template .= ' IGNORE';
}
}
$sql_template .= ' INTO ' . PMA_backquote($table);
$tmp_fields = PMA_DBI_get_columns($db, $table);
if (empty($csv_columns)) {
$fields = $tmp_fields;
} else {
$sql_template .= ' (';
$fields = array();
$tmp = preg_split('/,( ?)/', $csv_columns);
foreach ($tmp as $key => $val) {
if (count($fields) > 0) {
$sql_template .= ', ';
}
/* Trim also `, if user already included backquoted fields */
$val = trim($val, " \t\r\n\0\x0B`");
$found = false;
foreach ($tmp_fields as $id => $field) {
if ($field['Field'] == $val) {
$found = true;
break;
}
}
if (!$found) {
$message = PMA_Message::error(__('Invalid column (%s) specified! Ensure that columns names are spelled correctly, separated by commas, and not enclosed in quotes.' ));
$message->addParam($val);
$error = true;
break;
}
$fields[] = $field;
$sql_template .= PMA_backquote($val);
}
$sql_template .= ') ';
}
$required_fields = count($fields);
$sql_template .= ' VALUES (';
}
// Defaults for parser
$i = 0;
$len = 0;
$line = 1;
$lasti = -1;
$values = array();
$csv_finish = false;
$tempRow = array();
$rows = array();
$col_names = array();
$tables = array();
$col_count = 0;
$max_cols = 0;
while (!($finished && $i >= $len) && !$error && !$timeout_passed) {
$data = PMA_importGetNextChunk();
if ($data === false) {
// subtract data we didn't handle yet and stop processing
$offset -= strlen($buffer);
break;
} elseif ($data === true) {
// Handle rest of buffer
} else {
// Append new data to buffer
$buffer .= $data;
unset($data);
// Do not parse string when we're not at the end and don't have new line inside
if (($csv_new_line == 'auto' && strpos($buffer, "\r") === false && strpos($buffer, "\n") === false)
|| ($csv_new_line != 'auto' && strpos($buffer, $csv_new_line) === false)) {
continue;
}
}
// Current length of our buffer
$len = strlen($buffer);
// Currently parsed char
$ch = $buffer[$i];
while ($i < $len) {
// Deadlock protection
if ($lasti == $i && $lastlen == $len) {
$message = PMA_Message::error(__('Invalid format of CSV input on line %d.'));
$message->addParam($line);
$error = true;
break;
}
$lasti = $i;
$lastlen = $len;
// This can happen with auto EOL and \r at the end of buffer
if (!$csv_finish) {
// Grab empty field
if ($ch == $csv_terminated) {
if ($i == $len - 1) {
break;
}
$values[] = '';
$i++;
$ch = $buffer[$i];
continue;
}
// Grab one field
$fallbacki = $i;
if ($ch == $csv_enclosed) {
if ($i == $len - 1) {
break;
}
$need_end = true;
$i++;
$ch = $buffer[$i];
} else {
$need_end = false;
}
$fail = false;
$value = '';
while (($need_end && $ch != $csv_enclosed)
|| (!$need_end && !($ch == $csv_terminated
|| $ch == $csv_new_line || ($csv_new_line == 'auto'
&& ($ch == "\r" || $ch == "\n"))))) {
if ($ch == $csv_escaped) {
if ($i == $len - 1) {
$fail = true;
break;
}
$i++;
$ch = $buffer[$i];
}
$value .= $ch;
if ($i == $len - 1) {
if (!$finished) {
$fail = true;
}
break;
}
$i++;
$ch = $buffer[$i];
}
// unquoted NULL string
if (false === $need_end && $value === 'NULL') {
$value = null;
}
if ($fail) {
$i = $fallbacki;
$ch = $buffer[$i];
break;
}
// Need to strip trailing enclosing char?
if ($need_end && $ch == $csv_enclosed) {
if ($finished && $i == $len - 1) {
$ch = null;
} elseif ($i == $len - 1) {
$i = $fallbacki;
$ch = $buffer[$i];
break;
} else {
$i++;
$ch = $buffer[$i];
}
}
// Are we at the end?
if ($ch == $csv_new_line || ($csv_new_line == 'auto' && ($ch == "\r" || $ch == "\n")) || ($finished && $i == $len - 1)) {
$csv_finish = true;
}
// Go to next char
if ($ch == $csv_terminated) {
if ($i == $len - 1) {
$i = $fallbacki;
$ch = $buffer[$i];
break;
}
$i++;
$ch = $buffer[$i];
}
// If everything went okay, store value
$values[] = $value;
}
// End of line
if ($csv_finish || $ch == $csv_new_line || ($csv_new_line == 'auto' && ($ch == "\r" || $ch == "\n"))) {
if ($csv_new_line == 'auto' && $ch == "\r") { // Handle "\r\n"
if ($i >= ($len - 2) && !$finished) {
break; // We need more data to decide new line
}
if ($buffer[$i + 1] == "\n") {
$i++;
}
}
// We didn't parse value till the end of line, so there was empty one
if (!$csv_finish) {
$values[] = '';
}
if ($analyze) {
foreach ($values as $ley => $val) {
$tempRow[] = $val;
++$col_count;
}
if ($col_count > $max_cols) {
$max_cols = $col_count;
}
$col_count = 0;
$rows[] = $tempRow;
$tempRow = array();
} else {
// Do we have correct count of values?
if (count($values) != $required_fields) {
// Hack for excel
if ($values[count($values) - 1] == ';') {
unset($values[count($values) - 1]);
} else {
$message = PMA_Message::error(__('Invalid column count in CSV input on line %d.'));
$message->addParam($line);
$error = true;
break;
}
}
$first = true;
$sql = $sql_template;
foreach ($values as $key => $val) {
if (!$first) {
$sql .= ', ';
}
if ($val === null) {
$sql .= 'NULL';
} else {
$sql .= '\'' . PMA_sqlAddSlashes($val) . '\'';
}
$first = false;
}
$sql .= ')';
/**
* @todo maybe we could add original line to verbose SQL in comment
*/
PMA_importRunQuery($sql, $sql);
}
$line++;
$csv_finish = false;
$values = array();
$buffer = substr($buffer, $i + 1);
$len = strlen($buffer);
$i = 0;
$lasti = -1;
$ch = $buffer[0];
}
} // End of parser loop
} // End of import loop
if ($analyze) {
/* Fill out all rows */
$num_rows = count($rows);
for ($i = 0; $i < $num_rows; ++$i) {
for ($j = count($rows[$i]); $j < $max_cols; ++$j) {
$rows[$i][] = 'NULL';
}
}
if (isset($_REQUEST['csv_col_names'])) {
$col_names = array_splice($rows, 0, 1);
$col_names = $col_names[0];
}
if ((isset($col_names) && count($col_names) != $max_cols) || !isset($col_names)) {
// Fill out column names
for ($i = 0; $i < $max_cols; ++$i) {
$col_names[] = 'COL '.($i+1);
}
}
if (strlen($db)) {
$result = PMA_DBI_fetch_result('SHOW TABLES');
$tbl_name = 'TABLE '.(count($result) + 1);
} else {
$tbl_name = 'TBL_NAME';
}
$tables[] = array($tbl_name, $col_names, $rows);
/* Obtain the best-fit MySQL types for each column */
$analyses = array();
$analyses[] = PMA_analyzeTable($tables[0]);
/**
* string $db_name (no backquotes)
*
* array $table = array(table_name, array() column_names, array()() rows)
* array $tables = array of "$table"s
*
* array $analysis = array(array() column_types, array() column_sizes)
* array $analyses = array of "$analysis"s
*
* array $create = array of SQL strings
*
* array $options = an associative array of options
*/
/* Set database name to the currently selected one, if applicable */
if (strlen($db)) {
$db_name = $db;
$options = array('create_db' => false);
} else {
$db_name = 'CSV_DB';
$options = null;
}
/* Non-applicable parameters */
$create = null;
/* Created and execute necessary SQL statements from data */
PMA_buildSQL($db_name, $tables, $analyses, $create, $options);
unset($tables);
unset($analyses);
}
// Commit any possible data in buffers
PMA_importRunQuery();
if (count($values) != 0 && !$error) {
$message = PMA_Message::error(__('Invalid format of CSV input on line %d.'));
$message->addParam($line);
$error = true;
}
?>
Jump to Line
Something went wrong with that request. Please try again.