공공데이터 CSV 데이터베이스(sqlite)로 데이터이전하기(csv->sqlite로 마이그레이션)[exam6_data]

 

a.공공데이터 포털에서 상권정보 데이터를 다운로드 후에 인천데이터만

압축을 풀어 엑셀에서 불러오기

 

-상권정보 다운로드 링크

https://www.data.go.kr/data/15083033/fileData.do

소상공인시장진흥공단_상가(상권)정보.csv

 

엑셀에서 그대로 열면 한글이 깨지며, 상가정보 파일에 함께 들어있는 설명서를참고하여 아래와 같은 방법으로 열어야 한글깨짐이 방지됩니다.

엑셀을 열어 파일/외부데이터가져오기 클릭하여 아래사항 확인후 불러오기

1. UTF-8의 코드 페이지 넘버는 65001입니다.

2.구분 기호는 [쉼표] 선택합니다.

3.기본이 쉼표로 선택되어 있음

4.불러온후에 빈행이나 열을 삭제를 함

5.파일은 test11.csv로 저장함

 

b.테스트 데이버베이스 생성 및 테이블 구조 만들기

sqlite를 실행하여 새 데이터베이스에서 test11.db 로 데이터베이스 생성후

테이블이름 생성(sangga) 및 csv의 한글컬럼에 대응하는 영문컬럼생성하기

테이블명 및 컬럼명은 sqlite 에서 생성하고 파일로 익스포트시키면 아래와 같은 스크립트로 다운받을수 있습니다.

 

 

CREATE TABLE "sangga" (

             "business_id"     TEXT,

             "business_nm"   TEXT,

             "branch_nm"      TEXT,

             "large_cd"          TEXT,

             "large_nm"         TEXT,

             "mid_cd"           TEXT,

             "mid_nm"          TEXT,

             "small_cd"         TEXT,

             "small_nm"        TEXT,

             "stand_cd"         TEXT,

             "stand_nm"        TEXT,

             "sido_cd"           TEXT,

             "sido_nm"          TEXT,

             "gu_cd" TEXT,

             "gu_nm"             TEXT,

             "admin_cd"        TEXT,

             "admin_nm"       TEXT,

             "legal_cd"          TEXT,

             "legal_nm"         TEXT,

             "lot_cd"             TEXT,

             "land_cd"           TEXT,

             "land_nm"          TEXT,

             "bun1"  TEXT,

             "bun2"  TEXT,

             "bun_addr"        TEXT,

             "street_cd"         TEXT,

             "street_bun0"     TEXT,

             "street_bun1"     TEXT,

             "street_bun2"     TEXT,

             "build_cd"         TEXT,

             "build_nm"        TEXT,

             "street_addr"     TEXT,

             "oldpost_cd"      TEXT,

             "newpost_cd"    TEXT,

             "dong_info"       TEXT,

             "floor_info"       TEXT,

             "ho_info"           TEXT,

             "lng"     TEXT,

             "lat"      TEXT

)

 

c.test11.csv 파일을 생성한 테이블 구조로 데이터 마이그레이션하기

가져오는 방법은 몇가지 있지만 여기에서는 sqlite를 실행하여 파일/가져오기/csv에서 가져오기를 통하여

test11.csv의 데이터를 새로 생성한 sangga테이블로 가져오는 것을 해보겠습니다.

공공데이터에서 가져온 상가데이터인 test11.csv는 10만건 정도의 30-40메가 정도로 파일크기가 되는데 엑셀을 열때도 pc성능에 따라 약간의 시간이 걸릴수도 

있고 엑셀파일을 불러올 때 오류가 나올수도 있어 전체 데이터를 마이그레이션 하기전에 1000여건정도의 데이터만 테스트로 마이그레이션을 해보면서 이상없이 

데이터가 마이그레이션 되는 것을 확인해봅니다.

test11.csv 파일원본은 그대로 두고 100건 정도의 데이터만 남기고 test11_imsi.csv로 저장을 한후에 

이 파일을 메모장으로 연후 다른이름으로 저장하기를 클릭하면 인코딩방식을 선택할수 있는데 거기서

 utf-8로 선택후에 저장을 합니다.

또 엑셀의 상단 컬럼명 부분은 삭제를 합니다.

엑셀파일의  지수로된 부분은 셀서식을 숫자로 변경하면 원래 값대로 보이게 됩니다.

sqlite에서 csv파일 가져오기를 할 때 테이블명은 sangga로 변경하고 첫행에 필드명 포함은 체크해제를 한후 불러옵니다.

파일을 메모장을 거치지 않고 그대로 불러오면 인코딩이 맞지 않아 한글부분이 깨지게 되며, 테이블명은 기존에 생성한 테이블명으로 데이터를 가져와야 하므로 기존의 테이블명을 적어준 것입니다.

각 컬럼명에 매칭되어 데이터를 잘 가지고 왔다면 전체데이터를 같은 방식으로 가져옵니다.

사전에 테스트로 불러온 데이터는 삭제를 한후 불러와야 하므로, 먼저

sqlite의 sql실행탭에서 아래의 sql명령어를 실행하여 sangga테이블에 들어간 데이터 전체를 삭제합니다.

delete from sangga