從 EXCEL 匯入 UTF8 CSV 變成亂碼? 使用 EXCEL 或 POWERBI 如何處理?

實務上，我們常常會轉存文字檔的編碼為 UTF8，因為這會有利於我們跨平台交換資料的便利性。再者有些企業經營的是國際市場，所以會有需要儲存日文、韓文、越南、泰文、各式歐洲語系的需求，如果只使用過往的台灣中文編碼 BIG5碼會有很多問題。

而很多人會為工作方便，常常會將 CSV 的檔案關聯直接設定為 EXCEL ，好處是直接在檔案總管點2下，檔案就直接會顯示在 EXCEL 上。但是如果你試著對於 UTF8 進行這樣的作業，你會發現如下的結果：「亂碼!!!!」

而且除了亂碼之外，其實你會發現有時候連資料筆數、資料欄位都整個錯亂；例如：你原先有50筆資料，但變成了48筆或是53筆資料。這可不是非同小可的錯誤啊，即使你只是想要求算總體營業額，也許不用知道細項的名目，但事已至此，你不動手處理這個問題也是不行。

 

圖示：直接點擊 UTF8 後的EXCEL 畫面

 

這時候就要借用 EXCEL 的「資料」→ 「從文字/CSV」功能；此功能會出現讓你選擇檔案編碼的選單：操作範例如下。這個功能在舊版的 EXCEL 中也有具備，可見 EXCEL 早就具備處理多國編碼的能力，但為什麼要藏在這麼隱密的地方呢? 這我就不知道啦。

而在新版的 EXCEL 2016中，其實整個介面都與 POWER QUERY 完全整併。所以你會發現後續看到的畫面與 POWERBI 中讀入 CSV 完全相同。

 

 

出現編碼選擇的畫面。原來系統一開始判讀編碼錯誤，誤認為這個編碼是 BIG5

 

將其選擇成正確的編碼型式(UTF8)；附註一提，如果你需要處理日文編碼的話，一樣比照辦理，找到適合的日文編碼就可以正確讀檔。

OK，這下就完全正常。

資料正確匯入後，就可以進行下一步的分析。

 

這邊我直接筆述比較同樣讀入CSV在 EXCEL2016、POWREBI 的作業程序：

「資料」→「從文字/CSV」→「選擇編碼」 (POWER QUERY)

「GET DATA」→ 「TEXT/CSV」→「選擇編碼」

就可以發現2者的程序是非常近似的，只是一般使用者不易察覺到 EXCEL 的這項功能，導致常常有人問到為什麼在 EXCEL 中是亂碼。

 

而在實務或是教學中，我個人是偏好直接用 POWERBI 或是 Tableau 直接來進行檔案的匯入。大部份的情況下，這2套軟體都能直接對應到實際的編碼，有時連選單都不用再進行處理。

Windows系統讀取包含日文、韓文的UTF8 文件 [R]

眾所皆知，w indows系統在處理編碼上是非常弱勢的；

相對於 linux 與 mac 在讀寫各式不同編碼的純文件時，只需要指定好 encoding 的內容就可以正常讀取；目前經過我交叉測試，在windows中如果UTF8文件中包含了日文、韓文的UTF8文件還是無法正確讀取…

將問題與解法整理成如下2張圖:


可依情境加以使用不同解決方案，這2種方式都可以正確讀取含有日文、韓文等文字的UTF8文件…若使用原始系統搭載的 read.csv() 則執行指令時會報錯!!

備註：

(1)readxl套件，請參考:
http://www.r-bloggers.com/get-data-out-of-excel-and-into-r-with-readxl/

(2)readr套件，請參考:
http://www.r-bloggers.com/readr-0-2-0/

readr還含許多方便的機能，例如猜文件編碼的函數
guess_encoding(文件名稱), 非常實用!!!

Windows 處理UTF8 CSV就是悲劇… Excel與R搭配時的解法…

Excel處理CSV UTF8 編碼就是悲劇!!!!

• excel 2016在英文版介面下，匯入文字檔(csv、txt)無法選擇UTF8編碼，只有2種編碼ansi、mac可選
• excel 2016在中文版介面下，匯入文字檔(csv、txt)，可選擇UTF8編碼及其他世界各國文字編碼
• 總之微軟放棄不是很想認真處理 UTF8 CSV的問題…

利用Excel協助Data Clean :

• 不要讓excel直接處理csv UTF8格式
• 正常讀取utf的文字編輯軟體打開uft8文件
• 於文字編輯軟體中全選文字→複製貼上至excel→excel中進行資料剖析(這樣比由匯入文字檔還快…)

• Data Clean成功後，直接轉存成xlsx

被excel存檔過後的csv會自動變成BIG5編碼 → 避免夜長夢多，請不要這樣做

excel要存檔成UTF8，有UTF8文字檔的 → 超難用!!!!

PowerBI 也無法處理 UTF8 csv → 存成預設為UTF8的 xlsx 保證沒問題

與R搭配時，不想管編碼問題直接用excel存成xlsx、xls:

• 避免處理編碼的問題時，直接把csv改存成xlsx或xls格式
• 安裝套件 install.packages(readxl) # 此套件可讀入單頁sheet
  • xlsx格式: 使用xml進行資料儲存，預設已是UTF8編碼;可將副標名改為zip後，解壓縮即可驗證
  • xls格式: 編碼套件 readxl 會幫忙轉成uft8
  • 所以 read_excel 指令中沒有指定編碼的選項!!!
• 讀入資料，記住如果xlsx、xls內有多張sheet要指定sheet，否則只能讀入一個sheet
  • 例: read_excel(/Users/xxx/Downloads/WeareJordan.xls,sheet=3)
  • 別忘了在 windows 底下目錄名稱要用2個\指定
    • 例: read_excel(c:\Users\zzz\Desktop\CHINA.xls )
• 在mac底下，讀入的檔名不可以是數字開頭的檔名，例如: 444_js.xls 要改成變數命名規則 js.xls ；winodws則無此限制，但為了未來跨平台，請直接以英文做為檔名開頭
• 在windows底下，檢視匯入的資料時，若非big5編碼內的文字會以uft8的編碼型式顯示；而在mac則無此問題

長治久安之道，資料量小時請存成xlsx，資料量多時請用資料庫管理…

• 資料量少的時候而且資料有大量與Excel協作機會時，可以考慮直接將資料亦寫成 xlsx 輸出
  • 安裝套件 install.packages(openxlsx)
  • 使用資料 write.xlsx() 寫檔至 xlsx
• 隨著資料愈來愈多，可以考慮存成直接支援uft8的資料庫系統；例如:sqlite可跨平台使用，且支援UTF8openxlsx
  • Excel亦支援各式資料庫直接進行連結