Import data from excel to databse

Senthilkumar · 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

Phi11W · 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.

Senthilkumar · 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

ginerjm · November 3, 2023

How about showing us the code? That link you gave - what is it?

Senthilkumar · November 3, 2023

The code is available with the link is provided above

ginerjm · November 3, 2023

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

phppup · November 3, 2023

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

ginerjm · 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...

Senthilkumar · 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); 
 
?>

ginerjm · November 3, 2023

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

Barand · November 3, 2023

According to the error message, the relevant file is cell.php. You seem to have posted everything but that.

cyberRobot · 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

cyberRobot · 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?

kicken · 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.

Barand · 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.

Barand · 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?

mac_gyver · 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');
}

Senthilkumar · November 4, 2023

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

maxxd · 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.

Barand · 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
               ");

Senthilkumar · 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.

Senthilkumar · 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

Barand · 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?

Senthilkumar · November 17, 2023

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

Barand · 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);
    }

Sign In

Import data from excel to databse

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information