Jump to content

Import data from excel to databse


Senthilkumar
Go to solution Solved by Barand,

Recommended Posts

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

Link to comment
Share on other sites

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. 

 

Link to comment
Share on other sites

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 by ginerjm
Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

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?

 

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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 by Senthilkumar
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • Solution

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

 

  • Like 1
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • 2 weeks later...
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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.