how to insert data from an Excel spreadsheet into a table in a mysql database , unpacking some fields

[shadow-walker]

I'm using phpexcel , I created a script that so far it takes me an Excel spreadsheet and I put the field values ​​in an array of associative arrays , I found a way then print these values ​​in a table html , to see if the recreated excel files exactly as imported and I did , here is my function :

 

// DB Connection Init

//

$db_address="127.0.0.1";

$db_user="b2bmomo_b2b";

$db_password="Tyed_^8f_.Hr";

$db_name="b2bmomo_b2b";

$db = new mysqli($db_address, $db_user, $db_password, $db_name);

if ($db->connect_errno) {

    die("Failed to connect to MySQL: (".$db->connect_errno.") ".$db->connect_error);

}

    $excelfilepath='/home/b2bmomo/test/b2b.xlsx';

    $Extension = strtolower(pathinfo($excelfilepath, PATHINFO_EXTENSION));

    if ($Extension != 'xlsx'){

        throw new Exception('File Extension must be xlsx');

    }

    if (!is_readable($excelfilepath)){

                throw new Exception('File ('.$excelfilepath.') not readable');

    }

 

    try {

        $inputFileType = PHPExcel_IOFactory::identify($excelfilepath);

        $objReader = PHPExcel_IOFactory::createReader($inputFileType);

        $phpExcel = $objReader->load($excelfilepath);

        $sheet = $phpExcel->getSheet(0);

    } catch (Exception $e) {

        echo $e->getMessage();

    }

  $highestrow = $sheet->getHighestRow();

  $highestcolumn = $sheet->getHighestColumn();

  $columncount = PHPExcel_Cell::columnIndexFromString($highestcolumn);

  $titles = $sheet->rangeToArray('A1:' . $highestcolumn . "1");

  $body = $sheet->rangeToArray('A2:' . $highestcolumn . $highestrow);

  $table = array();

  for ($row = 0; $row <= $highestrow - 2; $row++) {

    $a = array();

    for ($column = 0; $column <= $columncount - 1; $column++) {

      $a[$titles[0][$column]] = $body[$row][$column];

    }

    $table[$row] = $a;

  }?>

<table class="table table-bordered">

    <?php

$sheet = $phpExcel->getSheet(0);

 $highestColumn = $sheet->getHighestColumn();

 $highestRow = $sheet->getHighestRow();

for ($row = 1; $row <= $highestRow; $row++):

        $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);

        ?>

        <tr>

            <?php foreach ($rowData[0] as $col): ?>

                <td><?php echo $col; ?></td>

            <?php endforeach; ?>

        </tr>

    <?php endfor; ?>

</table>

$query=?

now I want to make sure that the data instead of my excel sheet, they were imported within a table in a mysql database , but unpacked , in the sense that having my excel sheet like this :

 

http://i.stack.imgur.com/9yrC1.jpg

 

the name of excel sheet is wall

 

the first field is One figure, then Amount As it stands, however The Second Course I would just import it by unzipping the field in 4 different data taking the first Excel's value as an example, I would take 2001 and inserting it into a field of a table machines call, the second in 2009 value and import it into another field in the table, the third value 147 in another field in the table and the last value 937, maybe doing a explode? I would do the same thing then in power taking more and as the first line I would like to put in a field the first value of the table and in another field in the second value, I used the phpdotenv class to store variables in the directory names in which they will the excel, now I should use this procedure:

 

Create a file in it includes config.app that laws init.php with https://github.com/vlucas/phpdotenv (phpdotenv is already loaded is configured)

2) In config.app files are the following variables, which will read:

XLSX_FILES = / home / b2bmomo / www / import /

XLSX_STORE = / home / b2bmomo / www / store /

3) At this point, $ ENV [ "XSLX_FILES"] will contain "/ home / b2bmomo / www / import /" and $ _ENV [ "XSLX_STORE"] will contain "/ home / b2bmomo / www / store /", and are global

4) Are you looking for if there is a file with extension XLSX $ _ENV [ "XSLX_FILES"]

5) For each file that you find

a) I take the name of the file, which will be called by convection all <something> <something else> .xslsx

b) Depending on the value of <something> (hint: use switches)

- If <something> == "WALL"

Importing a file WALL

- default

error: unrecognized file type

IMPORT WALL

// Archive file

I move WALL_ <something else> .xslsx in $ _ENV [ "XSLX_STORE"]. <something else> .xslsx

// Amount of data in memory

I read $ _ENV [ "XSLX_STORE"]. <Something else> .xslsx in a table (array of arrays hash)

// I prepare the cars table

for each array element

recovery from the "MANUFACTURER" fields, "DATA / MODEL / TYPE" and "POWER" the data for the table

check if there is already a record with this data

If there is not

I insert it

[shadow-walker]

it's possible that nobody can help me?

[shadow-walker]

up

[Jacques1]

Stop pushing your thread. Your problems aren't any more important than the problems of other users, which means you'll have to wait until somebody helps you (or doesn't -- there's no guarantee).

 

In the meantime, I recommend you learn how to use code tags and embed images. Most users will skip a thread immediately when they see a wall of code with no syntax highlighting.

Edited February 19, 2016 by Jacques1

[dapcigar]

let me understand your question. you have an excel sheet and you want to upload it to a table in your DB right? if that is what you want to do, you can use this code to perform a mass upload.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title></title>
</head>
<body>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
  <tr><td height="1" valign="top"><table width="100%" border="0" cellpadding="0" cellspacing="0" bgcolor="#fff">
      <tr>
        <td valign="top"><table width="100%" border="0" cellspacing="0" cellpadding="0">
  <tr><td width="60%" valign="top"><table width="100%" border="0" cellpadding="0" cellspacing="0">
    <tr><td align="center" valign="top"> </td></tr>
    <tr>
      <td height="40" align="center" valign="middle"><?PHP echo $msg; ?></td>
      </tr>
    <tr><td align="center" valign="top"></td></tr>
    <tr>
      
  <td align="center" valign="top"><form id="xlsSheet"
name="xlsSheet" method="post" action="<?PHP $_SERVER['PHP_SELF']; ?>"
onsubmit="return valPwd();" enctype="multipart/form-data" >
    
    <table width="497" border="0" cellspacing="0" cellpadding="0">
      <tr>
        
        <td valign="top" bgcolor="#999999"><table
width="100%" border="0" cellspacing="1" cellpadding="0">
          
          <tr>
            <td height="35" bgcolor="#D9ECFF"
style="padding-left:50px;font-weight:bold;">
Choose a valid csv file.</td>
            </tr>
          
  <tr align="center"><td bgcolor="#ffffff"><br />
    <table width="100%" border="0" cellspacing="0"
cellpadding="3">
      <tr>
        <td align="left">Upload  CSV File : </td>
        <td align="left"><input name="filename"
type="file" class="button" />        </td>
        </tr>
      <tr>
        <td></td>
        </tr>
      <tr>
        <td align="right"></a></td>
        </tr>
      </table>
    <br />
    <input name="submit" type="submit" value="Upload File"
style="background-color: #A31C21; border-radius: 6px;
font-weight: bold; height: 30px; width: 125px; margin-right:35px;"
/>
    <br />
  <br />
  </td></tr>
          
          <tr><td height="35" bgcolor="#D9ECFF" class="text3"> </td></tr>
          
          </table></td>
        </tr>
      </table>
    
    </form></td>
      </tr>
    </table></td>
    </tr>
          </table></td>
      </tr>
    </table></td>
  </tr>
</table>
</body>
</html>
<?php
 
if(isset($_POST['submit'])) {
//Connect to Database
$db = new PDO('mysql:host=localhost;dbname=(database name);charset=utf8',
'root', '');
//include('mysql_connect.php'); 
//Upload File
    if (is_uploaded_file($_FILES['filename']['tmp_name'])) {
 
echo "<h2>" . "File ". $_FILES['filename']['name']
." uploaded successfully." . "</h2>";
  }
 
//Import uploaded file to Database
$handle = fopen($_FILES['filename']['tmp_name'], "r");
$count =0;
 
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
$import = $db->exec("INSERT into  (your Table)
(firstname, lastname, jobtitle, email, project, type, username, password)
values('$data[0]','$data[1]','$data[2]','$data[3]', '$data[4]','$data[5]','$data[6]','$data[7]')");
 
$count++;
}
 
fclose($handle);
 
//Print interted rows
$msg="<h3 style='color:green;'>".$count.
"  Rows Imported !</h3>";
}
 
?>

save this file as upload.php. locate your spreadsheet and upload. make sure you save your excel sheet in CSV format.

 

Hope this helps..