เรียกใช้การสอบถาม SQL ในไฟล์ Excel

แม้ว่าการดำเนินการของ Excel สามารถจัดการกับสถานการณ์การทำงานอัตโนมัติของ Excel ส่วนใหญ่ได้ แต่การสอบถาม SQL สามารถดึงและจัดการข้อมูล Excel จำนวนมากได้อย่างมีประสิทธิภาพมากกว่า

สมมติว่าโฟลว์ต้องแก้ไขเฉพาะการลงทะเบียนของ Excel ที่มีค่าเฉพาะ หากต้องการให้บรรลุฟังก์ชันนี้โดยไม่ต้องมีการสอบถาม SQL คุณต้องมีลูป เงื่อนไข และการดำเนินการของ Excel หลายรายการ

หรือคุณสามารถใช้ฟังก์ชันนี้กับการสอบถาม SQL โดยใช้การดำเนินการเพียงสองอย่างเท่านั้น คือ เปิดการเชื่อมต่อ SQL และ ดำเนินการคำสั่ง SQL

เปิดการเชื่อมต่อ SQL ไปยังไฟล์ Excel

ก่อนเรียกใช้การสอบถาม SQL คุณต้องเปิดการเชื่อมต่อกับไฟล์ Excel ที่คุณต้องการเข้าถึง

ในการสร้างการเชื่อมต่อ ให้สร้างตัวแปรใหม่ชื่อ %Excel_File_Path% และเริ่มต้นด้วยพาธไฟล์ Excel หรือคุณสามารถข้ามขั้นตอนนี้และใช้พาธแบบฮาร์ดโค้ดของไฟล์ได้ในภายหลังในโฟลว์

ตอนนี้ปรับใช้การดำเนินการ เปิดการเชื่อมต่อ SQL และเติมสตริงการเชื่อมต่อต่อไปนี้ในคุณสมบัติ

ผู้ให้บริการ=Microsoft.ACE.OLEDB.12.0 แหล่งข้อมูล=%Excel_File_Path% คุณสมบัติเพิ่มเติม="Excel 12.0 Xml HDR=YES"

หมายเหตุ

ในการใช้สตริงการเชื่อมต่อที่นำเสนอได้สำเร็จ คุณต้องดาวน์โหลดและติดตั้ง Microsoft Access Database Engine 2010 Redistributable

เปิดการเชื่อมต่อ SQL ไปยังไฟล์ Excel ที่ป้องกันด้วยรหัสผ่าน

จำเป็นต้องใช้วิธีการอื่นในสถานการณ์ที่คุณเรียกใช้การสอบถาม SQL บนไฟล์ Excel ที่ป้องกันด้วยรหัสผ่าน การดำเนินการ เปิดการเชื่อมต่อ SQL ไม่สามารถเชื่อมต่อกับไฟล์ Excel ที่ป้องกันด้วยรหัสผ่าน ดังนั้นคุณต้องลบการป้องกัน

ให้เปิดไฟล์ Excel โดยใช้การดำเนินการ เปิด Excel ไฟล์มีการป้องกันด้วยรหัสผ่าน ดังนั้นให้ใส่รหัสผ่านที่เหมาะสมในฟิลด์ รหัสผ่าน

ถัดไป ปรับใช้การดำเนินการอัตโนมัติของ UI ที่เหมาะสมและไปที่ ไฟล์>ข้อมูล>ป้องกันสมุดงาน>เข้ารหัสด้วยรหัสผ่าน คุณสามารถค้นหาข้อมูลเพิ่มเติมเกี่ยวกับระบบอัตโนมัติของ UI และวิธีใช้การดำเนินการที่เกี่ยวข้องได้ใน ทำให้แอปพลิเคชันเดสก์ท็อปทำงานอัตโนมัติ

หลังจากเลือก เข้ารหัสด้วยรหัสผ่าน เติมสตริงว่างในกล่องโต้ตอบป๊อปอัปโดยใช้การดำเนินการ เติมฟิลด์ข้อความในหน้าต่าง ในการเติมสตริงว่าง ให้ใช้นิพจน์ต่อไปนี้: %""%

ในการกดปุ่ม ตกลง ในกล่องโต้ตอบและใช้การเปลี่ยนแปลง ปรับใช้การดำเนินการ กดปุ่มในหน้าต่าง

สุดท้ายปรับใช้การดำเนินการ ปิด Excel เพื่อบันทึกสมุดงานที่ไม่มีการป้องกันเป็นไฟล์ Excel ใหม่

หลังจากบันทึกไฟล์แล้ว ให้ทำตามคำแนะนำใน เปิดการเชื่อมต่อ SQL ไปยังไฟล์ Excel เพื่อเปิดการเชื่อมต่อ

เมื่อการจัดการไฟล์ Excel เสร็จสิ้น ให้ใช้การดำเนินการ ลบไฟล์ เพื่อลบสำเนาไฟล์ Excel ที่ไม่มีการป้องกัน

อ่านเนื้อหาของสเปรดชีต Excel

แม้ว่าการดำเนินการ อ่านจากเวิร์กชีต Excel สามารถอ่านเนื้อหาของเวิร์กชีต Excel ได้ การวนซ้ำอาจใช้เวลานานมากในการวนซ้ำข้อมูลที่ดึงมา

วิธีที่มีประสิทธิภาพมากขึ้นในการดึงค่าเฉพาะจากสเปรดชีตคือการจัดการไฟล์ Excel เป็นฐานข้อมูลและดำเนินการคำสั่ง SQL กับไฟล์เหล่านั้น วิธีนี้เร็วกว่าและเพิ่มประสิทธิภาพของโฟลว์

ในการดึงเนื้อหาทั้งหมดของสเปรดชีต คุณสามารถใช้การสอบถาม SQL ต่อไปนี้ในการดำเนินการ ดำเนินการคำสั่ง SQL

SELECT * FROM [SHEET$]

หมายเหตุ

เมื่อต้องการใช้การสอบถาม SQL นี้ในโฟลว์ของคุณ ให้แทนที่ตัวยึดตำแหน่ง แผ่นงาน ที่มีชื่อของสเปรดชีตที่คุณต้องการเข้าถึง

เมื่อต้องการเรียกแถวที่มีค่าเฉพาะในคอลัมน์ที่ระบุ ให้ใช้การสอบถาม SQL ต่อไปนี้:

SELECT * FROM [SHEET$] WHERE [COLUMN NAME] = 'VALUE'

หมายเหตุ

เมื่อต้องการใช้การสอบถาม SQL นี้ในโฟลว์ของคุณ ให้แทนที่:

• แผ่นงาน ที่มีชื่อสเปรดชีตที่คุณต้องการเข้าถึง
• ชื่อคอลัมน์ ที่มีคอลัมน์ที่มีค่าที่คุณต้องการค้นหา คอลัมน์ในแถวแรกของเวิร์กชีต Excel จะถูกระบุเป็นชื่อคอลัมน์ของตาราง
• ค่า ที่มีค่าที่คุณต้องการหา

ลบข้อมูลจากแถว Excel

แม้ว่า Excel จะไม่รองรับการสอบถาม DELETE SQL คุณสามารถใช้การสอบถาม UPDATE เพื่อตั้งค่าเซลล์ทั้งหมดของแถวที่ระบุให้เป็น null

ให้แม่นยำยิ่งขึ้น คุณสามารถใช้การสอบถาม SQL ต่อไปนี้:

UPDATE [SHEET$] SET [COLUMN1]=NULL, [COLUMN2]=NULL WHERE [COLUMN1]='VALUE'

ในขณะที่พัฒนาโฟลว์ของคุณ คุณต้องแทนที่ตัวยึดตำแหน่ง แผ่นงาน ที่มีชื่อของสเปรดชีตที่คุณต้องการเข้าถึง

ตัวยึด COLUMN1 และ COLUMN2 แทนชื่อของคอลัมน์ที่จะจัดการ ตัวอย่างนี้มีคอลัมน์สองคอลัมน์ แต่ในสถานการณ์จริง จำนวนคอลัมน์อาจแตกต่างกัน คอลัมน์ในแถวแรกของเวิร์กชีต Excel จะถูกระบุเป็นชื่อคอลัมน์ของตาราง

ส่วน [COLUMN1]='VALUE' ของการสอบถามกำหนดแถวที่คุณต้องการอัปเดต ในโฟลว์ของคุณ ให้ใช้ชื่อคอลัมน์และค่าตามชุดค่าผสมที่อธิบายแถวต่างๆ อย่างไม่ซ้ำกัน

ดึงข้อมูล Excel ยกเว้นแถวที่ระบุ

ในบางสถานการณ์ คุณอาจต้องดึงเนื้อหาทั้งหมดของสเปรดชีต Excel ยกเว้นแถวที่ระบุ

วิธีที่สะดวกในการบรรลุผลนี้คือการตั้งค่าของแถวที่ไม่ต้องการให้เป็นค่าว่าง จากนั้นดึงค่าทั้งหมดยกเว้นค่าว่าง

หากต้องการเปลี่ยนค่าของแถวเฉพาะในสเปรดชีต คุณสามารถใช้การสอบถาม SQL UPDATE ตามที่นำเสนอใน ลบข้อมูลจากแถว Excel:

UPDATE [SHEET$] SET [COLUMN1]=NULL, [COLUMN2]=NULL WHERE [COLUMN1]='VALUE'

ถัดไป ให้เรียกใช้การสอบถาม SQL ต่อไปนี้เพื่อดึงแถวทั้งหมดของสเปรดชีตที่ไม่มีค่า Null:

SELECT * FROM [SHEET$] WHERE [COLUMN1] IS NOT NULL OR [COLUMN2] IS NOT NULL

ตัวยึด COLUMN1 และ COLUMN2 แทนชื่อของคอลัมน์ที่จะจัดการ ตัวอย่างนี้มีคอลัมน์สองคอลัมน์ แต่ในตารางจริง จำนวนคอลัมน์อาจแตกต่างกัน คอลัมน์ทั้งหมดในแถวแรกของเวิร์กชีต Excel จะถูกระบุเป็นชื่อคอลัมน์ของตาราง