|
Terima kasih atas masukannya Pak Wadi, Pak Hermawan dan Pak Bhakti.
Salam,
Afri >>> Wadi Achmed < [email protected]> 10/11/2010 3:18 PM >>>
Sorry kelewatan.
konstanta xlUp = -4162
cheers,
Wadi Achmed
2010/10/11 Wadi Achmed <[email protected]>
LastRow=Sheet.Range("A65536").End(xlUp).Row.
Kalo mau import yang lebih cepet: Dari excel saveas ke tab separated, lalu import ke datawindow dan update + commit.
Selesai deh.
Syntax nya gimana? monggo digoogling.
Cheers,
Wadi ACh
2010/10/11 Afrianto Dulbahri ST. <[email protected]>
Om Wadi,
Setuju sih... cuma, saya tahunya cara ini aja. Mungkin Om Wadi bisa memberikan pencerahan yang lebih sip... :)
Pengalaman saya, import data dengan cara yang saya gunakan di Intel Dual Core 1.7 GHz / 2 GB RAM dari file Excel yang ada di harddisk (10 kolom, ± 4000 baris), perlu waktu sekitar 50 detik.
Salam,
Afri >>> Wadi Achmed < [email protected]> 10/11/2010 2:45 PM >>>
Haaaah gak ada cara yang lebih efisien kah?
Kasihan dong processor dan memory nya.
Kenapa gak memanfaatkan Range & matematika sederhana?
Cheers,
Wadi Achmed
On Mon, Oct 11, 2010 at 1:40 PM, Afrianto Dulbahri ST. <[email protected]> wrote:
Dear Bilie,
Mengacu ke jawaban saya sebelumnya, dilakukan iterasi terlebih dahulu.... Contoh script-nya adalah:
/* SEARH FOR NUMBER OF ROWS IN EXCEL */
i = 2
anemployee_id = lole_excel.application.Sheets("Sheet1").Cells(1,1).value
DO WHILE ISNULL(anemployee_id) = FALSE
i = i + 1
anemployee_id = lole_excel.application.Sheets("Sheet1").Cells(i,1).value
// MessageBox("Baris ke : " + string(i), "Nilai Tanggal " + STRING(antanggal))
LOOP
inbaris = i - 1
-----
Asumsinya, Row pertama adalah header, data yang diimport ada di "Sheet1", dan data pada kolom pertama jika data akan menandakan bahwa baris tersebut akan diimport.
Variable inbaris pada script di atas akan menghasilkan jumlah baris yang akan diimport.
Salam,
Afri >>> "bilie" < [email protected]> 10/11/2010 2:31 PM >>>
Thanx pak,.. Nanya lagi nh,.. Lanjut dri kasus yg sama,.. Kalo udh connect ke excel dan kita mau mengetahui brp jumlah bris yg ada di file excel tersbt, gmn yah fungsiny? Thanx pak
bilie christiansen, s.kom
-----Original Message-----
From: "Afrianto Dulbahri ST." <[email protected]>
Sender: [email protected]
Date: Fri, 8 Oct 2010 13:34:27
To: <[email protected]>
Reply-To: [email protected]
Subject: Re: [indopb] Mau Tanya dong
Dear Bilie,
Berikut ini adalah contohnya.
Salam,
Afri
>>>>
ANY anemployee_id, ansurname, anchperiod, anchpromoted_flag, andcpromotion_percentage, andcgeneral_increase
ANY andcinternal_factor, andccompa_ratio, andccompetitiveness_factor
STRING employee_id, surname, chperiod, chpromoted_flag
DECIMAL dcpromotion_percentage, dcgeneral_increase
DECIMAL dcinternal_factor, dccompa_ratio, dccompetitiveness_factor
INTEGER ll_result
LONG i, j, inbaris
STRING chfile_location, chremark, chdummy
DATE dtshiftdate
oleobject lole_excel
lole_excel = create oleobject
string docname, named
integer value
value = GetFileOpenName("Select File", docname, named, "XLS", "Excel 97-2003 Files (*.XLS), *.XLS, Excel 2007 Files (*.XLSX), *.XLSX")
//MessageBox("File Yang dipilih", docname)
IF value = 1 THEN
dw_gi_increment.Reset()
ll_result = lole_excel.ConnectToNewObject("Excel.Application")
lole_excel.application.workbooks.open(docname)
lole_excel.application.Sheets("Sheet1").Select
// Baca File Excel-nya
/* SEARH FOR NUMBER OF ROWS IN EXCEL */
i = 2
anemployee_id = lole_excel.application.Sheets("Sheet1").Cells(1,1).value
DO WHILE ISNULL(anemployee_id) = FALSE
i = i + 1
anemployee_id = lole_excel.application.Sheets("Sheet1").Cells(i,1).value
// MessageBox("Baris ke : " + string(i), "Nilai Tanggal " + STRING(antanggal))
LOOP
inbaris = i - 1
// MessageBox("Jumlah Baris", STRING(inbaris, "#,##0"))
FOR i = 2 to inbaris
st_2.Text = STRING(i - 1, "#,##0")
anemployee_id = lole_excel.application.Sheets("Sheet1").Cells(i,1).value
ansurname = lole_excel.application.Sheets("Sheet1").Cells(i,2).value
anchperiod = lole_excel.application.Sheets("Sheet1").Cells(i,3).value
anchpromoted_flag = lole_excel.application.Sheets("Sheet1").Cells(i,4).value
andcpromotion_percentage = lole_excel.application.Sheets("Sheet1").Cells(i,5).value
andcgeneral_increase = lole_excel.application.Sheets("Sheet1").Cells(i,6).value
andcinternal_factor = lole_excel.application.Sheets("Sheet1").Cells(i,7).value
andccompa_ratio = lole_excel.application.Sheets("Sheet1").Cells(i,8).value
andccompetitiveness_factor = lole_excel.application.Sheets("Sheet1").Cells(i,9).value
employee_id = STRING(anemployee_id)
surname = STRING(ansurname)
chperiod = STRING(anchperiod)
chpromoted_flag = STRING(anchpromoted_flag)
dcpromotion_percentage = DEC(andcpromotion_percentage)
dcgeneral_increase = DEC(andcgeneral_increase)
dcinternal_factor = DEC(andcinternal_factor)
dccompa_ratio = DEC(andccompa_ratio)
dccompetitiveness_factor = DEC(andccompetitiveness_factor)
dw_gi_increment.InsertRow(0)
dw_gi_increment.SetItem(dw_gi_increment.RowCount(), "employee_id", employee_id)
dw_gi_increment.SetItem(dw_gi_increment.RowCount(), "surname", surname)
dw_gi_increment.SetItem(dw_gi_increment.RowCount(), "chperiod", chperiod)
dw_gi_increment.SetItem(dw_gi_increment.RowCount(), "chpromoted_flag", chpromoted_flag)
dw_gi_increment.SetItem(dw_gi_increment.RowCount(), "dcpromotion_percentage", dcpromotion_percentage)
dw_gi_increment.SetItem(dw_gi_increment.RowCount(), "dcgeneral_increase", dcgeneral_increase)
dw_gi_increment.SetItem(dw_gi_increment.RowCount(), "dcinternal_factor", dcinternal_factor)
dw_gi_increment.SetItem(dw_gi_increment.RowCount(), "dccompa_ratio", dccompa_ratio)
dw_gi_increment.SetItem(dw_gi_increment.RowCount(), "dccompetitiveness_factor", dccompetitiveness_factor)
st_4.Text = STRING(dw_gi_increment.RowCount(), "#,##0")
NEXT
delete ellipse.tblsim_grade_promotion
using sqlca;
commit using sqlca;
dw_gi_increment.Update()
COMMIT using sqlca;
// END OF READ Excel
lole_excel.application.workbooks.close()
lole_excel.application.visible = true
if isvalid(lole_excel) then destroy lole_excel
ELSE
MessageBox("File Open Error", "Can not Open the file")
END IF
>>> "bilie" <[email protected]> 10/8/2010 12:22 PM >>>
Para master, aku mau tanya nih,.. Gmana carany import record dri file excel to datawindow? Mohon penjelasanny agak detail, maklum bru bljr nih,hehehe thank you
bilie christiansen, s.kom
|
