Excel mendapatkan data dari sql server dengan parameter

Saya mencoba membuat kueri yang menarik data dari database SQL. Jika saya membuat kueri dengan menghubungkan ke database SQL, memilih tabel yang relevan, memfilternya, menggabungkannya untuk mendapatkan apa yang saya butuhkan, itu sangat lambat. Saya menemukan bahwa jika saya membuat sumber kueri dengan memasukkan kueri SQL di awal, maka menyetel ulang hasil yang saya perlukan jauh lebih cepat

Masalah yang saya miliki adalah saya ingin menggunakan beberapa parameter dalam kueri SQL. Nilai parameter akan diperoleh dari sel di lembar kerja Excel. Nilai-nilai ini akan dimasukkan oleh pengguna untuk memberikan apa yang mereka butuhkan

Berikut ini menunjukkan informasi yang saya lihat jika saya melihat baris Sumber dalam kueri saya

= Sql. Database("baxserv", "BaxDB", [Query="SELECT
AA_NOMINAL_TRAN_VIEW. NCODE SEBAGAI 'Akun',
AA_NOMINAL_TRAN_VIEW. DET_NOM_PERIOD SEBAGAI 'Periode',
AA_NOMINAL_TRAN_VIEW. DET_NOM_YEAR SEBAGAI 'Tahun',
SL_PL_NL_DETAIL. DET_DATE SEBAGAI 'Tanggal',
AA_NOMINAL_TRAN_VIEW. DET_TYPE SEBAGAI 'Jenis',
AA_NOMINAL_TRAN_VIEW. NAMA SEBAGAI 'Nama Akun',
AA_NOMINAL_TRAN_VIEW. HOME_DEBIT SEBAGAI 'Debit',
AA_NOMINAL_TRAN_VIEW. HOME_CREDIT SEBAGAI 'Kredit',
SL_PL_NL_DETAIL. DET_HEADER_KEY SEBAGAI 'No Header',
SL_PL_NL_DETAIL. DET_HEADER_REF SEBAGAI 'Referensi Tajuk'

DARI BAXDB. dbo. AA_NOMINAL_TRAN_VIEW AA_NOMINAL_TRAN_VIEW,

BAXDB. dbo. NL_MAJORHEADING NL_MAJORHEADING,

BAXDB. dbo. SL_PL_NL_DETAIL SL_PL_NL_DETAIL

DI MANA SL_PL_NL_DETAIL. DET_PRIMARY = AA_NOMINAL_TRAN_VIEW. DET_PRIMARY
DAN AA_NOMINAL_TRAN_VIEW. NMAJORHEADCODE = NL_MAJORHEADING. NL_MAJORCODE

DAN ((AA_NOMINAL_TRAN_VIEW. NCODE IN (' MTR001', 'MTR003', 'MTR004', 'MTR005 '))

DAN (AA_NOMINAL_TRAN_VIEW. DET_NOM_YEAR = ' C ')

DAN (AA_NOMINAL_TRAN_VIEW. DET_NOM_PERIOD ANTARA 1 DAN 2 )

DAN (SL_PL_NL_DETAIL. DET_BATCH_FLAG = 0))

PESAN DENGAN AA_NOMINAL_TRAN_VIEW. DET_PRIMARY", CreateNavigationProperties=false])

Nilai yang ditunjukkan dengan warna biru adalah yang ingin saya dapatkan dari lembar kerja.  

Setiap saran tentang cara terbaik untuk melakukan ini akan diterima dengan penuh syukur

terima kasih

Bax

Treasi Mynda

Posting. 4336

14 Maret 2021 - 12. 03 sore

2

Tidak tahu, maaf, Bax. Saya tidak tahu SQL

Saya terkejut kueri lebih cepat jika Anda memasukkan kode SQL di pengaturan lanjutan, daripada menggunakan GUI Power Query untuk membuat kueri itu sendiri. Menggunakan pengaturan lanjutan biasanya merusak pelipatan kueri, jadi secara teori seharusnya lebih lambat

Mynda

Katalin Bombea

Iasi, Rumania

Posting. 1793

14 Maret 2021 - 2. 14 sore

3

Hai,

Teks kueri SQL hanyalah string teks, gunakan simbol gabungan dan seperti di string excel atau VBA apa pun, Anda dapat mengambil data dari tabel dengan pengaturan atau nama yang ditentukan

Param1 = Excel. CurrentWorkbook(){[Name="TablenameOrDefinedName"]}[Konten]{0}[Column1],
Param2 = Excel. CurrentWorkbook(){[Name="TablenameOrDefinedName2"]}[Konten]{0}[Column1],

...

"PILIH DI ('" & Param1 & "', '" & Param2 & "', '" & Param3 & "', '" & Param4 & "')) AND (AA_NOMINAL_TRAN_VIEW.DET_NOM_YEAR = '" & Param5 & "') ORDER OLEH AA_NOMINAL_TRAN_VIEW. DET_PRIMARY"

baxbax

Posting. 104

26 Maret 2021 - 3. 06 pagi

4

Hai Mynda/Catalin,

Saya terkejut bahwa kueri lebih cepat menggunakan SQL. Saya sangat berharap untuk menggunakan logika PQ daripada SQL

Terima kasih atas masukan mengenai sintaks SQL. Saya bisa membuatnya berfungsi dengan baik jika sel lembar kerja berisi satu nilai. Namun seperti yang ditunjukkan dalam contoh saya, bidang NCODE memiliki banyak nilai. Ini adalah kode akun buku besar nominal. Saya berharap bisa memasukkan ini ke dalam daftar atau string gabungan di lembar kerja dan mendapatkan nilai dari itu. Apakah ini mungkin?

Terima kasih atas masukan Anda

Bersulang

Bax

Katalin Bombea

Iasi, Rumania

Posting. 1793

26 Maret 2021 - 3. 31 pagi

5

Anda sudah memiliki sintaks yang diperlukan untuk membaca teks dari sel tabel excel

SQLString = Excel. CurrentWorkbook(){[Name="TablenameOrDefinedName"]}[Konten]{0}[Column1],

Anda dapat membuat string seluruhnya di excel lalu menggunakannya di PQ

= Sql. Database("baxserv", "BaxDB", [Query= SQLString ])

baxbax

Posting. 104

26 Maret 2021 - 10. 05 sore

6

Hai Katalin,

Saya berjuang untuk mendapatkan ini untuk bekerja. Jika Anda melihat file terlampir, mudah-mudahan Anda akan melihat apa yang saya coba lakukan

Ada lembar bernama "Parameter" yang memiliki tabel yang menunjukkan nilai yang ingin saya ambil dan masukkan ke kueri SQL. Saya telah membuat kueri untuk masing-masing ini

Jika Anda melihat kueri Nominal_Query, Anda akan melihat SQL. Saya telah berhasil memasukkan nilai YearParam ke string tetapi ketika saya mencoba yang lain saya mendapatkan pesan kesalahan.  

Saya mencoba menambahkan seluruh string SQL ke sel lembar kerja dan menariknya ke dalam kueri tetapi itu juga memberi saya kesalahan

terima kasih

Bax

Katalin Bombea

Iasi, Rumania

Posting. 1793

27 Maret 2021 - 1. 57 pagi

7

Sayangnya, tidak ada yang Anda katakan tercermin dalam file. kueri tidak diambil dari sheet, hanya satu parameter yang digunakan dalam file yang Anda lampirkan, YearParam

Dan tentu saja, deskripsi kesalahan penting benar-benar hilang, saya asumsikan Anda sadar bahwa kami tidak dapat menguji kueri Anda terhadap basis data lokal Anda untuk melihat kesalahan apa yang ditampilkan

Harap berikan deskripsi mendetail tentang kesalahan yang Anda terima. tangkapan layar, atau pesan kesalahan. Saya mencoba menebak bintang, tetapi saya pikir mereka tidak disejajarkan dengan benar. 🙂

baxbax

Posting. 104

30 Maret 2021 - 1. 25 pagi

8

Hai Katalin,

Saya tidak mencoba menunjukkan kepada Anda pesan kesalahan yang saya terima Saya sadar bahwa Anda tidak akan dapat terhubung ke database

Apa yang saya coba tunjukkan dalam file adalah bagaimana saya membuat bidang parameter. Saya telah memasukkannya ke dalam tabel yang disebut "Table_Parameters" di lembar kerja Parameter. Saya kemudian membuat kueri hanya koneksi dari tabel ini. Untuk setiap baris dalam tabel saya membuat kueri dan kemudian menelusuri untuk memberi saya nilai alfa atau numerik. Berikut ini menunjukkan kueri yang ada di file contoh

[Gambar Tidak Dapat Ditemukan]

Saya kemudian mencoba menggunakan sintaks SQL yang Anda berikan pada jawaban pertama Anda untuk mengganti bidang nilai ini ke dalam kode. Jadi untuk tahun Nominal, baris dalam kode SQL menunjukkan (AA_NOMINAL_TRAN_VIEW. DET_NOM_YEAR='" & YearParam & "') dan ini berfungsi dengan baik. Saya kemudian mencoba mengganti di awal dan akhir periode. Kode SQL sebagai standar untuk ini berbunyi, (AA_NOMINAL_TRAN_VIEW. DET_NOM_PERIOD Antara 1 Dan 2). Saya mencoba mengganti nilai kueri berikut, (AA_NOMINAL_TRAN_VIEW. DET_NOM_PERIOD Antara '" & StartPeriodParam & "' Dan '" & EndPeriodParam & "'). Saya kemudian mendapatkan pesan kesalahan yang mengatakan "Expression. Kesalahan. Kami tidak dapat menerapkan operator & untuk mengetik Teks dan Angka. ". Akhirnya saya mencoba menggunakan AccountsParam. Dalam tabel ada beberapa nilai dalam sel untuk dipisahkan dengan koma. Saya berharap bisa memasukkan ini sebagai parameter jadi jika saya perlu mengubah nilainya, saya hanya perlu memperbarui sel lembar kerja. Sekali lagi saya mendapatkan error, "DataSource. Kesalahan. Microsoft SQL. Sintaks salah di dekat 'MTR001'. "

Pertanyaan saya saat ini adalah apakah metode yang saya gunakan untuk membuat parameter dari tabel lembar kerja sudah benar?

terima kasih

Bax

Katalin Bombea

Iasi, Rumania

Posting. 1793

30 Maret 2021 - 3. 16 pagi

9

Jauh lebih jelas dari sebelumnya

"Ekspresi. Kesalahan. Kami tidak dapat menerapkan operator & untuk mengetik Teks dan Angka. "  Itu terjadi karena StartPeriodParam adalah angka dan PQ hanya dapat menggabungkan string teks. Cukup gunakan

(AA_NOMINAL_TRAN_VIEW. DET_NOM_PERIOD Antara '" & Teks. Dari(MulaiPeriodParam) & "' Dan '" & Teks. Dari(EndPeriodParam) & "')

Perhatikan bahwa baris di atas akan mengembalikan teks kueri seperti ini

(AA_NOMINAL_TRAN_VIEW. DET_NOM_PERIOD Antara '1' Dan '2'), Anda mungkin tidak perlu menambahkan tanda kutip tunggal

Tanda kutip tunggal adalah opsional ketika nilai string tidak mengandung spasi apa pun, Jika ada spasi dalam nilai string, itu akan menimbulkan kesalahan - sintaks tidak valid

Anda dapat membuat teks kueri dengan cara apa pun yang Anda suka, seluruhnya dalam sel excel atau sebagian dalam sel excel, pada akhirnya semuanya akan menjadi potongan teks, jadi tidak masalah bagaimana Anda menggabungkan potongan-potongan itu

"Sumber data. Kesalahan. Microsoft SQL. Sintaks salah di dekat 'MTR001'. "

Seperti yang disarankan di atas, gunakan langkah terpisah

SQLString = BuildYourQueryTextHereToBeUsedInNextStep,

LuncurkanQueryToDB= Sql. Database("baxserv", "BaxDB", [Query= SQLString ])

Anda akan dapat memeriksa teks kueri jika Anda memilih langkah SQLString, setiap kesalahan dapat dengan mudah diidentifikasi

Bagaimana cara menggunakan parameter dalam kueri SQL di Excel?

Bagaimana cara mendapatkan data dari SQL Server ke Excel menggunakan kueri?

Bagaimana cara mengekspor data secara otomatis dari SQL Server ke Excel?

Bisakah Excel menarik data dari server?