...

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



prayner
02-15-2006, 01: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, 06: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?



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum