PostgreSQL ODBC를 이용한 Excel 연동

출처: https://blog.naver.com/geartec82/221660895942

 

(TXT,CSV,EXCEL)

PostgreSQL ODBC 통한 다양한 데이터 연동

(TXT,CSV,EXCEL)

 

 

 

 

 

1. ODBC(Open Datbase Connectivity)란?

​

다양한 DBMS를 통합적으로 관리하고 다양한 데이터 소스를 이용하여 Application 을 구성해야만 한다면 ? Connection 을 일일이 선언해줘야 하고 관리 측면에서도 어렵기 마련이다. 하나의 소스를 가지고 ODBC 매니져를 통하여 데이터를 조회, 삽입, 삭제, 편집을 가능하도록 하는 드라이버라고 말할 수 있다.

DBMS 에 관계없이 어떤 응용 프로그램에서나 모두 접근하여 사용할 수 있도록 하기 위하여 MS(마이그로소프트) 에서 표준규격을 제공하고 DBMS 벤더는 그 표준에 따라 ODBC 드라이버를 제공한다.

즉 응용프로그램과 DBMS 간 사이에서 ODBC 드라이버 관리자자가 각각의 ODBC 드라이버를 통하여 하나의 응용프로그램이 다양한 DBMS 에서 조회 및 수정이 가능하다.

 

 

타 DBMS와 마찬가지로 PostgreSQL도 ODBC 드라이버를 제공하고 있다. http://www.postgresql.org/ftp/odbc/versions/msi/ 에서 다운로드 가능하며 다운로드 하고 압축을 해제하면 msi 또는 exe 확장자 파일이 확인되는데 설치는 어렵지 않다. 보통 psqlodbc 라는 명칭을 가지고 제공하고 있으며 반드시 사용하는 DBMS 의 버전과 OS 에 맞추어 다운로드 한다.

필자는 windows 10 64bit 이므로 표기된 버전을 다운로드하고 설치하였다.

 

2. Windows 10 ODBC 설정하기

​

ODBC 데이터 원본 만들기

Windows + C 또는 찾기 에서 ODBC 데이터 원본을 선택한다. Application 이 64bit 가 아니고 32bit 환경에서 작동되는 것이라면 ODBC도 32bit로 다운로드 하고 ODBC 데이터 원본도 32bit 를 선택하여 다음 단계를 진행하도록 한다.

​

​

​

​

 

​

사용자 DSN 추가

 

PostgreSQL Unicode를 선택한다.

​

​

자동으로 아래와 같이 팝업이 나타나고 DB Connection 정보를 기입하고 저장하기 전에 반드시 Test 버튼을 통하여 정상적으로 연결되는지 확인한 후 저장한다.

​

 

다음과 같이 PostgreSQL에 ODBC로 연결할 수 있는 DSN 이 생성되었다.

​

이제 모든 응용프로그램은 ODBC 연결하고자 할 때 PostgreSQL35W를 이용하여 이용할 수 있다.

​

 

3. EXCEL 2010, 2013 에서 직접 데이터베이스 접속하기

​

그럼 많이 사용하고 있는 Excel 프로그램으로 ODBC를 이용하여 데이터베이스에 직접 접속하여 데이터 베이스에 있는 데이터를 엑셀파일에서 직접 연동할 수 있도록 해보자.

​

데이터 -> 기타원본에서 ->데이터 연결 마법사를 선택합니다.

 

ODBC DSN -> PostgreSQL35W(사용자가 생성한 ODBC DSN)를 선택합니다.

 

데이터 베이스를 선택하고 오브젝트를 선택한 후 마침을 누른다.

 

다음과 같이 데이터가 EXCEL로 표시된다.

 

 

 

 

 

4. EXCEL 2010, 2013 에서 SQL 질의하고 그 결과를 Excel 로 저장

(파워쿼리를 이용하는 방법)

​

MS(마이크로 소프트는) ODBC를 이용하여 SQL을 질의하고 그 결과를 엑셀에서 확인하고 가공할 수 있는 파워쿼리(Power Query)라는 추가 기능을 제공합니다. 해당 링크에서 다운로드하고 설치하면 무료로 사용이 가능합니다.

다운로드 링크 : https://www.microsoft.com/ko-KR/download/details.aspx?id=39379

 

설치가 정상적으로 설치가 완료 되었다면 다음과 같이 파워쿼리 탭이 생성됩니다.

 

​

파워쿼리-> 기타원본에서 -> “ODBC에서” 를 선택합니다.

 

 

ODBC에서 데이터 원본 DSN 을 선택하고 고급옵션에서 불러올 데이터 대상을 조회하는 SQL을 입력합니다.

 

 

SQL 결과물이 프리뷰 되며 로드를 선택합니다.

 

 

 

 

최종적으로 Excel에 데이터가 표시 됩니다.

 

5. psql 에서 COPY 명령어를 통한 txt, csv 파일(Excel) 파일 Upload 하기

​

copy [테이블명](컬럼1, 컬럼2, 컬럼3) FROM '[import할 파일 경로/파일명]' DELIMITER '[구분자]' CSV

 

ex)

copy zipcode FROM '/home/postgres/csv_sample.txt' DELIMITER '|' CSV

 

postgres=# copy zipcode FROM '/home/postgres/csv_sample.txt' DELIMITER '|' CSV ; COPY 585835

 

 

 

 

 

 

 

 

6. Dbeaver를 이용한 txt, csv 파일(Excel) 파일 Upload 하기

​

​

 

데이터 베이스를 접속한 후 타겟 테이블을 선택 후 Import Data를 선택합니다.

 

데이터 베이스를 접속한 후 CSV 파일을 선택 합니다.

​

Source name을 클릭하여 소스파일( csv, txt) 파일을 선택한 후 소스파일의 컬럼 구분자를 확인하고명시하여 Next 를 진행합니다.

 

​

정상적으로 데이터가 입력되었다면 다음과 같이 다음과 같이 Upload 가 완료되었다는 메시지가 팝업 됩니다.

 

 

 

 

7. 만약 한글이 깨진다면....

현재 Psql encoding 설정 확인

postgres=# show server_encoding postgres-# ; server_encoding ----------------- UTF8 (1개 행) postgres=# show client_encoding ; client_encoding ----------------- EUC_KR (1개 행)

​

사용하고 있는 server_encoding 값에 맞추거나 client_encoding 값을 UTF8 로 변경

postgres=# set CLIENT_ENCODING to 'UTF8'; SET postgres=# show CLIENT_ENCODING; client_encoding ----------------- UTF8 (1개 행)

 

소스 파일(txt, csv, Excel) 파일의 캐릭터 셋 (Character Set)확인

csv 파일 메모장으로 열고 다른 이름으로 저장 기존 ANSI 에서 UTF8로 변경하여 저장을 하고 다시 진행하시면 문제없이 진행 가능합니다.

​

8. 추가적으로 ....

​

PostgreSQL에서 사용할 수 있는 문자 집합

이름	설명	언어	서버?	ICU?	바이트/문자	별칭
BIG5	Big Five	중국어 번체	아니요	아니요	1-2	WIN950, Windows950
EUC_CN	Extended UNIX Code-CN	중국어 간체	예	예	1-3
EUC_JP	Extended UNIX Code-JP	일본어	예	예	1-3
EUC_JIS_2004	Extended UNIX Code-JP, JIS X 0213	일본어	예	아니요	1-3
EUC_KR	Extended UNIX Code-KR	한국어	예	예	1-3
EUC_TW	Extended UNIX Code-TW	중국어 번체, 대만	예	예	1-3
GB18030	National Standard	중국어	아니요	아니요	1-4
GBK	Extended National Standard	중국어 간체	아니요	아니요	1-2	WIN936, Windows936
ISO_8859_5	ISO 8859-5, ECMA 113	라틴어/키릴어	예	예	1
ISO_8859_6	ISO 8859-6, ECMA 114	라틴어/아랍어	예	예	1
ISO_8859_7	ISO 8859-7, ECMA 118	라틴어/그리스어	예	예	1
ISO_8859_8	ISO 8859-8, ECMA 121	라틴어/히브리어	예	예	1
JOHAB	JOHAB	한국어 (한글)	아니요	아니요	1-3
KOI8R	KOI8-R	키릴어 (러시아어)	예	예	1	KOI8
KOI8U	KOI8-U	키릴어 (우크라이나어)	예	예	1
LATIN1	ISO 8859-1, ECMA 94	서유럽어	예	예	1	ISO88591
LATIN2	ISO 8859-2, ECMA 94	중유럽어	예	예	1	ISO88592
LATIN3	ISO 8859-3, ECMA 94	남유럽어	예	예	1	ISO88593
LATIN4	ISO 8859-4, ECMA 94	북유럽어	예	예	1	ISO88594
LATIN5	ISO 8859-9, ECMA 128	터키어	예	예	1	ISO88599
LATIN6	ISO 8859-10, ECMA 144	스칸디나비아어	예	예	1	ISO885910
LATIN7	ISO 8859-13	발트어	예	예	1	ISO885913
LATIN8	ISO 8859-14	켈트어	예	예	1	ISO885914
LATIN9	ISO 8859-15	유로 및 액센트 사용 LATIN1	예	예	1	ISO885915
LATIN10	ISO 8859-16, ASRO SR 14111	루마니아어	예	아니요	1	ISO885916
MULE_INTERNAL	Mule 내부 코드	Multilingual Emacs	예	아니요	1-4
SJIS	Shift JIS	일본어	아니요	아니요	1-2	Mskanji, ShiftJIS, WIN932, Windows932
SHIFT_JIS_2004	Shift JIS, JIS X 0213	일본어	아니요	아니요	1-2
SQL_ASCII	미지정 (텍스트 참조)	아무거나	예	아니요	1
UHC	Unified Hangul Code	한국어	아니요	아니요	1-2	WIN949, Windows949
UTF8	유니코드, 8비트	모두	예	예	1-4	Unicode
WIN866	Windows CP866	키릴어	예	예	1	ALT
WIN874	Windows CP874	태국어	예	아니요	1
WIN1250	Windows CP1250	중유럽어	예	예	1
WIN1251	Windows CP1251	키릴어	예	예	1	WIN
WIN1252	Windows CP1252	서유럽어	예	예	1
WIN1253	Windows CP1253	그리스어	예	예	1
WIN1254	Windows CP1254	터키어	예	예	1
WIN1255	Windows CP1255	히브리어	예	예	1
WIN1256	Windows CP1256	아랍어	예	예	1
WIN1257	Windows CP1257	발트어	예	예	1
WIN1258	Windows CP1258	베트남어	예	예	1	ABC, TCVN, TCVN5712, VSCII

 

<PG 문자집합 출처> http://postgresql.kr/docs/11/multibyte.html

[출처] PostgreSQL ODBC를 이용한 Excel 연동|작성자 DB장이