PDA

View Full Version : Alter a csv to sql script - in php



prayner
02-15-2006, 02:33 AM
I found a script on codewalkers, but needed to modify it. Its a csv2 sql converter. Below is my partly modified script.
<H2>CSV to SQL convertor</H2>

<!-- Input form begin -->

<FORM NAME="csv2sql" METHOD=POST ACTION="<? echo $PHP_SELF; ?>">
<INPUT TYPE="HIDDEN" NAME="ref" VALUE="csv2sql">
Insertion table:
<BR>
<INPUT CLASS="DEFAULT" TYPE="TEXT" NAME="table_name" VALUE="" SIZE=50>
<BR><BR>
CSV data:
<BR>
<TEXTAREA CLASS="DEFAULT" NAME="csv_data" ROWS=30 COLS=100></TEXTAREA>
<BR><BR>
<INPUT CLASS="DEFAULT" TYPE=SUBMIT VALUE=" Convert to SQL query ">
</FORM>

<!-- Input form end -->

<?php

// Parse incoming information if above form was posted
if($_POST[ref] == "csv2sql") {

echo "<h2>SQL Query Output:</h2>";

// Get information from form & prepare it for parsing
$table_name = $_POST[table_name];
$csv_data = $_POST[csv_data];
$csv_array = explode("\n",$csv_data);
$column_names = explode(";",$csv_array[0]);

// Generate base query
$base_query = "INSERT INTO $table_name";

// Loop through all CSV data rows and generate separate
// INSERT queries based on base_query + the row information
$last_data_row = count($csv_array) - 1;
for($counter = 1; $counter < $last_data_row; $counter++)
{
$value_query = " VALUES (";
$first = true;
$data_row = explode(";",$csv_array[$counter]);
$value_counter = 0;
foreach($data_row as $data_value)
{
if(!$first)
$value_query .= ", ";
$data_value = trim($data_value);
$value_query .= "'$data_value'";
$first = false;
}
$value_query .= ")";

// Combine generated queries to generate final query
$query = $base_query .$value_query .";";
echo "$query <BR>";
}
}

?>

It worked fine, but not with my implentation.
I have it "working" at http://www.sqitroma.com/sql/csv2sql.php

Current output is:INSERT INTO deputy VALUES ('3,\'Hughes W H\',\'Labor\'');
But I want to strip out the backslashes, and have single inverted commas around each value. The values in my csv were single inverted around the last 2. I also want to strip out any other characters.
My csv looks like:
1,'Deakin A','Protectionist'

I want the sql to look like:
INSERT INTO deputy VALUES (1,'Deakin A','Protectionist');

I'm almost there, but need some help to modify the last bits.

Cheers

StupidRalph
02-15-2006, 07:29 AM
Have you tried to use the stripslashes() to remove the backslashes?
But aren't the backslashes in your code simply escaping the single quotes?