Share via

嗨，Scripting Guy！

  • 發行項

嗨，Scripting Guy！

歡迎使用 TechNet 專欄，Microsoft Scripting Guys 會在此為您解答有關系統管理指令碼的常見問題。您有關於系統管理指令碼方面的問題嗎？請將電子郵件傳送到 scripter@microsoft.com。我們無法保證能夠逐一回答每個問題，不過我們會盡力而為。

還有，別忘了瞧瞧全新經過改良的嗨，Scripting Guy！過往文件。

今天的問題：要如何將固定寬度的檔案匯入 Microsoft Excel？

要如何將固定寬度的檔案匯入 Microsoft Excel？

問

嗨，Scripting Guy！要如何將固定寬度的檔案匯入 Microsoft Excel？

-- JP
答

JP，您好。先跟您招供一下，真是令人羞愧，又那麼一剎那 (真的只有一剎那) 我們懷疑起 Microsoft Excel 的能力。好啦！我們哪有那個膽？這純粹是誤解，不好意思。不知為何，您的問題乍看之下似乎很棘手，所以才會想到使用 ActiveX Data Objects (ADO) 等奇巧的指令碼撰寫技巧，來代替 Excel 處理固定寬度文字檔案，真是小看 Excel 了。

Excel：我們在此向您鄭重道歉。

 

給 Excel 的小紙條：老實說，我們不確定是否應該建議大家乾脆把其他軟體全部解除安裝，只用您就好，但這樣可能會得罪 Microsoft Word 使用者和現在仍使用 WordPerfect 的十一個人。我們考慮考慮再給您答覆。


現在是拋開過去的錯誤，回去工作的時候了。JP，我們假設您有一個像下面一樣簡單的固定寬度文字檔案：

 
First         Last              Department     
Gail          Erickson          Human Resources
Ken           Myer              Accounting     
Pilar         Ackerman          Research       
Jonathan      Haas              Accounting     
Syed          Abbas             Accounting

順便一提，我們知道 Excel 有一個叫做 OpenText 的方法可以把文字檔在試算表內開啟，根據經驗，這種方法最適合處理使用逗號或定位點分隔的數值檔案。但如果處理像您的的固定寬度檔案，就不知道了。咱們查詢過 Excel 文件，試著按照指示結果敗得一踏塗地。

通常這就是放棄的時候了，但想到這可能極具挑戰性，就決定堅持下去。如果真需要專業能力解決這個問題，說不定會贏得諾貝爾獎或什麼的。

我們先是撰寫一個指令碼，用上了所有必須的參數仍然沒有辦法，所以決定採用有系統的方式切入問題：首先準備一個指令碼，裡面只有一個包含文字檔案路徑的參數，以這裡為起點。雖知指令碼會失敗，但至少錯誤訊息有助於判斷還需要哪些參數還有設定參數的方式。

所以我們按照打算，先開始準備一個只有三行的指令碼：

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

objExcel.Workbooks.OpenText "C:\Scripts\Test.txt"

結果一言難盡，反正很成功：指令碼開啟文字等檔並將每個欄位和記錄都擺在適當的位置，我們沒想到會真的成功，但想想這大概是因為生命的本質本來就不複雜。我們接著添加更多記錄和欄位，然後把日期、數字和文字都參雜進去。再跑一次簡單的指令碼，居然又順利完成了。所以說，只要固定寬度檔案裡的欄位間至少有一個空格，只要呼叫 OpenText 方法，接下來的交給 Excel 就得了。

只有一個欄位的文字超出範圍，跑到另一個欄位時，才需要更複雜的指令碼。例如下面的這個檔案 (注意 Erickson-Wilkerson 這個名字)：

First         Last              Department     
Gail          Erickson-WilkersonHuman Resources
Ken           Myer              Accounting     
Pilar         Ackerman          Research       
Jonathan      Haas              Accounting     
Syed          Abbas             Accounting

遇到 Erickson-Wilkerson 時，Excel 就無法處理，因為欄位間並沒有空格作為區隔，Excel 乾脆把這個檔案當作只有一個欄位，而把所有的資料都放進欄位 A。跟我們期待的還有點距離。

幸好這個問題我們已經知道怎麼在 OpenText 方法內添加參數來解決。如果您有一個如上述「擠成一團」的文字檔案，可以使用類似下面的指令碼匯入檔案：

Const xlFixedWidth = 2

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.OpenText _
    "C:\Scripts\Test.txt",,,xlFixedWidth,,,,,,,,,Array(Array(0,1),Array(14,1),Array(32,1))

這個的確是比較複雜，容我們為您一一解說。首先定義一個叫做 xlFixedWidth 的常數，值設為 2。方才說過，Excel 沒辦法把這個檔案視為固定寬度的檔案，所以要使用這個常數來明確指定。

接著建立 Excel.Application 物件的執行個體，然後將 Visible 屬性設為 True，設定後者是為了能夠在畫面上看見 Excel。最後再呼叫 OpenText 方法，傳送一長串的參數：

objExcel.Workbooks.OpenText _
    "C:\Scripts\Test.txt",,,xlFixedWidth,,,,,,,,,Array(Array(0,1),Array(14,1),Array(32,1))

我們不打算解釋字串裡每一個空的參數，如果您有興趣，不妨瞧瞧 MSDN 上的《Excel VBA 語言參考》(英文)。簡單的說，剛才傳送的是文字檔案的路徑，幾對空白參數後，傳送常數 xlFixedWidth 通知 Excel 所處理的檔案類型。

接著來到這個部分：

Array(Array(0,1),Array(14,1),Array(32,1))

這裡要處理的是定義文字檔案內的每一個欄位 (也就是試算表裡的每一個欄)。所有的欄位定義必須以陣列的方式傳送，所以外部函數 Array 派上用場。那陣列裡的每一個元素到底代表什麼意思呢？例如 Array(0,1)？

問得好。每一個欄位定義必須以陣列的方式傳送，本例中為二維陣列，陣列中的第一個數字代表欄位中第一個字元的位置。第一欄始於字元位置 1，要將 Array 的開始位置設為 0，因為陣列通常是從 1 開始，而陣列在檔案內的開始位置一定比字元位置少 1。第二個欄位從字元位置 15 開始，所以第二個陣列元素的起點在 14 (15 – 1)。

我們也不喜歡，但就像民謠搖滾創作歌手約翰普萊恩唱的，「這就是世界運轉的方式」。

元素裡的第二個數字，也就是 Array(0,1) 裡的 1，代表欄位的資料類型。這裡替每個欄為使用值 1，代表泛型資料。您也可以指定下表中的任何一個值：

常數 值
xlDMYFormat 4
xlDYMFormat 7
xlEMDFormat 10
xlGeneralFormat 1
xlMDYFormat 3
xlMYDFormat 6
xlSkipColumn 9
xlTextFormat 2
xlYDMFormat 8
xlYMDFormat 5

剛才說過，這個方式稍微複雜，但能保證 Excel 正確地開啟並剖析您的文字檔案，就算資料有點複雜也不是問題。而且今天只不過用了只行指令碼，一點都沒有用到 ADO 或其他技術。對不起，Excel，我們知道錯了。

如需詳細資訊

查看嗨，Scripting Guy！- 過往文件

 

回到頁首 回到頁首