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
9 if (! defined('PHPMYADMIN')) {
16 if ($plugin_param !== 'table') {
20 if (isset($plugin_list)) {
21 $plugin_list['csv'] = array(
25 array('type' => 'bool', 'name' => 'replace', 'text' => 'strReplaceTable'),
26 array('type' => 'bool', 'name' => 'ignore', 'text' => 'strIgnoreDuplicates'),
27 array('type' => 'text', 'name' => 'terminated', 'text' => 'strFieldsTerminatedBy', 'size' => 2, 'len' => 2),
28 array('type' => 'text', 'name' => 'enclosed', 'text' => 'strFieldsEnclosedBy', 'size' => 2, 'len' => 2),
29 array('type' => 'text', 'name' => 'escaped', 'text' => 'strFieldsEscapedBy', 'size' => 2, 'len' => 2),
30 array('type' => 'text', 'name' => 'new_line', 'text' => 'strLinesTerminatedBy', 'size' => 2),
31 array('type' => 'text', 'name' => 'columns', 'text' => 'strColumnNames'),
33 'options_text' => 'strOptions',
35 /* We do not define function when plugin is just queried for information above */
39 $replacements = array(
44 $csv_terminated = strtr($csv_terminated, $replacements);
45 $csv_enclosed = strtr($csv_enclosed, $replacements);
46 $csv_escaped = strtr($csv_escaped, $replacements);
47 $csv_new_line = strtr($csv_new_line, $replacements);
49 if (strlen($csv_terminated) != 1) {
50 $message = PMA_Message
::error('strInvalidCSVParameter');
51 $message->addParam('strFieldsTerminatedBy', false);
53 // The default dialog of MS Excel when generating a CSV produces a
54 // semi-colon-separated file with no chance of specifying the
55 // enclosing character. Thus, users who want to import this file
56 // tend to remove the enclosing character on the Import dialog.
57 // I could not find a test case where having no enclosing characters
58 // confuses this script.
59 // But the parser won't work correctly with strings so we allow just
61 } elseif (strlen($csv_enclosed) > 1) {
62 $message = PMA_Message
::error('strInvalidCSVParameter');
63 $message->addParam('strFieldsEnclosedBy', false);
65 } elseif (strlen($csv_escaped) != 1) {
66 $message = PMA_Message
::error('strInvalidCSVParameter');
67 $message->addParam('strFieldsEscapedBy', false);
69 } elseif (strlen($csv_new_line) != 1 && $csv_new_line != 'auto') {
70 $message = PMA_Message
::error('strInvalidCSVParameter');
71 $message->addParam('strLinesTerminatedBy', false);
76 if (isset($csv_replace)) {
77 $sql_template = 'REPLACE';
79 $sql_template = 'INSERT';
80 if (isset($csv_ignore)) {
81 $sql_template .= ' IGNORE';
84 $sql_template .= ' INTO ' . PMA_backquote($table);
86 $tmp_fields = PMA_DBI_get_fields($db, $table);
88 if (empty($csv_columns)) {
89 $fields = $tmp_fields;
91 $sql_template .= ' (';
93 $tmp = split(',( ?)', $csv_columns);
94 foreach ($tmp as $key => $val) {
95 if (count($fields) > 0) {
96 $sql_template .= ', ';
100 foreach ($tmp_fields as $id => $field) {
101 if ($field['Field'] == $val) {
107 $message = PMA_Message
::error('strInvalidColumn');
108 $message->addParam($val);
113 $sql_template .= PMA_backquote($val);
115 $sql_template .= ') ';
118 $required_fields = count($fields);
120 $sql_template .= ' VALUES (';
122 // Defaults for parser
130 while (!($finished && $i >= $len) && !$error && !$timeout_passed) {
131 $data = PMA_importGetNextChunk();
132 if ($data === FALSE) {
133 // subtract data we didn't handle yet and stop processing
134 $offset -= strlen($buffer);
136 } elseif ($data === TRUE) {
137 // Handle rest of buffer
139 // Append new data to buffer
142 // Do not parse string when we're not at the end and don't have new line inside
143 if (($csv_new_line == 'auto' && strpos($buffer, "\r") === FALSE && strpos($buffer, "\n") === FALSE)
144 ||
($csv_new_line != 'auto' && strpos($buffer, $csv_new_line) === FALSE)) {
149 // Current length of our buffer
150 $len = strlen($buffer);
151 // Currently parsed char
154 // Deadlock protection
155 if ($lasti == $i && $lastlen == $len) {
156 $message = PMA_Message
::error('strInvalidCSVFormat');
157 $message->addParam($line);
164 // This can happen with auto EOL and \r at the end of buffer
167 if ($ch == $csv_terminated) {
168 if ($i == $len - 1) {
179 if ($ch == $csv_enclosed) {
180 if ($i == $len - 1) {
191 while (($need_end && $ch != $csv_enclosed)
192 ||
(!$need_end && !($ch == $csv_terminated
193 ||
$ch == $csv_new_line ||
($csv_new_line == 'auto'
194 && ($ch == "\r" ||
$ch == "\n"))))) {
195 if ($ch == $csv_escaped) {
196 if ($i == $len - 1) {
204 if ($i == $len - 1) {
214 // unquoted NULL string
215 if (false === $need_end && $value === 'NULL') {
224 // Need to strip trailing enclosing char?
225 if ($need_end && $ch == $csv_enclosed) {
226 if ($finished && $i == $len - 1) {
228 } elseif ($i == $len - 1) {
237 // Are we at the end?
238 if ($ch == $csv_new_line ||
($csv_new_line == 'auto' && ($ch == "\r" ||
$ch == "\n")) ||
($finished && $i == $len - 1)) {
242 if ($ch == $csv_terminated) {
243 if ($i == $len - 1) {
251 // If everything went okay, store value
256 if ($csv_finish ||
$ch == $csv_new_line ||
($csv_new_line == 'auto' && ($ch == "\r" ||
$ch == "\n"))) {
257 if ($csv_new_line == 'auto' && $ch == "\r") { // Handle "\r\n"
258 if ($i >= ($len - 2) && !$finished) {
259 break; // We need more data to decide new line
261 if ($buffer[$i +
1] == "\n") {
265 // We didn't parse value till the end of line, so there was empty one
269 // Do we have correct count of values?
270 if (count($values) != $required_fields) {
273 if ($values[count($values) - 1] == ';') {
274 unset($values[count($values) - 1]);
276 $message = PMA_Message
::error('strInvalidCSVFieldCount');
277 $message->addParam($line);
284 $sql = $sql_template;
285 foreach ($values as $key => $val) {
292 $sql .= '\'' . addslashes($val) . '\'';
300 * @todo maybe we could add original line to verbose SQL in comment
302 PMA_importRunQuery($sql, $sql);
306 $buffer = substr($buffer, $i +
1);
307 $len = strlen($buffer);
312 } // End of parser loop
313 } // End of import loop
315 // Commit any possible data in buffers
316 PMA_importRunQuery();
318 if (count($values) != 0 && !$error) {
319 $message = PMA_Message
::error('$strInvalidCSVFormat');
320 $message->addParam($line);