從 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套軟體都能直接對應到實際的編碼，有時連選單都不用再進行處理。

Excel 與 PowerBI 協作的第一步

常有人問 Excel 匯入到 PowerBI 有什麼技巧?

會這樣問的人，可能是發現在實務中，因為大家工作繁忙，所以常常將 Excel 的用途給無限提升。有人直接用 Excel 當做週報格式、有人用來繪製課表、有人用來編寫個人收支計劃。是故常常有許多 Excel 的資料其實是不適合/無法匯入 PowerBI。

 

在此我們舉一個最簡單的範例，假如我們有以下的資料。在匯入 PowerBI 前我們先將其轉換為 Table 格式。如果能順利轉換成 Table 的格式，保證 100%可以匯入 PowerBI

 

 

將資料全選後( Ctrl + A) 在 Excel 的插入 → 表格 (Table)

轉變成如下表，藍白相間的格式。至此，打完收工。

 

 

轉換為 Table 格式後的資料有什麼特點呢? 或者需要什麼樣的前提?

( ROW : → , Column: ↓，因為行列的表示法，台灣與中國日本剛好翻譯相反，故我通常不用以避免 GOOGLE 資料時搞得自己混亂異常)

1. 一組 ROW 就是一筆資料(例如：編號1的這一位，他就是畢業於土木系，年齡是23歲)
2. 一組 Column 就是相同屬性的資料(例如：年齡這個欄位放的都是數字)
3. Table 中間不能夾藏一組空白的資料(例如：原始資料中，你如果把第5筆資料給完整刪掉，將轉換成 Table時只會有前面4筆資料)
4. 一定要有欄位名稱(例：「年齡」這個位於C1 欄位變數名稱一定要有，因為這關於後續匯入 PowerBI 的作業
5. 儲存格可以有空白(例：如果編號14尚未提供年齡資訊，該儲存格為空白是可以接受的)
6. 千萬不可以在要轉成 Table 的資料中有合併儲存格!!!

 

其實 Table 的概念是從資料庫中借過來的，如果有使用過資料庫軟體的經驗，就會發現 Table 非常類似資料庫中的資料表(Table)；此處台灣微軟將其翻譯為表格(Table)可能是為了讓使用者不感到害怕，但缺點就是其實很多進階的使用者會將其與一般的表格給搞混。

再者最近在 R 中揭起了 Tidy Data 的風潮，而 Excel 中的 Table 基本上也符合 Tidy Data 的要求；Tidy Data 指的是什麼呢?

 

1. Each variable forms a column.
2. Each observation forms a row.
3. Each type of observational unit forms a table.

有沒有與 Excel Table 的要求很接近呢?

 

當你在 Excel 後設定好 Table 後，其實對於你資料分析相關的作業都會變得非常方便。例如：排序、製作樞紐分析圖表…等等。