นำเข้าข้อมูลจาก PDF ไปยัง Excel ผ่าน Power Query

งานถ่ายโอนข้อมูลจากสเปรดชีตในไฟล์ PDF ไปยังแผ่นงาน Microsoft Excel นั้น “สนุก” อยู่เสมอ โดยเฉพาะอย่างยิ่งหากคุณไม่มีซอฟต์แวร์การจดจำราคาแพง เช่น FineReader หรืออะไรทำนองนั้น การคัดลอกโดยตรงมักจะไม่นำไปสู่สิ่งที่ดีเพราะ หลังจากวางข้อมูลที่คัดลอกลงในแผ่นงานแล้ว ข้อมูลส่วนใหญ่จะ "ติดกัน" ไว้ในคอลัมน์เดียว จึงต้องแยกจากกันโดยใช้เครื่องมือ ข้อความตามคอลัมน์ จากแท็บ ข้อมูล (ข้อมูล — ข้อความเป็นคอลัมน์).

และแน่นอนว่า การคัดลอกทำได้เฉพาะไฟล์ PDF ที่มีเลเยอร์ข้อความเท่านั้น เช่น เอกสารที่เพิ่งสแกนจากกระดาษเป็น PDF วิธีนี้ใช้ไม่ได้ผลในหลักการ

แต่ก็ไม่ได้เศร้าขนาดนั้นจริงๆ 🙂

หากคุณมี Office 2013 หรือ 2016 ในเวลาไม่กี่นาที หากไม่มีโปรแกรมเพิ่มเติม คุณสามารถถ่ายโอนข้อมูลจาก PDF ไปยัง Microsoft Excel ได้ และ Word และ Power Query จะช่วยเราในเรื่องนี้

ตัวอย่างเช่น ลองใช้รายงาน PDF นี้พร้อมข้อความ สูตร และตารางจำนวนมากจากเว็บไซต์ของคณะกรรมาธิการเศรษฐกิจสำหรับยุโรป:

… และลองดึงออกมาใน Excel พูดตารางแรก:

ไปกันเถอะ!

ขั้นตอนที่ 1. เปิด PDF ใน Word

ด้วยเหตุผลบางอย่าง น้อยคนนักที่จะรู้ แต่ตั้งแต่ปี 2013 Microsoft Word ได้เรียนรู้ที่จะเปิดและรู้จักไฟล์ PDF (แม้แต่ไฟล์ที่สแกน ซึ่งก็คือ ไม่มีเลเยอร์ข้อความ!) ทำได้ด้วยวิธีมาตรฐานโดยสมบูรณ์: เปิด Word คลิก ไฟล์ – เปิด (ไฟล์ — เปิด) และระบุรูปแบบ PDF ในรายการดรอปดาวน์ที่มุมล่างขวาของหน้าต่าง

จากนั้นเลือกไฟล์ PDF ที่เราต้องการแล้วคลิก จุดเปิด (เปิด). Word บอกเราว่ากำลังจะเรียกใช้ OCR ในเอกสารนี้เป็นข้อความ:

เราเห็นด้วยและในไม่กี่วินาทีเราจะเห็น PDF ของเราเปิดสำหรับการแก้ไขใน Word แล้ว:

แน่นอนว่าการออกแบบ สไตล์ ฟอนต์ ส่วนหัวและส่วนท้าย ฯลฯ จะหลุดออกจากเอกสารไปบางส่วน แต่สิ่งนี้ไม่สำคัญสำหรับเรา เราต้องการแค่ข้อมูลจากตารางเท่านั้น โดยหลักการแล้ว ในขั้นตอนนี้ เป็นการดึงดูดให้คัดลอกตารางจากเอกสารที่รู้จักไปยัง Word แล้ววางลงใน Excel บางครั้งก็ใช้งานได้ แต่บ่อยครั้งขึ้นนำไปสู่การบิดเบือนข้อมูลทุกประเภท ตัวอย่างเช่น ตัวเลขสามารถเปลี่ยนเป็นวันที่หรือยังคงเป็นข้อความ อย่างเช่นในกรณีของเรา เพราะ PDF ใช้ตัวคั่นที่ไม่ใช่:

ดังนั้นอย่าตัดมุม แต่ทำให้ทุกอย่างซับซ้อนขึ้นเล็กน้อย แต่ถูกต้อง

ขั้นตอนที่ 2: บันทึกเอกสารเป็นเว็บเพจ

ในการโหลดข้อมูลที่ได้รับลงใน Excel (ผ่าน Power Query) เอกสารของเราใน Word จะต้องได้รับการบันทึกในรูปแบบหน้าเว็บ ซึ่งในกรณีนี้ รูปแบบนี้คือตัวหารทั่วไปชนิดหนึ่งระหว่าง Word และ Excel

โดยไปที่เมนู ไฟล์ – บันทึกเป็น (ไฟล์ — บันทึกเป็น) หรือกดปุ่ม F12 บนแป้นพิมพ์และในหน้าต่างที่เปิดขึ้น ให้เลือกประเภทไฟล์ เว็บเพจในไฟล์เดียว (หน้าเว็บ — ไฟล์เดียว):

หลังจากบันทึกแล้ว คุณควรได้ไฟล์ที่มีนามสกุล mhtml (หากคุณเห็นนามสกุลไฟล์ใน Explorer)

ขั้นตอนที่ 3 การอัปโหลดไฟล์ไปยัง Excel ผ่าน Power Query

คุณสามารถเปิดไฟล์ MHTML ที่สร้างขึ้นใน Excel ได้โดยตรง แต่ก่อนอื่นเราจะรับเนื้อหาทั้งหมดของ PDF พร้อมกันพร้อมข้อความและตารางที่ไม่จำเป็นจำนวนมาก และประการที่สอง เราจะสูญเสียข้อมูลอีกครั้งเนื่องจากไม่ถูกต้อง ตัวคั่น ดังนั้น เราจะนำเข้าไปยัง Excel ผ่าน Add-in ของ Power Query นี่เป็นโปรแกรมเสริมที่ไม่มีค่าใช้จ่ายใดๆ ทั้งสิ้น ซึ่งคุณสามารถอัปโหลดข้อมูลไปยัง Excel จากแหล่งที่มาเกือบทุกแหล่ง (ไฟล์ โฟลเดอร์ ฐานข้อมูล ระบบ ERP) แล้วแปลงข้อมูลที่ได้รับในทุกวิถีทางที่เป็นไปได้ ทำให้ได้รูปร่างที่ต้องการ

หากคุณมี Excel 2010-2013 คุณสามารถดาวน์โหลด Power Query ได้จากเว็บไซต์ทางการของ Microsoft – หลังจากติดตั้งแล้ว คุณจะเห็นแท็บ Power Query. หากคุณมี Excel 2016 หรือใหม่กว่า คุณไม่จำเป็นต้องดาวน์โหลดอะไรเลย ฟังก์ชันการทำงานทั้งหมดมีอยู่แล้วใน Excel ตามค่าเริ่มต้นและอยู่บนแท็บ ข้อมูล (วันที่) ในกลุ่ม ดาวน์โหลดและแปลง (รับและแปลง).

ดังนั้นเราจึงไปที่แท็บ ข้อมูล, หรือ บนแท็บ Power Query และเลือกทีม เพื่อรับข้อมูล or สร้างแบบสอบถาม – จากไฟล์ – จาก XML. ในการทำให้มองเห็นได้ไม่เฉพาะไฟล์ XML ให้เปลี่ยนตัวกรองในรายการดรอปดาวน์ที่มุมล่างขวาของหน้าต่างเป็น ไฟล์ทั้งหมด (เอกสารทั้งหมด) และระบุไฟล์ MHTML ของเรา:

โปรดทราบว่าการนำเข้าจะไม่เสร็จสมบูรณ์เนื่องจาก Power Query คาดหวัง XML จากเรา แต่จริงๆ แล้วเรามีรูปแบบ HTML ดังนั้น ในหน้าต่างถัดไปที่ปรากฏขึ้น คุณจะต้องคลิกขวาที่ไฟล์ที่ Power Query ไม่เข้าใจ และระบุรูปแบบของไฟล์:

หลังจากนั้น ไฟล์จะถูกจดจำอย่างถูกต้อง และเราจะเห็นรายการตารางทั้งหมดที่มี:

คุณสามารถดูเนื้อหาของตารางได้โดยคลิกปุ่มซ้ายของเมาส์ในพื้นหลังสีขาว (ไม่ใช่ในคำว่า Table!) ของเซลล์ในคอลัมน์ข้อมูล

เมื่อกำหนดตารางที่ต้องการแล้ว ให้คลิกที่คำสีเขียว ตาราง – และคุณ "ผ่าน" เข้าไปในเนื้อหา:

ยังคงต้องทำขั้นตอนง่ายๆ สองสามขั้นตอนเพื่อ "หวี" เนื้อหา กล่าวคือ:

1. ลบคอลัมน์ที่ไม่จำเป็น (คลิกขวาที่ส่วนหัวของคอลัมน์ – ลบรายการออกจากรถเข็น)
2. แทนที่จุดด้วยเครื่องหมายจุลภาค (เลือกคอลัมน์ คลิกขวา – การแทนที่ค่า)
3. ลบเครื่องหมายเท่ากับในส่วนหัว (เลือกคอลัมน์ คลิกขวา – การแทนที่ค่า)
4. ลบบรรทัดบนสุด (หน้าแรก – ลบบรรทัด – ลบบรรทัดบน)
5. ลบบรรทัดว่าง (หน้าแรก – ลบบรรทัด – ลบบรรทัดว่าง)
   
6. ยกแถวแรกไปที่ส่วนหัวของตาราง (หน้าแรก – ใช้บรรทัดแรกเป็นหัวข้อ)
7. กรองข้อมูลที่ไม่จำเป็นออกโดยใช้ตัวกรอง

เมื่อนำตารางมาอยู่ในรูปแบบปกติแล้ว ก็สามารถถอดออกจากแผ่นงานได้โดยใช้คำสั่ง ปิดและดาวน์โหลด (ปิด & โหลด) on หลัก แท็บ และเราจะได้รับความงามดังกล่าวซึ่งเราสามารถทำได้แล้ว:

• การแปลงคอลัมน์เป็นตารางด้วย Power Query
• การแยกข้อความติดหนึบออกเป็นคอลัมน์