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">
• check the count number of rows:
• If the table contains the <tbody> tag. Go to Chrome DevTools. Key in the following code to console.

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

// If the table NOT contains the <tbody> tag
 var tableId = "tableId"; 
 var rows = document.getElementById(tableId).getElementsByTagName("tr").length;
 console.log("count number of rows which contains heading: " + rows);

• And press the Enter key to get the count number of rows.
• check the count number of column:

 var tableId = "tableId"; 
 var column_count = document.getElementById(tableId).rows[0].cells.length;
 console.log("count number of column: " + rows);

• And press the Enter key to get the count number of column.

使用 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);

// 如果網頁表格不包含 <tbody> tag.，在 chrome 開發者工具的 console 輸入
 var tableId = "tableId"; 
 var rows = document.getElementById(tableId).getElementsByTagName("tr").length;
 console.log("包含表頭那一行的資料列數: " + rows);

• 按 Enter，取得資料列數
• 檢查資料欄位數

 var tableId = "tableId"; 
 var column_count = document.getElementById(tableId).rows[0].cells.length;
 console.log("資料欄數: " + rows);

• 按 Enter，取得資料欄位數

References

• JavaScript to get rows count of a HTML table - Stack Overflow

further reading

• javascript - Select all DIV text with single mouse click - Stack Overflow