PHP mysql import data through excel

Question

I want to import data using php mysql from excel sheet containing 1.6 million records. What is the best way to do this?

this is the sample code I have used to iterate excel file and insert data in database:

public function iterateData($file_name) {
        $fileDirectory = '';
        $file_name = $fileDirectory . $file_name;
        if (file_exists($file_name)) {
            $this->truncateTable();
            include 'PHPExcel2/Classes/PHPExcel/IOFactory.php';
            $objReader = PHPExcel_IOFactory::createReader('Excel2007');
            $objPHPExcel = $objReader->load($file_name);
            $count = 1;
            foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
                foreach ($worksheet->getRowIterator() as $row) {
                    $cellIterator = $row->getCellIterator();
                    $cellIterator->setIterateOnlyExistingCells(true); // Loop all cells, even if it is not set
                    $cellValues = array();
                    foreach ($cellIterator as $cell) {
                        if (!is_null($cell)) {
                            $cellValues[] = $cell->getCalculatedValue();
                        }
                    }
                    if (isset($cellValues[0]) && $cellValues[0] != 'Product' && $cellValues[0] != '') {
                        $this->inserInDatabase($cellValues);
                    } elseif (empty($cellValues[0]) && empty($cellValues[1]) && empty($cellValues[2])) {
                        continue;
                    }
                }
                if ($objPHPExcel->getSheetCount() == $count) {
                    return TRUE;
                }
                $count++;
            }
        } else {
            return FALSE;
        }
    } private function inserInDatabase($data) {
        $dbDetails = array(
            'db_name' => '*',
            'db_pass' => '*',
            'db_host' => 'localhost',
            'db_user' => '*'
        );
        $dbh = dbConnect::connect($dbDetails);
        $date = date('Y-m-d H:i:s');
        $sql = "INSERT INTO product_description (product_id, prpoduct_description, price, created_date) values ('" . mysql_escape_string($data[0]) . "', '" . mysql_escape_string($data[1]) . "', '" . mysql_escape_string($data[2]) . "', '$date')";
        if (!$dbh->dbh->query($sql)) {
            die('Database Connection Failed.');
        }
    }

16*100,000 is more record than an Excel file can hold in a single worksheet (the limit is 1,048,576 rows); so is this just a CSV file, or is it an Excel file with multiple worksheets? — Mark Baker
– Mark Baker, Commented May 16, 2016 at 11:54
So you're currently using PHPExcel, and (I'd guess) having memory problems? Consider loading each worksheet one at time; or chunk loading blocks of rows at a time; and cell caching? — Mark Baker
– Mark Baker, Commented May 16, 2016 at 12:11

Ibrahim Fleifel · Accepted Answer · 2016-05-16 11:40:43Z

1

export you excel data to csv format, and then import the csv format to mysql

answered May 16, 2016 at 11:40

Ibrahim Fleifel

1763 bronze badges

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

Comments

gayatri darade · Accepted Answer · 2019-01-09 12:30:28Z

you can import using ; if($request->hasFile('excelFile')){

        $inputFileType = 'Xlsx';

        $inputFileName = $request->file('excelFile')->getRealPath();

        /**  Create a new Reader of the type defined in $inputFileType  **/

        $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);

        /**  Advise the Reader that we only want to load cell data  **/

        $reader->setReadDataOnly(true);

        /**  Load $inputFileName to a Spreadsheet Object  **/

        $spreadsheet = $reader->load($inputFileName);

        foreach ($spreadsheet->getActiveSheet()->toArray() as $key => $row) {

                $data['question'] = $row[0];

                $data['option1'] = $row[1];

                $data['option2'] = $row[2];

                $data['option3'] = $row[3];

                $data['option4'] = $row[4];

                $data['correct'] = $row[5];

                $data['status'] = 1;

                $data['receiver'] = 'all';

                $data['createdOn'] = date("Y-m-d H:i:s");

                if(!empty($data)) {
                    DB::table('questions')->insert($data);
                }
        }

    }

Collectives™ on Stack Overflow

PHP mysql import data through excel

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related