BLUE STUDY

우편번호가 새롭게 바뀔 때마다 ".dbf" 형식으로 우편번호 파일이 올라옵니다. Site 운영자라면 고충을 아시겠지만 지역명이 바뀐 경우 사용자들은 민감하게 반응합니다. 그래서 우편번호는 되도록 최신것으로 유지하는것이 좋습니다. 그런데 우편번호가 ".dbf" File로 올라오기 때문에 바로 Site에 적용하기가 힘듭니다. (아니, SQL 로 변환하기가 귀찮다고 해야겠죠? ^^)

그래서, 직접 변환하는 방법이 궁금하신 분들을 위하여 작지만 재미있는 Technic을 하나 알려드릴까 합니다. 물론, 제가 소개하는 방법은 그냥 저의 개인적인 꼼수일 뿐이고 다른 분들은 어떻게 하시는지 모르겠습니다. 더 좋은 방법이 있다면 그것으로 하는게 좋겠지만 이 방법도 알고 계시면 다른 경우에도 상당히 유용하게 사용하시리라 믿습니다. ^^;

이 예제 에서는

CREATE TABLE zipcode (
NO int(5) PRIMARY KEY,
ZIPCODE       VARCHAR(7),
SIDO       VARCHAR(10),
GUGUN VARCHAR(14),
DONG VARCHAR(44),
BUNJI VARCHAR(18)
);

형식으로 SQL 문을 만들어보도록 하겠습니다. 우선 ".dbf" File을 Download 받아서 Microsoft Excel 로 File을 엽니다. 그 다음에 Field를 원하는 순서대로 바꿔줍니다. F Field에 SEQ 가 있으니까 A 로 옮겨주시면 되겠죠? F Field에서 Mouse 오른쪽 Button을 Click 하신 후 '잘라내기'를 하신 뒤에 A Field에서 Mouse 오른쪽 Button을 누르시고 '잘라낸 셀 삽입'을 하시면 됩니다. 그리고 다른 이름으로 저장을 한 후 'csv 로 저장'을 선택하시면 모든 Field들이 쉼표로 구분된 Text File이 생성됩니다.

File을 열어보시면

ZIPCODE,SIDO,GUGUN,DONG,BUNJI,SEQ
1,135-806,서울,강남구,개포1동 경남아파트,
2,135-807,서울,강남구,개포1동 우성3차아파트,(1∼6동)
3,135-806,서울,강남구,개포1동 우성9차아파트,(901∼902동)
...

와 같이 되어있습니다. 이 정도 하면 아마도 감이 오셨을 겁니다 ^^;

간단한 테크닉을 이용하여

insert into zipcode values ('1', '135-806', '서울', '강남구', '개포1동 경남아파트', '');
insert into zipcode values ('2', '135-807', '서울', '강남구', '개포1동 우성3차아파트', '(1∼6동)');
insert into zipcode values ('3', '135-806', '서울', '강남구', '개포1동 우성9차아파트', '(901∼902동)');
...

이런식으로 변환만 해주면 끝입니다. ^^ 변환하는 데에는 정규 표현식(Regular Expression)이 사용됩니다. 정규 표현식은 알고 있으면 정말 유용하죠 ^^ 정규 표현식을 사용하기 위하여 저는 UltraEdit라는 Edit를 사용하였습니다. 이게 아니더라도 EditPlus 나 vi 등의 다양한 Editor로도 변환이 가능합니다. UltraEdit로 하기로 했으니까 간략히 살펴보면, UltraEdit의 도움말을 열어서 regular expression 이라고 검색하면 설명이 나옵니다. 보면, Regular Expressions (UltraEdit Syntax)와 Regular Expressions (Unix Syntax) 두 종류의 정규표현식을 사용할 수 있습니다. 그냥 Unix 형식의 정규표현식을 사용해도 되는데 UE 에서는 어떻게 하나 공부도 하고 호기심도 충족시킬겸해서 UltraEdit 형식의 정규표현식을 사용하도록 하겠습니다. 자세한건 설명서를 읽어 보세요 ^^ 응용을 즐기시는 분이라면 여기까지만 힌트를 주면 알아서 하시리라 믿습니다 ^^

그래도 하나씩 해보면, 우선 UltraEdit로 아까 변환한 csv File을 열고 필요 없는 첫번째 줄을 지웁니다. 그리고 ctrl+r (replace) 을 누르면 replace 창이 뜹니다. 여기서 Regular Expression 에 V Check를 해주시고 Find What 에는 %^(*^)$ 을, Replace With 에는 s^1 을 넣고 Replace All 을 해주시면 모든 라인의 맨 앞에 s 라는 글자가 붙습니다. 이건 제가 임의로 붙인건데 작업 도중에 s 표시가 있는 라인은 아직 변환이 되지 않은 라인을 의미합니다. 하다 보니까 한번에 변환이 다 안되더라구요 ^^ 그래서 Check를 해두는 겁니다. 한번에 되는 방법이 있으면 알려주세요~

이제 본격적으로 변환을 해보면

Find What : %s^(*^),^(*^),^(*^),^(*^),^(*^),^(*^)$
Replace With : insert into zipcode values ('^1', '^2', '^3', '^4', '^5', '^6');

와 같이 해서 변환을 해봅니다. 그러면 우리가 다음과 같이 원하던 멋진 결과가 나옵니다 ^^

insert into zipcode values ('1', '135-806', '서울', '강남구', '개포1동 경남아파트', '');
insert into zipcode values ('2', '135-807', '서울', '강남구', '개포1동 우성3차아파트', '(1∼6동)');
insert into zipcode values ('3', '135-806', '서울', '강남구', '개포1동 우성9차아파트', '(901∼902동)');
...

그런데 중간쯤에 잘 보시면 줄의 마지막에 , 가 없기 때문에 필드 개수가 모자라서 미처 변환되지 않은 지역도 있습니다. 이런 곳들만 골라서 다시 변환해주는 작업이 필요합니다. 그래서 아까 s 표시를 해둔것이죠 ^^;

그래서 이번에는

Find What : %s^(*^),^(*^),^(*^),^(*^),^(*^)$
Replace With : insert into zipcode values ('^1', '^2', '^3', '^4', '^5', '');

와 같이 해서 변환을 하면 전부 다 변환이 되었습니다.

만약 테이블 이름을 다르게 하고 싶으시면 zipcode 를 다른것으로 바꾸시면 됩니다. 그럼 이 결과를 원하는 이름으로 저장을 합니다. zipcode20030217.sql 과 같이 하면 되겠죠? ^^ 그럼 다음에 다른 곳에서 응용을 하기 위해서 위에서 사용한 정규표현식을 간단히 살펴보면, % 는 줄의 맨처음, $ 는 줄의 맨 마지막을 나타냅니다. 그리고 * 는 줄바꿈을 제외한 모든 문자를 나타냅니다. ^(, ^) 는 ^( 와 ^) 사이에 있는 문자를 한 묶음으로 묶은것입니다. 그리고 ^1 은 아까 묶었던 묶음중 첫번째 것을 나타냅니다.

그러니까 정규표현식으로

%s^(*^),^(*^),^(*^),^(*^),^(*^),^(*^)$
insert into zipcode values ('^1', '^2', '^3', '^4', '^5', '^6');

와 같이 하면

"s1,135-806,서울,강남구,개포1동 경남아파트," 이 "insert into zipcode values ('1', '135-806', '서울', '강남구', '개포1동 경남아파트', '');"으로 바뀌는거죠. 즉, ^1 은 '1', ^2 는 '135-806', ^3 은 '서울'을 나타냅니다. 그러면 이렇게 만든 SQL 문을 어떻게 실제 우편번호 DB 에 적용을 하는가도 알아보겠습니다. 궁금해하시는 분이 많더라구요 ^^ 주의할 사항은, 기존의 우편번호 Table Schema가 우리가 새로 바꾸려고 하는것과 다른 경우에는 위의 제 방법을 약간 수정하셔서 기존의 구조에 맞게 변환을 해주신 후 테이블을 바꿔주시면 됩니다. 만약에 현재 우편번호 내용이 localhost 에 있는 MySQL DBMS 안의 seoz 라는 Database 의 zipcode 라는 Table 에 있다고 하면, mysql 에 Command Line에 접속하셔서

mysql> Use seoz;
mysql> Delete From zipcode;

을 실행하셔서 기존에 있던 Table의 내용을 지운 후에

c:\> mysql -uroot -p seoz < c:\zipcode20030217.sql

과 같이 하시면 우편번호 Data가 들어갑니다. 역시 주의 사항은 우편번호 Table의 구조가

CREATE TABLE zipcode (
NO int(5) PRIMARY KEY,
ZIPCODE&nbsp; &nbsp; &nbsp; &nbsp;VARCHAR(7),
SIDO&nbsp; &nbsp; &nbsp; &nbsp;VARCHAR(10),
GUGUN VARCHAR(14),
DONG VARCHAR(44),
BUNJI VARCHAR(18)
);

와 같아야 하고 꼭 기존의 자료를 delete 를 하셔서 중복된 자료가 생겨 낭패를 보는 일이 없도록 해야합니다. ^^

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.