Solved

Excel data to sql table

Posted on 2013-01-24
3
393 Views
Last Modified: 2013-01-25
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.
0
Comment
Question by:joao_c
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 110

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 38816676
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
 

Author Closing Comment

by:joao_c
ID: 38819308
Thanks Ray
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 38820374
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

Featured Post

Get HTML5 Certified

Want to be a web developer? You'll need to know HTML. Prepare for HTML5 certification by enrolling in July's Course of the Month! It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this tutorial viewers will learn how to style a corner ribbon overlay for an image using CSS Create a new class by typing ".Ribbon":  Define the class' "display:" as "inline-block": Define its "position:" as "relative": Define its "overflow:" as …
The viewer will the learn the benefit of plain text editors and code an HTML5 based template for use in further tutorials.

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question