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. , [email protected],    |
|   Fazi, Khan, 123456, hyd. , [email protected],  |
-------------------------------------------------

And sometime the CSV files are like;

-------------------------------------------------
|   Name, Father Name,  Contact,  email, City,  |
-------------------------------------------------
|   Ali, Ahmed, 123456,  [email protected],   isb. , |
|   Fazi, Khan, 123456,  [email protected], 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
– Barmar, Commented Jun 30, 2017 at 16:08
@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
– Ali Barkati, Commented Jun 30, 2017 at 16:24

random_user_name · Accepted Answer · 2017-06-30 16:31:49Z

1

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'       => '[email protected]'
    'City'        => 'isb.'
);

Regardless of the order of the columns in the CSV.

answered Jun 30, 2017 at 16:31

random_user_name

26.3k7 gold badges81 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ali Barkati Over a year ago

Thank You @cale_b

random_user_name Over a year ago

Glad I could help!

davidb Over a year ago

@cale_b, could you please help stackoverflow.com/questions/47455990/…

davidb Over a year ago

@cale_b, could you please help stackoverflow.com/questions/47473001/…

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related