Excel Data 로딩 시 Null로 처리되는 문제

Excel Data 로딩 시 Null로 처리되는 문제 

 

한대성

  

   

Excel 파일의 데이터를 테이블로 입력하는 경우에 대해 살펴봅시다.

 

 

Case	Excel File		Table
Case 1
Case 2
Case 3

 

Excel 데이터를 로드할 경우, Excel의 ISAM 드라이브에서 처음 몇 개의 행을 읽어서 데이터의 유형을 결정하게 됩니다. 이 때, 데이터 유형은 텍스트 형(nvarchar)이거나 숫자 형(Float)형 둘 중의 하나로 결정됩니다. Case 2의 경우, ISAM 드라이브에서 몇 개의 행을 읽어본 후 파악한 결과, 데이터는 숫자 형으로 판단했기 때문에 “‘0313”으로 입력된 세 번째 행의 데이터는 문자 데이터로 간주해서 Null로 처리됩니다. Case 3의 경우 “’0313”으로 입력된 데이터를 보고, 문자형 데이터라 판단했기 때문에 “3209”와 같은 숫자 형 데이터를 Null로 처리하게 됩니다.

 

이러한 현상은 Excel Data를 연결할 때의 연결 문자열과 상관 있습니다. 기본적으로 Excel 파일의 연결 문자열은 다음과 같이 IMEX에 대한 속성이 생략된 형태입니다.

 

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Code.xls;Extended Properties="EXCEL 8.0;HDR=YES;";

 

IMEX 속성은 Excel 파일이 데이터 출력(Export)용인지 데이터 입력(Import)용인지를 설정하는 속성으로, 생략하면 0, 즉, Export모드로 인식하여 위와 같은 현상이 발생합니다.

 

Case 2, Case 3과 같이 텍스트 형태와 숫자 형태가 섞여 있는(셀의 속성과는 상관없이) Excel 데이터를 로딩할 경우에는 다음과 같이 연결 문자열에 IMEX=1이라는 값을 추가하면 됩니다.

 

 

Case 2	Case 3

 

IMEX=1로 설정한 경우, 다음 레지스트리에 있는 값을 읽어오게 됩니다.

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel]

TypeGuessRows의 값은 데이터의 유형을 유추할 때 읽어올 행 수(8)이며, ImportMixedType은 읽어온 데이터를 처리할 유형(Text)입니다. 이 값이 Text이기 때문에 숫자 형의 데이터라도 모두 문자형(Text)로 인식하게 됩니다.

 

 

IMEX=1로 설정할 경우에는 다음과 같은 사항을 주의해야 합니다. Import 모드이기 때문에 데이터를 읽어오는 경우에는 문제없이 잘 수행되나, 해당 연결로 데이터를 출력하는 경우에는 에러가 발생하게 됩니다. 따라서, 읽어오기 위한 Excel 연결과, 내보내기 위한 Excel 연결을 따로 관리하는 것을 권장합니다.

 

 

 

 

 

 

본 게시판에 실린 글은 누구나 복사하셔서 이용하셔도 되지만, 반드시 출처(SQLLeader.com) 및 링크를 밝혀주셔야 합니다.