Skip to content

Instantly share code, notes, and snippets.

@KarelWintersky
Last active Aug 19, 2017
Embed
What would you like to do?
CSV to SQL converter (insert and update)
<?php
/**
* (c) Karel Wintersky, 13 Sept. 2016
*/
$content = '';
if ($_POST["ref"] === "csv2sql") {
$table_name = $_POST["table_name"];
$csv_delimeter = @isset($_POST['DELIMETER']) ?: ',';
$csv_enclosure = @isset($_POST['ENCLOSURE']) ?: '"';
$csv_data = $_POST["csv_data"];
$sql_mode = @isset($_POST['mode']) ?: 'insert';
$csv_as_array = explode("\n", $csv_data);
$header_columns = str_getcsv($csv_as_array[0]); // read first row as header
// Generate base query
$base_query
= ($sql_mode === 'update')
? "INSERT INTO `$table_name` ("
. implode(', ', array_map(
function($column){
return trim($column);
}, $header_columns))
. ') '
: "UPDATE `$table_name` SET ";
unset($csv_as_array[0]);
// Loop through all CSV data rows and generate separate queries
foreach ($csv_as_array as $row) {
$data_row
= (strpos($row, $csv_enclosure) !== FALSE)
? str_getcsv($row, $csv_delimeter, $csv_enclosure)
: str_getcsv($row);
if ($sql_mode == 'update') {
$content .= $base_query
. implode(', ', array_map( function($row_column, $header_column) {
return "`{$header_column}` = '{$row_column}'";
}, $data_row, $header_columns))
. ';'
. PHP_EOL;
} else {
$content .= $base_query
. " VALUES ("
. implode(', ', array_map( function($value) {
return "'" . trim($value) . "'";
} , $data_row))
. ');'
.PHP_EOL;
}
} // foreach
}
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<meta name="author" content="Karel Wintersky, 2016">
<meta name="copyright" content="(c) Karel Wintersky">
<meta name="description" content="CSV to SQL simple convertor">
<meta http-equiv="pragma" content="no-cache">
<title>CSV to SQL convertor</title>
</head>
<body>
<h2>CSV to SQL convertor</h2>
<form name="csv2sql" method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
<input type="hidden" name="ref" value="csv2sql">
Insertion table: <input type="text" name="table_name" value="" size="50" required>
<br/>
Column delimeter:
<select name="delimeter">
<option value=",">,</option>
<option value=";">;</option>
<option value="|">|</option>
</select>
Field enclosure:
<select name="ENCLOSURE">
<option value='"'>"</option>
<option value="'">'</option>
</select>
Mode:
<select name="mode">
<option value="insert">insert</option>
<option value="update">update</option>
</select>
<br/>
CSV Data:<br/>
<textarea name="csv_data" id="csv_data" rows="30" cols="120"><?php echo $content; ?></textarea>
<br/><br/>
<input type="submit" value=" Convert to SQL query ">
<input type="reset" value=" CLEAR " onclick="document.getElementById('csv_data').value=''">
</form>
</body>
</html>
@KarelWintersky
Copy link
Author

KarelWintersky commented Sep 13, 2016

Хочешь сделать что-то хорошо - сделай это сам.©
Задолбался искать в интернете конвертор, сделал ручками.

@KarelWintersky
Copy link
Author

KarelWintersky commented Dec 16, 2016

Впрочем, из всех инструментов нашел только один более или менее пригодный: https://sqlizer.io/
И то, он предполагает, что мы вставляем данные в таблицу абсолютно новую.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment