Convert HTML Table To CSV/Excel
Jump to navigation
Jump to search
Approach 1[edit]
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: Using the Google Drive[edit]
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 on the Chrome
- Right click the table and then click the Inspect item of the menu.
- (optional) add tableId if the table has no id ex: <table ... ... id="tableId">. Howto: Inspect and Edit Pages and Styles | Web Tools - Google Developers.
- Check the count number of rows: Go to Chrome DevTools. Key in the following code to console.
var tableId = "tableId"; var tbody_exists = document.getElementById(tableId).getElementsByTagName("tbody").length; if( tbody_exists == 1 ){ // If the table contains the <tbody> tag var rows = document.getElementById(tableId).getElementsByTagName("tbody")[0].getElementsByTagName("tr").length; }else{ // If the table NOT contains the <tbody> tag var rows = document.getElementById(tableId).getElementsByTagName("tr").length; } console.log("count number of rows: " + rows);
- And press the Enter key to get the count number of rows.
- check the count number of column: [1]
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">
- 在網頁原始碼別處點一下,自動儲存修改
- 檢查資料列數
var tableId = "tableId"; var tbody_exists = document.getElementById(tableId).getElementsByTagName("tbody").length; if( tbody_exists == 1 ){ // 如果網頁表格包含 <tbody> tag var rows = document.getElementById(tableId).getElementsByTagName("tbody")[0].getElementsByTagName("tr").length; }else{ // 如果網頁表格不包含 <tbody> tag var rows = document.getElementById(tableId).getElementsByTagName("tr").length; } console.log("count number of rows: " + rows);
- 按 Enter,取得資料列數
- 檢查資料欄位數
var tableId = "tableId"; var column_count = document.getElementById(tableId).rows[0].cells.length; console.log("資料欄數: " + rows);
- 按 Enter,取得資料欄位數
References[edit]
further reading