Written by 
Recently we bought a copy of the O’Reilly book “PHP Hacks” written by Jack Herrington. Inspired by his many wonderful hacks and the need to convert CSV values into SQL Insert statements I wrote a simple PHP script to do the job. To use it simply drop it in your webserver and point a browser to it. Fire up your favorite spreadsheet (Openoffice, right?), create a table (remember to use the first rows for the column names) then cut-n-paste the table into the textarea, hit Submit and Voilá: your spreadsheet data is converted into SQL inserts. Have fun!
The program displays a form for you to write the table name to use in the inserts and a textarea where you can paste you Excel / Openoffice data. If you cut-n-paste the pasted text will be CSV delimited by tabs. Once you submit the form the script reads the lines and uses explode() to convert the CSV data into arrays and implode to convert the arrays into lists used in the SQL output.
<html>
<head>
<style>
body { padding: 20px; font-size: 120%; }
label {color: #333; }
input { padding: 5px; }
textarea { padding: 5px; font-size: 10px; }
</style>
</head>
<body>
<form method="post" action="csv2sql.php">
<label for="table_name">Table name</label><br/>
<input size="40" type="text" name="table_name" <?php if (isset($_POST['table_name'])) { echo "value="" . $_POST['table_name'] . """; } ?>/><br/><br/>
<label for="csv">CSV File (copy and paste from Excel into this textbox)</label><br/>
<textarea rows="20" cols="100" name="csv"><?php if (isset($_POST['csv'])) { echo $_POST['csv']; } ?></textarea><br/>
<input type="submit" value="Submit" /><input type="reset" value="Borrar" /><br/>
</form>
<?php
if (!isset($_POST['csv']) || !isset($_POST['table_name']))
{
exit();
}
// read lines
$lines = explode("\n", $_POST['csv']);
$field_line = array_shift($lines);
$field_names = explode("\t", $field_line);
$field_name_list = trim(implode(",", $field_names), "\r");
echo "<label for="foo">SQL commands:</label><br/>";
echo "<textarea rows="20" cols="100" name="foo">";
foreach($lines as $line)
{
if (strlen(trim($line,"\r")) > 0)
{
$field_values = explode("\t", trim($line,"\r"));
$new_values = array();
foreach($field_values as $value)
{
$new_values[] = "'" . $value . "'";
}
$values = implode(",", $new_values);
$sql = "insert into " . $_POST['table_name'];
$sql .= " ($field_name_list) values ($values);\n";
echo $sql;
}
}
echo "</textarea>";
?>
</body>
</html>
A few improvements to the code would be:
- Add a drop down to select what delimiter is used in the textarea to delimit de CSV data. This way you can also simply type the CSV file in the textarea instead of using Excel.
- Add a checkbox in case you like your SQL to be uppercase
- Add validations and error checking. The code has none but this is a hack so we are allowed to do that
.
Hi, thanks for the script. Looks like when you pasted the code, your escaped characters got un-escaped.
For example:
$lines = explode(“n”, $_POST['csv']);
should probably read
$lines = explode(“\n”, $_POST['csv']);
The line-break, tabs specical characters just need to be updated on the published code, otherwise works great!