解決 Navicat 「存放裝置空間不足,無法完成此操作」問題訊息

問題: 匯出資料庫查詢結果為 Excel 檔案時,Navicat 顯示「[Rows xxx][Exp] 存放裝置空間不足,無法完成此操作」問題訊息。該問題會導致匯出的結果筆數有少。


了解問題:

目前發現可能兩種原因會造成這個問題,由於沒有看到相關說明文件,如果發現有漏掉,還請在文章下方留言。

發現問題原因主要來自微軟 Excel 的規格及限制 中儲存格的兩個限制
(1) 一個儲存格可以包含的字元總數: 32,767 個字元、
(2) 公式內容的長度:  8,192 個字元」。字元的英文是 characters,每一個中文字或英文字,都算是一個字元。

為了確認資料是不是超過 Excel 儲存格限制,先利用 MySQL CHAR_LENGTH() 函數,了解欄位的字元數,範例 SQL 語法:
SELECT CHAR_LENGTH(`string_column`) FROM `table` ORDER BY
CHAR_LENGTH(`string_column`) DESC 
另外錯誤訊息的 [Rows xxx] 也提示了出問題是資料列中的第 xxx 行。如果要在 Excel 計算字元長度,請用 LEN 函數。(更新: Navicat v. 11 錯誤訊息 [Row1] 不代表問題出在第 1 列)

解決方式:

限制 (1) 一個儲存格可以包含的字元總數: 32,767 個字元。
利用 MySQL SUBSTRING() 函數將超過 32,767 個字元的文字刪除,範例 SQL 語法:
SELECT SUBSTRING(`string_column`, 1, 32767)
FROM `table`
限制 (2) 公式內容的長度: 8,192 個字元
實際資料測試,如果欄位內容是以等號 (= 或 =) 、加號 (+ 或 +)、減號 (- 或 -) 或 At 符號 又稱小老鼠 (@ 或 @) 等符號,都會被視作公式。將欄位內容的第一個字前面插入單引號,將誤判為公式的文字內容轉換成字串。利用 MySQL CONCAT() 函數加入單引號,範例 SQL 語法:
SELECT CONCAT('\'', `string_column`)
FROM `table`
也可以加入判斷第一個字是不是以上述符號開頭,改寫 SQL 語法:

SELECT
IF(
LEFT(`string_column`, 1) IN ('=', '=', '+', '+', '-', '-', '@', '@'),
CONCAT('\'', `string_column`),
`string_column`
)
FROM `table`

將上方兩個範例 SQL 語法整合:

SELECT
SUBSTRING(
IF(
LEFT(`string_column`, 1) IN ('=', '=', '+', '+', '-', '-', '@', '@'),
CONCAT('\'', `string_column`),
`string_column`
) ,
1,
32767)
FROM `table`

 完成!


沒有作用的解決方法:

  • 原以為是硬碟空間不足,造成「存放裝置空間不足,無法完成此操作」錯誤,但是實際上硬碟空間仍然足夠。


參考資料:
  1. Excel 的規格及限制
  2. MySQL SUBSTRING() function - w3resource
  3. MySQL CONCAT() function - w3resource
  4. How to troubleshoot errors when you save Excel workbooks
  5. 資料表 - Navigate Wiki
  6. What does the @ symbol do in Excel? - Quora
圖片素材:
更新記錄:

  • 加上 @ 符號的處理 2016/9/10
  • 加上 + 符號的處理 2016/10/14
  • 加上全型符號 = + -@ 符號的處理 2017/9/19


留言