TEXT 형이 포함된 Excel 데이터 가져오기 문제

qBank[0].sql

qBank_org.xls

TEXT 형이 포함된 Excel 데이터 가져오기 문제

한대성

질문 원본: http://www.sqlleader.com/mboard.asp?exec=view&strBoardID=SS2005QNA&intSeq=1218

말씀해주신 내용을 확인하고 적용해 보았으나 여전히 안됩니다.  그래서 DB에 입력되어 있는 내용을 '데이터 내보내기'를 통해 엑셀로 저장하고 동일하게 새로운 테이블 생성하고 그곳에 '데이터 가져오기'를 시도했습니다.  오류검사는 통과가 되었지만 짤림현상으로 인해 실행을 중지한다는 메시지와 함께 정지가 되더군요.  '데이터 내보내기'를 통해 가져온 데이터를 다시 넣는데 바로 안되는 이유는 무엇인가요?   '데이터 내보내기'를 통해 저정한 엑셀파일도 함께 보냅니다.

  

 

 

우선, Excel의 데이터를 Excel Driver로 읽어올 때에는 다음과 같은 여섯 가지의 유형으로 변환해서 읽어오게 됩니다.

 

Excel 유형	변환 유형
Currency	Currency(DT_CY)
Boolean	Boolean(DT_BOOL)
Date/time	Date(DT_DATE)
String	Unicode string, Length 255(DT_WSTR)
Memo	Unicode text stream(DT_NTEXT)

(참고 : http://msdn2.microsoft.com/en-us/library/ms141683.aspx)

 

위의 표에서와 같이 Excel의 문자형 데이터는 모두 Unicode형 255자리의 데이터로 인식을 하게 됩니다.

그런데 첨부하신 엑셀 파일의 데이터를 잠시 살펴보겠습니다.

tBody 열의 크기를 len() 함수를 이용해서 확인해 본 결과 위와 같이 439, 650 등과 같이 255자리를 넘습니다.

 

그럼 SSIS, 즉 데이터 가져오기/내보내기에서 에러가 나는 원인에 대해 설명 드리겠습니다.

해당 엑셀 파일을 가져오기/내보내기를 통해 설정한 후, 실행하지 말고 SSIS 패키지로 저장하면 다음과 같은 패키지가 만들어집니다.

여기서, 원본 – qBank$ 부분이 엑셀 파일에서 데이터를 읽어오는 부분이며, 읽어온 데이터를 데이터 변환 1이라는 변환 작업으로 출력합니다. 이 때, SSIS 엔진에서는 읽어오는 데이터들의 열 정보를 파악하게 되는데, Excel Driver를 통해서 각 열의 메타 정보를 읽고 설정하게 됩니다.

tBody열이 String형이기 때문에, 무조건 그냥 DT_WSTR(Unicode형) 255자리로 설정되었습니다.

 

에러는 여기에서 발생합니다. 만약 패키지를 실행시키면, 원본 – qBank$라는 작업에서 실제 엑셀 파일(qBank_org.xls)에서 데이터를 읽어오게 됩니다. 이 때 tBody의 자릿수는 255자리라고 설정을 했는데, 들어오는 데이터는 이 크기 이상의 데이터가 들어오게 되어 잘림 오류가 발생하는 것입니다.

 

이전 버전인 DTS에서도 이와 비슷한 에러가 발생됩니다. (Excel Driver를 이용하는 건 동일하기 때문이겠지요..)

 

 

좀 더 명확히 확인하기 위해 다음과 같이 쿼리 분석기에서 쿼리를 수행하고 결과 테이블을 생성한 후에 열을 확인해 보겠습니다.

SELECT * INTO AAA FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;DATABASE=d:\qBank_org.xls', 'Select * from [qBank$]') --(80개 행 적용됨)   SP_COLUMNS AAA

우선, 모든 String형 데이터가 255 자리로 인식되는 것을 확인할 수 있습니다. 그리고 에러는 발생하지 않았습니다. 하지만, 255자리가 넘는 데이터는 에러 없이 그냥 255자리로만 잘려서 입력됩니다.

 

 

결론은,

『255 자릿수를 넘는 문자형 데이터를 가진 엑셀 데이터를 원본으로 사용할 경우에는 잘림 에러가 발생한다. 』 입니다.

 

만약, 에러가 발생되면 해당 행을 버리고서라도, 또는 별도의 경로로 에러 정보를 관리하고자 한다면 오류 처리라는 것을 이용해야 합니다.

 

오류 처리는 가져오기/내보내기 부분에서 직접 설정할 수는 없으며, SSIS 패키지로 저장한 후에 설정해야 합니다. 원본 – qBank$을 더블 클릭해서 편집기를 연 다음, 오류 출력 탭에서 각 열 별로 오류 출력에 관한 사항을 설정할 수 있습니다.

(자세한 것은 다음 세미나 자료 중, Demo2_3 참고)

 

 

그럼 어떻게 해야 하냐면,

1.       엑셀 대신 Access나 다른 DBMS를 직접 지정해서 사용합니다.

2.       또는,

A.        텍스트 형 열 데이터와 일반 데이터를 분리해서 생성합니다. 일반 데이터를 엑셀로 내보내고 가져오는 데에는 특별한 문제가 없습니다.

B.        텍스트 형 열의 데이터는 엑셀 파일 외에 Access 또는 텍스트 한정자를 지정한 플랫 파일 등을 이용합니다.

 

물론 더 다른 방법들도 있을 것인데, 테스트 해 보고 좋은 방법이 있으면 덧붙이겠습니다.

 

참고하시기 바랍니다.