윈도우 서버에 업로드한 엑셀 데이터를 처리할 때 셀 데이터 타입(서식)을 추정하지 않게 하는 방법

윈도우 서버를 이용해서 엑셀 파일을 처리하는 개발 작업을 할 때 Microsoft.ACE.OLEDB provider는 필수적으로 설치해야 합니다.

ACE.OLEDB provider는 엑셀 파일을 읽기 위해서 컬럼 단위의 셀 서식(데이터 타입)을 결정해야 합니다.

읽은 엑셀 데이터를 후처리하거나, DB 테이블에 저장하려면 데이터 타입을 알아야 하기 때문입니다.

이때 OLEDB 프로바이더는 셀의 데이터 타입을 판단하는 속도를 높이기 위해 처음 8행까지만 읽은 후 셀 서식, 즉 컬럼의 데이터 타입을 결정합니다.

엑셀 데이터 행이 많은 경우 이 방법은 굉장히 효율적이지만, 중간에 다른 타입의 데이터가 나오게 될 경우 낭패를 보게될 수도 있습니다. OLEDB 프로바이더 에러입니다.

대부분의 경우는 문자열 타입이어야 할 셀 서식을 숫자 타입으로 처리를 하는 경우에 발생합니다.

처음 8행을 읽었을 때는 숫자만 나오는 컬럼을 숫자 타입으로 처리를 하다 중간에 문자열을 만나게 되면 에러를 발생시키게 됩니다.

8이라는 숫자는 정해진 고정값이 아니라 레지스트리에 설정 값으로 저장된 처음 읽는 행 수를 저장한 환경 변수입니다.

레지스트리 편집기를 실행해서 "typeguessrows" 문자열을 검색합니다.

처음 나오는 검색 결과에 값을 적용해야 하므로 왼쪽 트리에서 "컴퓨터"를 선택한 후 처음부터 전체 검색을 하면 됩니다. 시간이 조금 걸립니다.

속성 값 위치는

컴퓨터\HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Jet\4.0\Engines\Excel

컴퓨터\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\16.0\Access Connectivity Engine\Engines\Excel

등으로 설치되는 버전에 따라서 약간씩 다를 수 있습니다.(검색해서 찾는 것을 권장합니다.)

검색된 속성 값(TypeGuessRows)의 데이터를 보면 8로 설정된 기본값을 볼 수 있습니다.

이름 항목 중에서 "TypeGuessRows" 문자열을 더블클릭하면 편집기 창이 열립니다.

값을 "0"으로 변경한 후 "확인"을 눌러 값을 적용합니다.

값을 "0"으로 변경하면 셀 타입을 미리 추정하지 않고 전체 행을 다 스캔한 후 셀의 데이터 타입을 결정합니다.

엑셀 행 수가 수만 행으로 너무 길어서 데이터 타입을 판단하는데 시간 지연이 발생할 경우 "8" 대신 적절한 행 수를 입력해서 섹 서식을 판단하는 정확도를 조금 더 높일 수 있습니다.