巨大なデータファイルのインポート(Microsoft Excel)
Excelには、ワークシートに含めることができる行数に最大65,535の制限があります。ただし、この数を超える行を含む生データファイルが存在する可能性は非常に高くなります。そのファイルをExcelにインポートする必要がある場合は、Excel2007以降のバージョンにアップグレードしないとほとんど不可能に見える可能性があります。 (これらの新しいバージョンは65,535行の制限を超えました。)ただし、実行できることがいくつかあります。
1つの可能性は、生のテキストファイル(インポートするファイル)のコピーを作成してから、各ファイルのサイズを縮小することです。たとえば、Excelにインポートする必要のある行が合計110,000行あり、65,535行の制限の下で操作している場合、生のテキストファイルのコピーを2つ作成できます。最初のテキストファイルの後半と2番目の前半を削除します。したがって、最初のファイル(現在は55,000行)を1つのワークシートにインポートし、2番目のファイル(これも55,000行)をインポートできます
秒に。
入力ファイルを分割したくない場合は、ファイルをAccessにインポートすることを検討してください。 Excelとは異なり、Accessにはインポートできる行数に事実上制限がありません。次に、Accessでファイルを操作するか、ファイルの一部をエクスポートしてExcelで使用することができます。
最後に、マクロを使用して、大きなソースファイルのレコードをインポートできます。これを行うには多くの方法がありますが、アプローチの背後にある基本的な考え方は、ソースファイルから各行をフェッチし、それをワークシートの新しい行に配置することです。マクロは、配置された行数を追跡し、必要に応じて新しいワークシートに切り替える必要があります。
Public Sub LoadFile() Dim strLine As String Dim I As Long Dim J As Long Dim iLen As Integer Dim iSh As Integer Dim lL As Long Dim sDelim As String Dim MaxSize As Long sDelim = Chr(9) MaxSize = 65000 I = 0 Open "C:\MyDir\MyFile.txt" For Input As #5 Do While Not EOF(5) iSh = (I / MaxSize) + 1 lL = I Mod MaxSize Line Input #5, strLine If Right(strLine, 1) <> sDelim Then strLine = Trim(strLine) & sDelim End If J = 0 Do While Len(strLine) > 1 iLen = InStr(strLine, sDelim) Worksheets("Sheet" & iSh).Offset(lL, J).Value = _ Trim(Left(strLine, iLen - 1)) strLine = Trim(Right(strLine, Len(strLine) - iLen)) J = J + 1 Loop I = I + 1 Loop Close #5 End Sub
このマクロは、データを格納するのに十分なワークシートがブックに既にあり、それらにSheet1、Sheet2、Sheet3などの番号が付けられていることを前提としています。プログラムでチェックする2つの変数は、sDelimとMaxSizeの設定です。 1つ目は、読み取られる情報のフィールド区切り文字として使用される文字を指定します。 2番目は、各ワークシートに必要な最大行数を指定します。 (Excelのバージョンで許可されている値よりも大きいMaxSizeを設定しないでください。)
最後に、マクロがテキストファイルMyFile.txtを開くことに注意してください。このOpenステートメントを変更して、インポートする実際のソースファイルを開くようにします。
注:
このページ(または_ExcelTips_サイトの他のページ)で説明されているマクロの使用方法を知りたい場合は、役立つ情報を含む特別なページを用意しました。
_ExcelTips_は、費用効果の高いMicrosoftExcelトレーニングのソースです。
このヒント(2533)は、Microsoft Excel 97、2000、2002、および2003に適用されます。