Namun ada beberapa kasus di mana saya dapat menggunakan pendekatan yang lebih otomatis; . Karena itu, saya terkadang membuat makro Excel yang akan mengimpor data dari spreadsheet ke database
PengaturanTip. Ini mengasumsikan data Anda tidak dalam format tab pivot/silang. Jika ya, lihat tutorial saya tentang cara mengonversi format sedemikian rupa sehingga datanya ramah impor basis data
Mulailah dengan membuat Excel Macro. Dari tab "Pengembang" di pita (mungkin tidak ada secara default; Anda dapat menambahkannya dari menu Opsi), klik tombol untuk "Lihat Kode"
Jika Anda menggunakan mesin Windows, pintasan keyboardnya adalah Alt + F11
Dari menu atas. Sisipkan >> Modul
Di panel baru (tempat Anda dapat mengetikkan kode), buat fungsi publik
Public Function ImportData()EndTip. jika Anda mengetik "impordata fungsi publik" dan menekan 'Enter' maka sisanya akan terisi sebelumnya; . e. itu akan menambahkan tanda kurung tutup dan kata kunci 'End'
Pastikan untuk menyimpan file Anda dengan. xlsm, karena ini akan menjadi buku kerja yang mendukung makro (bukan hanya buku kerja "biasa")
Ikhtisar Konsep
Kami akan menjalankan pernyataan INSERT, di mana SQL mengikuti akan mengikuti format ini
INSERT INTO myTableName(field1, field2)
VALUES
(row1Value1, row1Value2),
(row2Value1, row2Value2)_
Cukup jelas. Kami akan memecah ini menjadi dua bagian
- SQL Header (MASUKKAN KE NamaTabelku)
- "Nilai" yang dihasilkan secara dinamis yang merupakan gabungan dari baris data individual dalam spreadsheet
Pertama kita perlu membuat string koneksi. Saya akan menyerahkan nilai persisnya kepada Anda. Anda tentu saja harus memastikan bahwa pengguna memiliki izin keamanan database yang sesuai. Contoh di bawah menggunakan Active Directory — jadi ini mendeteksi login Windows saya dan tidak menggunakan akun pengguna SQL lokal
Saya terhubung melalui ODBC. Jika Anda tidak memiliki ini di mesin Anda, unduh drivernya
Mengingat kasus penggunaan saya, string koneksi saya terlihat seperti ini
Const strDbConn As String = "server=myServerName;Database=myDatabaseName;Trusted_Connection=Yes;Driver={ODBC Driver 17 for SQL Server}"Saya telah membuat ini sebagai variabel global, ditempatkan di atas deklarasi fungsi yang baru saja kita buat
Sebagai variabel global, kita dapat menggunakannya di dalam fungsi lain
Saya memiliki fungsi sederhana untuk menjalankan SQL
Kesalahan ADODB?
Kode dalam posting ini berfungsi untuk saya tanpa perlu menambahkan referensi eksternal. Tetapi jika Anda mendapatkan kesalahan…
Di masa lalu - mungkin itu adalah versi Excel sebelumnya - saya perlu menambahkan referensi untuk menggunakan kode ADODB
Anda dapat melakukan ini dengan membuka Alat >> Referensi
Menurut artikel ini, Anda perlu mencentang opsi untuk Microsoft ActiveX Data Objects [x. x] Perpustakaan
Memasukkan data dengan SQL. tajuk
Kita perlu membuat bagian awal dari pernyataan SQL, yang akan menjadi statis (mis. e. tidak berubah)
Buat fungsi yang mengembalikan hasil hard-coded untuk bagian pertama pernyataan INSERT untuk nama tabel dan bidang yang diberikan
Tentu saja Anda harus memperbaruinya untuk mencerminkan nama tabel dan bidang Anda
Memasukkan data dengan SQL. data yang sebenarnya
Kami membutuhkan cara untuk secara dinamis menghasilkan nilai-nilai yang akan dimasukkan ke dalam database. Sebelum kita membuat SQL, kita membutuhkan cara untuk mengulang baris di Excel, dari atas ke bawah. Langkah pertama adalah menentukan berapa banyak baris yang kita miliki
Saya menggunakan fungsi di bawah ini untuk mendapatkan jumlah baris di lembar kerja
Saya mendeklarasikannya sebagai tipe Long karena Integer tidak cukup besar untuk kumpulan data saya. Bilangan bulat dibatasi hingga ~32.000 baris
Saat kami mengulang setiap baris, kami akan membuat string yang mengambil nilai dari masing-masing kolom dan menambahkannya ke pernyataan SQL. Kode untuk itu ada di sini
Anda tentu saja harus menyelaraskan huruf kolom Anda dengan nama bidang yang tepat dan memperbarui tipe datanya. Misalnya, nilai numerik tidak boleh diapit tanda kutip
Saya memiliki dua fungsi "pembantu";
Jika nilainya berbasis teks, kita perlu mengganti ("escape") satu kutipan, jadi saya memiliki fungsi ini
Saya juga membuat fungsi nz() (seperti pada "null-zero" yang merupakan bawaan untuk beberapa bahasa pemrograman) yang akan mengganti nilai numerik dengan "0" jika diperlukan
Inilah kode untuk mengulang setiap baris dalam spreadsheet, dan menghasilkan pernyataan SQL
Rincian langkah demi langkah adalah
- Panggil fungsi yang meneruskan nama lembar kerja (tab) dengan data untuk menemukan berapa banyak baris dalam kumpulan data
- Buat variabel yang menampung awal pernyataan SQL
- Ulangi setiap baris dalam kumpulan data, buat pernyataan INSERT … VALUES SQL
- Setelah setiap baris data, tambahkan koma
- Hapus koma terakhir dalam pernyataan itu
- Jalankan SQL
Saya mengambil pendekatan untuk menambahkan koma setelah setiap baris, dan kemudian menghapus koma terakhir dalam string. Saya menggunakan fungsi ini
Cara mengatasi batasan impor data
Saya mengalami masalah, terkadang "nilai" yang digabungkan menjadi terlalu panjang, dan membuat Excel crash. Saya pikir saya harus membatasi skrip untuk mengimpor 500 baris sekaligus
Ini kode yang diperbarui
Anda dapat melihat bahwa (pada baris 24), ketika variabel dihasilkan yang menyimpan nilai, setelah mencapai 500 karakter, ia memasukkan data- yang kita miliki sejauh ini- ke dalam database. Kemudian setelah loop, itu memasukkan batch 'final' (baris 39)
Saya meninggalkan beberapa kode di sana, berkomentar, yang memberi tahu pengguna tentang kemajuan sejauh mana proses impor berlangsung
Sunting. di kode terakhir, ada pemeriksaan lain untuk hanya menjalankan kumpulan kode terakhir jika diperlukan. Jika Anda mencoba mengimpor sejumlah baris yang habis dibagi 500, Anda akan mendapatkan pesan kesalahan. Ada di baris 40 di "seluruh kode"
Menyatukannya
Untuk membuatnya lebih berguna, kita mungkin perlu menghapus data yang ada terlebih dahulu
Meskipun Anda mungkin dapat memperoleh "tanggal terakhir diperbarui" dari kumpulan data, kode contoh ini menunjukkan pernyataan SQL tambahan untuk menjalankan kueri UPDATE guna menunjukkan tanggal impor
Saya telah membuat subrutin bernama autoImport yang melakukan ini
- Menghapus data yang ada
- Menjalankan fungsi yang mengimpor data
- Menjalankan kueri pembaruan untuk menunjukkan kapan data diperbarui
Bonus. hapus nilai kosong dengan Kode VBA
Saya menulis artikel terpisah tentang bagaimana Anda berpotensi mempercepat proses impor. menghapus beberapa baris yang tidak menambah nilai (secara harfiah dan kiasan). Ini berpotensi secara signifikan mengurangi jumlah baris, sehingga mengurangi waktu pemrosesan
Dalam kasus penggunaan khusus saya, saya memiliki tabel tempat saya menjalankan kueri agregat dari satu bidang. Jika bidang itu memiliki nilai "nol", maka saya tidak perlu mengimpornya