I will try to explain situation as well as possible:

I have script, that imports CSV file data to MS Access database.
I have 2 access Tables:

A) Users and their information(ID, name, last name etc.)

B) Table which contains data from CSV file

Problem is, data imported from file, (2nd table) contains Users name and lastname. I want to get idea, how to, while reading csv file line by line, check what name line contains, and assign userID from table 1 instead of name and lastname on table 2. It should be done while importing, because, on each import there are roughly 3k lines being imported. Any ideas appreciated. Images given bellow.

Import script:

<?php
function qualityfunction() {
error_reporting(0); 
require_once '/Classes/PHPExcel.php'; // (this should include the autoloader)
require_once '/CLasses/PHPExcel/IOFactory.php';
$excel_readers = array(
    'Excel5' , 
    'Excel2003XML' , 
    'Excel2007'
);
$files = glob('data files/quality/QA*.xls');
$sheetname= 'AvgScoreAgentComments';
if (count($files) >0 ) {
foreach($files as $flnam) {
$reader = PHPExcel_IOFactory::createReader('Excel5');
$reader->setReadDataOnly(true);
$reader->setLoadSheetsOnly($sheetname); 
$path = $flnam;
$excel = $reader->load($path);
$writer = PHPExcel_IOFactory::createWriter($excel, 'CSV');
$writer->save('data files/quality/temp.csv');

/*
$filename = basename($path);  
if (strpos($filename,'tes') !== false) {
echo 'true';
}*/
            require "connection.php";
            $handle = fopen("data files/quality/temp.csv", "r");
            try {
      $import= $db->prepare("INSERT INTO quality(
                              qayear,
                              qamonth,
                              lastname,
                              firstname,
                              score) VALUES(
                              ?,?,?,?,?)");
    $i = 0;        
    while (($data = fgetcsv($handle, 1000, ",", "'")) !== FALSE) {
        if($i > 3) {
            $data = str_replace('",', '', $data); 
            $data = str_replace('"', '', $data); 
            $import->bindParam(1, $data[1], PDO::PARAM_STR);             
            $import->bindParam(2, $data[2], PDO::PARAM_STR);                
            $import->bindParam(3, $data[3], PDO::PARAM_STR);
            $import->bindParam(4, $data[4], PDO::PARAM_STR); 
            $import->bindParam(5, $data[7], PDO::PARAM_STR);            
            $import->execute();
        }
        $i++;
    }

    fclose($handle);
    $removal=$db->prepare("DELETE FROM quality WHERE score IS NULL;");
    $removal->execute();
            }
catch(PDOException $e) {  
    echo $e->getMessage()."
";

}};

Data table 1 (Users info):

Data table 2 (In which data from CSV file is imported)

从excel导入数据到Mysql数据库中的ID列出现了奇怪的显示

dpwqb

2个回复