Thep Excel
OleDb.DataSourceเชื่อมต่อฐานข้อมูล OLE DB
OleDb.DataSource ใช้สำหรับเชื่อมต่อกับฐานข้อมูล OLE DB (SQL Server, Access, MySQL ผ่าน ODBC เป็นต้น) และนำเข้าตารางหรือแสดงผลจากคำสั่ง SQL
Syntax & Arguments
| อาร์กิวเมนต์ | ชนิด | ค่าเริ่มต้น | คำอธิบาย |
|---|---|---|---|
| connectionString | Text or Record | สตริงหรือระเบียนที่ระบุการเชื่อมต่อ OLE DB (เช่น ‘Provider=SQLOLEDB;Server=myserver;Database=mydb;’ หรือระเบียนที่มี property ต่างๆ) | |
| [options]ไม่บังคับ | Record | null | ระเบียนที่ปรับแต่งพฤติกรรม เช่น [Query=”SELECT…”, CommandTimeout=#duration(0,0,30,0)] |
[ ] = อาร์กิวเมนต์ที่ไม่บังคับ
Additional Notes
OleDb.DataSource(connectionString, [options]) ส่งกลับตาราง SQL ที่มีตารางและมุมมองทั้งหมดจากแหล่งข้อมูล OLE DB ที่ระบุโดยสตริงการเชื่อมต่อ
ประโยชน์คือสามารถเข้าถึงข้อมูลจากฐานข้อมูลต่างๆ ได้โดยไม่ต้องเตรียมข้อมูลในไฟล์ Excel หรือ CSV ก่อน เหมาะสำหรับสถานการณ์ที่ข้อมูลเปลี่ยนแปลงบ่อยและต้องรีเฟรชอัตโนมัติ
ส่วนตัวผมมักใช้ OleDb.DataSource พร้อมกับ [Query] option เพื่อเขียนคำสั่ง SQL ที่ซับซ้อนในเครื่องเมื่อต้องการกรองข้อมูลหรือรวมตารางหลายตารางก่อนนำเข้า ลดขนาดข้อมูลที่ต้องประมวลผลในตัว Power Query ได้เยอะครับ 😎
Examples
เชื่อมต่อ SQL Server พื้นฐานOleDb.DataSource("Provider=SQLOLEDB;Server=localhost;Database=Sales;Integrated Security=SSPI;")
เชื่อมต่อกับ SQL Server บนเครื่องเดียวกัน ใช้การรับรองความถูกต้อง Windows โดยอัตโนมัติ
ดึงข้อมูลจากตารางด้วย QueryOleDb.DataSource( "Provider=SQLOLEDB;Server=myserver;Database=mydb;", [Query = "SELECT OrderID, CustomerID, Amount FROM Orders WHERE Amount > 1000"] )
ใช้ [Query] option เพื่อกรองข้อมูลในเซิร์ฟเวอร์ ประหยัดแบนด์วิดท์และการประมวลผล
กำหนด Command TimeoutOleDb.DataSource( "Provider=SQLOLEDB;Server=server.com;Database=LargeDB;", [Query = "SELECT * FROM BigTable", CommandTimeout = #duration(0, 1, 0, 0)] )
ตั้ง CommandTimeout เป็น 1 ชั่วโมง (#duration ระบุ ชั่วโมง:นาที:วินาที) เหมาะสำหรับคำสั่ง SQL ที่ใช้เวลานาน
เชื่อมต่อ MySQL ผ่าน ODBCOleDb.DataSource("Provider=MSDASQL;Driver={MySQL ODBC 8.0 DRIVER};Server=localhost;Database=mydb;UID=user;PWD=pass;")
ใช้ ODBC Driver ในการเชื่อมต่อ MySQL ระบุชื่อผู้ใช้และรหัสผ่าน
เชื่อมต่อ Access DatabaseOleDb.DataSource("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\data\\mydata.accdb;")
ใช้ Access Database Engine เพื่อเชื่อมต่อไฟล์ .accdb ระบุเส้นทางแบบเต็มของไฟล์
FAQs
OleDb.DataSource ต่างจาก Sql.Database อย่างไร+
Sql.Database ใช้สำหรับ SQL Server โดยตรง เข้าถึง metadata ตารางอัตโนมัติ ส่วน OleDb.DataSource เป็นแบบทั่วไปกว่า สามารถใช้กับฐานข้อมูลใด ๆ ที่รองรับ OLE DB (SQL Server, MySQL, Access, PostgreSQL ผ่าน ODBC เป็นต้น)
ต้องติดตั้งอะไรเพิ่มเติมหรือไม่+
ต้องมี OLE DB Provider หรือ ODBC Driver ที่เหมาะสมในเครื่อง เช่น สำหรับ MySQL ต้องติดตั้ง MySQL ODBC Driver
ใช้ [Query] ดีไหม+
ดีมาก เพราะประมวลผลกรองข้อมูลในเซิร์ฟเวอร์ก่อน ลดข้อมูลที่นำเข้า Power Query จึงเร็วขึ้นและใช้หน่วยความจำน้อยลง
ถ้า Query ใช้เวลานาน ต้องทำยังไง+
ใช้ [CommandTimeout] เพื่อเพิ่มเวลาโต (เช่น 1 ชั่วโมง) หรือปรับแต่ง Query ให้มีประสิทธิภาพ เช่น เพิ่ม INDEX หรือลดจำนวนแถวตั้งแต่ต้น
ต้องระบุ password ในสตริงการเชื่อมต่อหรือไม่+
หลีกเลี่ยงหากทำได้ ใช้ Integrated Security=SSPI แทนสำหรับ SQL Server หรือจัดเก็บ credential ในที่ปลอดภัยเช่น Azure Key Vault
HierarchicalNavigation=false ใช้เมื่อไหร่+
ใช้เมื่อต้องการรายการแบนราบของตารางทั้งหมด ไม่อยากให้จัดกลุ่มตามชื่อสคีมา
Resources & Related
ฟังก์ชันที่ผู้เขียนโยงไว้กับ OleDb.DataSource จัดกลุ่มตามหมวด · ชี้ที่ชื่อใดชื่อหนึ่ง แล้วฟังก์ชันที่ใช้คู่กันจะมีจุดทอง
Access.Database ใช้สำหรับการเชื่อมต่อกับไฟล์ฐานข้อมูล Microsoft Access (.mdb, .accdb) และดึงข้อมูลจากตาราต่างๆ
MySQL.Database เป็นฟังก์ชันตัวเชื่อมต่อ (connector) ที่ใช้ส่งข้อมูลจากฐานข้อมูล MySQL ไปยัง Power Query ช่วยให้คุณสามารถโหลดตารางและมุมมองจากเซิร์ฟเวอร์ MySQL ได้
Sql.Database เป็นฟังก์ชันที่ใช้เชื่อมต่อกับฐานข้อมูล SQL Server แล้วดึงข้อมูลเข้ามาประมวลผลใน Power Query ได้เลยครับ
.
ฟังก์ชันนี้จะคืนค่าออกมาเป็น table ที่แสดงรายการของ tables, views และ stored functions ทั้งหมดที่มีในฐานข้อมูลที่เราระบุ ที่เจ๋งคือฟังก์ชันนี้รองรับการทำ query folding ซึ่งทำให้การประมวลผลข้อมูลเกิดขึ้นที่ SQL Server โดยตรง ได้ประสิทธิภาพสูงสุดและลดการใช้ memory ลงมาก
.
นอกจากนี้ยังมี options หลากหลายให้เราปรับแต่งการเชื่อมต่อได้ เช่น การกำหนด CommandTimeout สำหรับ query ที่ใช้เวลานาน การเขียน native SQL query เพื่อดึงข้อมูลแบบกำหนดเองเลย การทำ cross-database folding เพื่อทำงานข้ามหลายฐานข้อมูล และรองรับ authentication หลายรูปแบบด้วย ส่วนตัวผมใช้บ่อยมากเวลาทำงานกับข้อมูลขนาดใหญ่จาก SQL Server ครับ 😎
Table.SelectRows เป็นฟังก์ชันสำหรับกรองตาราง (filter table) ใน Power Query โดยใช้ฟังก์ชันเงื่อนไข (condition function) เพื่อตรวจสอบแต่ละแถว ถ้าเงื่อนไขคืนค่า true จะเก็บแถวนั้นไว้ ถ้าเป็น false จะตัดทิ้ง สามารถใช้ keyword 'each' ร่วมกับการอ้างอิงคอลัมน์ด้วย [ColumnName] เพื่อเขียนเงื่อนไขได้สะดวก รองรับการกรองตามตัวเลข ข้อความ วันที่ การตรวจสอบค่า null และเงื่อนไขที่ซับซ้อนด้วย and/or operators ฟังก์ชันนี้รองรับ Query Folding ซึ่งช่วยเพิ่มประสิทธิภาพเมื่อทำงานกับ data source ขนาดใหญ่
ยังไม่มีบทความที่เกี่ยวข้องกับฟังก์ชันนี้
Comments
อีเมลของคุณจะไม่ถูกเผยแพร่