Importing messy excel file (one coloumn has multiple rows) , how?

Reply
New Contributor
Posts: 4

Importing messy excel file (one coloumn has multiple rows) , how?

hello friends , im always stuck with my work when importing file, but in one field(coloumn) , some data consist of two rows.. and when i import things happen as i not expected.. somebody help me... pls

lets begin with some code :

%MACRO IMPORT_PORT_2012(dirname=,filetype=,filename=,wsname=,tblname=) ;

/*       %OPEN_WB(wb_name=%STR(&dir_raw_data.\&dirname.\&filename..xls)) ;*/

/*       %WAITING_PROCESS ;*/

       FILENAME fname DDE "EXCEL|&wsname.!R1C1:R65536C256";

       DATA &tblname   ;

            FORMAT    periode                       date9.       no_rekening                   $7.         nomor_nasabah                 $7.

                      nama_lengkap                  $50.         tempat_lahir                  $30.        tanggal_lahir                 date9.

                      alamat                        $100.        kelurahan                     $30.        kecamatan                     $30.

                      kota                          $30.         kode_pos                      $5.         pekerjaan                     $30.

                      jabatan                       $30.         bidang_usaha                  $50.        nama_tempat_usaha             $80.

                      pendapatan                    comma20.     tempat_lahir_pasangan         $30.        tanggal_lahir_pasangan        date9.

                      pekerjaan_pasangan            $30.         nama_dealer                   $50.        jenis_produk                  $30.

                      kode_cabang                   $5.          nama_cabang                   $15.        tanggal_realisasi             date9.

                      harga_kendaraan               comma20.     uang_muka                     comma20.    pokok_hutang                  comma20.

                      effektif                      comma7.2     angsuran                      comma20.    jenis_angsuran                $3.

                      nama_surveyor                 $30.         wilayah_kolektor              $30.        status_TBO                    $50.

                      wilayah_calon_nasabah_dati_2  $30.         nilai_pertanggungan           comma20.    nilai_premi                   comma20.

                      perusahaan_asuransi           $50.         jenis_asuransi                $30.        cara_bayar                    $3.

                      urutan_pemilik                3.           tanggal_stnk                  date9.      tanggal_pajak_stnk            date9.

                      jenis_kendaraan               $20.         tipe_kendaraan                $30.        merk_kendaraan                $20.

                      jangka_waktu                  3.           tahun_buat                    4.          tahun_rakit                   4.

                      cilinder                      $50.         saldo_pokok                   comma20.    saldo_bunga                   comma20.

                      saldo_total                   comma20.     denda                         comma20.    hari_tunggakan                10.

                      angsuran_tertunggak           comma20.

                      ;

            INFORMAT  periode                       date9.       no_rekening                   $7.         nomor_nasabah                 $7.

                      nama_lengkap                  $50.         tempat_lahir                  $30.        tanggal_lahir                 date9.

                      alamat                        $100.        kelurahan                     $30.        kecamatan                     $30.

                      kota                          $30.         kode_pos                      $5.         pekerjaan                     $30.

                      jabatan                       $30.         bidang_usaha                  $50.        nama_tempat_usaha             $80.

                      pendapatan                    comma20.     tempat_lahir_pasangan         $30.        tanggal_lahir_pasangan        date9.

                      pekerjaan_pasangan            $30.         nama_dealer                   $50.        jenis_produk                  $30.

                      kode_cabang                   $5.          nama_cabang                   $15.        tanggal_realisasi             date9.

                      harga_kendaraan               comma20.     uang_muka                     comma20.    pokok_hutang                  comma20.

                      effektif                      comma7.2     angsuran                      comma20.    jenis_angsuran                $3.

                      nama_surveyor                 $30.         wilayah_kolektor              $30.        status_TBO                    $50.

                      wilayah_calon_nasabah_dati_2  $30.         nilai_pertanggungan           comma20.    nilai_premi                   comma20.

                      perusahaan_asuransi           $50.         jenis_asuransi                $30.        cara_bayar                    $3.

                      urutan_pemilik                3.           tanggal_stnk                  date9.      tanggal_pajak_stnk            date9.

                      jenis_kendaraan               $20.         tipe_kendaraan                $30.        merk_kendaraan                $20.

                      jangka_waktu                  3.           tahun_buat                    4.          tahun_rakit                   4.

                      cilinder                      $50.         saldo_pokok                   comma20.    saldo_bunga                   comma20.

                      saldo_total                   comma20.     denda                         comma20.    hari_tunggakan                10.

                      angsuran_tertunggak           comma20.

                      ;

            INFILE fname

            TRUNOVER

            FIRSTOBS  = 2

            LS        = 1500

            DSD

            DLM = '09'x

            NOTAB

            ;

New Contributor
Posts: 4

Re: Importing messy excel file (one coloumn has multiple rows) , how?

200320033907
200020003907
200820081998
TYPE HILUX 2.0L M/T_PERUBAHAN INPUTAN DARI NOPOL D 8989 QZ KE KB 8206 PZ_NAMA DARI ARIFIN BUDIMAN KE EWIN_NO STNK DARI_0798090/JB/2008 KE 0233769/KB/2011

example of data

Super User
Posts: 3,115

Re: Importing messy excel file (one coloumn has multiple rows) , how?

I see you are using the TAB character ('09'X) as the delimiter between columns. Your output data suggests that you may have tabs also in your columns with will cause a misalignment - data items will end up in the wrong columns. Suggest you view your input data in hex format to spot where the hidden tabs might be.

New Contributor
Posts: 4

Re: Importing messy excel file (one coloumn has multiple rows) , how?

thanks saskiwi,

how can i view my input data(excel) in hex format ? so i can spot the hidden tabs, and replace it with nothing..

thanks

Super User
Posts: 3,115

Re: Importing messy excel file (one coloumn has multiple rows) , how?

The way I am familiar with, you will first have to save your Excel spreadsheet as a CSV file. Then you browse it in SAS using the FSLIST procedure. This will only work if you have a full SAS install on your desktop, and not just EG. FSLIST has a hex mode that allows you to see each record in both 'normal' and hex mode. Tabs will show up as an additional '09' within a field.

Ask a Question
Discussion stats
  • 4 replies
  • 310 views
  • 0 likes
  • 2 in conversation