how to insert csv array data in php mysql with respect to columns

Question

I want to import the data from different CSV file to MySQL. (different files consist required columns on different positions but the header values are same in all the files)

Some CSV files are like;

-------------------------------------------------
|   Name, Father Name,  Contact, City, email,   |
-------------------------------------------------
|   Ali, Ahmed, 123456, isb. , ali@mail.com,    |
|   Fazi, Khan, 123456, hyd. , faiz@ymail.com,  |
-------------------------------------------------

And sometime the CSV files are like;

-------------------------------------------------
|   Name, Father Name,  Contact,  email, City,  |
-------------------------------------------------
|   Ali, Ahmed, 123456,  ali@mail.com,   isb. , |
|   Fazi, Khan, 123456,  faiz@ymail.com, hyd. , |
-------------------------------------------------

After exploring and working on many suggestions I got this solution. but I don't know how can I use this code to insert the rows of array in database.

$names      = array('Name', 'Father Name', 'contact', 'email');
$picked     = array();
$theData    = array();
$isFirstRow = true;
if (($handle = fopen("test.csv", "r")) !== FALSE) {
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
    $numCols = count($data);
    $row     = array();


    // first row to select columns for extraction
    if($isFirstRow) {
        for($c=0; $c<$numCols; $c++)
            if(!in_array($data[$c], $names)
                continue;
            else
                $picked[] = $c;
        $isFirstRow = false;
    }

    // process remaining rows
    else {
        for($c=0; $c < $numCols; $c++)
            if(in_array($c, $picked))
                $row[] = $data[$c];
        $theData[] = $row;
    }

  }
fclose($handle);
}

Kindly help me that how I can insert the data of $theData[] in MySQL according to the $names[]. Thank You in Advance :)

Have you considered using the `LOAD DATA LOCAL INFILE` query in MySQL? — Barmar, Jun 30 '17 at 16:08
Have a look over [this answer](https://stackoverflow.com/a/44118215/3584881) — Bhaumik Pandhi, Jun 30 '17 at 16:09
@Barmar Thank you for your response, I think `LOAD DATA LOCAL INFILE` doesn't work to load specific columns, If it can so please let me know. — Ali Barkati, Jun 30 '17 at 16:24
It does. See https://stackoverflow.com/questions/25859217/mysql-import-csv-excel-file-into-mysql — Barmar, Jun 30 '17 at 16:32

score 1 · Accepted Answer · answered Jun 30 '17 at 16:31

I've battled this many times, and the most versatile solution I have come up with is to "map" your known fields to the column in the CSV represents that field.

Below is your code (modified), with comments, to explain the process.

Note that this routine requires that the first row of the CSV contains field names, and that they match your required field names.

$names      = array( 'Name', 'Father Name', 'contact', 'email' );
$picked     = array();
$theData    = array();
// new array to store the "mapping"
$map        = array();

$handle = fopen("test.csv", "r");
if ( FALSE !== $handle ) {
    // get the first row
    $row = fgetcsv( $handle, 1000, ',');

    // loop over desired fields, assign the column index to map array
    foreach( $names AS $name ) {
        // array_search will find the field name in the row array and return the index
        // note: this is a case-insensitive array-search
        // see this Q&A for more info: https://stackoverflow.com/a/4170079/870729
        $index = array_search( strtolower( $name ), array_map( 'strtolower', $row ) );
        if ( FALSE !== $index ) {
             $map[ $index ] = $name;
        }
    }

    // if not all fields present, error and exit
    if ( count( $map ) < count( $names ) ) {
         echo 'All fields must be present: ' . implode( ', ', $names );
         die();
    }

    while ( $data = fgetcsv($handle, 1000, "," ) ) {
        $row     = array();

        // loop over known fields / index and assign to record
        foreach( $map AS $index => $field ) {
             // $index is the column number / index
             // $field is the name of the field
             $row[ $field ] = $data[ $index ];
        }    

        $theData[] = $row;
    }

    fclose($handle);
}

Now when you look at $theData, it should contain full "field mapping" for you, so you could insert each row based on those field names:

Example:

var_dump( $theData[0] );

Will result in something like so:

array(
    'Name'        => 'Ali',
    'Contact'     => '123456',
    'Father Name' => 'Ahmed',
    'email'       => 'ali@mail.com'
    'City'        => 'isb.'
);

Regardless of the order of the columns in the CSV.

@cale_b, could you please help https://stackoverflow.com/questions/47455990/php-csv-import-need-help-decimal-values-becomes-rounded-values-when-inserting — davidb, Nov 23 '17 at 13:46
@cale_b, could you please help https://stackoverflow.com/questions/47473001/how-to-convert-few-columns-as-array-in-php-while-csv-import — davidb, Nov 25 '17 at 09:41

how to insert csv array data in php mysql with respect to columns

1 Answers1