CSV

DB를 건드려 본적이 있는 사람이라면 누구나 엑셀파일을 CSV로 저장하여 DB에 넣는 일을 경험하였을 것이고,
생각만큼 잘 안될 경우를 접하였을 것입니다.
엑셀↔MySQL, 아웃룩주소록↔MySQL, 엑셀↔아웃룩주소록 상호변환에 대해 정리중인 문서를 올립니다.
아무쪼록 내용이 부실하더라도 이해하시고, 미비한 부분은 정리가 완료되는대로 수정하도록 하겠습니다.

1. CSV 파일이란?
흔히 엑셀에 의해서 만들어지는 것으로만 알고 있는 CSV(Comma-Separated Values) 파일은 아무런 구조적 상호관계가 없는 레코드들이 들어있는 플랫파일, 필드의 구분은 Comma(, 0x2C) 레코드의 구분은 엔터(0x0D0A)로 한다.

2. 엑셀에서 CSV로 변환이 가능하지 않거나, 다르게 변환되는 문자
1) 탭
기본적으로 엑셀은 행(Line)과 열(Column)으로 구성되어 있으며, 열의 구분은 탭(0x09)로 되어 있다.
그리고 한개의 셀 안에 탭을 포함시키는 것 또한 불가능하다.(제가 아는 바로는)
그러므로 CSV 파일로 변환할 경우, 탭은 없다. 그렇지만, 텍스트데이터에서 강제적으로 탭을 입력한다면, 값으로 인식될 수 있다.
2) 콤마 ,
CSV이라는 것 자체가 콤마에 의해 구분되므로 엑셀의 셀안에 콤마가 있을 경우 그 필드의 시작과 끝에 모두 쿼테이션마크(" 0x22)를 넣어 둘러싸게 된다.
3) 셀의 처음위치에서의 어퍼스트로피 '
엑셀의 셀의 처음위치에 어퍼스트로피를 사용할 경우 그셀의 (셀 서식 - 표시형식)을 강제적으로 텍스트의 형식으로 변경하고 자기 자신은 보이지 않게 한다. 예를 들어 (셀 서식 - 표시형식)이 일반으로 되어 있을 경우 0123 은 123 이 되고 '0123 은 0123 이 된다.
※ 주의사항 : 첫번째 바이트의 어퍼스트로피만 해당, 나머지는 문자열로 취급
어퍼스트로피가 CSV로 변환될때에는 셀에 보이는 그대로 변환된다.
4) 엔터
엑셀의 셀 안에서 엔터를 사용하는 방법은 "alt + 엔터" 이다. 이것을 CSV로 변환할 경우
사용한 엔터는 \n(0x0A Line Feed) 로 되며 필드의 처음과 끝을 쿼테이션마크로 둘러싸게 된다.
5) 쿼테이션마크 "
콤마가 있을 경우 쿼테이션마크를 사용한다고 했다. 그렇다면 쿼테이션마크는 어떻게 될까...
쿼테이션마크는 쿼테이션마크 2개로 변환된다. 그리고 필드의 처음과 끝을 다시 쿼테이션마크로 둘러싸게 된다.
예를 들어 " 1개는 """" 4개가 된다.
6) 변환이 될지 의심스럽지만 변환되지 않는 문자
그레이브(`), 샵(#), 백슬래쉬(\), 달러($), 퍼센트(%), 기타등등의 문자

3. MySQL 구문을 사용하여 CSV 파일을 MySQL 테이블 안에 넣기
굳이 MySQL이 아니고 다른 DB라도 동일하게 처리되어야 될 것 같기도 하다.
MySQL이 CSV를 곧바로 받아들이기엔 CSV 자체에는 문제가 있다. SQL문을 통해 필드의 구분(fields terminated by ','), 레코드의 구분(lines terminated by '\r\n'), 필드의 둘러싸임(fields enclosed by '\"')이 모두 동일한 방법으로 되어야 하는데,
엑셀로부터 콤마(,)나 쿼테이션마크(")가 포함되어 그 필드만 따로 구분만 되어있다면,
정상적으로 이 구문을 사용할 경우 에러가 보이게 된다.
여기서 주의할 점은 lines terminated by '\r\n' 을 하지 않았을 경우 \r(0x0D Carrige Return)이 맨 마지막 필드안에 포함된다는 점이다.
이럴 경우는 절대로 이 방법은 택하지 말자. CSV전체의 셀 둘러싸임을 모두 동일하게 수정하고, 쿼테이션으로 사용해야 될 부분을 \" 로 수정하고, 싱글쿼테이션으로 사용해야될 부분은 \'로 수정하여야 정상적으로 원하는 결과를 얻을 수 있을 것이다.
실제로 이 일을 해보면 노가다라는 것을 알 수 있을 것이다.
사용조건 : CSV파일의 모든 필드의 구분, 모든 레코드의 구분, 모든 필드둘러싸임 이 동일하여야 한다.
사용방법 : LOAD DATA INFILE ~ 구문을 사용한다.

4. 엑셀로부터 텍스트에디터에 붙여넣고 MySQL 테이블 안에 넣기 => Tab-Seperated Values
가장 손쉽게 할 수 있는 방법이다. 엑셀로부터 셀을 선택후 복사하여 텍스트에디터에 붙여넣으면 필드는 탭구분, 레코드는 엔터로 처리된다. 파일을 마음대로 파일로 저장후 MySQL안에 넣을 때, fields terminated by '\t' lines terminated by '\r\n' 만 해주면 된다.
그러나, 엑셀의 버그인지 잘 모르겠지만, 마지막 필드가 없을 경우 NULL필드로 처리되어 필드구분자가 안들어가게 되기때문에(CSV로 저장할때도 마찬가지임) 필드수가 틀리게 될 수도 있다.
이럴때에는 필드의 순서를 바꾸어 항상 값이 있는 데이터가 가장 끝에 오게 하던지 하는 방법을 사용하면 된다.
사용자가 아니라 개발자가 작업할 때는 이 방법을 적극 추천한다.
사용조건 : 텍스트에디터에서 받쳐 줄 만한 파일크기, 한개의 필드안에 탭이 없을 것
사용방법 : LOAD DATA INFILE ~ 구문을 사용한다.

5. PHP의 fgetcsv 펑션을 사용하여 CSV파일을 MySQL 테이블 안에 넣기 DB에 통째로 넣는 일은 초기에 하게 되고, 평소에는 거의 하지 않는 짓(?)이다.
특별히 CSV파일 업로드 기능을 두거나 하지 않는 경우엔 프로그램까지 동원할 필요가 없을 듯 싶다.
사용조건 : 일반유저가 CSV 파일 업로드 기능 사용, 동일한 테이블, 동일한 필드에 업로드시에만 사용할 것!!
물론 꼭 그래야 되는 것 아니지만, 그래야 후일이 편하다.
대다수의 일반유저가 메모장이나 텍스트에디터이란 것을 모르고, 엑셀과 웹브라우저만 안다.
사용방법 : 파일 업로드후 필드체크하고 fgetscv 펑션을 사용하여 배열화시키고 insert into 구문을 사용한다.

6. PHP가 아닌 csv 파일을 지원하지 않는 다른 CGI에서의 처리방법
PHP의 fgetcsv에 해당하는 함수를 만들고 insert into 구문을 사용한다.

7. DB 테이블로부터 CSV 또는 TSV(Tab-Serperated Value) 파일을 추출하여 엑셀로
여기서 또한 그냥 넣으면 절대로 안된다.

ex) 3-4,5,서울 강남구 신사동,33-8,3/6 => 03월04일,5,서울 강남구 신사동,Aug-33,03월06일

날짜형식이나 숫자형식에 맞으면 데이터타입이 자동으로 변경된다. 날짜 형식으로 바뀌는 건 절망적이다.
눈여겨 보았으면 잘 알겠지만, 앞에 나온 어퍼스트로피(')를 사용하여 텍스트로 구성된 필드가 정상적으로 보이게 한다.

8. DB 테이블로부터 XLS 파일 만들기
엑셀파일을 텍스트파일로 구성할 수 있다. 이방법은 PHP가 웹페이지를 parsing해주는 것과 동일하다.
이때 각 셀에 들어가는 데이터는 html의 table과 동일하게 구성하면 되며, 참조사이트는 매우 많다.
여기서도 주의사항은 텍스트형식의 필드에서는 반드시 어퍼스트로피(')를 사용해야 한다는 점이다.