Solved

Excel data to sql table

Posted on 2013-01-24
3
390 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
  • 2
3 Comments
 
LVL 109

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 109

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

This article demonstrates how to create a simple responsive confirmation dialog with Ok and Cancel buttons using HTML, CSS, jQuery and Promises
Find out what you should include to make the best professional email signature for your organization.
In this tutorial viewers will learn how to style elements, such a divs, with a "drop shadow" effect using the CSS box-shadow property Start with a normal styled element, such as a div.: In the element's style, type the box shadow property: "box-shad…
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

830 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