Microsoft Excel
Query
Tips
Membuat statements INSERT dari Microsoft Excel - Tips
November 01, 2017
Permasalahan:
Gue dapet project di perusahaan dengan jumlah karyawannya 10.000 – 50.000, dan harus memasukkan data karyawan tersebut secara keseluruhan menggunakan script query tanpa upload melalui aplikasi. Bisa dibayangkan gue harus ngetik manual kan?
Contohnya:
INSERT INTO NAMA_TABLE (Nama_Colomn_1, Nama_Colomn_1, .......) VALUES (‘Data_1’, ’Data_2’);
Gak mungkin banget gue insert seperti itu satu persatu dengan data yang berbeda – beda, kalo datanya sama sih bukan masalah, gue tinggal buat satu baris query terus copy paste aja, nah kalo gini kapan kelarnya coba?
Solusi:
Biasanya data yang sudah dipersiapkah user itu menggunakan Mcrosoft Excel. Kalo pake yang lain copy aja datanya ke Microsoft Excel biar gampang lah ya.
Berikut ini contoh datanya:
- Tanda panah merah menunjukan nama kolom (column name)
- Tanda garis merah vertikal menunjukan data (values)
- Nama sheet pada Excel diubah menjadi nama table (table name)
- Pada ribbon "View" pilih "Macro"
- Klik "Macro"
- Masukkan nama macro yang akan dibuat
- Terakhir pilih "Create", maka akan muncul tampilan seperti dibawah ini
Setelah muncul tampilan seperti diatas, masukkan code berikut ini:
========================================================================
Sub CreateInsertScript()
Dim Row As Integer
Dim Col As Integer
'Untuk menyimpan kolom yang tersedia pada sheet yang aktif
Dim ColNames(100) As String
Col = 1
Row = 1
Dim ColCount As Integer
ColCount = 0
'Mengambil kolom pada sheet
Do Until ActiveSheet.Cells(Row, Col) = "" 'Melakukan looping ketika menemukan blank
ColNames(ColCount) = "" + ActiveSheet.Cells(Row, Col) + ""
ColCount = ColCount + 1
Col = Col + 1
Loop
ColCount = ColCount - 1
'Masukkan untuk baris pertama dan terakhir
Row = InputBox("Masukkan urutan angka baris pertama No.")
Dim MaxRow As Integer
MaxRow = InputBox("Masukkan urutan angka baris terakhir No.")
'Lokasi penyimpanan untuk statements insert
File = "F:\Script\InsertScript.txt"
fHandle = FreeFile()
Open File For Output As fHandle
Dim CellColCount As Integer
Dim StringStore As String 'Temporary variable untuk menyimpan partial statement
Do While Row <= MaxRow
StringStore = ""
CellColCount = 0
'ActiveSheet.Name untuk membaca Sheet yang sedang aktif
'Bisa dipake untuk nama Table di Database
StringStore = StringStore + "INSERT INTO " + ActiveSheet.Name + " ("
Do While CellColCount <= ColCount
StringStore = StringStore + ColNames(CellColCount)
'Membuat pemisah "," setelah kolom terakhir
If CellColCount <> ColCount Then
StringStore = StringStore + ","
End If
CellColCount = CellColCount + 1
Loop
'Untuk membuat query "INSERT INTO TableName (Col1,Col2,..."
Print #fHandle, StringStore + ")"
'Untuk membuat nilai kolom
StringStore = " VALUES("
CellColCount = 0
Do While CellColCount <= ColCount
StringStore = StringStore + "'" + CStr(ActiveSheet.Cells(Row, CellColCount + 1)) + "'"
If CellColCount <> ColCount Then
StringStore = StringStore + ","
End If
CellColCount = CellColCount + 1
Loop
'Disini untuk membuat "VALUES('value1','value2',..."
Print #fHandle, StringStore + ");"
Print #fHandle, " "
Row = Row + 1
Loop
Close #fHandle
MsgBox ("Script berhasil dibuat!")
End Sub
========================================================================
Code by: Arun Mallick
Modified by: Muhamad Fahrurrozi
========================================================================
Modified by: Muhamad Fahrurrozi
========================================================================
Setelah menambahkan code diatas, kemudian save.
Selanjutnya, coba jalankan seperti dibawah ini:
- Klik "Macro"
- Pilih macro yang sudah dibuat
- Klik "Run"
Maka akan muncul tampilan seperti berikut ini:
- Pilih data baris terakhir, kemudian klik "Ok"
- Dan script berhasil dibuat dalam bentuk query
Dan ini hasil generate querynya
Semoga membantu :)
0 comments