DB 마이그레이션 테이블을 통한 엑셀 일괄 업로드 방법

엑셀 일괄 업로드 절차

1. 테이블 CSV 파일에 엑셀 데이터 입력 후 인코딩을 UTF-8로 변경합니다.
2. DBeaver 툴에서 마이그레이션 테이블에 CSV 파일을 가져옵니다.
3. INSERT SELECT문으로 원본 테이블에 마이그레이션 테이블 데이터를 일괄 등록합니다.

---

테이블 설계 참고

원본 테이블 설계

제약조건	컬럼명	데이터 타입	기본값	설명
PK	QSTN_STNDRD_ID	varchar(20)		문항기준ID
	QSTN_DVSN_CDV	varchar(50)		문항구분코드값
	PUBSER_CDV	varchar(50)		출판사코드값
	BOK_NM	varchar(2000)		교재명
	SHC_LVL	varchar(50)		학교급
	SHC_YEAR	varchar(4)		학년
	SHC_TRM	varchar(4)		학기
	SBJ_CDV	varchar(50)		과목코드값
	EDU_CRS_CDV	varchar(50)		교육과정코드값
	PUB_YEAR	varchar(4)		출판년도
	SOURCE_ID	varchar(50)		출처ID
	DITB_YN	char(1)	'N'	관리자배포여부
	FILE_STNG	varchar(2000)		파일설정
	DEL_YN	char(1)		삭제여부
	FRST_REGER_ID	varchar(20)		최초등록자ID
	FRST_REG_DT	varchar(14)		최초등록일시
	FRST_REGER_IP	varchar(50)		최초등록자IP
	LAST_MDFER_ID	varchar(20)		최종수정자ID
	LAST_MDFCN_DT	varchar(14)		최종수정일시
	LAST_MDFER_IP	varchar(50)		최종수정자IP

교과서 정보를 저장하는 원본 테이블입니다.

마이그레이션 테이블 설계

제약조건	컬럼명	데이터 타입	기본값	설명
	QSTN_STNDRD_ID	varchar(20)		문항기준ID
	SOURCE_ID	varchar(50)		출처ID
	PUBSER	varchar(50)		출판사
	BOK_NM	varchar(2000)		교재명
	SHC_LVL	varchar(50)		학교급
	SHC_YEAR	varchar(4)		학년
	SHC_TRM	varchar(4)		학기
	SBJ	varchar(50)		과목
	EDU_CRS	varchar(50)		교육과정

엑셀로 전달받은 교과서 데이터를 임시 저장하는 마이그레이션 테이블입니다.

---

엑셀 일괄 업로드 방법

마이그레이션 테이블 DELETE

DELETE FROM im_qstn_stndrd_mig;

마이그레이션 테이블에서 모든 데이터를 삭제합니다.

CSV 내보내기

DBeaver 툴 > 마이그레이션 테이블명 검색 > 테이블 우클릭 > 데이터 내보내기 > CSV 파일로 내보내기 > 다음 > 다음 > CSV 파일을 저장할 폴더 지정 > 진행

이미 내보내기 했던 테이블 CSV 파일이 있다면, 재사용합니다.

엑셀에서 CSV 열기 (CSV 편집)

빈 엑셀 열기 > 데이터 탭 > 외부 데이터 가져오기 > 텍스트 > 구분 기호로 분리됨 체크 > 다음 > 구분기호 : 쉼표 체크 > 다음 > 각 열 선택 후 텍스트 서식으로 변경 > 마침 > 기존 워크시트 선택 > 확인

컬럼을 텍스트 타입으로 지정해서 열지 않으면, 일반 형식으로 열려서 01이 1로 변환되어 버립니다.

CSV 파일에 엑셀 데이터 입력
전달받은 엑셀 데이터를 마이그레이션 테이블 컬럼에 맞춰서 붙여 넣습니다.

Autoincrement로 자동 증가되지 않는 PK라면, MAX 값 조회해서 +1부터 채웁니다.

CSV 파일 저장

Ctrl + S > 다른 이름으로 저장 > 파일 형식 : CSV (쉼표로 분리) (.*.csv) 선택 > 파일명 입력 > 저장 > 예 > 닫기 > 저장 안함

CSV 파일 인코딩 변환

csv 파일을 메모장으로 열고, 다른 이름으로 저장 > 인코딩: UTF-8 선택 후 저장
엑셀로 다시 열었을 때 한국어가 중국어처럼 깨져있어도 정상입니다.

인코딩 변환 이유
엑셀에서 CSV 파일을 저장하면 기본 인코딩이 ANSI이기 때문에,
인코딩 변환 없이 가져오면 DB 인코딩 UTF-8과 달라서 한글이 전부 깨질 수 있습니다.

CSV 가져오기

DBeaver 툴 > 마이그레이션 테이블명 검색 > 테이블 우클릭 > 데이터 가져오기 > CSV에서 가져오기 > Target CSV 파일 지정 > 다음 > 다음 > 진행

INSERT 쿼리 실행

INSERT SELECT 쿼리에서 SELECT 부분을 먼저 조회해보고, 데이터 및 한글 인코딩이 크게 이상 없으면 실행합니다.

INSERT SELECT 쿼리 예시

INSERT INTO im_qstn_stndrd (
    qstn_stndrd_id,
    qstn_dvsn_cdv,
    pubser_cdv,
    bok_nm,
    shc_lvl,
    shc_year,
    shc_trm,
    sbj_cdv,
    edu_crs_cdv,
    source_id,
	  ditb_yn,
    del_yn,
    frst_reger_id,
    frst_reg_dt,
    frst_reger_ip
)
SELECT
	QSTN_STNDRD_ID,
	'01' AS QSTN_DVSN_CDV,
	'01' AS PUBSER_CDV,
	BOK_NM,
	CASE
		WHEN SHC_LVL = '중등' THEN 'M0'
		WHEN SHC_LVL = '초등' THEN 'E0'
		WHEN SHC_LVL = '고등' THEN 'H0'
	END AS SHC_LVL,
	concat('0', SHC_YEAR) AS SHC_YEAR,
	CASE
		WHEN SHC_TRM = '1학기' THEN 1
		WHEN SHC_TRM = '2학기' THEN 2
		ELSE '0'
	END AS SHC_TRM,
	CASE
		WHEN SBJ = '수학' THEN '01'
		WHEN SBJ = '국어' THEN '02'
		WHEN SBJ = '과학' THEN '03'
		WHEN SBJ = '영어' THEN '04'
		WHEN SBJ = '사회' THEN '05'
		WHEN SBJ = '역사' THEN '06'
		WHEN SBJ = '도덕' THEN '07'
	END AS SBJ_CDV,
	'2015' EDU_CRS_CDV,
	SOURCE_ID,
	'N' AS DITB_YN,
	'N',
	'USRCNFRM_99999999999',
	DATE_FORMAT(NOW(), '%Y%m%d%H%i%s'),
	'0.0.0.0'
FROM
	im_qstn_stndrd_mig a
WHERE
	QSTN_STNDRD_ID NOT IN (SELECT QSTN_STNDRD_ID FROM im_qstn_stndrd);

교과서 마이그레이션 테이블에서 원본 교과서 테이블로 INSERT 하는 쿼리 예시입니다.
NOT IN을 사용하여 교과서 테이블에 이미 존재하는 교과서 ID는 INSERT 되지 않도록 했습니다.

데이터 정상 확인

SELECT * FROM im_qstn_stndrd
WHERE FRST_REG_DT LIKE '20240402%'

오늘 원본 테이블에 INSERT 한 데이터가 정상적인지 조회하여 확인합니다.