Convert HTML Table To CSV/Excel
Jump to navigation
Jump to search
Approach 1
Pros: Keep the original text format such as link, color
Cons: Copy and paste the content manually
Steps:
- Copy the HTML table manually
- Paste to Microsoft Excel or LibreOffice Calc
- 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:
- Go to Google Drive (Google 雲端硬碟)
- Add new spreadsheet
- 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
- 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("rows count of table 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("rows count of table which contains heading: " + rows);
- And press the Enter key to get the rows of table.
- check the count number of column: [1]
var tableId = "tableId";
var column_count = document.getElementById(tableId).rows[0].cells.length;
console.log("column count of table: " + 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);
// 如果網頁表格不包含 <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
further reading