Convert HTML Table To CSV/Excel

Approach 1

Pros: Keep the original text format such as link, color

Cons: Copy and paste the content manually

Steps:

1. Copy the HTML table manually
2. Paste to Microsoft Excel or LibreOffice Calc
3. Save file as CSV/Excel

Approach 2

Pros: The table content was imported automatically.

Cons: Text only. Losing the original text format such as link, color.

Steps:

1. Go to Google Drive (Google 雲端硬碟)
2. Add new spreadsheet
3. Using IMPORTHTML function. Key in the content into the cell =IMPORTHTML("URL of HTML Table","table",1) 1 means the first table occurred on the web page
4. Save file as CSV/Excel

Checking the rows of data after import

• Inspect the table element on the Chrome
• (optional) add tableId if the table has no id ex: <table ... ... id="tableId">
• If the table contains the <tbody> tag. Go to Chrome DevTools. Key in the following code to console.

 var tableId = "tableId"; 
 var rows = document.getElementById(tableId).getElementsByTagName("tbody")[0].getElementsByTagName("tr").length;
 console.log("rows count of table which not contains heading: " + rows);

• And press the Enter key to get the rows of table.

• If the table contains the <tbody> tag. Key in the following code to console.

 var tableId = "tableId"; 
var rows = document.getElementById(tableId).getElementsByTagName("tr").length;
console.log("rows count of table which contains heading: " + rows);

• And press the Enter key to get the rows of table.

使用 chrome 檢查表格資料列數

• 選取網頁語法，按右鍵 Edit as HTML
• 手動幫表格加上 id ex: <table ... ... id="tableId">
• 按網頁別處，自動儲存
• 如果網頁表格包含 <tbody> tag，在 chrome 開發者工具的 console 輸入

 var tableId = "tableId"; 
 var rows = document.getElementById(tableId).getElementsByTagName("tbody")[0].getElementsByTagName("tr").length;
 console.log("不包含表頭那一行的資料列數: " + rows);

• 按 Enter，取得資料列數

• 如果網頁表格不包含 <tbody> tag.，在 chrome 開發者工具的 console 輸入

 var tableId = "tableId"; 
var rows = document.getElementById(tableId).getElementsByTagName("tr").length;
console.log("包含表頭那一行的資料列數: " + rows);

• 按 Enter，取得資料列數