Senthilkumar Posted November 3, 2023 Share Posted November 3, 2023 Dear Team, Every month, I am downloading the billing data from SAP in Excel and importing it into the MySQL database (not all the columns from Excel). For one month, the downloaded file size was 17 MB, and it has over 31000 rows in Excel. So the file is not getting imported into the database. To reduce the number of rows, I planned to group the data in the columns Material (11) and Sales Office (45), like we are using in the MySQL query (group by Material). So that the rows will be reduced while inserting into the database. I am attaching my dump, PHP, and Excel at the below link. https://drive.google.com/file/d/1fTS-mkTKe93ZtxMmn8afkK6L2lUSn7PH/view?usp=sharing Please suggest how to do this.z Quote Link to comment Share on other sites More sharing options...
Phi11W Posted November 3, 2023 Share Posted November 3, 2023 MySQL should be able to cope with loading 31000 rows with ease. What error(s) are you getting when try to load it? I would suggest loading the whole file into a "staging" table and then transferring data from that into your "proper" tables. Regards, Phill W. Quote Link to comment Share on other sites More sharing options...
Senthilkumar Posted November 3, 2023 Author Share Posted November 3, 2023 I am getting the following error Fatal error: Allowed memory size of 536870912 bytes exhausted (tried to allocate 33554432 bytes) in file:///C:/Users/senthilkumar.rp/Desktop/Calibration/Calibration/Calibration/Admin/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Cell/Cell.php:209 Quote Link to comment Share on other sites More sharing options...
ginerjm Posted November 3, 2023 Share Posted November 3, 2023 How about showing us the code? That link you gave - what is it? Quote Link to comment Share on other sites More sharing options...
Senthilkumar Posted November 3, 2023 Author Share Posted November 3, 2023 The code is available with the link is provided above Quote Link to comment Share on other sites More sharing options...
ginerjm Posted November 3, 2023 Share Posted November 3, 2023 (edited) Well - I clicked on it and all I see are filenames. Clicking on them does nothing. We like to actually SEE code that you want us to examine. Especially the part the is heart of the problem and not all the rest of it. That usually means the php part and not the css or html part. So please copy and paste in into your next response (using the <> icon to place into a block by itself) and that will give us all a chance to help you. Edited November 3, 2023 by ginerjm Quote Link to comment Share on other sites More sharing options...
phppup Posted November 3, 2023 Share Posted November 3, 2023 (edited) I tried the link also, but got nowhere because I do not have a Google account. One thought: if the file is too large, can you move it in batches (maybe 1000 rows at a time)? Edited November 3, 2023 by phppup Quote Link to comment Share on other sites More sharing options...
ginerjm Posted November 3, 2023 Share Posted November 3, 2023 Perhaps if we do see the code we can see how he is handling it. But I'm thinking that is not going to happen... Quote Link to comment Share on other sites More sharing options...
Senthilkumar Posted November 3, 2023 Author Share Posted November 3, 2023 Billing.php <?PHP include("../connection.php"); ?> <!DOCTYPE html> <html lang="en"> <head> <meta charset="utf-8" /> <meta http-equiv="X-UA-Compatible" content="IE=edge" /> <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no" /> <meta name="description" content="" /> <meta name="author" content="" /> <title>Branch Table</title> <link href="https://cdn.jsdelivr.net/npm/simple-datatables@7.1.2/dist/style.min.css" rel="stylesheet" /> <link href="css/styles.css" rel="stylesheet" /> <link href="css/bootstrap.min.css" rel="stylesheet" /> <link href="vendor/fontawesome-free/css/fontawesome.min.css" rel="stylesheet" /> <script src="https://use.fontawesome.com/releases/v6.3.0/js/all.js" crossorigin="anonymous"></script> <link href="vendor/fontawesome-free/css/all.min.css" rel="stylesheet" type="text/css" /> <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/1.12.1/jquery.min.js"></script> <script src="https://cdnjs.cloudflare.com/ajax/libs/alasql/0.5.5/alasql.min.js"></script> </head> <body class="sb-nav-fixed" style="font-family:Cambria"> <nav class="sb-topnav navbar navbar-expand navbar-light bg-secondary border border-success p-2 mb-2 border-opacity-50 shadow"> <!-- Navbar Brand--> <img src="../Image/Logo.png" style="margin-left:5px" /> <!-- Sidebar Toggle--> <button class="btn btn-link btn-sm order-1 order-lg-0 me-4 me-lg-0 text-white" id="sidebarToggle" href="#!" style="margin-left:15px"> <i class="fas fa-bars"></i> </button> <!-- Navbar Search--> <form class="d-none d-md-inline-block form-inline ms-auto me-0 me-md-3 my-2 my-md-0 text-white"> <div class="input-group"> Srilekha R </div> </form> <!-- Navbar--> <ul class="navbar-nav ms-auto ms-md-0 me-3 me-lg-4 text-white"> <li class="nav-item dropdown text-white"> <a class="nav-link dropdown-toggle text-white" id="navbarDropdown" href="#" role="button" data-bs-toggle="dropdown" aria-expanded="false"> <i class="fas fa-user fa-fw"></i> </a> <ul class="dropdown-menu dropdown-menu-end" aria-labelledby="navbarDropdown"> <li> <a class="dropdown-item" href="#!">Settings</a> </li> <li> <a class="dropdown-item" href="#!">Activity Log</a> </li> <li> <hr class="dropdown-divider" /> </li> <li> <a class="dropdown-item" href="#!">Logout</a> </li> </ul> </li> </ul> </nav> <style> .sb-sidenav-menu .nav .nav-link .sb-nav-link-icon { color: white; } .sb-sidenav-menu .nav .nav-link { color: white; } .sb-sidenav-menu .nav .nav-link .sb-nav-link-icon { color: white; } .sb-sidenav-menu .nav .nav-link:hover { color: black; box-shadow: inset 2px 2px 2px 2px gray; background-color: lightgray } </style> <div id="layoutSidenav"> <div id="layoutSidenav_nav" style="margin-top:57px !important; height:100vh"> <nav class="sb-sidenav accordion sb-sidenav bg-secondary border border-success p-2 mb-2 border-opacity-50 shadow" id="sidenavAccordion"> <div class="sb-sidenav-menu"> <div class="nav"> <a class="nav-link " href="dashboard.php"> <div class="sb-nav-link-icon"> <i class="fas fa-tachometer-alt"></i> </div> Dashboard </a> <a class="nav-link collapsed" href="#" data-bs-toggle="collapse" data-bs-target="#collapseLayouts" aria-expanded="false" aria-controls="collapseLayouts"> <div class="sb-nav-link-icon"> <i class="fas fa-columns"></i> </div> Master <div class="sb-sidenav-collapse-arrow"> <i class="fas fa-angle-down"></i> </div> </a> <div class="collapse fade-up" id="collapseLayouts" aria-labelledby="headingOne" data-bs-parent="#sidenavAccordion"> <nav class="sb-sidenav-menu-nested nav"> <a class="nav-link" href="layout-static.html">Manpower Master</a> <a class="nav-link" href="layout-sidenav-light.html">Infrastructure</a> <a class="nav-link" href="layout-sidenav-light.html">Engineer Skill Matrix</a> <a class="nav-link" href="Department_Master.php">Department Master</a> <a class="nav-link" href="Branch_Master.php">Branch Master</a> </nav> </div> <a class="nav-link collapsed" href="#" data-bs-toggle="collapse" data-bs-target="#projectionandBilling" aria-expanded="false" aria-controls="collapseLayouts"> <div class="sb-nav-link-icon"> <i class="fas fa-columns"></i> </div> Projection & Billing <div class="sb-sidenav-collapse-arrow"> <i class="fas fa-angle-down"></i> </div> </a> <div class="collapse fade-up" id="projectionandBilling" aria-labelledby="headingOne" data-bs-parent="#sidenavAccordion"> <nav class="sb-sidenav-menu-nested nav"> <a class="nav-link" href="layout-static.html">Monthly Projection</a> <a class="nav-link" href="layout-sidenav-light.html">Monthly Billing</a> </nav> </div> <a class="nav-link collapsed" href="#" data-bs-toggle="collapse" data-bs-target="#collapsePages" aria-expanded="false" aria-controls="collapsePages"> <div class="sb-nav-link-icon"> <i class="fas fa-book-open"></i> </div> Pages <div class="sb-sidenav-collapse-arrow"> <i class="fas fa-angle-down"></i> </div> </a> <div class="collapse" id="collapsePages" aria-labelledby="headingTwo" data-bs-parent="#sidenavAccordion"> <nav class="sb-sidenav-menu-nested nav accordion" id="sidenavAccordionPages"> <a class="nav-link collapsed" href="#" data-bs-toggle="collapse" data-bs-target="#pagesCollapseAuth" aria-expanded="false" aria-controls="pagesCollapseAuth"> Authentication <div class="sb-sidenav-collapse-arrow"> <i class="fas fa-angle-down"></i> </div> </a> <div class="collapse" id="pagesCollapseAuth" aria-labelledby="headingOne" data-bs-parent="#sidenavAccordionPages"> <nav class="sb-sidenav-menu-nested nav"> <a class="nav-link" href="login.html">Login</a> <a class="nav-link" href="register.html">Register</a> <a class="nav-link" href="password.html">Forgot Password</a> </nav> </div> <a class="nav-link collapsed" href="#" data-bs-toggle="collapse" data-bs-target="#pagesCollapseError" aria-expanded="false" aria-controls="pagesCollapseError"> Error <div class="sb-sidenav-collapse-arrow"> <i class="fas fa-angle-down"></i> </div> </a> <div class="collapse" id="pagesCollapseError" aria-labelledby="headingOne" data-bs-parent="#sidenavAccordionPages"> <nav class="sb-sidenav-menu-nested nav"> <a class="nav-link" href="401.html">401 Page</a> <a class="nav-link" href="404.html">404 Page</a> <a class="nav-link" href="500.html">500 Page</a> </nav> </div> </nav> </div> <a class="nav-link" href="charts.html"> <div class="sb-nav-link-icon"> <i class="fas fa-chart-area"></i> </div> Charts </a> <a class="nav-link" href="tables.html"> <div class="sb-nav-link-icon"> <i class="fas fa-table"></i> </div> Tables </a> </div> </div> <div class="sb-sidenav-footer text-white" style="margin-bottom:55px"> <div class="small">Last Logged in:</div> 16-Oct-2023 10.52 PM </div> </nav> </div> <div id="layoutSidenav_content"> <main class="text-muted"> <div class="container-fluid px-4"> <h2 class="mt-2">Outstanding Report</h2> </div> <!--End of download file--> <!--upload file--> <?php if (!empty($_GET['status'])) { switch ($_GET['status']) { case 'succ': $statusType = 'alert-success'; $statusMsg = 'Member data has been imported successfully.'; break; case 'err': $statusType = 'alert-danger'; $statusMsg = 'Something went wrong, please try again.'; break; case 'invalid_file': $statusType = 'alert-danger'; $statusMsg = 'Please upload a valid Excel file.'; break; default: $statusType = ''; $statusMsg = ''; } } ?> <!-- Display status message --> <?php if (!empty($statusmsg)) { ?> <div class="col-xs-12 p-3"> <div class="alert <?php echo $statustype; ?>"> <?php echo $statusmsg; ?> </div> </div> <?php } ?> <div class="row p-3"> <!-- import link --> <!--Upload code--> <div class="col-md-12 head"> <div class="float-start"> <a href="javascript:void(0);" class="btn btn-success" onclick="formToggle();"> <i class="fas fa-plus"></i> Import Excel </a> </div> </div> <!-- excel file upload form --> <div class="col-md-12 mt-4" id="importfrm" style="display: none;"> <form class="row g-3" action="Billing_Import.php" method="post" enctype="multipart/form-data" style="border: 2px dashed #007bff;float:left"> <div class="col-auto"> <label for="fileinput" class="visually-hidden">file</label> <input type="file" class="form-control" name="file" id="fileinput" /> </div> <div class="col-auto"> <input type="submit" class="btn btn-primary mb-3" name="importsubmit" value="import" /> </div> </form> </div> </div> </main> </div> </div> <script src="vendor/bootstrap/js/bootstrap.bundle.min.js" crossorigin="anonymous"></script> <script src="js/scripts.js"></script> <script src="vendor/bootstrap/js/bootstrap5.2.bundle.min.js"></script> <script src="vendor/jquery/jquery.min.js"></script> <script src="vendor/bootstrap/js/bootstrap.js"></script> <script src="js/Toggle.js"></script> </body> </html> Billing_Import.php <?php // Load the database configuration file include_once 'connection.php'; // Include PhpSpreadsheet library autoloader require_once 'vendor/autoload.php'; use PhpOffice\PhpSpreadsheet\Reader\Xlsx; if(isset($_POST['importsubmit'])){ // Allowed mime types $excelMimes = array('text/xls', 'text/xlsx', 'application/excel', 'application/vnd.msexcel', 'application/vnd.ms-excel', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); // Validate whether selected file is a Excel file if(!empty($_FILES['file']['name']) && in_array($_FILES['file']['type'], $excelMimes)){ // If the file is uploaded if(is_uploaded_file($_FILES['file']['tmp_name'])){ $reader = new Xlsx(); $spreadsheet = $reader->load($_FILES['file']['tmp_name']); $worksheet = $spreadsheet->getActiveSheet(); $worksheet_arr = $worksheet->toArray(); // Remove header row unset($worksheet_arr[0]); foreach($worksheet_arr as $row){ $Sales_Document_Type = $row[0]; $Billing_Date = $row[2]; $Material= $row[11]; $Gross_Amount= $row[25]; $Sales_Office= $row[45]; $Plant= $row[47]; $Dateformat = date('Y-m-d', strtotime($Billing_Date)); $amount = str_replace(',', '', $Gross_Amount); // Check whether member already exists in the database with the same email $prevQuery = "SELECT billing FROM members WHERE Billing_Date = '".$Dateformat."' "; $prevResult = mysqli_query($conn, $prevQuery); if($prevResult->num_rows > 0){ // Update member data in the database $db = mysqli_query($conn,"UPDATE members SET first_name = '".$first_name."', last_name = '".$last_name."', email = '".$email."', phone = '".$phone."', status = '".$status."', modified = NOW() WHERE email = '".$email."'"); }else{ // Insert member data in the database $db = "INSERT INTO billing (Sales_Document_Type, Billing_Date, Gross_Amount, Sales_Office, Plant, Material) VALUES ('$Sales_Document_Type', '$Dateformat', '$amount', '$Sales_Office', '$Plant','$Material')"; $result = mysqli_query($conn, $db); } } $qstring = '?status=succ'; }else{ $qstring = '?status=err'; } }else{ $qstring = '?status=invalid_file'; } } // Redirect to the listing page header("Location: Billing.php".$qstring); ?> Quote Link to comment Share on other sites More sharing options...
ginerjm Posted November 3, 2023 Share Posted November 3, 2023 (edited) As I said - we really don't need to see the html and non-php stuff. Show where the line 209 of the error message is and the lines leading up to that error. Please. Edited November 3, 2023 by ginerjm Quote Link to comment Share on other sites More sharing options...
Barand Posted November 3, 2023 Share Posted November 3, 2023 According to the error message, the relevant file is cell.php. You seem to have posted everything but that. Quote Link to comment Share on other sites More sharing options...
cyberRobot Posted November 3, 2023 Share Posted November 3, 2023 31 minutes ago, Barand said: According to the error message, the relevant file is cell.php. That's likely part of the PhpSpreadsheet solution being used. This is probably the GitHub repo:https://github.com/PHPOffice/PhpSpreadsheet/tree/master/src/PhpSpreadsheet/Cell Quote Link to comment Share on other sites More sharing options...
cyberRobot Posted November 3, 2023 Share Posted November 3, 2023 8 hours ago, Senthilkumar said: I am getting the following error Fatal error: Allowed memory size of 536870912 bytes exhausted (tried to allocate 33554432 bytes) in file:///C:/Users/senthilkumar.rp/Desktop/Calibration/Calibration/Calibration/Admin/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Cell/Cell.php:209 Are you getting the error when running Billing_Import.php? If so, have you looked into minimizing the queries executed within the foreach loop? I'm guessing that the script is running 31,000 select queries, for example. What about building a single query in advance...then running it after processing the 31,000 rows? Quote Link to comment Share on other sites More sharing options...
kicken Posted November 3, 2023 Share Posted November 3, 2023 PHPSpreadsheet uses a lot of memory, about 1.6KB per cell according to the docs. If I did my math right, you spreadsheet contains just over 2.3 million cells, meaning it will require roughly 3.7 Gigabytes of memory just to load the data in the cells. Even more for the various other things your script is going to be doing. The documentation linked above talks about ways you can implement a cache to try and conserve memory. You can also adjust the settings to load only data and apply a filter to control which data is loaded. The best option, in my opinion though, is to abandon trying to read actual excel files and instead generate and read CSV files. You can easily convert your worksheet to a CSV by opening it in Excel and re-saving it as a CSV, then upload that to your import script. You can then read this CSV file line-by-line and only use as much memory as a single line requires. Quote Link to comment Share on other sites More sharing options...
Barand Posted November 3, 2023 Share Posted November 3, 2023 And in redesigning the app as @kicken suggested, you should be able to achieve your aims with 2 or 3 queries instead of the 62,000 you are currently running. Quote Link to comment Share on other sites More sharing options...
Barand Posted November 3, 2023 Share Posted November 3, 2023 In the members update query ... UPDATE members SET first_name = '$first_name', last_name = '$last_name', email = '$email', phone = '$phone', status = '$status', modified = NOW() WHERE email = '$email'; where do the variables ($first_name, $last_name , $email, $phone, $status, $modified) come from? Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted November 4, 2023 Share Posted November 4, 2023 once you switch to using a .csv file, you need to use a data-driven design, where you simply define a data structure (array) that controls what general purpose code does. see the following (untested, but should work) example - <?php /* data used - [0] => Sales Document Type [2] => Billing Date [11] => Material [25] => Gross Amount [45] => Sales Office [47] => Plant */ /* note: if you insure that the order in the following definition matches the order in the data file, you can use the array intersection method in the following code. otherwise, you must use the (commented out) loop method in the following code. */ // define mapping of data index to database column $db_col = [0=>'Sales_Document_Type', 2=>'Billing_Date', 11=>'Material', 25=>'Gross_Amount', 45=>'Sales_Office', 47=>'Plant']; // database table name $table = 'billing'; // number of initial lines to skip in the file $skip = 1; // for demonstration purposes, the file is hard-coded to be the converted csv of the example . xlsx file // in the actual code, this would be set to the temporary uploaded file $file = 'Aug Spares.XLSX - Sheet1.csv'; // open file if (($handle = fopen($file, "r")) !== FALSE) { // dynamically build the sql query $sql = "INSERT INTO `$table` (`".implode('`,`',array_values($db_col))."`) VALUES (".implode(',',array_fill(0,count($db_col),'?')).")"; // this example uses the much simpler and more modern PDO database extension $stmt = $pdo->prepare($sql); // start line counter at 1 $line = 1; // read lines from file while (($row = fgetcsv($handle, 1000, ",")) !== FALSE) { // process lines after initial skipped lines if($line > $skip) { // special processing // convert Billing_Date to standard format $row[2] = date('Y-m-d', strtotime($row[2])); // remove comma separator from Gross_Amount $row[25] = str_replace(',', '', $row[25]); // build array of value using array intersection method $params = array_values(array_intersect_key($row,$db_col)); /* // build array of values using a loop $params = []; foreach(array_keys($db_col) as $key) { $params[] = $row[$key]; } */ $stmt->execute($params); } $line++; } fclose($handle); } else { die('unable to open uploaded (temporary) file'); } Quote Link to comment Share on other sites More sharing options...
Senthilkumar Posted November 4, 2023 Author Share Posted November 4, 2023 (edited) Dear All, Thanks for the reply. As instructed by Mr. MacGyver, I modified the code. The table is now being filled with all of the data that has been imported. <?php // Load the database configuration file define("HOST",'localhost'); define("USERNAME", 'root'); define("PASSWORD", '123456'); define("DATABASE", 'sbms'); // default database name function pdoConnect($dbname=DATABASE) { $db = new PDO("mysql:host=".HOST.";dbname=$dbname;charset=utf8",USERNAME,PASSWORD); $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC); $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); return $db; } $pdo = pdoConnect(); if (isset($_POST['importsubmit'])) { $db_col = [0 => 'Sales_Document_Type', 2 => 'Billing_Date', 11 => 'Material', 25 => 'Gross_Amount', 45 => 'Sales_Office', 47 => 'Plant']; // database table name $table = 'billing'; // number of initial lines to skip in the file $skip = 1; $file = $_FILES['file']['tmp_name']; // open file if (($handle = fopen($file, "r")) !== FALSE) { // dynamically build the sql query $sql = "INSERT INTO `$table` (`" . implode('`,`', array_values($db_col)) . "`) VALUES (" . implode(',', array_fill(0, count($db_col), '?')) . ")"; // this example uses the much simpler and more modern PDO database extension $stmt = $pdo->prepare($sql); // start line counter at 1 $line = 1; // read lines from file while (($row = fgetcsv($handle, 1000, ",")) !== FALSE) { // process lines after initial skipped lines if ($line > $skip) { // special processing // convert Billing_Date to standard format $row[2] = date('Y-m-d', strtotime($row[2])); // remove comma separator from Gross_Amount $row[25] = str_replace(',', '', $row[25]); // build array of value using array intersection method $params = array_values(array_intersect_key($row, $db_col)); $stmt->execute($params); } $line++; } fclose($handle); } else { die('unable to open uploaded (temporary) file'); } } // Redirect to the listing page header("Location: Billing.php"); ?> Is it possible to group the rows material- and plant-wise and sum the gross amount before importing? So that the number of rows will reduced while importing Edited November 4, 2023 by Senthilkumar Quote Link to comment Share on other sites More sharing options...
maxxd Posted November 4, 2023 Share Posted November 4, 2023 Don't worry about aggregating the data on the way into the database; do that while you're selecting the data from the database. All SQL dialects are built to handle large amounts of data efficiently by default (obviously the table schema design helps, but the point stands) so don't exclude data you may be able to use later. Quote Link to comment Share on other sites More sharing options...
Solution Barand Posted November 4, 2023 Solution Share Posted November 4, 2023 I'd do it like this... ################################################################################ # CREATE TEMPORARY STAGING TABLE # ################################################################################ $pdo->exec("CREATE TEMPORARY TABLE tempdata ( id int not null auto_increment primary key, sales_doc_type varchar(10), billing_date date, material varchar(10), gross_amount int, sales_office varchar(10), plant varchar(10) ) "); ################################################################################ # READ CSV, # # EXTRACT REQUIRED DATA, # # STORE IN ARRAY FOR INSERTION TO TEMP STAGING TABLE # ################################################################################ $fp = fopen('test1.csv', 'r'); $req_cols = [ 0 => 'sales_doc_type', 2 => 'billing_date', 11 => 'material', 25 => 'gross_amount', 45 => 'sales_office', 47 => 'plant' ]; $import_data = []; while ($allrow = fgetcsv($fp)) { $row = array_intersect_key($allrow, $req_cols); $randdays = rand(5,30); $row[2] = date('Y-m-d', strtotime($row[2])); $row[25] = str_replace(',', '', $row[25]); $import_data[] = vsprintf("('%s','%s','%s','%s','%s','%s')", $row); } fclose($fp); ################################################################################ # ADD RECS FROM ARRAY TO TEMP TABLE - 2000 AT A TIME # ################################################################################ $chunks = array_chunk($import_data, 2000); foreach ($chunks as $ch) { $sql = "INSERT INTO tempdata (sales_doc_type, billing_date, material, gross_amount, sales_office, plant) VALUES " . join(',', $ch); $pdo->exec($sql); } ###################################################################################################################### # # # Now we have the data from the csv stored in a temporary staging table # # which makes it easy to manipulate the data with queries to update our # # database tables with efficient queries rather than running multiple # # queries inside loops. # # # # NOTE - as I never received a reply to my question about the origin of the # # data used toupdate the members table I have had to omit that part # # of the processing. The UPDATE query will be similar to the INSERT # # below except it will update where there is a matching record # # # ###################################################################################################################### ################################################################################ # INSERT TEMPDATA REC INTO billing WHERE NO MATCHING billing_date IN members # ################################################################################ $pdo->exec("INSERT INTO billing (sales_doc_type, billing_date, gross_amount, sales_office, plant, material) SELECT t.sales_doc_type , t.billing_date , t.gross_amount , t.sales_office , t.plant , t.material FROM tempdata t LEFT JOIN members m ON DATE(t.billing_date) = m.billing_date WHERE m.billing_date IS NULL "); 1 Quote Link to comment Share on other sites More sharing options...
Senthilkumar Posted November 6, 2023 Author Share Posted November 6, 2023 Dear Mr. Barand, Thanks for providing the code. It is working perfectly and fast as well. On 11/4/2023 at 5:19 AM, Barand said: where do the variables ($first_name, $last_name , $email, $phone, $status, $modified) come from? Sorry for not replying. This is just testing data. I am not updating any rows. I am only inserting the data every month. There won't be any duplicate data updates. Quote Link to comment Share on other sites More sharing options...
Senthilkumar Posted November 17, 2023 Author Share Posted November 17, 2023 On 11/4/2023 at 11:37 PM, Barand said: ################################################################################ # CREATE TEMPORARY STAGING TABLE # ################################################################################ $pdo->exec("CREATE TEMPORARY TABLE tempdata ( id int not null auto_increment primary key, sales_doc_type varchar(10), billing_date date, material varchar(10), gross_amount int, sales_office varchar(10), plant varchar(10) ) "); ################################################################################ # READ CSV, # # EXTRACT REQUIRED DATA, # # STORE IN ARRAY FOR INSERTION TO TEMP STAGING TABLE # ################################################################################ $fp = fopen('test1.csv', 'r'); $req_cols = [ 0 => 'sales_doc_type', 2 => 'billing_date', 11 => 'material', 25 => 'gross_amount', 45 => 'sales_office', 47 => 'plant' ]; $import_data = []; while ($allrow = fgetcsv($fp)) { $row = array_intersect_key($allrow, $req_cols); $randdays = rand(5,30); $row[2] = date('Y-m-d', strtotime($row[2])); $row[25] = str_replace(',', '', $row[25]); $import_data[] = vsprintf("('%s','%s','%s','%s','%s','%s')", $row); } fclose($fp); ################################################################################ # ADD RECS FROM ARRAY TO TEMP TABLE - 2000 AT A TIME # ################################################################################ $chunks = array_chunk($import_data, 2000); foreach ($chunks as $ch) { $sql = "INSERT INTO tempdata (sales_doc_type, billing_date, material, gross_amount, sales_office, plant) VALUES " . join(',', $ch); $pdo->exec($sql); } ###################################################################################################################### # # # Now we have the data from the csv stored in a temporary staging table # # which makes it easy to manipulate the data with queries to update our # # database tables with efficient queries rather than running multiple # # queries inside loops. # # # # NOTE - as I never received a reply to my question about the origin of the # # data used toupdate the members table I have had to omit that part # # of the processing. The UPDATE query will be similar to the INSERT # # below except it will update where there is a matching record # # # ###################################################################################################################### ################################################################################ # INSERT TEMPDATA REC INTO billing WHERE NO MATCHING billing_date IN members # ################################################################################ $pdo->exec("INSERT INTO billing (sales_doc_type, billing_date, gross_amount, sales_office, plant, material) SELECT t.sales_doc_type , t.billing_date , t.gross_amount , t.sales_office , t.plant , t.material FROM tempdata t LEFT JOIN members m ON DATE(t.billing_date) = m.billing_date WHERE m.billing_date IS NULL "); Dear Mr.Barand, The above code is inserting headers along with data while importing a CSV file. In my CSV file, the last row has the total sum of the gross amount. How to ignore the header (first row) and sum value (last row) in this code Quote Link to comment Share on other sites More sharing options...
Barand Posted November 17, 2023 Share Posted November 17, 2023 Do an fgetcsv() prior to the loop to read the header and discard it $import_data = []; fgetcsv($fp); // discard the header row while ($allrow = fgetcsv($fp)) { $row = array_intersect_key($allrow, $req_cols); $randdays = rand(5,30); $row[2] = date('Y-m-d', strtotime($row[2])); $row[25] = str_replace(',', '', $row[25]); $import_data[] = vsprintf("('%s','%s','%s','%s','%s','%s')", $row); } The first row is easy - you know where it is. The last row is more of a problem. Is there a way of recognising it? Quote Link to comment Share on other sites More sharing options...
Senthilkumar Posted November 17, 2023 Author Share Posted November 17, 2023 (edited) Dear Mr.Barrand, Thanks for the reply. I changed the above code, and the header row is not inserting into the database. Except for the gross_amount column, all the columns are empty on the last row. Is it possible to ignore a value if it is empty? Edited November 17, 2023 by Senthilkumar Quote Link to comment Share on other sites More sharing options...
Barand Posted November 17, 2023 Share Posted November 17, 2023 if() statements are useful in situations like this $import_data = []; fgetcsv($fp); // discard the header row while ($allrow = fgetcsv($fp)) { $row = array_intersect_key($allrow, $req_cols); if (empty($row[0])) continue; // skip rows where no value in first col $randdays = rand(5,30); $row[2] = date('Y-m-d', strtotime($row[2])); $row[25] = str_replace(',', '', $row[25]); $import_data[] = vsprintf("('%s','%s','%s','%s','%s','%s')", $row); } Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.