Microsoft Excel
Export MySQL query to Excel file
Export MySQL query to Excel file
Export CSV from EXCEL
Excel 檔案,匯出 UTF-8 編碼的 CSV 檔案
- 雖然 Excel 本身也有提供匯出 「Unicode 文字 (*.txt)」,但是因為無法設定使用雙引號框住欄位值。會造成後續匯入檔案容易出問題。
- 解決 Excel 轉 CSV 檔案的數字變為科學符號的問題
軟體 | 操作方式 | 輸出 CSV 檔案格式 |
---|---|---|
LibreOffice Calc (LibreOffice Portable) 版本 6.2.4.2 | 檔案 -> 另存新檔 -> 存檔類型:文字 CSV (.csv) -> 字元集:Unicode (UTF-8) | UTF-8 編碼 不帶簽名(BOM) |
LibreOffice Calc (LibreOffice Portable) 版本 6.2.4.2 | 檔案 -> 另存新檔 -> 存檔類型:文字 CSV (.csv) -> 字元集:正體中文 (Big5) | Big5 編碼 |
Google 雲端硬碟 | 檔案 -> 下載 -> 逗號分隔值檔案 (.csv,目前工作表) | UTF-8 編碼 不帶簽名(BOM) |
Microsoft Excel on Mac 版本 16.33 | 檔案 -> 另存新檔 -> 檔案格式:CSV UTF-8 逗號分隔(.csv) | UTF-8 編碼,帶簽名(BOM) |
Microsoft Excel on Mac 版本 16.32 | 檔案 -> 另存新檔 -> 檔案格式:逗號分隔值 (.csv) | Big5 編碼 |
Microsoft Office 2016 on Win | 檔案 -> 另存新檔 -> (選擇存檔位置) -> 存檔類型:CSV 逗號分隔 (.csv) | Big5 編碼 |
Import CSV to EXCEL
- 匯入或匯出文字檔案 - Excel 支援 Unicode 編碼 Win 圖形教學: Excel打開是亂碼?教你換一個「開啟方式」,瞬間找回所有資料|經理人
- 欄位內容分隔符號 " (雙引號); 欄位分隔符號 , 不可夾雜空白。Unicode 檔案需要檔案有 BOM,這樣才能點選兩下 CSV,Excel 開啟時沒有問題。 (Excel 2007) Win
- 解決CSV檔亂碼 (Microsoft Excel 2007 或 OpenOffice.org 3.2) Win
- 解決 Mac 系統匯入中文 CSV 檔案遇到亂碼問題 (Excel on Mac 版本 16.20, Office 365) Mac
- Mac Office Excel 匯入中文 CSV 檔案 (Microsoft Excel for Mac 2011) Mac
- 解決 MAC 電腦匯入中文 CSV 檔案遇到亂碼問題 (LibreOffice v. 6.1.2.1) Mac
- 雖然 CSV 欄位已經用雙引號區隔,但是如果欄位裡面也有包含雙引號,匯入到 Excel,會導致欄位錯誤的問題。解法1: 原始資料的雙引號改成單引號、解法2: 不用 Excel ,改用 LibreOffice Calc
讓 CSV 可以方便讓 Excel 直接開啟
CSV 檔案 (測試的 Excel 版本: Excel 2016 on Win 、LibreOffice Calc v. 5.4.3.2 on Win 、Excel for Mac 2011 v. 14.7.7)
- 欄位間隔符號 , 、UTF-8 編碼、有 BOM[1] →
- 欄位間隔符號 , 、UTF-8 編碼、沒有 BOM →
- 欄位間隔符號 TAB 、UTF-8 編碼、有 BOM →
- 欄位間隔符號 TAB 、UTF-8 編碼、沒有 BOM (byte-order mark) →
相關文章
Import XLS to MySQL
- Toad for MySQL Freeware v7 32 bit 匯入 xls 檔案時,軟體視窗凍結沒有反應
- Excel2MySQL v 2.6.3 匯入後發現有掉資料
- $ Navicat for MySQL | MySQL 資料庫管理和開發工具
Batch convert TXT/CSV to EXCEL
原始檔案: 欄位值用雙引號框起來(enclosure)、不同欄位資料用定位鍵 Tab 間隔、檔案編碼 UTF8 沒有 BOM
"column_1" \t "column_2" \t "column_3"
批次轉檔的方案比較
- $ Advanced CSV Converter v 5.55 ok
- 轉檔時可以選擇 UTF-8 編碼,輸出的中文 Excel 不會亂碼。
- 但是不能辨識欄位值前後的雙引號 符號,所以原始檔案需要先去除欄位值前後的雙引號 符號。
- 試用版只能匯出 50 筆資料。
- ConvertXLS v. 8.54 中文亂碼
- Bytescout Spreadsheet Tools v. 1.10.0.21 中文亂碼
- Google 雲端硬碟 上傳 CSV 檔前,要勾選「轉換上傳檔案 -- 將已上傳的檔案轉換成 Google 文件編輯器格式」。上傳後,再下載會變成 Excel 格式。 [Last visited: 2016-12-30]
Copy & Paste
from spreadsheet application to another spreadsheet application
- Microsoft Excel --> Google Spreadsheet:
- columns & rows: Copy & Paste is ok
- Plain text --> Google Spreadsheet: ok
- columns: Tab separated columns --> Google Spreadsheet: ok
- rows: Enter separated rows --> Google Spreadsheet: ok
- Plain text --> Microsoft Excel: ok
- columns: Tab separated columns --> Microsoft Excel: ok
- rows: Enter separated rows --> Microsoft Excel: ok
- Microsoft Excel --> Table of Google Document:
- columns & rows: Copy & Paste is ok
- Plain text --> Table of Google Document: fail Suggest you copy to Microsoft Excel first and then copy paste to Google Document from Microsoft Excel.
- columns: Tab separated columns --> Table of Google Document: fail
- rows: Enter separated rows --> Table of Google Document: fail
- Plain text --> Microsoft Excel: ok
- columns: Tab separated columns --> Microsoft Excel: ok
- rows: Enter separated rows --> Microsoft Excel: ok
- How-to Create and Copy a Table in Google Mail (Gmail) from Excel - YouTube
further reading
from spreadsheet application to editor application
- Copy the table from Gmail/HTML ...
- Paste to MS Excel: the border line of table is missing
- Paste to MS Word: the border line of table is exists after posted
- Copy the table from MS Excel ...
- Paste to Gmail: the border line of table is missing
- Paste to MS Word: the border line of table is exists after posted
- Copy the table from MS Word ...
- Paste to Gmail: the border line of table is exists after posted
- Paste to MS Word: the border line of table is exists after posted
Excel online viewer
Google drive [Last visited: 2020-03-10]
- file limit: "Up to 5 million cells or 18,278 columns (column ZZZ) for spreadsheets that are created in or converted to Google Sheets."[2]
- file limit: 5MB[3]
Online Spreadsheet Maker | Create Spreadsheets for free - Zoho Sheet
- file limit: "Max number of cells with data = 1 million/workbook"[4]
Google spreadsheet
利用Google spreadsheet的函式,分析及統計可複選的問卷題目結果。
Excel 「樞紐分析表(Pivot Tables)」
區塊「Σ 值」的「計數 - 欄位名稱」(該欄位的項目個數),除非是沒有填入任何資料的「空值」,否則都會被計算加 1。包含
- 邏輯值 TRUE、FALSE、字串、數值、錯誤代碼 #NAME? 等資料,會被列入計算加 1
- IF 回傳的空值,也會被列入計算加 1 version: Excel 2013
Checking of data type
儲存格資料類型檢查與類型轉換
數字
- 預期: 可以「從最小到最大排序」、「從最大到最小排序」
- 異常: 如果是「從 A 到 Z 排序」、「從 Z 到 A 排序」,需要將通用格式,改成數值格式的年份:
- 法1: 將儲存成文字的數值轉換成數值格式 - Office 支援 [Last visited: 2018-11-05]
時間
- 預期: 可以「從最舊到最新排序」、「從最新到最舊排序」。
- 異常: 如果是「從 A 到 Z 排序」、「從 Z 到 A 排序」,需要將通用格式的年份,改成時間格式的年份:
時間: 年份
- 預期: 可以「從最舊到最新排序」、「從最新到最舊排序」。
- 異常: 如果是「從 A 到 Z 排序」、「從 Z 到 A 排序」,需要將通用格式的年份,改成時間格式的年份:
- 法1: 將 2010 改成 2010/01/01 ,再利用 YEAR 函數擷取出年份
- 法2: 將 2010 改成 2010/01/01 ,再利用 Tableau$ 轉換成年份 walk around approach!
Troubleshooting of Excel errors
Excel 效能議題
- 資料筆數大量時的替代方案
- 如果 Excel 資料筆數約百萬筆,操作速度約耗費數小時,可改用資料庫。資料庫資料處理後,再輸出成 Excel 檔,而不要在 Excel 檔上面進行資料處理或換算。
- 如果要刪除符合特定條件約一萬筆的資料列太慢時。改成將篩選出符合另一條件的資料列,再複製貼上到新的工作表,會比較快。如果直接複製貼上也花超過5分鐘時間,可以選擇直接貼上值。相關文章:解決 Excel 刪除資料太慢的問題
- 降低資料處理複雜度
- 全選工作表儲存格資料,複製後,選擇性貼上值到空白工作表。因為移除公式,所以另一工作表的操作速度會加快。 需要注意時間格式的數值會跑掉,變成一長串數字,需要額外設定儲存格格式成時間格式。
- 多重篩選條件會使用比較多的系統資源,在不使用篩選條件下,改成使用函數處理,會比較快。
- 電腦效能
- 軟體從 32位元 升級為 64位元版本,能較有效率使用記憶體。 e.g. Choose between the 64-bit or 32-bit version of Office - Office Support
- 關閉沒有在使用的 Excel 檔案,節省電腦效能
- 切換使用不同軟體
- 先使用 LibreOffice Calc 操作 ODS (OpenDocument Spreadsheet) 或 Excel XML 檔案格式,處理速度比較快。再輸出成 Excel 檔案格式。
Excel 匯入 CSV 檔案後,遇到欄位錯置
Excel 開啟 CSV 檔案,遇到欄位錯置
原因:
- 如果 CSV 檔案內的欄位值包含換行符號 (Return symbol),Excel 開啟時會出現錯誤:原本應該同一欄位,卻換行變成第二筆資料。
解決方法:
- 使用 LibreOffice 開啟 CSV 檔案,再轉換成 Excel 檔案。建議再用 Office Excel 開啟檔案確認有沒有問題
此文字檔案含有多餘單一工作表所能容納的資料。
Excel 匯入 CSV 檔案,遇到「此文字檔案含有多餘單一工作表所能容納的資料。」
原因:
- CSV 檔案資料筆數超出單一工作表所能容納的資料筆數上限:1,048,576 (rows)。[5]
解決方法:
- 限制筆數
不同試算表方案的工作表或儲存格的大小限制
Microsoft Excel (XLS or XLSX) & OpenDocument Spreadsheet (ODS) file limits
Merge data from different files
Merge multiple CSV / Text files by using Windows command (命令提示字元)[6]
- copy *.csv bundle.csv for different CSV files
- copy *.txt bundle.txt for different Text files
Merge Excel worksheets (copy data from multiple worksheets into one workbook)
PHP libraby
- PHPOffice/PhpSpreadsheet: A pure PHP library for reading and writing spreadsheet files
- rap2hpoutre/fast-excel: 🦉 Fast Excel import/export for Laravel
- Maatwebsite/Laravel-Excel: 🚀 Supercharged Excel exports and imports in Laravel
Further reading
References
Troubleshooting of ...
- PHP, cUrl, Python, selenium, HTTP status code errors
- Database: SQL syntax debug, MySQL errors, MySQLTuner errors or PostgreSQL errors
- HTML/Javascript: Troubleshooting of javascript, XPath
- Software: Mediawiki, Docker, FTP problems, online conference software
- Test connectivity for the web service, Web Ping, Network problem, Web user behavior, Web scrape troubleshooting
Template