[SQL] CSV 변환 (외부 데이터를 이용하여 테이블 생성)

[CSV변환]

 ⊙ 외부에서 발생하는 데이터를 데이터베이스에 저장할 것

 ⊙ 공공데이터는 국가에서 제공하는 경우가 많음 (공공데이터 https://www.data.go.kr)

 

[툴을 이용하여 생성]

⊙ zipcode를 create 하지말고 '데이터 임포트' 수행

⊙ '접속' → '테이블'  오른쪽 마우스 클릭

    → '데이터 임포트'

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

⊙ 우편번호 post.csv를 등록

 

 

⊙ 열 정의

  - 열에 맞는 칼럼명, 데이터 유형, 제약조건을 설정함

  - 널 가능? : 체크 시, null  /  체크 해제시, not null

성공적으로 데이터 임포트시, 나타나는 팝업문

 

zipcode 테이블에 데이터가 잘 들어갔는지 확인하려는 의도

select count(*) from zipcode;

 

 

문1) 시도별 우편번호의 개수를 구하고 이를 기준으로 내림차순 조회하시오

select sido, count(sido)  from zipcode  group by sido  order by count(sido);

 

 

 

 

 

 

 

+) 우편번호의 개수가 3000개 이상인 시도를 구하시오

select sido, count(sido)  from zipcode  group by sido  having count(sido)>=3000  order by count(sido);

 

문2) 서울지역의 구의 우편번호 개수를 구하시오

select gugun, count(gugun)  from zipcode  where gugun="서울특별시"  group by gugun;

 

 

 

 

 

 

 

 

 

 

 

 

 

 [명령어를 사용하여 테이블 생성]
  ⊙ 우편번호 테이블 삭제
  drop table zipcode;
 

⊙ 우편번호 테이블 생성
 create table zipcode(
   zipcode char(7)      not null      --우편번호 (A열), 일정한 숫자의 나열이라서 문자열(char / varchar)로 해도 됨
   ,sido   varchar(30)  not null     --시, 도 (B열)
   ,gugun  varchar(50)                  --구, 군 (C열)
   ,dong   varchar(50)                   --동     (D열)
   ,li     varchar(50)                        --리     (E열)
   ,bunji  varchar(255)                   --번지   (F열)
   ,etc    varchar(255)                     --기타   (G열)
 );
 

⊙  zipcode.ctl 파일 생성 (ctrl의 약자를 따서 ctl)
 →  아래의 내용을 txt에 적어서 파일명을 '~~.ctl' 로 생성 후 cmd (명령 프롬프트)에 가서 실행

 →  명령어 두 번째 줄에 들어간 테이블을 미리 create 시켜줘야함

 → 이 방법은 명령툴을 사용하지 않는 경우에 사용

load data infile 'post.csv'                         --원본 파일은 'post.csv'
insert into table zipcode                           -- 내용이 들어갈 테이블 (zipcode)을 지정
fields terminated by ','                             --구분을 , 를 사용할 것
trailing nullcols                                          --null값 허용
(zipcode,sido,gugun,dong,li,bunji,etc)

 

⊙ cmd에서 dir을 이용해 zicode.ctl 이 있는지 확인 (+ post.csv도 있는지 확인)

 

⊙ 컨트롤 파일 실행시 쓰는 명령어 : sqlldr 

>sqlldr 계정/비번 control="~~.ctl"

 

 

← 자료가 다 입력되어 들어가는 모습

    자료의 내용에 따라 길어지기도하고 짧아지기도 함

 

 

 

 

 

 

 

 

 

 

 

 

 

[도로명 DB.txt를 CSV로]

⊙ 도로명주소DB - 도로명주소 개발자 센터 : https://www.juso.go.kr/addrlink/main.do

  - 도로명주소 DB 다운로드부터 활용방법도 설명되어 있음

도로명주소 개발자센터

 

 

1) 엑셀에서 도로명 DB를 불러옴

 2) 원본 파일을 한국어로 볼 수 있게 설정

   - 한국어 (완성), 환국어 (조합) 등 여러가지가 존재하기 때문에 본인에게 맞춰서 사용

 

 3) 자료의 구분 기호에 맞춰 잘라 넣을 수 있게 만들어야 함

  - 이 파일의 경우, 데이터의 구분 기호가 ' | ' 였기 때문에 '기타' → ' | ' 선택 

  - 맞는 구분 기호 선택시 '데이터 미리보기' 에서 자료가 분리된 것을 확인 할 수 있음

  - '기타' 의 경우, 오타가 날 수 있기 때문에 복사붙여넣기 추천

 

 

4) 열 서식을 설정해줘야함

  - 맞는 열 서식을 설정해주지 않을 경우, 다르게 표현 될 수 있음

  - 도로명코드는 '텍스트'를 넣어줘야 바르게 표현됨