KarelWintersky/csv2sql.php

## csv2sql.php
<?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>
	<?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>