Menyajikan data awal pada suatu tabel MySQL tentu akan merepotkan jika data yang akan diinput banyak dan diinputkan secara manual. Namun akan memudahkan jika data sudah tersedia dalam bentuk excel dan dengan bantuan MS Visual Foxpro tentunya. Berikut akan ditampilkan bagaimana cara sederhana agar data dalam bentuk Excel dapat diimpor ke dalam format tabel MySQL. Kelemahan cara ini kalau gak salah hanya bisa maksimal 16382 record, saya sendiri belum mencoba. Tapi tidak ada salahnya jika kita sedikit mencoba dengan cara ini :
lcNamaFile = GETFILE([XLS],[File Excel],[Buka],0,[Impor File Excel])
lcTempFile = Getenv("TEMP")+'\'+Sys(2015)+'.xls'
IF EMPTY(lcNamaFile)
RETURN
ELSE
#DEFINE Pesan1 "Sedang Proses "
WAIT WINDOW Pesan1 NOWAIT TIMEOUT 0
loExcel = Createobject("excel.application")
loExcel.Workbooks.Open(m.lcNamaFile)
loExcel.ActiveWorkbook.SaveAs(m.lcTempFile,6)
loExcel.ActiveWindow.Close(.T.)
create CURSOR tb_imp(id_brg n(9),nm_brg c(100),hrg_brg n(12,2),sat_brg c(30),stok n(9),;
id_kat_brg n(3),merk_brg c(30),kuo n(9),ed d(8),id_lok n(3))
Select tb_imp
DELETE ALL
Append From (m.lcTempFile) Delimited
loExcel.Quit
SELECT tb_imp
DELETE FROM tb_imp WHERE id_brg=0
#DEFINE Pesan2 "Proses Selesai"
WAIT WINDOW Pesan2 TIMEOUT 1
WAIT clear
*USE
SET SAFETY on
MESSAGEBOX("Data telah diimpor!"+CHR(13)+"Klik PROSES untuk melanjutkan",0+64,"Informasi")
SELECT tb_imp
GO TOP
SUM (hrg_brg*stok) TO hrg_total
thisform.pageframe1.page15.txthrg_tot.Value=hrg_total
thisform.pageframe1.page15.grid1.RecordSource="tb_imp"
thisform.aturtabel15a
ENDIF
2. Memasukkan cursor ke dalam tabel transaksi (MySQL) tombol Proses :
Select * from Cinp WHERE MONTH(tgl_inp)=1 AND DAY(tgl_inp)=1 AND YEAR(tgl_inp)=YEAR(DATE()) into cursor cstok
*? _TALLY
IF _TALLY=0
IF MESSAGEBOX('Data ini akan dijadikan stok awal tahun ini?',4+32,'Perhatian')=7
RETURN
ELSE
************************************************************************************************************************
*Input Tabel Cbrg
SELECT cbrg
GO BOTTOM
thisform.pageframe1.page15.text1.Value=id_brg
SELECT tb_imp
GO TOP
DO WHILE NOT EOF()
replace tb_imp.id_brg WITH thisform.pageframe1.page15.text1.Value+RECNO()
IF EMPTY(tb_imp.ed)
replace tb_imp.ed WITH {^2030-01-01}
ENDIF
IF EMPTY(tb_imp.id_lok)
replace tb_imp.id_lok WITH 1
ENDIF
SELECT tb_imp
SKIP
ENDDO
SELECT tb_imp
GO TOP
DO WHILE NOT EOF()
SCATTER MEMVAR
thisform.sqlawal
msql= 'INSERT INTO brg (id_brg,nm_brg,hrg_brg,sat_brg,stok,merk_brg,id_jns_brg,kuo,jml_bl,id_kat_brg,ed,id_lok)'+;
'VALUES (?m.id_brg,?m.nm_brg,?m.hrg_brg,?m.sat_brg,?m.stok,?m.merk_brg,"1",?m.kuo,"0",?m.id_kat_brg,?m.ed,?m.id_lok);'
IF SQLEXEC( Koneksi, msql ) <= 0
=MESSAGEBOX("Gagal Insert/ Update Data ke Tabel Barang", 0+16, "Informasi")
RETURN
ENDIF
thisform.sqlakhir
SELECT tb_imp
SKIP
ENDDO
*Input Tabel Inp
SELECT cinp
GO BOTTOM
thisform.pageframe1.page15.txtid_inp.Value=id_inp+1
tgl_tran = TRAN(YEAR(DATE()), "@L 9999")+"-"+TRAN("1", "@L 99")+"-"+TRAN("1", "@L 99")
thisform.sqlawal
msql = 'INSERT INTO inp(id_inp,tgl_inp,tot_hrg,id_user,no_inp,id_sup,no_sp,keg,pek,tgl_sp) VALUES ('+;
'"' + TRANSFORM(thisform.pageframe1.page15.txtid_inp.value,"999999999") +'",'+;
'"' + TRAN(YEAR(DATE()), "@L 9999")+"-"+TRAN("1", "@L 99")+"-"+TRAN("1", "@L 99")+'",'+;
'"' + TRANSFORM(thisform.pageframe1.page15.txthrg_tot.value,"999999999999.99") +'",'+;
'"' + "1" +'",'+;
'"' + "0" +'",'+;
'"' + "1" +'",'+;
'"' + "0" +'",'+;
'"' + "0" +'",'+;
'"' + "0" +'",'+;
'"' +TRAN(YEAR(DATE()), "@L 9999")+"-"+TRAN("1", "@L 99")+"-"+TRAN("1", "@L 99")+'");'
IF SQLEXEC( Koneksi, msql ) <= 0
=MESSAGEBOX("Gagal Insert/ Update Data ke Tabel Input", 0+16, "Informasi")
RETURN
ENDIF
thisform.sqlakhir
*Input Tabel Det_Inp
SELECT * FROM cdet_inp WHERE id_inp =thisform.pageframe1.page15.txtid_inp.Value-1 INTO CURSOR cdet_inp15
SELECT cdet_inp15
GO BOTTOM
thisform.pageframe1.page15.txtid_det_inp.Value=id_det_inp
SELECT id_brg,stok,kuo,hrg_brg FROM tb_imp WHERE stok>0 INTO CURSOR cbrgstok2 READWRITE
SELECT cbrgstok2
GO TOP
DO WHILE NOT EOF()
replace cbrgstok2.kuo WITH thisform.pageframe1.page15.txtid_det_inp.Value+RECNO()
replace cbrgstok2.hrg_brg WITH thisform.pageframe1.page15.txtid_inp.Value
SELECT cbrgstok2
SKIP
ENDDO
SELECT cbrgstok2
GO TOP
DO WHILE NOT EOF()
SCATTER MEMVAR
thisform.sqlawal
msql= 'INSERT INTO det_inp (id_det_inp,id_inp,id_brg,jml_inp)'+;
'VALUES (?m.kuo,?m.hrg_brg,?m.id_brg,?m.stok);'
IF SQLEXEC( Koneksi, msql ) <= 0
=MESSAGEBOX("Gagal Insert/ Update Data ke Tabel Detail Input", 0+16, "Informasi")
RETURN
ENDIF
thisform.sqlakhir
SELECT cbrgstok2
SKIP
ENDDO
thisform.up_brg
thisform.up_det_inp
thisform.up_inp
thisform.progress
thisform.pageframe1.page15.grid1.RecordSource=""
ENDIF
ELSE
MESSAGEBOX('Data Sudah ada,Proses tidak dapat dilanjutkan?',16,'Perhatian')
RETURN
ENDIF
Cara ini sudah saya coba pada submenu Stok Awal. Mohon maaf codingnya berantakan soalnya saya copy paste langsung dari aplikasinya.
0 Response to "Source Code : Impor Data dari Excel ke MySQL"
Posting Komentar