CODEWITHSUNDEEP
mysqldatabasephpmyadminimport
Keefer
Keefer

Reputation: 2289

How to Map a CSV or Tab Delimited File to MySQL Multi-Table Database

I've got a pretty substantial XLS file a client provided 830 total tabs/sheets.

I've designed a multi table database with PHPMyAdmin (MySQL obviously) to house the information that's in there, and have populated about 5 of those sheets by hand to ensure the data will fit into the designed database.

Is there a piece of software or some sort of tool that will help me format this XLS document and map it to the right places in the database?

Upvotes: 2

Views: 2064

Answers (2)

Lèse majesté
Lèse majesté

Reputation: 8045

It's possible to import data from a spreadsheet into Access and map the fields to whatever table/column you want. It's also possible to use an ODBC connector to connect Access to a MySQL DB, essentially using Access as a front-end to MySQL.

Alternatively, you can do as toomanyairmiles suggests and simply write the PHP code to massage the CSV data into the format your MySQL DB needs. This is what I've done in the past when we needed to import sales data from disparate sources into an in-house sales/royalties-tracking system. If you need to do frequent imports, I would suggest automating a system (e.g. via an Excel macro) to export the individual sheets into CSVs in a single directory or, if it's more convenient, you can zip them together and upload the zip file to the PHP app.

If you're doing bulk imports into MySQL, you may want to consider using LOAD DATA INFILE, which is generally the fastest way to import data into MySQL—to the point where it's actually faster to write a new CSV file to disk after you've massaged the data, and then use LOAD DATA INFILE rather than doing a bulk INSERT directly. Doing this on my intranet actually cut the insertion time from over 3 seconds (already an improvement over the ~3min. it took to do thousands of individual INSERTs) down to 240ms.

Upvotes: 3

toomanyairmiles
toomanyairmiles

Reputation: 6485

According to this thread you can import a csv file exported from excel with php.

Quoting @noelthefish

as you seem to be using PHP here is a function that is built into PHP

array fgetcsv ( resource $handle [, int $length [, string $delimiter [, string $enclosure [, string $escape ]]]] )

<?php
$row = 1;
$handle = fopen("test.csv", "r");
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
    $num = count($data);
    echo "<p> $num fields in line $row: </p>\n";
    $row++;
    for ($c=0; $c < $num; $c++) {
        echo $data[$c] . "\n";
    }
}
fclose($handle);
?>

this is the very basic of what you need. You would of course put in the database update part within the while loop. Take out the ECHO statements as well unless you are debugging but basically with a little alteration this small piece of code will do what you need. if you need more info check out uk.php.net

Upvotes: 3

Related Questions