Convert HTML Table To CSV/Excel

From LemonWiki共筆
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:

  1. Copy the HTML table manually
  2. Paste to Microsoft Excel or LibreOffice Calc
  3. 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:

  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 on the Chrome Browser chrome.png

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