如何處理大體積 XLSX/CSV/TXT 檔案？

在開發過程中，可能會遇到這樣的需求，我們需要從本地的 Excel 或 CSV 等檔案中解析出資訊，這些資訊可能是考勤打卡記錄，可能是日曆資訊，也可能是近期賬單流水。但是它們共同的特點是資料多且繁雜，人工錄入的工作量龐大容易出錯，需要花費大量時間。那有沒有什麼方法能自動解析檔案並獲取有用資訊呢？

當這個檔案資料量也不是很多的時候，有很多前端工具可供選擇。例如 SheetJS，就提供了從 Excel、CSV 中解析出用資訊的很多方法，十分方便。

當資料量只是幾千條的程度的，選擇的餘地很多，但是一旦資料量級增加，處理就變得複雜。如果 XLSX/CSV 資料量達到了 100w+ 條，Office、WPS 想開啟看一下，都會需要很長的時間。

那又該如何從這樣大體積的 Excel/CSV/TXT 中解析出資料呢？

背景

下面我們通過一個假設的需求，來講述理解整個過程。假設我們需求是從本地 Excel、CSV、TXT（或者其他格式的）檔案中解析出資料，並經過清洗後存入本地資料庫檔案中。但是這些檔案體積可能是 5M、50M、500M 甚至更大。那麼在瀏覽器環境下如何上傳？Node 環境下應該如何解析？

首先，我們需要了解的是瀏覽器 Web 頁面如何上傳大體積檔案？

Web 頁面如何上傳大體積檔案？

Web 頁面一般也是可以上傳大檔案的，但是會面臨一個問題。如果要上傳的資料比較大，那麼整個上傳過程會比較漫長，再加上上傳過程的不確定因素，一旦失敗，那整個上傳就要從頭再來，耗時很長。

面對這個問題，我們可以通過將大檔案分成多份小檔案，每一次只上傳一份的方法來解決。這樣即使某個請求失敗了，也無需從頭開始，只要重新上傳失敗的那一份就好了。

如果想要使用這個方法，我們需要滿足以下幾項需求：

大體積檔案支援切片上傳
可以斷點續傳
可以得知上傳進度

首先看一下如何進行大檔案切割。Web 頁面基本都是通過 <input type='file' /> 來獲取本地檔案的。而通過 input 的 event.target.files 獲取到的 file，其實是一個 File 類的例項，是 Blob 類的子類。

Blob 物件表示一個不可變、原始資料的類檔案物件。它的資料可以按文字或二進位制的格式進行讀取，也可以轉換成 ReadableStream 來用於資料操作。簡單理解合一將 Blob 看做二進位制容器，表示存放著一個大的二進位制檔案。Blob 物件有一個很重要的方法：slice()，這裡需要注意的是 Blob 物件是不可變的，slice 方法返回的是一個新的 Blob，表示所需要切割的二進位制檔案。

slice() 方法接受三個引數，起始偏移量，結束偏移量，還有可選的 mime 型別。如果 mime 型別，沒有設定，那麼新的 Blob 物件的 mime 型別和父級一樣。而 File 介面基於 Blob，File 物件也包含了slice方法，其結果包含有源 Blob 物件中指定範圍的資料。

看完了切割的方法，我們就可以對二進位制檔案進行拆分了。拆分示例如下：

function sliceInPiece(file, piece = 1024 * 1024 * 5) {
  let totalSize = file.size; // 檔案總大小
  let start = 0; // 每次上傳的開始位元組
  let end = start + piece; // 每次上傳的結尾位元組
  let chunks = []
  while (start < totalSize) {
    // 根據長度擷取每次需要上傳的資料
    // File物件繼承自Blob物件，因此包含slice方法
    let blob = file.slice(start, end); 
    chunks.push(blob)

    start = end;
    end = start + piece;
  }
  return chunks
}

獲得檔案切割後的陣列後，就可以挨個呼叫介面上傳至服務端。


let file =  document.querySelector("[name=file]").files[0];

const LENGTH = 1024 * 1024 * 0.1;
let chunks = sliceInPiece(file, LENGTH); // 首先拆分切片

chunks.forEach(chunk=>{
  let fd = new FormData();
  fd.append("file", chunk);
  post('/upload', fd)
})

完成上傳後再至服務端將切片檔案拼接成完整檔案，讓 FileReader 物件從 Blob 中讀取資料。

當然這裡會遇到兩個問題，其一是面對上傳完成的一堆切片檔案，服務端要如知道它們的正確順序？其二是如果有多個大體積檔案同時上傳，服務端該如何判斷哪個切片屬於哪個檔案呢？

前後順序的問題，我們可以通過構造切片的 FormData 時增加引數的方式來處理。比如用引數 ChunkIndex 表示當前切片的順序。

而第二個問題可以通過增加引數比如 sourceFile 等（值可以是當前大體積檔案的完整路徑或者更嚴謹用檔案的 hash 值）來標記原始檔案來源。這樣服務端在獲取到資料時，就可以知道哪些切片來自哪個檔案以及切片之間的前後順序。

如果暫時不方便自行構架，也可以考慮使用雲服務，比如又拍雲端儲存就支援大檔案上傳和斷點續傳的。比如：

斷點續傳

在上傳大檔案或移動端上傳檔案時，因為網路質量、傳輸時間過長等原因造成上傳失敗，可以使用斷點續傳。特別地，斷點續傳上傳的圖片不支援預處理。特別地，斷點續傳上傳的檔案不能使用其他上傳方式覆蓋，如果需要覆蓋，須先刪除檔案。

名稱概念

檔案分塊：直接切分二進位制檔案成小塊。分塊大小固定為 1M。最後一個分塊除外。
上傳階段：使用 x-upyun-multi-stage 引數來指示斷點續傳的階段。分為以下三個階段: initate(上傳初始化), upload(上傳中), complete(上傳結束)。各階段依次進行。
分片序號：使用 x-upyun-part-id 引數來指示當前的分片序號，序號從 0 起算。
順序上傳：對於同一個斷點續傳任務，只支援順序上傳。
上傳標識：使用 x-upyun-multi-uuid 引數來唯一標識一次上傳任務, 型別為字串, 長度為 36 位。
上傳清理：斷點續傳未完成的檔案，會儲存 24 小時，超過後，檔案會被刪除。

可以看到，雲端儲存通過分片序號 x-upyun-part-id 和上傳標識 x-upyun-multi-uuid 解決了我們前面提到的兩個問題。這裡需要注意的是這兩個資料不是前端自己生成的，而是在初始化上傳後通過 responseHeader 返回的。

又拍雲初始化斷點續傳

前文說的都是使用 Web 頁面要如何上傳大檔案。接下來我們來看看 NodeJS 是如何解析、處理這類大體積檔案呢？

NodeJS 解析大體積檔案

首先需要明確一個概念 NodeJS 裡沒有 File 物件，但是有 fs（檔案系統) 模組。fs 模組支援標準 POSIX 函式建模的方式與檔案系統進行互動。\

POSIX 是可移植作業系統介面 Portable Operating System Interface of UNIX 的縮寫。簡單來說 POSIX 就是在不同核心提供的作業系統下提供一個統一的呼叫介面，比如在 linux 下開啟檔案和在 widnows 下開啟檔案。可能核心提供的方式是不同的，但是因為 fs 是支援 POSIX 標準的，因此對程式猿來說無論核心提供的是什麼，直接在 Node 裡調 fsPromises.open(path, flags[, mode]) 方法就可以使用。

這裡簡單用 Vue 舉例說明。Vue 在不同的環境下比如 Web 頁面或 Weex 等等的執行生成頁面元素的方式是不同的。比如在 Web 下的 createElement 是下方這樣：


export function createElement (tagName: string, vnode: VNode): Element {
  const elm = document.createElement(tagName)
  if (tagName !== 'select') {
    return elm
  }
  // false or null will remove the attribute but undefined will not
  if (vnode.data && vnode.data.attrs && vnode.data.attrs.multiple !== undefined) {
    elm.setAttribute('multiple', 'multiple')
  }
  return elm
}

在 Weex 下則是如下情況：

export function createElement (tagName: string): WeexElement {
  return document.createElement(tagName)
}

以上兩種情況下的 createElement 是不一樣的。同理，還有很多其他的建立模組或者元素的方式也是不同的，但是針對不同平臺，Vue 提供了相同的 patch 方法，來進行元件的更新或者建立。

import * as nodeOps from 'web/runtime![]()de-ops'\
import { createPatchFunction } from 'core![]()dom/patch'\
import baseModules from 'core![]()dom/modules/index'\
import platformModules from 'web/runtime/modules/index'\
\
// the directive module should be applied last, after all\
// built-in modules have been applied.\
const modules = platformModules.concat(baseModules)\
\
// nodeops 封裝了一系列DOM操作方法。modules定義了一些模組的鉤子函式的實現\
export const patch: Function = createPatchFunction({ nodeOps, modules })

import * as nodeOps from 'weex/runtime![]()de-ops'\
import { createPatchFunction } from 'core![]()dom/patch'\
import baseModules from 'core![]()dom/modules/index'\
import platformModules from 'weex/runtime/modules/index'\
\
// the directive module should be applied last, after all\
// built-in modules have been applied.\
const modules = platformModules.concat(baseModules)\
\
export const patch: Function = createPatchFunction({\
  nodeOps,\
  modules,\
  LONG_LIST_THRESHOLD: 10\
})

這樣，無論執行環境的內部實現是否不同，只要呼叫相同的 patch 方法即可。而 POSIX 的理念是與上面所舉例的情況是相通的。

簡單瞭解了 POSIX，我們回到 fs 模組。fs 模組提供了很多讀取檔案的方法，例如：

fs.read(fd, buffer, offset, length, position, callback)讀取檔案資料。要操作檔案，得先開啟檔案，這個方法的fd，就是呼叫 fs.open 返回的檔案描述符。
fs.readFile(path[, options], callback) 非同步地讀取檔案的全部內容。可以看做是fs.read的進一步封裝。

使用場景如下：

import { readFile } from 'fs';

readFile('/etc/passwd','utf-8', (err, data) => {
  if (err) throw err;
  console.log(data);
});

因為 fs.readFile 函式會緩衝整個檔案，如果要讀取的檔案體積較小還好，但是如果檔案體積較大就會給記憶體造成壓力。那有沒有對記憶體壓力較小的方式來讀取檔案呢？

有的，我們今天的主角 stream 流登場。

stream

stream 流是用於在 Node.js 中處理流資料的抽象介面。 stream 模組提供了用於實現流介面的 API。流可以是可讀的、可寫的、或兩者兼而有之。

fs 模組內有個 fs.createReadStream(path[, options])方法，它返回的是一個可讀流，預設大小為 64k，也就是緩衝 64k。一旦內部讀取緩衝區達到這個閾值，流將暫時停止從底層資源讀取資料，直到消費當前緩衝的資料。

消費資料的方法可以是調 pipe() 方法，也可以被事件直接消費。

// pipe 消費
readable.pipe(writable)

// 或者
// 事件消費
readable.on('data', (chunk) => {
  writable.write(chunk);
});
readable.on('end', () => {
  writable.end();
});

除了可讀流，也有可寫流 fs.createWriteStream(path[, options])，可以將資料寫入檔案中。

好了，所需要的前置知識基本就介紹完畢了，回到正題。假如我們有一個資料夾，裡面存放著數十個 XLSX/CSV 檔案，且每一個體積都超過了 500M。那該如何從這些檔案中讀取資訊，並寫入資料庫檔案中呢？

批量解析 CSV 檔案

假設我們需要解析的檔案路徑已經是知道的，可以通過路徑獲取到檔案，那麼將這些路徑存入一個陣列並命名為 needParseArr，我們需要按照順序一個個解析這些 CSV、XLSX 檔案資訊，並清洗然後寫入資料庫。

首先，是一個個讀的邏輯 (readOneByOne)。

async readOneByOne () {
   try {
    for (let i = 0; i < needParsePathArr.length; i++) {
      const filePath = needParsePathArr[i]
      console.log(`解析到第${i}個檔案，檔名：${filePath}`)
      await streamInsertDB(filePath)
    }
  } catch (err) {

  }
}

streamInsertDB 是我們的主要邏輯的入口。

async function streamInsertDB (filePath) {
  return new Promise((resolve, reject) => {
    const ext = path.extname(filePath)
    // 判斷了下檔案型別
    if (ext === '.csv') {
      // 解析csv
      parseAndInsertFromCSV(filePath, resolve, reject)
    } else if (ext === '.xlsx') {
      // 自執行函式
      (async function getName () {
        try {
          // 先轉換成csv。也可以不轉換，直接解析xlsx，後文會詳細解釋。
          const csvFileName = await convertXlsx2Csv(filePath)
          // 複用解析csv的邏輯
          parseAndInsertFromCSV(csvFileName, resolve, reject)
        } catch (error) {
          reject(`error: ${error.message || error}`)
        }
      })()
    }
  })
}

parseAndInsertFromCSV 中就是使用我們前面所提到的知識點的主要陣地。 下面簡單介紹一下各個函式：

chardet：這個函式的作用是監測 CSV 檔案的編碼格式的，畢竟不是每個 CSV 都是 UTF-8 編碼，帶中文的 CSV 編碼型別可能是 GBK 或者 GB18030、GB18031 等等，這種格式不經過處理直接讀取，中文會顯示為亂碼。所以需要執行轉換的函式 iconv 轉換一下。
pipe：可以用來建立管道鏈，可以理解為 pipe 的作用就像一個管道，可以對目標流邊讀邊寫，這裡我們是一邊解碼一邊重新編碼。
insertInBlock：這個函式是獲取到一定數量的資料後（本例中是從 CSV 中解析出 3 萬條左右資料的時候），暫停一下來執行一些操作，比如寫入資料庫或者對裡面的資料進行過濾、處理等等，根據實際需要來定。
csv：這個函式的作用就是讀出流中的具體資料的。

具體邏輯解釋可以看註釋。

const chardet = require('chardet');
const csv = require('fast-csv'); // 比較快解析csv的速度的工具
const iconv = require('iconv-lite');

const arrayFromParseCSV = []  // 存放解析出來的一行行csv資料的
let count = 0 // 計數
// resolve, reject 是外部函式傳進來的，用以判斷函式執行的狀態，以便正確的進行後續邏輯處理
function parseAndInsertFromCSV (filePath, resolve, reject) {
  const rs = fs.createReadStream(filePath)  // 建立可讀流
  // 這裡的防抖和柯里化
  const delayInsert = debounce((isEnd, cb = () => {}) => insertInBlock(isEnd, cb, rs, resolve, reject), 300)
  /// sampleSize: 5120 表示值讀取檔案前5120個位元組的資料，就可以判斷出檔案的編碼型別了，不需要全部讀取
  chardet.detectFile(filePath, { sampleSize: 5120 }).then(encoding => {
    // 如果不是UTF-8編碼，轉換為utf8編碼
    if (encoding !== 'UTF-8') {
      rs.pipe(iconv.decodeStream(encoding))
        .pipe(iconv.encodeStream('UTF-8'))
        .pipe(csv.parse({ header: false, ignoreEmpty: true, trim: true })) // 解析csv
        .on('error', error => {
          reject(`解析csv error: ${error}`)
        })
        .on('data', rows => {
          count++ // 計數，因為我們要分塊讀取和操作
          arrayFromParseCSV.push(rows) // 讀到就推送到陣列中
          if (count > 30000) { // 已經讀了30000行，我們就要先把這3w行處理掉，避免佔用過多記憶體。
            rs.pause() // 暫停可讀流
            delayInsert(false) // false 還沒有結束。注意：即使rs.pause, 流的讀取也不是立即暫停的，所以需要防抖。
          }          
        }).on('end', rowCount => {
          console.log(`解析完${filePath}檔案一共${rowCount}行`)
          delayInsert(true, () => {
            rs.destroy() // 銷燬流
            resolve('ok') // 一個檔案讀取完畢了
          })
        })
    }
  })
}

清洗資料和後續操作的邏輯在 insertInBlock 裡。

function insertInBlock (isEnd, cb, filePath, resolve, reject) {
  const arr = doSomethingWithData() // 可能會有一些清洗資料的操作
  // 假如我們後續的需求是將資料寫入資料庫
  const batchInsert = () => {
    batchInsertDatabasePromise().then(() => {
      if (cb && typeof cb === 'function') cb()
      !isEnd && rs.resume() // 這一個片段的資料寫入完畢，可以恢復流繼續讀了
    })
  }
  
  const truely = schemaHasTable() // 比如判斷資料庫中有沒有某個表，有就寫入。沒有先建表再寫入。
  if (truely) { //
     batchInsert()
   } else {
     // 建表或者其他操作，然後再寫入
     doSomething().then(() => batchInsert())
  }
}

這樣，解析和寫入的流程就完成了。雖然很多業務上的程式碼進行了簡略，但實現上大體類似這個流程。

批量解析 XLSX 檔案

轉化成 CSV？

在前面的程式碼例項中，我們利用了利用可寫流 fs.createWriteStream 將 XLSX 檔案轉換成 CSV 檔案然後複用解析 CSV 。這裡需要注意的是，在將資料寫入 CSV 格式檔案時，要在最開始寫入 bom 頭 \ufeff。此外也可以用 xlsx-extract 的 convert 函式，將 XLSX 檔案轉換成 TSV。


const { XLSX } = require('xlsx-extract')
new XLSX().convert('path/to/file.xlsx', 'path/to/destfile.tsv')
    .on('error', function (err) {
        console.error(err);
    })
    .on('end', function () {
        console.log('written');
    })

可能有人會疑惑，不是 CSV 麼，怎麼轉換成了 TSV 呢？

其實 tsv 和 CSV 的區別只是欄位值的分隔符不同，CSV 用逗號分隔值（Comma-separated values），而 TSVA 用的是製表符分隔值（Tab-separated values）。前面我們用來快速解析 CSV 檔案的 fast-csv 工具是支援選擇製表符\t作為值的分隔標誌的。

import { parse } from '@fast-csv/parse';
const stream = parse({ delimiter: '\t' })
    .on('error', error => console.error(error))
    .on('data', row => console.log(row))
    .on('end', (rowCount: number) => console.log(`Parsed ${rowCount} rows`));

直接解析？

那是否可以不轉換成 CSV，直接解析 XLSX 檔案呢？其實也是可行的。

const { xslx } = require('xlsx-extract') // 流式解析xlsx檔案工具
// parser: expat, 需要額外安裝node-expat,可以提高解析速度。
new XLSX().extract(filePath, { sheet_nr: 1, parser: 'expat' })
    .on('row', function (row) {
        // 每一行資料獲取到時都可以觸發
      }).on('error', function (err) {
        // error
     });

但是這種方式有一個缺陷，一旦解析開始，就無法暫停資料讀取的流程。xlsx-extract 封裝了 sax，沒有提供暫停和繼續的方法。

如果我們直接用可讀流去讀取 XLSX 檔案會怎麼樣呢？

const readStream = fs.createReadableStream('path/to/xlsx.xlsx')

可以看到現在流中資料以 buffer 的形式存在著。但由於 xlsx 格式實際上是一個 zip 存檔的壓縮格式，存放著 XML 結構的文字資訊。所以可讀流無法直接使用，需要先解壓縮。

解壓縮可以使用 npm 包 unzipper 。


const unzip = require('unzipper')
const zip = unzip.Parse();
rs.pipe(zip)
  .on('entry', function (entry) {
    console.log('entry ---', entry);
    const fileName = entry.path;
    const { type } = entry; // 'Directory' or 'File'
    const size = entry.vars.uncompressedSize; // There is also compressedSize;
    if (fileName === "this IS the file I'm looking for") {
      entry.pipe(fs.createWriteStream('output/path'));
    } else {
      entry.autodrain();
    }
  })

現在我們已經解壓了檔案。

前面提到，xlsx-extract 是封裝了 sax，而 sax 本身就是用來解析 XML 文字的，那我們這裡也可以使用 sax 來對可讀流進行處理。

sax 解析的原始碼可以看這裡，大致是根據每一個字元來判斷其內容、換行、開始、結束等等，然後觸發對應事件。

const saxStream = require('sax').createStream(false);
saxStream.on('error', function (e) {
  console.error('error!', e);
});
saxStream.on('opentag', function (node) {
  console.log('node ---', node);
});
saxStream.on('text', (text) => console.log('text ---', typeof text, text));

最後將兩者結合起來：

const unzip = require('unzipper');
const saxStream = require('sax').createStream(false);
const zip = unzip.Parse();

saxStream.on('error', function (e) {
  console.error('error!', e);
});
saxStream.on('opentag', function (node) {
  console.log('node ---', node);
});
saxStream.on('text', (text) => {
    console.log('text ---', typeof text, text)
});

rs.pipe(zip)
  .on('entry', function (entry) {
    console.log('entry ---', entry);
    entry.pipe(saxStream)
  })

使用本地的 XLSX 檔案測試後，控制檯列印出以下資訊：

這些資訊對應著 XLSX 文件裡的這部分資訊。Node 裡列印的 ST SI，代表著 xml 的標籤。

這樣，其實我們也拿到了 XLSX 裡的資料了，只不過這些資料還需要清洗、彙總、一一對應。同時由於我們是直接在可讀流上操作，自然也可以 pause、resume 流，來實現分塊讀取和其他操作的邏輯。

總結

對體積較小的 XLSX、CSV 檔案，基本 SheetJS 就可以滿足各種格式檔案的解析需求了，但是一旦文件體積較大，那麼分片、流式讀寫將成為必不可少的方式。

通過前面例子和程式碼的分解，我們可以瞭解這類問題的解決辦法，也可以擴充對類似需求的不同解決思路。一旦我們能對大體積檔案的分塊處理有一定的概念和了解，那麼在遇到類似問題的時候，就知道實現思路在哪裡了。

如何處理大體積 XLSX/CSV/TXT 檔案？

相關文章