ADO 이용한 엑셀(*.xlsx), 콤마구분자파일(*.csv), 탭구분자파일(*.txt) 자료 읽기

자료변환은 DataBase 연동 및 기존 자료 업로드들 위한 영원한 이슈인것 같다.

이기종간의 자료변환이라든지 기존 자료를 대량으로 업로드해야하는 상황이 많이 발생한다.

빠져들수록 신기하고 재미있다.

 

얼마전에 세콤의 근태자료를 업로드해야할 일이 있어 정리해 둔다.

 

1.엑셀

가장 많이 사용하는 것은 아마도 엑셀(*.xls,  *.xlsx) 파일 포맷이다.

엑셀은 ADO를 이용하는 것과 OleObject를 이용하는 방법이 있다.

 

ADO은 ConnectionString을 설정하는 것이 연결의 주요 포인트다.

 

AdoConnection1.ConnectionString := 'Provider=Microsoft.ACE.OLEDB.12.0;'

      + 'Data Source=' + 경로포함파일명+ ';'

      + 'Extended Properties="Excel 12.0 xml;HDR=YES";'

 

  ADOQuery1.Close;
  ADOQuery1.SQL.Text := 'Select * from [Sheet1$]';
  ADOQuery1.Open;

 

엑셀파일은 ADO로 읽는 것이 제일 빠르다. OleObject 는 느리지만 제어가능한 부분 많다.(엑셀의 모든 기능이 사용가능)

 

엑셀을 ADO로 자료변환시  단점은 해당 컬럼의 DataType에 속하지  않는 자료는 표시안됨 (DB무결성에 따라, integer타입 컬럼에 문자 값이 있는 경우)   따라서 엑셀원본파일의 모든 컬럼 표시형식을  "텍스트" 로 저장하고 읽어오면 된다.

 

주의할 것은 컴퓨터에 엑셀이 설치되어 있지 않거나  델파이 2007이하의 32비트 버전에서 컴파일한 응용프로그램으로

64비트 엑셀이 설치된 컴퓨터에서 사용하려면 AccessDatabaseEngine.exe 파일을 한번 설치할 필요가 있다.

 

(추가)  오피스 2013 버전부터는 엑셀이 설치되어 있어도 별도로 AccessDatabaseEngine은 설치해야 ADO 연동 기능을 이용할 수 있다.

 

http://www.microsoft.com/ko-kr/download/details.aspx?id=13255

 

2. csv 파일

csv 파일은 콤마(,) 구분자로 컬럼을 구분한 텍스트파일 자료이다.

윈도우의 기본 구분자(Delimiter) 이다. 기본 구분자는 레지스트리에 정의되어 있다.

HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ Jet \ 4.0 \ Engines \ Text 

 

구분자는 ConnectionString의 "FMT" 예약어를 사용한다.

FMT=Delimited   는 기본 구분자가 콤마일때 csv파일 포맷이다.

 

AdoConnection1.ConnectionString :=  'Provider=Microsoft.Jet.OLEDB.4.0;'
           + 'Data Source=' + 파일이위치한 디렉터리 + ';'
           + 'Extended Properties="text;HDR=Yes;FMT=Delimited";';

 

3. tab 구분자 파일 (Schema.ini 파일 사용)

탭(TabDelimited), 고정폭(FixedLength),
사용자정의(Delimited(;) -> 세미컬럼일때)
등으로 레지스트리를 변경해도 되지만 권장되지 않는다.

대신 Schema.ini 파일을 작성하여 텍스트 데이터파일 디렉터리에
위치하는 것이 바람직하다. 

 

결국 ConnectionString 의 Extended Properties 속성을 Schema.ini 파일을 대체한다고 볼 수 있다.

 

자료파일이 Data.txt 일때 Schema.ini 작성 예는 다음과 같다.

 

/* Data.txt 파일 내용 */

20141230 192711 8006211000000 8006211000000 미등록 사용자 4
20141230 202350 8104042000000 8104042000000 미등록 사용자 4
20141231 075234 5711019000291 5711019000291 미등록 사용자 1
20141231 075442 6711019000291 6711019000291 미등록 사용자 1
20141231 081016 7912141785827 7912141785827 홍길동 1
20141231 081915 7812141785827 7812141785827 김길동 1

 

/* Schema.ini 파일 내용 */

[Data.txt]
   ColNameHeader=True
   MaxScanRows=0
   Format=TabDelimited

 

[Data2.txt]

   ColNameHeader=False
   Format=TabDelimited

 

[Data3.csv]

  Format=CSVDelimited

  ColNameHeader=False

  MaxScanRows=0

  Col1=ID  Text

  Col2=Name Text

  Col3=Pay   Long

 

[Data4.txt]

  Format=FixedLength

 

[Data5.txt]

  Format=Delimited(';')

 

위 보기는 하나의 Schema.ini 파일에 여러 데이터파일에 대한 스키마를 정의할 수 있다

"Data5.txt" 는 사용자 정의 문자로 구분자를 사용할 경우이다.

Format=Delimited(custom character) 형식을 사용하고 위 예는 세미컬럼(;) 을 구분자로 사용한 예이다.

 

# ColNameHeader 는 첫 행을 컬럼명으로 사용할지 여부를 나타낸다.
# MaxScanRows 는 컬럼의 DataType을 결정하기 위해 검사할 레코드 수를 나타낸다 (0은 전체 스캔)

# Format=TabDelimited 는 탭을 구분자로 사용한 경우

# Format=CSVDelimited 는 콤마(,)를 구분자로 사용한 경우

# Format=FixedLength 는 고정길이 컬럼을 사용한 경우

 

# 세번째는 csv 파일포맷은 Schema.ini에 설정하지 않아도 되지만  001, 002 등 숫자앞에 0을 채운 자료를

  변환시 자동으로 1,2,3 등으로 변환되므로 Schema.ini 파일에 정의하면 001, 002, 003... 등으로 변환 가능하다.

 

 

(참고자료)

https://www.connectionstrings.com/textfile/

http://www.codeproject.com/Articles/26822/Read-Text-File-txt-csv-log-tab-fixed-length

https://gisnuts.com/terra/blog/2012/06/14/using-the-schemaini-file-to-import-a-csv-file-into-arcgis

.