엑셀 파일로 데이터 내보내기 시 문제

엑셀 파일로 데이터 내보내기 시 문제

 

한대성

 

 

 

SQL Server 2005의 가져오기/내보내기 기능 중에서 엑셀 파일에서 데이터를 가져오거나 내보낼 때 일부 문제가 있습니다.

 

다음과 같은 예제 데이터를 준비해 봅시다.

 

USE TEMPDB

GO

 

CREATE TABLE SampleData

(

                  Seq int identity,

                  Col varchar(10) default('abcdefghij')

)

GO

 

INSERT SampleData DEFAULT VALUES

GO 10

 

 

D:\ 루트에 결과가 저장될 빈(!) 엑셀 파일을 하나 만듭시다. (D:\Result.xls)

 

 

그런 다음, Management Studio에서 tempdb를 선택하고 내보내기 마법사를 실행합니다.

 

 

데이터 원본은 tempdb로 지정하고, 대상은 Microsoft Excel로 지정한 후, D:\Result.xls 파일을 선택합니다.

 

다음을 눌러 “하나 이상의 테이블 또는 뷰에서 데이터 복사”를 선택하고 내보내기 할 테이블인 SampleData 테이블을 선택한 후, 다음 à 다음을 눌러 내보내기를 합니다.

 

잘 처리됩니다.

 

엑셀 파일을 열어봐도 정상적으로 출력됩니다.

 

 

자, 이제 다시 데이터 내보내기를 해 봅시다. 동일한 테이블과 동일한 엑셀 파일입니다.

에러가 납니다. 유효성 검사 부분부터 에러가 발생합니다.

 

분명 동일한 방식으로 했는데, 처음에는 성공이고 두 번째는 실패라.

차이점이 뭘까요. 답은 첫 번째는 시트가 없는 상태였고, 두 번째는 시트가 있는 상태입니다.

 

좀 더 세밀하게 비교하기 위해 첫 번째와 두 번째의 작업을 패키지로 저장한 후 비교해 봅시다.

(SSIS 패키지로 저장하는 방법은 마지막 단계 전에 SSIS 패키지로 저장을 선택하면 됩니다.)

 

 

 

다르죠.

우선, 에러 원인을 설명하자면, 테이블의 col 열은 varchar(10) 즉 비유니코드 열입니다. 하지만, 엑셀 파일의 열은 유니코드(nvarchar 또는 nchar, ntext 등)로만 저장이 됩니다. SSIS에서는 이러한 유형이 차이를 가만 놔두지 않고 에러로 출력을 하는 것이지요. 에러를 내지 않으려면 첫 번째 그림의 오른쪽과 같이 데이터 변환 부분에서 열의 유형을 바꿔주는 작업이 있어야 합니다.

 

 

 

첫 번째 경우는 이 변환이 자동으로 추가되기 때문에 에러가 나지 않았습니다. 하지만 두 번째는 이러한 변환이 없기 때문에 패키지가 시작되기 전에 유효성 검사, 즉 패키지 내에 있는 각 작업 개체들의 메타 정보나 유형 등을 점검하는 단계에서 에러를 발생시키는 것이지요.

 

필자 개인적인 생각으로는, 만약 시트가 있는 경우에는 첫 번째 그림의 SQL 작업 준비, 즉 엑셀 시트를 만드는 작업만 빼고 수행해야 하겠지만, 실수인지 다른 의도가 있는 것인지 모르겠지만 데이터 유형 변환까지도 빼 버렸네요.

 

 

그럼 어떻게 해야 에러를 발생시키지 않고 처리를 할 수 있을까요?

 

첫 번째 방법은 다음과 같이 원본 테이블의 varchar 또는 char 열을 모두 유니코드 형태로 바꿔서 읽어오도록 쿼리 문을 지정하는 방법입니다.

데이터 내보내기에서 직접 테이블을 지정하지 말고, 다음과 같이 “전송할 데이터를 지정할 쿼리 작성” 을 선택한 후,

 

아래와 같이 유니코드 형태로 casting 하는 쿼리를 입력합니다.

 

주의할 것은 그 다음 단계에서 저장될 엑셀 시트를 제대로 지정해 주셔야 합니다.

 

 

귀찮지요?

 

 

 

다른 두 번째 방법은, 대상 시트를 지정하는 단계에서 그냥 새로운 시트 명을 입력하는 것입니다.

 

그런 다음, 엑셀 파일 내에서 데이터를 옮기거나 붙이도록 하는 것이지요.

 

세 번째 방법은, 위에서 설명한 것과 같이 패키지로 내려 받은 다음, SQL 작업 준비, 즉 시트를 생성하는 단계만 지우고선 이 패키지를 실행하는 방법입니다. 정기적으로 내려 받아야 하는 경우라면 고려해 볼만 합니다.

 

 

참고하세요^^