[ php ] csv 파일 테이블로 import 하기 ( LOAD DATA INFILE )





디렉토리에 들어있는 csv 파일들을 DB에 저장할 일이 생겼다.

파일 개수가 많아 php로 파일 리스트를 읽어 테이블들로 일괄 저장하려고 한다.

 

mysqlimport도 생각해봤는데 LOAD DATA INFILE 문 처리속도가 더 빠르다고 한다.

 

일단 DB에 접속하자.

 

$conn = mysqli_connect('localhost', 'userid', 'password','dbname');
if (mysqli_connect_errno($conn)) 
     echo "데이터베이스 연결 실패: " . mysqli_connect_error();
else
 echo "connection success";

 

csv파일을 import할 테이블도 생성하자.

기존 csv파일에 없는 idx필드를 생성해 primary key값으로 설정하고 자동 증가 옵션을 주었다.

 

$sql = "CREATE TABLE `테이블이름` (
     `idx` INT(11) NOT NULL AUTO_INCREMENT,
     PRIMARY KEY(idx),
     `날짜` INT(8) NOT NULL,
     `가격` INT(5) NOT NULL,
     ...

`시간` INT(4) NOT NULL 
 )";

 

if(mysqli_query($conn, $sql))

    echo "Table created successfully"; 

else
    echo "Error creating table:".mysqli_error($conn);
  

 

LOAD DATA INFILE 구문 규칙은 아래와 같다.

 

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name' [REPLACE | IGNORE] INTO TABLE tbl_name [CHARACTER SET charset_name] [{FIELDS | COLUMNS} [TERMINATED BY 'string'] [[OPTIONALLY] ENCLOSED BY 'char'] [ESCAPED BY 'char'] ] [LINES [STARTING BY 'string'] [TERMINATED BY 'string'] ] [IGNORE number LINES] [(col_name_or_user_var,...)] [SET col_name = expr,...]

[각주:1]

 

기본적으로 csv 파일 이름과 테이블 이름만 입력해도 사용 가능하다.

이럴 경우 나머지 옵션들은 기본 옵션으로 실행되게 된다.

 

파일 이름은 경로와 함께 나타낼 수 있으며 상대 경로 절대 경로 다 사용 가능하다.

기본적으로 현재 데이터베이스가 있는 디렉토리가 기본 경로가 된다.

csv파일이 데이터베이스와 다른 경로에 있기 때문에 절대 경로로 나타낸다.

필드가 , 로 구분되어 있기 때문에 FIELDS TERMINATED BY ',' 옵션을 추가했다.

 

참고로 기본 필드 값은 아래와 같다.

 

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'
LINES TERMINATED BY '\n' STARTING BY ''

 

 

응용프로그램에서 생성되는 많은 csv파일이 아래와 같은 포맷을 사용한다고 하니 참고하자.

 

LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name
  FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  LINES TERMINATED BY '\r\n'
  IGNORE 1 LINES;

 

 

csv파일의 첫 라인에는 필드 이름이 있었기 때문에 IGNORE 1 LINES"; 옵션도 추가했다.

IGNORE N LINES 옵션은 파일 N번째 라인부터 테이블로 저장하게 한다.

 

만약 테이블의 필드와 csv파일의 필드가 정확히 일치하면 그냥 아래와 같이 적어주면 된다.

 

$sql = "LOAD DATA INFILE '/var/lib/mysql-files/파일이름.csv'
     INTO TABLE `테이블이름`
     FIELDS TERMINATED BY ','
     IGNORE 1 LINES";
  

 

간혹 파일이 존재하는데 파일이 없다는 에러가 나올 수 있다. 



mysql> LOAD DATA INFILE '/home/changelist.csv' INTO TABLE changelist

    ->   FIELDS TERMINATED BY '\t'

    ->   IGNORE 1 LINES;

ERROR 29 (HY000): File '/home/changelist.csv' not found (Errcode: 13)



그럴 경우는 주로 권한의 문제로 파일이 있는 폴더나 파일 소유자를 mysql로 변경하면 해결 된다. 


chown mysql:mysql changelist.csv


 

그런데 나는 인덱스 필드를 추가했기 때문에 ( ) 옵션으로 필드들 이름을 하나하나 적어주기로 한다(...)

여기서 필드들 이름 순서는 파일에 나타나는 필드  순서와 동일해야 한다.


$sql = "LOAD DATA INFILE '/var/www/html/파일이름.csv'
     INTO TABLE `테이블이름`
     FIELDS TERMINATED BY ','
     IGNORE 1 LINES"

     (`날짜`,`가격`,... ,`시간`)";

 

if(mysqli_query($conn, $sql))
    echo "LOAD DATA INFILE successfully"; 
else
    echo "Error LOAD DATA INFILE:".mysqli_error($conn);

 

$conn->close();

 


필드 명까지 다 입력하고 php 파일을 실행하자 테이블이 생성되고 csv 파일이 테이블로 잘 변환된 것을 확인할 수 있었다 : )

 

만약 테이블에 null 값을 입력해야 한다면 (그리고 csv 파일에는 빈 입력값으로 들어있다면) 

빈 입력값을 \N으로 입력해야 한다



MySQL manual says:


When reading data with LOAD DATA INFILE, empty or missing columns are updated with ''. If you want a NULL value in a column, you should use \N in the data file. The literal word “NULL” may also be used under some circumstances.

So you need to replace the blanks with \N like this:


예시: 

1,2,3,4,5

1,2,3,\N,5

1,2,3



 

 

  1. 출처:http://dev.mysql.com/doc/refman/5.1/en/load-data.html [본문으로]
  • 2017.03.30 13:40 ADDR 수정/삭제 답글

    비밀댓글입니다

    • adana 2017.03.30 21:28 신고 수정/삭제

      네 이메일주소 알려주세요

[ php ] mysql / mysqli - select / insert / update 문 사용 예시

 

 

1. DB 연결하기


$conn = mysqli_connect('localhost', 'username', 'password','dbname');
if (mysqli_connect_errno($conn))
{
  echo "데이터베이스 연결 실패: " . mysqli_connect_error();
}
else
{

// to do something

}

 


2. select 문 사용


mysql_query 결과로 영향받은 row수를 알기 위해서는 mysqli_affected_rows 함수를 사용한다. 

- select 결과를 associative array로 반환받기 

$check_query = "SELECT * from ".$table." where code='".$code."' and period='".$period."'";

 $result=mysqli_query($conn,$check_query);

if(mysqli_affected_rows($conn) > 0)
{

  

 // mysqli_fetch_assoc 함수 

 // Fetch a result row as a numeric array and as an associative array:


 while ($row = mysqli_fetch_assoc($result))
 {
            $id = $row['id']."\t";
            $name = $row['name']."\t";
            $news .= $row['news'];

  }
                                 

}

mysqli_free_result($result);


 참고: http://www.w3schools.com/php/func_mysqli_fetch_array.asp

- sum 함수를 사용한 select 문 예시 


 $sum_query = "select Sum(`scores`) as value_sum from tablename";
        $result = $conn->query($sum_query);
        $row = mysqli_fetch_assoc($result);
        $sum_up_short = $row['value_sum'];



참고: http://www.tizag.com/mysqlTutorial/mysqlsum.php

3. insert 문 사용

$query = "INSERT INTO ".$table."(code, name_kor,name_eng, market, type, period,`".$field."`) VALUES ('".$code."','".$name_kor."','".$name_eng."','".$market."','".$type."','".$period."','".$value."');";

   if(mysqli_query($conn, $query))
       echo "insert successfully";
      else
       echo "Error:".$query."mesage:".mysqli_error($conn);  

3. update 문 사용

 $query = "UPDATE ".$tablename." SET `field0`='".$value0."' ,`field1`='".$value1."' ,`field2`='".$value2."' ,`".$field3."`='".$value3."' WHERE 날짜='".$time."' AND 종목코드='".$stockcode."'";
    
    if(mysqli_query($conn, $query))
       echo "update successfully";
      else
       echo "Error:".$query."mesage:".mysqli_error($conn);  
       

 

 4. DB 연결 닫기 

$conn->close();



 

php 시간 표현 예시 - dateTime 사용 예시



- 현재 시간 구하기 (YYYY-mm-dd) format




        $current_date = new DateTime();

        $current_date->format('Y-m-d');

  



- 현재 요일 구하기




function get_day_of_week($sql_date)

{

        $updated_date = new DateTime($sql_dat);

        $day_of_week = $updated_date->format('D');

        return $day_of_week;

}







- 두 시간 표현 간 시간 차이 구하기 

참고: http://haruair.com/blog/1871, http://php.net/manual/kr/datetime.diff.php





public DateInterval DateTime::diff ( DateTimeInterface $datetime2 [, bool $absolute = false ] )





       $date_db= new DateTime();

        $date_news=new DateTime("2014-01-28");

        $diff=$date_db->diff($date_news);

        print "\ndiff days".$diff->days; // 522



        if($date_news <= $date_db)

        {

            echo "\nnews data is older than or same as db\n";

            break;

        }

        else

        {

            echo "\nnews data is newer than db\n";




<?php
$date1 = new DateTime("now"); or $date1= new DateTime();

$date2 = new DateTime("tomorrow");

var_dump($date1 == $date2);
var_dump($date1 < $date2);
var_dump($date1 > $date2);
?>

위 예제의 출력:

bool(false)
bool(true)
bool(false)





- 특정 날짜의 하루 전 날 구하기 

참고: http://php.net/manual/en/datetime.modify.php



<?php
$date = new DateTime('2006-12-12');
$date->modify('-1 day');
echo $date->format('Y-m-d');
?>


  • dd 2018.01.09 21:30 ADDR 수정/삭제 답글


    $date->modify('+1 month');

php.ini 수정 - upload_max_filesize, memory_limit, post_max_size 증가

 

 

 

phpMyAdmin에서 외부 Mysql DB를 가져오기 하기 할떄 아래와 같은 메세지가 나왔다.

 

 너무 큰 파일을 업로드하려고 시도했습니다. 제한을 해결하기 위해서는 documentation를 참조하여 주십시오.

 

 

Starting with version 2.7.0, the import engine has been re–written and these problems should not occur. If possible, upgrade your phpMyAdmin to the latest version to take advantage of the new import features.

The first things to check (or ask your host provider to check) are the values of upload_max_filesize, memory_limit and post_max_size in the php.ini configuration file. All of these three settings limit the maximum size of data that can be submitted and handled by PHP. One user also said that post_max_size and memory_limit need to be larger than upload_max_filesize. There exist several workarounds if your upload is too big or your hosting provider is unwilling to change the settings

 

해당 문서를 확인해보니 php.ini의 upload_max_filesize, memory_limit and post_max_size 를 더 증가시켜야한다고 한다. 

일단 whereis로  php.ini가 어디있는지 찾는다.

 

#whereis php

 

찾은 php.ini파일을 일단 백업하고 아래와 같이 수정작업 ㄱㄱ

memory limit은 무제한으로 하기 위해 -1로 설정했다.

 

 ; Maximum allowed size for uploaded files.
; http://www.php.net/manual/en/ini.core.php#ini.upload-max-filesize
upload_max_filesize = 200M

 

; Maximum amount of memory a script may consume (128MB)
; http://www.php.net/manual/en/ini.core.php#ini.memory-limit
memory_limit = -1

 

; Maximum size of POST data that PHP will accept.
; http://www.php.net/manual/en/ini.core.php#ini.post-max-size
post_max_size = 300M


 

 

 

그리고 http 서비스를 재시작해서 변경사항을 반영하였다.

 

service httpd restart

 

이제 정상적으로 큰 사이즈 DB도 가져오기 가능해졌다.

 

 

CENT OS 5 에서 php 5.4로 업데이트 하기

 

json_encode($array, JSON_UNESCAPED_UNICODE) 를 사용하려고 했는데

PHP5.3은 JSON_UNESCAPED_UNICODE를 지원하지 않는다고 하여 php 버전을 업데이트 하기로 했다.

 

yum update php 를 해보았으나 아무 업데이트도 일어나지 않았다.

기본적으로 사용되는 저장소에 있는 php 버전이 낮아 자동으로 업데이트가 실행되지 않은 듯하다.  

 

yum에 추가적으로 사용할 만한 repository들을 검색하여 epel, remi, webtatic 저장소를 추가시켰다.

(참조:  http://lesstif.com/pages/viewpage.action?pageId=6979743)

 

 

 

 

yum repolist명령어로 새로운 저장소가 잘 추가되었는 지 확인하였다.

 

 

 

# yum repolist
repo id            repo name                                             status
addons             CentOS-5 - Addons                                           0
base               CentOS-5 - Base                                       3,654+8
epel               Extra Packages for Enterprise Linux 5 - x86_64          7,705
extras             CentOS-5 - Extras                                         265
updates            CentOS-5 - Updates                                     482+48
webtatic-el5       Webtatic Repository EL5 - x86_64                          135
repolist: 12,241

 

다른 저장소는 다 보이는데 remi가 리스트에 보이지 않는다.

이럴 경우에는   /etc/yum.repos.d/{repositoryname}.repo  를 열어서 enabled= 1 로 수정한다.

 

vi /etc/yum.repos.d/remi.repo

 

 

[remi]
name=Les RPM de remi pour Enterprise Linux 5 - $basearch
#baseurl=http://rpms.famillecollet.com/enterprise/5/remi/$basearch/
mirrorlist=http://rpms.famillecollet.com/enterprise/5/remi/mirror
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-remi

 

 

다시 yum repolist를 입력하면 아래와 같이 remi를 포함한 새로 추가된 세 개의 저장소가 모두 나타나는 것을 확인 할 수 있다.

 

# yum repolist
repo id                                                            repo name                                                                                              status
addons                                                             CentOS-5 - Addons                                                                                            0
base                                                               CentOS-5 - Base                                                                                        3,654+8
epel                                                               Extra Packages for Enterprise Linux 5 - x86_64                                                           7,705
extras                                                             CentOS-5 - Extras                                                                                          265
remi                                                               Les RPM de remi pour Enterprise Linux 5 - x86_64                                                         1,605
updates                                                            CentOS-5 - Updates                                                                                      482+48
webtatic-el5                                                       Webtatic Repository EL5 - x86_64                                                                           135
repolist: 13,846

 

 

각각의 저장소에서 가능한 php 정보를 확인해본다.

 

 

 # yum --enablerepo=remi info php
Installed Packages
Name       : php
Arch       : x86_64
Version    : 5.4.30
Release    : 1.el5.remi
Size       : 8.9 M
Repo       : installed
Summary    : PHP HTML-내장된 스크립트 언어. (PHP: Hypertext Preprocessor)
URL        : http://www.php.net/
License    : PHP and Zend and BSD
Description: PHP is an HTML-embedded scripting language. PHP attempts to make it
           : easy for developers to write dynamically generated webpages. PHP
           : also offers built-in database integration for several commercial
           : and non-commercial database management systems, so writing a
           : database-enabled webpage with PHP is fairly simple. The most common
           : use of PHP coding is probably as a replacement for CGI scripts.
           :
           : The php package contains the module which adds support for the PHP
           : language to Apache HTTP Server.

 

 

remi 저장소에 내가 찾던 5.4 버전이 있어 사용하기로 한다.  

--enablerepo=remi 옵션으로 사용할 저장소를 지정하고 php를 업데이트 한다.

#yum --enablerepo=remi update php


Dependency Installed:
  libXaw.x86_64 0:1.0.2-8.1
  libXmu.x86_64 0:1.0.2-5
  libXt.x86_64 0:1.0.2-3.2.el5
  libcurl.x86_64 0:7.21.7-5.el5.remi.2
  libedit.x86_64 0:2.11-2.20080712cvs.el5
  libssh2.x86_64 0:1.2.7-1.el5.remi
  mysql-libs.x86_64 0:5.5.38-1.el5.remi
  mysqlclient15.x86_64 0:5.0.67-1.el5.remi
  t1lib.x86_64 0:5.1.1-9.el5

Dependency Updated:
  mysql.i386 0:5.0.95-5.el5_9
  mysql.x86_64 0:5.5.38-1.el5.remi
  mysql-devel.i386 0:5.0.95-5.el5_9
  mysql-devel.x86_64 0:5.5.38-1.el5.remi
  mysql-server.x86_64 0:5.5.38-1.el5.remi

Replaced:
  php53.x86_64 0:5.3.3-22.el5_10        php53-cli.x86_64 0:5.3.3-22.el5_10
  php53-common.x86_64 0:5.3.3-22.el5_10 php53-devel.x86_64 0:5.3.3-22.el5_10
  php53-gd.x86_64 0:5.3.3-22.el5_10     php53-mbstring.x86_64 0:5.3.3-22.el5_10
  php53-mysql.x86_64 0:5.3.3-22.el5_10  php53-pdo.x86_64 0:5.3.3-22.el5_10

Complete!

 

 

업데이트가 끝난후 service httpd restart 로 웹서버 재구동하고 php -v 로 제대로 업데이트가 완료 된 것을 확인한다.

 

# php -v
PHP 5.4.30 (cli) (built: Jun 25 2014 15:41:34)
Copyright (c) 1997-2014 The PHP Group
Zend Engine v2.4.0, Copyright (c) 1998-2014 Zend Technologies


 

[php] explode 사용법 (문자열을 문자열로 분리하기)

 

 

explode는 문자열을 문자열로 분리해서 배열로 저장하는 함수이다.

웹상에서 문자열 처리할때 여러모로 요긴한 함수이기도 하다.

 

array explode ( string $delimiter , string $string [, int $limit ] )

 

첫번쨰 인수인 문자열($delimiter) 기준으로 두번째 인수인 문자열($string)을 분리한다.

옵션으로 넣을 수 있는 인수인 limit를 지정하면, 반환하는 배열은 최대 limit개의 원소를 가지고, 마지막 원소는 남은 string

모두를 포함한다. limit 값이 음수이면, 마지막 limit 개수의 원소를 제외하고 배열을 반환한다.  

 

예제

 

<?php 
            $line = "10    07     2       문장1    문장2     문장3"; (각 원소들끼리는 탭으로 구분)  

 

// limit 인자 없이 사용

$line_array = explode("\t", $line);

print_r($line_array);

 

//limit 양수 값(2) 사용

$line_array = explode("\t", $line, 2);

print_r($line_array);

 

//limit 음수 값(-2) 사용

$line_array = explode("\t", $line, -2);

print_r($line_array);

?>

 

위 예제의 출력

// limit 인자 없이 사용

 

Array
(
    [0] => 10
    [1] => 07
    [2] => 2
    [3] => 문장1
    [4] => 문장2
    [5] => 문장3

)

 

// limit 에 양수 값(2) 사용  

 

Array
(
    [0] => 10
    [1] =>  07  2      문장1   문장2       문장3

)


 

// limit에 음수 값(-2) 사용

Array
(
    [0] => 10
    [1] => 07
    [2] => 2
    [3] => 문장1

)