doumei4964
doumei4964
2015-12-30 18:28

PHP导入数据完整性

  • csv
  • php
  • mysql
  • sql

I will try to explain situation as well as possible:

  1. I have script, that imports CSV file data to MS Access database.
  2. 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):

enter image description here

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

enter image description here

  • 点赞
  • 回答
  • 收藏
  • 复制链接分享

1条回答

为你推荐