Minggu, 03 Juli 2011

Koneksi data ke Text File 04 - Text file butuh perlakuan khusus

(4 dari 4)

Muatan :

  • Kilasan tentang data text file yang membutuhkan perlakuan khusus
  • Data text file dan susunan file schema.ini yang digunakan
  • Perlakuan khusus terhadap data text menggunakan query


Kilasan tentang data text file yang membutuhkan perlakuan khusus

Pada coretan pertama tentang koneksi data ke text file, telah dijelaskan sekilas mengenai data text file hasil suatu report sistem aplikasi. Hasil report tersebut menyertakan format bilangan yang dilengkapi pemisah ribuan. Penulisan data tanggal juga berbeda antara data kolom DueDate dengan kolom InvDate.

Coretan kali ini akan difokuskan pada salah satu cara menyusun hasil koneksi yang tidak dipengaruhi oleh locale setting komputer user. Koneksi memanfaatkan Microsoft Jet 4.0 OLE DB. Pada penggunaan Microsoft Text ODBC harus membuat DSN User dan koneksi melalui MS Query.




Data text file dan susunan file schema.ini yang digunakan

  • Data
  • Kondisi
    1. Data text file disusun menggunakan locale setting English (1033).
    2. Komputer user menggunakan locale setting English (1033) dan ada yang menggunakan locale setting Indonesia (1057), yang bisa jadi berbeda karakter pemisah ribuannya.
    3. Antar kolom dipisahkan dengan karakter &ltTab&gt.
    4. Baris judul data tidak akan disertakan dalam hasil koneksi.
    5. Data tanggal pada kolom ke-5 sering salah konversi.
    6. Data nilai pada kolom ke-6 menyertakan karakter pemisah ribuan.
    7. Baris total tidak akan disertakan dalam hasil koneksi.
    8. Data tanggal pada kolom ke-4 akan ditampilkan sebagai data bertipe datetime.

  • Konfigurasi dalam file schema.ini
    Untuk kondisi nomor 3 sampai nomor 6 membutuhkan konfigurasi khusus dalam file schema.ini seperti di bawah ini. Pada kondisi nomor :
    1. Format=TabDelimited
    2. ColNameHeader=TRUE
    3. DateTimeFormat=dd/mm/yyyy
      dan definisi kolom ke-5 adalah bertipe Date, yaitu Col5=INVDATE Date
    4. DecimalSymbol=.
      dan definisi kolom ke-6 adalah bertipe teks, yaitu Col6=AMOUNT Char Width 40

    Konfigurasi file schema.ini untuk tabel data text file tersebut adalah :
      [bln06.txt]
      Format=TabDelimited
      ColNameHeader=TRUE
      DateTimeFormat=dd/mm/yyyy
      DecimalSymbol=.
      Col1=RECNO Integer
      Col2=INVNO Char Width 17
      Col3=CUSTID Char Width 3
      Col4=DUEDATE Char Width 8
      Col5=INVDATE Date
      Col6=AMOUNT Char Width 40

  • Keterangan
    Penggunaan opsi DecimalSymbol=. akan membuat seluruh nilai dengan format 'General' seragam memiliki tanda desimal berupa karakter titik. Kolom nilai yang menyertakan pemisah ribuan diset agar bertipe teks agar tampil apa adanya lebih dulu, dan akan diproses oleh query koneksi.


Perlakuan khusus terhadap data text menggunakan query

Tahap selanjutnya adalah membuat koneksi data ke text file dengan konfigurasi file schema.ini yang dibahas di atas. Cara pembuatan koneksi data telah dijelaskan pada coretan yang lalu. Bisa menggunakan Microsoft Text ODBC maupun Microsoft Jet 4.0 OLE DB. Pengubahan query dapat dilakukan melalui :
  • tombol Properties pada form Import Data saat mengatur output hasil koneksi ke Excel.
  • Klik kanan tabel hasil koneksi -> Table -> Edit Query
Perlakuan khusus dilakukan melalui query. Berikut perlakuan khusus yang diperlukan untuk data seperti di atas.
  1. Menyusun query untuk mengambil seluruh kolom dari tabel selain kolom yang membutuhkan perlakuan khusus, seperti DUEDATE dan AMOUNT pada contoh ini. Query juga disertai filter agar baris total tidak ikut dalam output. Dengan asumsi bahwa kolom INVDATE tidak pernah NULL (blank), maka query-nya adalah :
      SELECT RECNO,INVNO,CUSTID,INVDATE
      FROM bln06#txt
      WHERE NOT(INVDATE IS NULL)

  2. Menambahkan kolom tanda pemisah desimal, yang diberi nama desi
    Kalimat query nomor 1 akan menjadi :
      SELECT RECNO,INVNO,CUSTID,INVDATE
      ,IIf(1*'1,000'=1,',','.') AS desi
      FROM bln06#txt
      WHERE NOT(INVDATE IS NULL)

  3. Menambahkan kolom yang berisi bilangan AMOUNT yang di kiri tanda desimal kemudian diberi nama krAmo. Juga menambahkan kolom yang berisi bilangan AMOUNT yang di kanan tanda desimal kemudian diberi nama knAmo. Untuk AMOUNT yang tidak memiliki bilangan di kanan desimal, diberi angka 0 sebagai bilangan di kanan desimal. Asumsi jumlah digit maksimal di kanan desimal adalah 9 digit. Query nomor 2 menjadi :
      SELECT RECNO,INVNO,CUSTID,INVDATE
      ,IIf(1*'1,000'=1,',','.') AS desi

      ,Left(AMOUNT,Instr(AMOUNT+'.','.')-1) AS krAmo
      ,Mid(AMOUNT,Instr(AMOUNT+'.','.')+1,9)+'0' AS knAmo

      FROM bln06#txt
      WHERE NOT(INVDATE IS NULL)

  4. Menambahkan leading zero pada kolom krAmo agar seluruh record memiliki posisi karakter pemisah ribuan yang sama, yaitu pada karakter ke-4. Query diubah pada bagian krAmo, sehingga menjadi :
      SELECT RECNO,INVNO,CUSTID,INVDATE
      ,IIf(1*'1,000'=1,',','.') AS desi

      ,String(4 - Instr( Left(AMOUNT,Instr(AMOUNT+'.','.')-1) +',' , ',' ) ,'0') +
      Left(AMOUNT,Instr(AMOUNT+'.','.')-1) AS krAmo
      ,Mid(AMOUNT,Instr(AMOUNT+'.','.')+1,9)+'0' AS knAmo
      FROM bln06#txt
      WHERE NOT(INVDATE IS NULL)

  5. Menambahkan kolom DUEDATE yang berformat DDMMYYYY menjadi berformat YYYY-MM-DD. Hasil dari pengubahan format ini, kemudian dikonversi menjadi bertipe datetime dengan fungsi CDate. Nama kolom diubah menjadi DUE_DATE karena pada proses yang melalui MS Query, dituntut penamaan kolom yang berbeda dengan nama kolom yang diproses. Query yang ditambahkan kolom DUE_DATE bertipe datetime adalah :
      SELECT RECNO,INVNO,CUSTID,INVDATE
      ,IIf(1*'1,000'=1,',','.') AS desi
      ,String(4 - Instr( Left(AMOUNT,Instr(AMOUNT+'.','.')-1) +',' , ',' ) , '0' ) +
      Left(AMOUNT,Instr(AMOUNT+'.','.')-1) AS krAmo
      ,Mid(AMOUNT,Instr(AMOUNT+'.','.')+1,9)+'0' AS knAmo

      ,CDate(Right(DUEDATE,4)+'-'+Mid(DUEDATE,3,2)+'-'+Left(DUEDATE,2)) AS DUE_DATE
      FROM bln06#txt
      WHERE NOT(INVDATE IS NULL)

  6. Menyusun query output yang berasal dari hasil nomor 5. Query output ini berisi seluruh kolom selain kolom bilangan yang dipecah menjadi kiri dan kanan desimal. Dalam kasus ini, berarti tanpa kolom AMOUNT. Nama kolom DUE_DATE dikemballikan lagi namanya menjadi DUEDATE. Query tersebut adalah :
      SELECT RECNO,INVNO,CUSTID,DUE_DATE AS DUEDATE,INVDATE
      FROM (
        SELECT RECNO,INVNO,CUSTID,INVDATE
        ,IIf(1*'1,000'=1,',','.') AS desi
        ,String(4 - Instr( Left(AMOUNT,Instr(AMOUNT+'.','.')-1) +',' , ',' ) , '0' ) +
        Left(AMOUNT,Instr(AMOUNT+'.','.')-1) AS krAmo
        ,Mid(AMOUNT,Instr(AMOUNT+'.','.')+1,9)+'0' AS knAmo
        ,CDate(Right(DUEDATE,4)+'-'+Mid(DUEDATE,3,2)+'-'+Left(DUEDATE,2)) AS DUE_DATE
        FROM bln06#txt
        WHERE NOT(INVDATE IS NULL)
      ) AS dt1

  7. Menyusun kolom AMOUNT berformat 'General' dari data kolom krAmo, desi, dan knAmo. Asumsi yang digunakan adalah jumlah digit maksimal bilangan di kiri desimal adalah 15 digit (tanpa pemisah ribuan). Berarti, kolom krAmo akan diambil setiap 3 digit sebanyak 5 kali untuk membentuk 15 digit bilangan dikiri tanda desimal tanpa pemisah ribuan. Posisi karakter yang menjadi titik start adalah 1,5,9,13,17. Kemudian digabungkan dengan karakter pemisah desimal (kolom desi) dan bilangan dikanan desimal (kolom knAmo). Hasil penggabungan yang masih bertipe teks dikonversi menjadi bertipe numerik. Query output nomor 6 akan menjadi :
      SELECT RECNO,INVNO,CUSTID,DUE_DATE AS DUEDATE,INVDATE
        ,(
          Left(krAmo,3)
          +Mid(krAmo,5,3)
          +Mid(krAmo,9,3)
          +Mid(krAmo,13,3)
          +Mid(krAmo,17,3)
          +desi
          +knAmo
        ) * 1
        AS AMOUNT
      FROM (
        SELECT RECNO,INVNO,CUSTID,INVDATE
        ,IIf(1*'1,000'=1,',','.') AS desi
        ,String(4 - Instr( Left(AMOUNT,Instr(AMOUNT+'.','.')-1) +',' , ',' ) , '0' ) +
        Left(AMOUNT,Instr(AMOUNT+'.','.')-1) AS krAmo
        ,Mid(AMOUNT,Instr(AMOUNT+'.','.')+1,9)+'0' AS knAmo
        ,CDate(Right(DUEDATE,4)+'-'+Mid(DUEDATE,3,2)+'-'+Left(DUEDATE,2)) AS DUE_DATE
        FROM bln06#txt
        WHERE NOT(INVDATE IS NULL)
      ) AS dt1
Query hasil nomor 7 yang digunakan sebagai isi dari Command Text dalam tab Definition. Command Type yang digunakan adalah SQL.


Closing :

Seluruh kolom dengan data bilangan yang menyertakan karakter pemisah ribuan diolah seperti kolom AMOUNT. Proses yang begitu panjang terhadap kolom seperti ini, disebabkan karena data provider yang digunakan tidak memiliki fungsi Replace.

Waspadai penggunaan karakter titik dua (") sebagai isi sebuah field pada text file yang tidak memiliki text delimiter. Lihat coretan tentang persiapan untuk lebih jelasnya.


File(s) :

Seluruh coretan dengan seri Koneksi data ke TextFile menggunakan file :

Unzip flatfile_datasource.zip ke root directory drive E:


Coretan terkait :



1 komentar: