Kode vba untuk memasukkan data dari excel ke sql server

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

Tip. 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()End

Tip. 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")

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

Bagaimana cara memasukkan data dari Excel ke SQL Server menggunakan VBA?

Bagaimana cara mengimpor data dari Excel ke SQL Server?

Bisakah VBA digunakan di SQL Server?

Bagaimana cara memasukkan data dari Excel ke database Access menggunakan VBA?