Read CSV file and store into MySQL Database

Question

I'm developing a website that allows user to browse and select a .csv file from their computer.
The website will then read the .csv file (items separated by commas) and store all items into maybe a list or an array? After that it will be store into my Database.

May i know how can this be done? Code snippets and references will be much appreciated.

Thanks in advance!

score 2 · Answer 1 · answered Jan 11 '12 at 08:24

Hope you are using PHP to develop your website and want to import user CSV file into your MySQL Server database.

You can use the following PHP class to import your data: http://www.legend.ws/blog/tips-tricks/csv-php-mysql-import/

Assuming that you have already created the required database tables.

Alternately, you can use the MySQLImport utility to import any CSV file into your MySQL database. Refer http://dev.mysql.com/doc/refman/5.0/en/mysqlimport.html URL to get more details on mySQLImport command.

score 0 · Answer 2 · answered Feb 08 '13 at 03:45

CSV. From MySQL to 'csv'. From 'csv' to MySQL This script converts table data to 'csv' file. You can recreate table from 'csv' file in another computer.

<?php
// connect to database
require_once '../db_config.php';
$conn = new mysqli ( $dbhost, $dbuser, $dbpass, 'olimrefdb' );
if ($conn->connect_errno) die ( 'no db connection' );   

$tableToConvert = 'mdata';
$csvFileName = $tableToConvert . '_' . date ( "Ymd" );//will contain 'csv' data 

// -----------------------------MySQL table to CSV--------------------------
$tableSchema = $conn->query ( "SHOW CREATE TABLE $tableToConvert" )->fetch_assoc ();
$result = $conn->query ( "SELECT * FROM $tableToConvert" );

$handle = fopen ( $csvFileName.'.csv', 'w' );//to store 'csv' data file
$handle_schema = fopen ( $csvFileName.'_schema.csv', 'w' );//to store table schema 

fputcsv ( $handle_schema, $tableSchema );
while ( $row = $result->fetch_assoc () ) {
    fputcsv ( $handle, $row );
}
fclose ( $handle );
fclose ( $handle_schema );

// -----------------------------CSV to MySQL table--------------------------

$handle = fopen ( $csvFileName.'.csv', 'r' );//open 'csv' data file
$handle_schema = fopen ( $csvFileName.'_schema.csv', 'r' );//open table schema

$tableSchema = fgetcsv ( $handle_schema );
$newTableName = "$tableSchema[0]_" . date ( "Ymd" );

// change table name in schema
$tableSchema [1] = preg_replace ( "/$tableSchema[0]/", $newTableName, $tableSchema [1], 1 );

$conn->query ( "DROP TABLE IF EXISTS $newTableName" );
$conn->query ( $tableSchema [1] );//CREATE TABLE 
//  $conn->query ( "CREATE TABLE $newTableName LIKE $tableToConvert");//the same, but without using schema

while ( $row = fgetcsv ( $handle ) ) {
    $data = implode ( "','", $row ); //convert array to string

    if ($conn->query ( "INSERT INTO $newTableName values('$data')" )) {
    } else {
        echo '<span style="color:red">Problem:</span> ' . $data . '<br>';
        var_dump ( $conn->error_list );
        fclose ( $handle );
        exit ();
    }
}

fclose ( $handle ); 
echo 'Congratulation!!!' . '<br>';

score 0 · Answer 3 · answered Jan 11 '12 at 08:17

0

see fgetcsv - http://php.net/manual/en/function.fgetcsv.php, has a good example there also, but I wouldn't store in an array first though, put them straight into the database so that you save ram and an extra loop.

answered Jan 11 '12 at 08:17

Geoffrey

10,843
3
33
46

score 0 · Answer 4 · answered Jan 11 '12 at 08:46

This will upload a CSV file and import the data into the specified table.

//Import the contents of a CSV file after uploading it
//http://www.bin-co.com/php/scripts/csv_import_export/
//Aruguments : $table - The name of the table the data must be imported to
//                $fields - An array of fields that will be used
//                $csv_fieldname - The name of the CSV file field
function CSVImport($table, $fields, $csv_fieldname='csv') {
    if(!$_FILES[$csv_fieldname]['name']) return;

    $handle = fopen($_FILES[$csv_fieldname]['tmp_name'],'r');
    if(!$handle) die('Cannot open uploaded file.');

    $row_count = 0;
    $sql_query = "INSERT INTO $table(". implode(',',$fields) .") VALUES(";

    $rows = array();

    //Read the file as csv
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        $row_count++;
        foreach($data as $key=>$value) {
            $data[$key] = "'" . addslashes($value) . "'";
        }
        $rows[] = implode(",",$data);
    }
    $sql_query .= implode("),(", $rows);
    $sql_query .= ")";
    fclose($handle);

    if(count($rows)) { //If some recores  were found,
        //Replace these line with what is appropriate for your DB abstraction layer
        mysql_query("TRUNCATE TABLE $table") or die("MySQL Error: " . mysql_error()); //Delete the existing records
        mysql_query($sql_query) or die("MySQL Error: " . mysql_error()); // and insert the new ones.

        print 'Successfully imported '.$row_count.' record(s)';
    } else {
        print 'Cannot import data - no records found.';
    }
}

for more details refer this link : http://www.bin-co.com/php/scripts/csv_import_export/

Read CSV file and store into MySQL Database

4 Answers4

Linked