[JDBC] Connect To Microsoft Excel File via JDBC

일단 제목은 거창하게 잡았다. [JDBC를 통해 Microsoft Excel File 연결하기] 그럴듯하다.  일반적으로 JDBC 프로그래밍을 하면 Oracle, MySQL 같은 RDBMS 를 가지고 한다. 당연하다. 하지만 꼭 RDBMS 의 데이터만 핸들링하란 법은 없다. JDBC 스펙에 의하면 비정형화되어 있는 파일들(csv, xls, txt) 같은 파일들도 핸들링할 수 있다고 나와있다. 그래서 이번 글에서는 일반 텍스트파일을 Microsoft Excel 파일로 변환해서 JDBC를 통해 연결하는 프로그램을 설명하려고 한다.

일단 특별한 프로그램이나 툴을 설치하지 않는다면 일반적인 Text 파일들은 직접 Java 가 핸들링할 수 없다. 그래서 할 수 없이 Excel 파일로 변환한 다음, ODBC 를 거쳐 JDBC까지 연결하도록 하자.

1. 먼저 필자는 Editplus 를 이용해서 아래의 그림과 같이 텍스트 파일을 만들었다. 앞에서부터 제품이름, 제품수량, 제품판매단가, 매입단가, 운송료이다. 현재 이 파일의 이름은 data.dat 이다.

data.dat

 
2. 직접 JDBC가 텍스트 파일을 핸들링할 수 없기에, xls 파일로 변환하기 위해 Microsoft Excel 프로그램을 열었다.

 
3. 파일 열기를 하고, 이미 생성한 data.dat 파일을 선택했다.그러면 아래와 같이 [Text Import Wizard] 창이 나타난다. 먼저 [Original data type] 섹션에서 기본값 [Delimited] 가 선택돼있는 것을 확인하고 [Next] 버튼을 누른다. 미리보기를 통해 열고자 하는 파일이 보인다.

 
4. 두번째 스텝. [Delimiters] 섹션에 보면 기본값은 [Tab]이다. 필자가 생성한 파일은 탭으로 구분하지 않고, 컴마로 구분했기 때문에 [Comma] 를 선택했다. 그럼, 미리보기창에 보면 각각의 데이터가 Delimiter 기준으로 나눠짐을 볼 수 있다. [Next] 를 클릭하자.

 
5. 마지막 스텝. [Column data format] 의 기본값 [General] 을 확인하고, [Finish] 버튼을 클릭하면 변환작업이 모두 끝난다.

 
6. 변환작업이 모두 끝나면 아래의 그림처럼 [data.dat] 파일이 Excel 파일로 변환됐음을 확인할 수 있다.그리고 컴머로 구분된 자료들이 각각의 Excel 의 셀에 잘 위치하고 있음을 알 수 있다. 그리고 특별히 기억할 것은 지금 오픈한 파일의 이름이 Sheet 의 이름으로 들어온다는 것이다. 파일 아래의 Sheet 이름을 확인하자. 이건 매우 중요하다.

 
7. 이제 JDBC작업을 위해 이 data.dat 파일을 xls 파일로 변환하자. 한가지 아쉬운 점은 xlsx 즉 Excel 2007 버전 이상파일로 변환할 수 없다는 것이다. 그래서 필자는 할 수 없이  [다른 이름으로 저장]할 때 [Excel 97-2003 Workbook]으로 저장했다.

 
8. 필자는 C 드라이브의 Temp 폴더에 data.xls 라는 이름으로 저장했다.

 
9. 저장이 모두 끝났다. 

data.xls

 
10. 이제 ODBC 로 연결하자. [제어판] 으로 들어가서 [Performance and Maintenance] 로 가자.

 
11. [관리도구] [Administrative Tools] 로 가자.

 
12. [Data Source (ODBC)] 가 보인다. 더블클릭하자.

13. [ODBC Data Source Administrator] 창이 나타난다. 여러 탭 중에 [System DSN] 탭을 선택한다. 그리고 [Add] 버튼을 클릭하여 새로운 DSN 을 추가하자.

14. [Create New Data Source] 창이 나타났다. 목록 중에서 [Driver do Microsoft Excel(*.xls)] 을 선택한다. 왜냐하면 지금 ODBC 로 연결할 파일의 확장자가 xls 즉 Excel 파일이기 때문이다. [Finish] 버튼 클릭. 

 
15. [Finish] 버튼을 클릭하면 [ODBC Microsoft Excel Setup] 창이 나타난다. 여기서 몇가지 중요한 포인트가 있다. 

• Data Source Name : mydata (<-- 여러분들은 알아서 의미있는 이름을 주면 된다.)
  Description : (<-- 굳이 작성하지 않아도 된다.)
  
  [Select Workbook...] 버튼을 클릭하여 Excel 파일을 선택하도록 하자. 

 
16. [Select Workbook]창이 나타난다. 여기서 위에서 xls 파일 저장한 디렉토리에 가서 저장한 파일을 지정한다. 그리고 [OK] 버튼을 클릭하여 창을 닫자.

 
17. 그러면 아래와 같이 [Version] 밑에 [Workbook:] 이 들어오고 방금 선택한 파일의 경로가 나온다. [OK] 버튼을 클릭하여 창을 닫자.

 
18. 아래 그림과 같이 [mydata] 라는 이름의 Data Source Name 이 완성됐다. [OK] 버튼을 클리하여 ODBC 관리자 창을 닫자.

 
19. 이제 Java 프로그래밍 할 차례이다. 아래와 같이 코딩했다. 

JdbcExcel.java

이 코드에서 중요한 부분은 
1) JDBC Driver 가 JDBC-ODBC Bridge Driver 라는 점.
2) JDBC url 은 jdbc:odbc:Data Source Name 이라는 점
3) Select 할 때 테이블의 이름은 Excel 의 Sheet 이름이며, Sheet 의 이름을 갖고 올 때  반드시 [Sheet Name$]로 해야한다는 점
4) Excel 파일의 각 셀에 저장한 값을 갖고 올 때 데이타타입별로 갖고 올 수 있다는 점. 즉 갖고 올 데이터가 정수이면 비록 텍스트파일에 저장돼있을 때 스트링이었다 하더라도 정수로 갖고 올 수 있다는 점이다.

20. 결과는 아래와 같다. 성공적으로 데이터를 갖고 온 것임을 알 수 있다. 이로써, 텍스트 파일이나 csv 혹은 xls 파일같은 비정형 데이터도 JDBC 에서 처리할 수 있다는 것을 확인했다. 재밌다. 여기서 한 가지 주의할 점은 Excel 파일의 데이터를 갖고 올 때 첫 행 글이 들어오지 않을 것이다. 이것은 첫 행은 헤더로 보기 때문이다. 그래서 Excel 파일에서는 첫 행에 반드시 제목을 주어야 한다. 잊지 말자. 

 
※ 필자의 본 글을 작성하기 위해 참조한 사이트 주소를 소개합니다. 
http://javaprogrammingforums.com/jdbc-database-tutorials/356-how-connect-excel-spreadsheet-using-jdbc-java.html

http://www.ehow.com/how_4810548_use-jdbc-connect-ms-excel.html 

http://www.jdbctutorial.net/jdbc-read-data-microsoft-excel-sheet.php