Need suggestions for insert date from excel (column wise) to mysql database (row wise)

Senthilkumar · November 6, 2023

Dear Experts,

I need suggestions from your side. I have an Excel sheet that I am using to enter the target values. My excel format looks like below image (column wise)

I want to import those values into the MySQL database table in the below format (row-wise) because my actual value is updated in the same format. If my target table is also in the same format, it will be easy to display target and actual

My excel file is attached with the below link

https://docs.google.com/spreadsheets/d/1V15nBijl0sVeh-vGsz1XlRoyzdcslMNI/edit?usp=sharing&ouid=102271950721773454662&rtpof=true&sd=true

Can anyone suggest how to do this? or suggest how to change the Excel file to get my required output.

Barand · November 6, 2023

22 minutes ago, Senthilkumar said:

I want to import those values into the MySQL database table in the below format

How may times do I have to tell you not to to store spreadsheets in a database?

I give up. As far as I'm concerned, you're on your own.

Senthilkumar · November 6, 2023

As per your previous suggestion, all the data will be stored in the database using IDs only. The image displayed is just a sample. Please refer to my attached Excel. There, I put IDs for all the columns. While inserting the data, I will use that ID only. If this format is wrong, can you please suggest the correct format?

Edited November 6, 2023 by Senthilkumar

Senthilkumar · November 7, 2023

Dear Mr.Barand,

Please guide me

Barand · November 7, 2023

Step 1 - export the Excel file to CSV.

Senthilkumar · November 7, 2023

ok.

Barand · November 7, 2023

Which excel format is the correct one, the one in your first post or the one in your link?

Senthilkumar · November 7, 2023

The Excel sheet that is in the link is correct. I hidden the ID column and took the snap from that excel for reference only

Barand · November 7, 2023

Very similar to the last one - I'm sure you should have been able to work it out. But, as an example for others...

################################################################################
#  CSV COLUMNS                                                                 #
################################################################################

$main     = [  1 => 'branch_id',
               3 => 'dept_id',
               5 => 'grp_id',
               7 => 'category_id',
               9 => 'sub_id'
            ];
            
$descrips = [ 10 => 'Wear Part',                // if you have ids for these items, use those instead of text descriptions
              11 => 'Consumables',
              12 => 'Accessories',
              13 => 'Electrical',
              14 => 'Other Parts',
              15 => 'Pipeline',
              16 => 'Mixer Drum'
            ];  

$qtrs     = [ 17 => 'Q1',
              18 => 'Q2',
              19 => 'Q3',
              20 => 'Q4'
            ];

################################################################################
#  READ CSV AND CREATE DATA ARRAY                                              #
################################################################################
            
$fp = fopen('test2.csv', 'r');

$hdr = fgetcsv($fp);  // ignore header row
$data = [];
while ($line = fgetcsv($fp))  {
     $reca = array_intersect_key($line, $main );
     $recc = array_intersect_key($line, $qtrs );
     foreach (array_intersect_key($line, $descrips ) as $k => $d)  {            // need a record for each description with a value
         if ($d) {
             $recb = [ $descrips[$k], (empty($d) ? 0 : $d ) ];
             $data[] = vsprintf("(%d,%d,%d,%d,%d,'%s',%d,%d,%d,%d,%d)", array_merge($reca, $recb, $recc));
         }
     }
}
fclose($fp);

################################################################################
#  WRITE RECORDS FROM ARRAY TO target TABLE                                    #
################################################################################

$chunks = array_chunk($data, 2000);
foreach ($chunks as $ch)  {
    $pdo->exec("INSERT INTO target (branch_id, dept_id, grp_id, category_id, sub_id, description, value, Q1, Q2, Q3, Q4)
                VALUES ". join(',', $ch)
               );
}

Senthilkumar · November 7, 2023

Dear Barrand,

Thanks for your support. I converted the Excel file to CSV. Then, using your code, I imported the data row-wise with ID only. Please refer the below image

image.png.d9345a422382499b937ae4aec1c5f203.png

And using the ID, i display name from different tables

				<table class="table">
						<tr>
							<th>Branch</th>
							<th>Department</th>
							<th>Group</th>
							<th>Product  Category</th>
							<th>Sub Category</th>
							<th>Description</th>
							<th>Target(Rs)</th>
							<th>Q1</th>
							<th>Q2</th>
							<th>Q3</th>
							<th>Q4</th>
						</tr>
					<?PHP
					$Query = "SELECT t.*, b.branchName, d.deptName,g.grpName FROM target t 
								INNER JOIN branch b ON t.branch_id = b.branchID
								INNER JOIN deparment d ON t.dept_id = d.deptID
								INNER JOIN group1 g ON t.grp_id = g.grpID";
					$result = mysqli_query($conn, $Query);
					while ($row = mysqli_fetch_assoc($result)) {

						?>
						<tr>
							<td><?PHP echo $row['branchName']; ?></td>
							<td><?PHP echo $row['deptName']; ?></td>
							<td><?PHP echo $row['grpName']; ?></td>
							<td><?PHP echo $row['category_id']; ?></td>
							<td><?PHP echo $row['sub_id']; ?></td>
							<td><?PHP echo $row['description']; ?></td>
							<td><?PHP echo $row['value']; ?></td>
							<td><?PHP echo $row['Q1']; ?></td>
							<td><?PHP echo $row['Q2']; ?></td>
							<td><?PHP echo $row['Q3']; ?></td>
							<td><?PHP echo $row['Q4']; ?></td>
						</tr>
						<?PHP
					}
					?>
				</table>

I think this time I am not using the database, like an Excel spread sheet.

Barand · November 7, 2023

It's better, but not there yet. The Qn percentages are the same throughout each group of 7 descriptions and should be in a separate table with each quarter in its own row

 target_pcents
------------------
  branch_id
  dept_id
  grp_id
  category_id
  sub_id
  year
  quarter
  pcent

The part descriptions are also repeated many times. They should be another table and the id used instead of the description

Senthilkumar · November 7, 2023

Thanks for the suggestion. I have a separate table for the description. For each description, there will be 8-digit codes available. Shall I use an 8-digit code or row ID

Edited November 7, 2023 by Senthilkumar

Barand · November 7, 2023

8 minutes ago, Senthilkumar said:

Shall I use an 8-digit code or row ID

Whichever is the primary key.

Senthilkumar · November 7, 2023

Ok. Thank you. Any other suggestion from your side for improving myself?

Edited November 7, 2023 by Senthilkumar

Barand · November 7, 2023

Learn about normalizing data. First, Second and Third Normal Forms should suffice.

For example, I am assuming that the quarterly target percentages are defined for each branch_id, dept_id, grp_id, category_id, sub_id but, as far as I know, they could be set at branch level or branch/department level or even company-wide. Normalization requires knowledge of your data which I do not have, nor can I see all the data (only 1 line in the link) to analyse for myself.

Senthilkumar · November 8, 2023

Dear Mr.Barand,

You are correct. The quarterly target percentage is set individually for each branch, department,group, category, and subcategory. Initially, the percentage will be set to 15, 25, 25, and 35 by default for all. The admin will change that percentage based on the branch performance for every year. The value he selected will be divided into 12 months based on the percentage entered.Please refer to the below image.

This above-mentioned monthly target is a basic target. Every month, the tatger will change based on the actual values updated. Consequetively the target will be added for the next month based on the actual update.

The target file and the formulated example file are attached here for your reference.

https://drive.google.com/drive/folders/1upVIFjTxfBKNgYhp2zK3jtooIeNhCLtN?usp=sharing

Barand · November 8, 2023

In which case ...

CREATE TABLE `target` (                                                                 CREATE TABLE `target_pcent` (
  `id` int(11) NOT NULL AUTO_INCREMENT,                                                   `id` int(11) NOT NULL AUTO_INCREMENT,
  `branch_id` int(11) DEFAULT NULL,                                                       `branch_id` int(11) DEFAULT NULL,
  `dept_id` int(11) DEFAULT NULL,                                                         `dept_id` int(11) DEFAULT NULL,
  `grp_id` int(11) DEFAULT NULL,                                                          `grp_id` int(11) DEFAULT NULL,
  `category_id` int(11) DEFAULT NULL,                                                     `category_id` int(11) DEFAULT NULL,
  `sub_id` int(11) DEFAULT NULL,                                                          `sub_id` int(11) DEFAULT NULL,
  `description` varchar(50) DEFAULT NULL,                                                 `year` year(4) DEFAULT NULL,
  `value` int(11) DEFAULT NULL,                                                           `quarter` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`)                                                                      `pcent` tinyint(4) DEFAULT NULL,
) ENGINE=InnoDB;                                                                          PRIMARY KEY (`id`)
                                                                                        ) ENGINE=InnoDB;
                                                                                        

Table: target                                                                           Table: target_pcent
+----+-----------+---------+--------+-------------+--------+-------------+-------+      +------+-----------+---------+--------+-------------+--------+------+---------+-------+
| id | branch_id | dept_id | grp_id | category_id | sub_id | description | value |      | id   | branch_id | dept_id | grp_id | category_id | sub_id | year | quarter | pcent |
+----+-----------+---------+--------+-------------+--------+-------------+-------+      +------+-----------+---------+--------+-------------+--------+------+---------+-------+
| 1  | 1         | 1       | 1      | 1           | 1      | Wear Part   | 100   |      | 1    | 1         | 1       | 1      | 1           | 1      | 2023 | 1       | 15    |
| 2  | 1         | 1       | 1      | 1           | 1      | Consumables | 50    |      | 2    | 1         | 1       | 1      | 1           | 1      | 2023 | 2       | 25    |
| 3  | 1         | 1       | 1      | 1           | 1      | Accessories | 50    |      | 3    | 1         | 1       | 1      | 1           | 1      | 2023 | 3       | 25    |
| 4  | 1         | 1       | 1      | 1           | 1      | Electrical  | 50    |      | 4    | 1         | 1       | 1      | 1           | 1      | 2023 | 4       | 35    |
| 5  | 1         | 1       | 1      | 1           | 1      | Other Parts | 50    |      +------+-----------+---------+--------+-------------+--------+------+---------+-------+
+----+-----------+---------+--------+-------------+--------+-------------+-------+

revised code ...

################################################################################
#  CSV COLUMNS                                                                 #
################################################################################

$main     = [  1 => 'branch_id',
               3 => 'dept_id',
               5 => 'grp_id',
               7 => 'category_id',
               9 => 'sub_id'
            ];
            
$descrips = [ 10 => 'Wear Part',
              11 => 'Consumables',
              12 => 'Accessories',
              13 => 'Electrical',
              14 => 'Other Parts',
              15 => 'Pipeline',
              16 => 'Mixer Drum'
            ];  

$qtrs     = [ 17 => 1,
              18 => 2,
              19 => 3,
              20 => 4
            ];

################################################################################
#  READ CSV AND CREATE DATA ARRAY                                              #
################################################################################
            
$fp = fopen('test2.csv', 'r');

$hdr = fgetcsv($fp);  // ignore header row
$data = [];
$pcdata = [];
while ($line = fgetcsv($fp))  {
     $reca = array_intersect_key($line, $main );
     $qarray = array_intersect_key($line, $qtrs );
     if (array_sum($qarray) == 0) {
         $qarray = [17=>15, 18=>25, 19=>25, 20=>35];          // default tatget percents
     }
     foreach ($qarray as $k => $pc)  {
         if ($pc) {
             $recc = [ date('Y'), $qtrs[$k], (empty($pc) ? 0 : $pc ) ];
             $pcdata[] = vsprintf("(%d,%d,%d,%d,%d,'%s',%d,%d)", array_merge($reca, $recc));
         }
     }
     
     foreach (array_intersect_key($line, $descrips ) as $k => $d)  {            // need a record for each description with a value
         if ($d) {
             $recb = [ $descrips[$k], (empty($d) ? 0 : $d ) ];
             $data[] = vsprintf("(%d,%d,%d,%d,%d,'%s',%d)", array_merge($reca, $recb));
         }
     }
}
fclose($fp);

################################################################################
#  WRITE RECORDS FROM ARRAYS TO target TABLE and target_pcent TABLE                                   #
################################################################################

$chunks = array_chunk($data, 2000);
foreach ($chunks as $ch)  {
    $pdo->exec("INSERT INTO target (branch_id, dept_id, grp_id, category_id, sub_id, description, value)
                VALUES ". join(',', $ch)
               );
}
         
$chunks = array_chunk($pcdata, 2000);
foreach ($chunks as $ch)  {
    $pdo->exec("INSERT INTO target_pcent (branch_id, dept_id, grp_id, category_id, sub_id, year, quarter, pcent)
                VALUES ". join(',', $ch)
               );
}

Senthilkumar · November 8, 2023

Dear Barand,

I created and split the data into two tables. But how can I display the percentage of Q1, Q2, Q3 and Q4 values in tables?

				<table class="table">
					<tr>
						<th>Branch</th>
						<th>Department</th>
						<th>Group</th>
						<th>Category</th>
						<th>Equipment</th>
						<th>Description</th>
						<th>Target(Rs)</th>
						<th>Q1(%)</th>
						<th>Q2(%)</th>
						<th>Q3(%)</th>
						<th>Q4(%)</th>
					</tr>
					<?PHP
					$Query = "SELECT t.*, b.branchName, d.deptName,g.grpName, c.catName, p.subCatName FROM target t
								INNER JOIN branch b ON t.branch_id = b.branchID
								INNER JOIN deparment d ON t.dept_id = d.deptID
								INNER JOIN dept_group g ON t.grp_id = g.grpID
								INNER JOIN category c ON t.category_id = c.catID
								INNER JOIN productcategory p ON t.sub_id = p.subCatID";
					$result = mysqli_query($conn, $Query);
					while ($row = mysqli_fetch_assoc($result)) {

						?>
						<tr>
							<td>
								<?PHP echo $row['branchName']; ?>
							</td>
							<td>
								<?PHP echo $row['deptName']; ?>
							</td>
							<td>
								<?PHP echo $row['grpName']; ?>
							</td>
							<td>
								<?PHP echo $row['catName']; ?>
							</td>
							<td>
								<?PHP echo $row['subCatName']; ?>
							</td>
							<td>
								<?PHP echo $row['description']; ?>
							</td>
							<td>
								<?PHP echo $row['value']; ?>
							</td>
							<td>
								<?PHP echo $row['Q1']; ?>
							</td>
							<td>
								<?PHP echo $row['Q2']; ?>
							</td>
							<td>
								<?PHP echo $row['Q3']; ?>
							</td>
							<td>
								<?PHP echo $row['Q4']; ?>
							</td>
						</tr>
						<?PHP
					}
					?>
				</table>

Barand · November 8, 2023

Something like this perhaps (I'm using ids here as I don't have all your supporting tables)

<?php

$tdata = "";
$data = [];

$res = $pdo->query("SELECT t.branch_id
                         , t.dept_id
                         , t.grp_id
                         , t.category_id
                         , t.sub_id
                         , concat(t.branch_id, t.dept_id, t.grp_id, t.category_id, t.sub_id, t.description) as `key`
                         , t.description
                         , t.value
                         , pc.quarter
                         , pc.pcent
                    FROM target t 
                         JOIN target_pcent pc USING (branch_id, dept_id, grp_id, category_id, sub_id)     
                   ");
foreach ($res as $row)  {
    if (!isset($data[$row['key']]))   {
        $data[$row['key']] = [ 'main' => [ 'bid' => $row['branch_id'],
                                           'did' => $row['dept_id'],
                                           'gid' => $row['grp_id'],
                                           'cid' => $row['category_id'],
                                           'sid' => $row['sub_id'],
                                           'desc' => $row['description'],
                                           'val' => $row['value']
                                         ],  
                               'qtrs' => []
                             ];
    }
    $data[$row['key']]['qtrs'][$row['quarter']] = $row['pcent'];
}

foreach ($data as $k => $row)  {
    $tdata .= "<tr><td>" . join("</td><td>", $row['main']) . "</td><td>" .join("</td><td>", $row['qtrs']) . "</td></tr>\n";
}
?>
<!DOCTYPE html>
<html lang='en'>
<head>
<meta charset='utf-8'>
<title>Example</title>
<style type='text/css'>
</style>
</head>
<body>
    <table class="table">
        <tr>
            <th>Branch</th>
            <th>Department</th>
            <th>Group</th>
            <th>Category</th>
            <th>Equipment</th>
            <th>Description</th>
            <th>Target(Rs)</th>
            <th>Q1(%)</th>
            <th>Q2(%)</th>
            <th>Q3(%)</th>
            <th>Q4(%)</th>
        </tr>
        <?= $tdata ?>
    </table>           
</body>
</html>

image.png.aa21cc305e25e44db98acca09b669adc.png

Senthilkumar · November 9, 2023

Dear Mr.Barand,

Please confirm the below method is correct or worng.

I modified the code for save the target month wise using the value and percentage.

<?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['partssubmit'])) {

################################################################################
#  CREATE TEMPORARY STAGING TABLE                                              #
################################################################################

	$pdo->exec("CREATE TEMPORARY TABLE tempdata (
            id int not null auto_increment primary key,
            branch_id int(11),
            dept_id int(11),
            grp_id int(11),
			category_id int(11),
            sub_id int(11),
            description varchar(50),
            value int(11),
			Q1 int(11),
			Q2 int(11),
			Q3 int(11),
            Q4 int(11)
            )
            ");

################################################################################
#  CSV COLUMNS                                                                 #
################################################################################

$main     = [  1 => 'branch_id',
               3 => 'dept_id',
               5 => 'grp_id',
               7 => 'category_id',
               9 => 'sub_id'
            ];
            
$descrips = [ 10 => 'Wear Part',                // if you have ids for these items, use those instead of text descriptions
              11 => 'Consumables',
              12 => 'Accessories',
              13 => 'Electrical',
              14 => 'Other Parts',
              15 => 'Pipeline',
              16 => 'Mixer Drum'
            ];  

$qtrs     = [ 17 => 'Q1',
              18 => 'Q2',
              19 => 'Q3',
              20 => 'Q4'
            ];

################################################################################
#  READ CSV AND CREATE DATA ARRAY                                              #
################################################################################
$file = $_FILES['file']['tmp_name'];            
$fp = fopen($file, 'r');

$hdr = fgetcsv($fp);  // ignore header row
$data = [];
while ($line = fgetcsv($fp))  {
     $reca = array_intersect_key($line, $main );
     $recc = array_intersect_key($line, $qtrs );
     foreach (array_intersect_key($line, $descrips ) as $k => $d)  {            // need a record for each description with a value
         if ($d) {
             $recb = [ $descrips[$k], (empty($d) ? 0 : $d ) ];
             $data[] = vsprintf("(%d,%d,%d,%d,%d,'%s',%d,%d,%d,%d,%d)", array_merge($reca, $recb, $recc));
         }
     }
}
fclose($fp);

################################################################################
#  WRITE RECORDS FROM ARRAY TO target TABLE                                    #
################################################################################

$chunks = array_chunk($data, 2000);
foreach ($chunks as $ch)  {
    $pdo->exec("INSERT INTO tempdata (branch_id, dept_id, grp_id, category_id, sub_id, description, value, Q1, Q2, Q3, Q4)
                VALUES ". join(',', $ch)
               );
}

$pdo->exec("INSERT INTO target1 (branch_id, dept_id, grp_id, category_id, sub_id, description, value, Q1, Q2, Q3, Q4,Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dece)
                SELECT t.branch_id
						, t.dept_id
						, t.grp_id
						, t.category_id
						, t.sub_id
						, t.description
						, t.value
						, t.Q1
						, t.Q2       
                        , t.Q3 
                        , t.Q4 
                        ,((t.value*(t.Q1/100))/3) As Jan
                        ,((t.value*(t.Q1/100))/3) As Feb
                        ,((t.value*(t.Q1/100))/3) As Mar
                        ,((t.value*(t.Q2/100))/3) As Apr
                        ,((t.value*(t.Q2/100))/3) As May
                        ,((t.value*(t.Q2/100))/3) As Jun
                        ,((t.value*(t.Q3/100))/3) As Jul
                        ,((t.value*(t.Q3/100))/3) As Aug
                        ,((t.value*(t.Q3/100))/3) As Sep
                        ,((t.value*(t.Q4/100))/3) As Oct
                        ,((t.value*(t.Q4/100))/3) As Nov
                        ,((t.value*(t.Q4/100))/3) As Dece
                FROM tempdata t
               ");

}

// Redirect to the listing page
header("Location: Target.php");

?>

The output i am getting is

Also i need sugession for how to change the target subsequently for evey month based on the previous month target. Is there any new table to be create for store the target month wise. if yes how to create the table and update the data every month

target1 schema.txt

Barand · November 9, 2023

What is the current process for adjusting targets. How are the revised values determined?

Senthilkumar · November 9, 2023

The current process for adjusting the target is to import billing details from a CSV file that is downloaded from SAP. I will share the details with you shortly. There are still some tables that need to be finalized.

Sign In

Need suggestions for insert date from excel (column wise) to mysql database (row wise)

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

Join the conversation

Important Information