Excel data to sql table

Hello Experts.

I have already an sql table with information, and I need to add a ot of rows to that existing table, that I have in an excel.

What's the easy way to do this?

The info in the excel has the same layout and columns as in the sql table.

Also I dont want to lose any information already in the sql table, just add what I have in the excel.


Thank you.
joao_cAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Ray PaseurConnect With a Mentor Commented:
Save the Excel file as a CSV format.  You can probably modify this (partial) script to read the CSV file and make INSERT queries.  You can add your own data base credentials at the top and you will need to run the query near line 52.

$fpo = fopen($csv, 'r');
if (!$fpo ) die("CRAP.  CANNOT OPEN $csv");

// GET THE FIELD NAMES FROM THE TOP OF THE CSV FILE
$top = fgetcsv($fpo);
$cnt = count($top);

// SET UP KEY NAMES FOR USE IN OUR QUERY
$query_cols = implode(',', $top);

// SET A ROW COUNTER
$counter = 0;

// KEEP TRACK OF ROWS THAT HAVE THE WRONG NUMBER OF FIELDS
$errors = array();

// LOOP THROUGH THE CSV RECORDS PERFORMING CERTAIN TESTS
while (!feof($fpo))
{
    $counter++;

    // GET A RECORD
    $csvdata = fgetcsv($fpo);

    // SKIP OVER EMPTY ROWS
    if (empty($csvdata)) continue;

    // CHECK THE NUMBER OF FIELDS
    if ($cnt != count($csvdata))
    {
        $errors[] = $counter;
        continue;
    }

    // MAYBE ASSIGN KEYS TO THE ROW OF FIELDS - ACTIVATE THIS TO SEE THE ASSOCIATIVE ARRAY
    //  $csvdata = array_combine($top, $csvdata);
    //  var_dump($csvdata);

    // ESCAPE THE INFORMATION FOR USE IN THE QUERY
    foreach ($csvdata as $ptr => $value)
    {
        $csvdata[$ptr] = mysql_real_escape_string($value);
    }

    // SET UP VALUE FIELDS
    $query_data = "'" . implode("', '", $csvdata) . "'";

    // SET UP A QUERY
    $sql = "INSERT INTO myTable ( $query_cols ) VALUES ( $query_data )";

    // RUN THE QUERY HERE....
    var_dump($sql);
}


// SHOW THE NUMBER OF ROWS PROCESSED
echo "<br/>RECORDS PROCESSED $counter " . PHP_EOL;

// SHOW THE NUMBERS OF THE ROWS WITH THE WRONG NUMBER OF FIELDS
if (count($errors))
{
    echo "<br/>ROWS WITH THE WRONG NUMBER OF FIELDS: " . PHP_EOL;
    var_dump($errors);
}

Open in new window

0
 
joao_cAuthor Commented:
Thanks Ray
0
 
Ray PaseurCommented:
To quote the grading guidelines for EE, "...you should consider the A grade the default unless it [the response] is deficient."  Please read the guidelines and then tell me why you marked the grade down to a "B" without any comment or explanation of the deficiency in the answer.  
http://www.experts-exchange.com/help/viewHelpPage.jsp?helpPageID=26

Looking forward to understanding what went wrong, ~Ray
0
All Courses

From novice to tech pro — start learning today.