start.php - master - alx.alekseev/git

start.php
97 строк3 KB
alx.alekseev
update start.php
12 дек 2024, 18:23
12 дек 2024, 18:2310554de
<?php

$dbHost = 'localhost';
$dbUser = 'root';
$dbPassword = 'xxxxxx';
$dbName = 'xxxxxx';
$pathFilesCSV = '../filescsv';
	
$link = new mysqli($dbHost, $dbUser, $dbPassword, $dbName);//настройка php.ini должна быть MYSQL_ATTR_LOCAL_INFILE => true 

	if ($link==false) {
    	print_r(mysqli_connect_error());
	}

$handle = opendir($pathFilesCSV);
while (false !== ($file = readdir($handle))) {
    if ($file != '.' && $file != '..') {
                $files[] = $file;
            }
}
closedir($handle);
        
foreach($files as $f1){
  $x = explode('.',$f1);
  $tblName=$x[0];
  $tblName = str_replace([';', '(', ')', '-'],' ', $tblName);
  $tblName=preg_replace('|\s+|is',' ',$tblName);
  $tblName = str_replace(' ', '_', $tblName);
	$fields=[];

  $NumStr = 0;
  if (($f = fopen($pathFilesCSV."/".$f1, "r")) !== FALSE) {
      print_r("\n".date('d.m.Y H:i:s')." - Обработка файла $f1");
      $dataArr = fgetcsv($f, 3000, "|", '"');
			$cntDoubleField=0;	
			$pk='';				
      foreach ($dataArr as $dataVal){
					$typeField = "varchar(250)";
					//Для тех файлов у которых слишком большие поля
					if ($tblName == 'xxxxx'){
						$typeField = "TEXT(10000)";
					}
					
					$dataVal = iconv("windows-1251", "utf8", trim($dataVal));
					if (trim($dataVal) == 'SN') {
						$typeField = "int NOT NULL";
						$dataVal = 'SN';
					}
					if (trim($dataVal) == 'Системный номер') { 
						$typeField = "int NOT NULL";
						$dataVal = 'S_NUM';
						$pk='PRIMARY KEY(`S_NUM`)';
					}
					
					$txt="`".$dataVal."` ".$typeField;
					if (in_array($txt,$fields)){
						//Найден дубликат названия поля
						$cntDoubleField++;
						$txt="`".iconv("windows-1251", "utf8", trim($dataVal))."_$cntDoubleField"."` ".$typeField;
					}
                    $fields[] = $txt;
      }
			if (!empty($pk)) $fields[]=$pk;
      fclose($f);
  }

  $sql = "CREATE TABLE `$tblName`(" . implode(",", $fields) . ")";
	$result = mysqli_query($link, $sql);
	if ($result === false) {
    print_r("\nSQL ошибка ".mysqli_error($link));
    exit();
  }
	unset($result);
			
  //print_r($sql);
     

			$sql="LOAD DATA LOCAL INFILE '".$pathFilesCSV."/".$f1."' INTO TABLE $tblName CHARACTER SET cp1251 FIELDS TERMINATED BY '|' ENCLOSED BY '\"' LINES TERMINATED BY '".'\r\n'."' IGNORE 1 ROWS";
            print_r("\n".$sql);
            $res = $link->query($sql);
            if ($res === false) {
                print_r("\nSQL ошибка ".mysqli_error($link));
                exit();
            }
            unset($res);
			if(!unlink($pathFilesCSV."/".$f1)){
				print_r("\nОшибка удаления $fl");
			}else{
				print_r("\nФайл удалён");
			}
			print_r("\n".date('d.m.Y H:i:s')." STOP\n");



}

?>