この記事では、ExcelのマクロでCSVファイルを複数のファイルに分割する方法についてご説明します。
【動画】ExcelのマクロでCSVファイルを複数のファイルに分割する実際の動き
本題に入る前に、まずは次の動画をご覧ください。
分割の元となるCSVファイルをADODB.streamを使って読み込み、その読み込んだデータを1行ずつ新たなCSVファイルに書き込んで保存していきます。
今回は書き込む最大行を決めているので、その最大行数でCSVを書き込み保存し、その最大行数を超えるデータは別に新たにCSVファイルを生成してそのCSVファイルに残りを書き込んでいきます。
分割の元となるCSVファイルの行数分CSVファイルに書き込み保存するまで繰り返し行います。
前提：今回のCSVファイルの文字コードはUTF-8
今回の説明で扱うCSVファイルの文字コードはUTF-8のCSVを扱います。
マクロ作成の流れ
このインスタンスは、CSVファイルを読み込んだり、CSVファイルにデータを書き込みなどの操作を行うのに必要です。
Excelファイルの例
今回は次のExcelファイルを用意しました。
readFile
名前がreadFileのセルには、分割の元となるCSVファイルの置き場をフルパスで入力します。
maxLine
名前がmaxLineのセルには、何行で分割するか、その行数を入力します。
例えば500行ずつ分割したい場合は500と入力します
outputPath
名前がoutputPathのセルには、分割したCSVファイルの出力先を入力します。
各セルに値を入力したサンプルは下の画像の通りです。
コードの例
Option Explicit
Sub test()
Dim ws As Worksheet 'ワークシート用変数
Dim readFile As String '読み込むファイルの名前
Dim maxRows As Long '分割する1ファイルの最大行数
Dim OutputPath As String '出力ファイルの名前
Dim strm As adodb.stream 'バイナリ データまたはテキストのストリームのインスタンス用変数
Dim fso As FileSystemObject 'FileSystemObjectのインスタンス用変数
Dim folder As folder 'フォルダ用変数
Dim readFileStr() As String '読み込んだ行データ
Dim cnt As Long 'カウンタ用変数
Dim fnCnt As Long 'ファイル名用カウンタ変数
Dim outputFile As String '出力するファイルの名前
Dim tmpData() As Byte '一時格納用配列
'出力するファイル名の接頭文字列
Const newFilePrefxStr As String = "Output"
'シートを読み込む
Set ws = Worksheets("top")
'読み込むファイルの名前を取得する
readFile = ws.Range("readFile").Value
'分割する1ファイルの最大行数を取得する
maxRows = ws.Range("maxLine").Value
'出力先の場所を取得する
If Right(ws.Range("outputPath").Value, 1) <> "\" Then
'入力されたパスの末尾に「\」が付いていない場合
'入力されたパスの末尾に「\」を付けてoutPutPathに格納する
OutputPath = ws.Range("outputPath").Value & "\"
Else
'入力されたパスの末尾に「\」が付いている場合
'入力されたパスをoutPutPathに格納する
OutputPath = ws.Range("outputPath").Value
End If
'データを読み込む用のバイナリ データまたはテキストのストリームのインスタンスを生成する
Set strm = New ADODB.stream
'FileSystemObjectのインスタンスを生成する
Set fso = New FileSystemObject
'ファイルのコピー先のフォルダを取得する
Set folder = fso.GetFolder(OutputPath)
If folder.Files.count > 0 Then
'フォルダ内にファイルが存在する場合
'フォルダ内のファイルを全て削除する
fso.DeleteFile OutputPath & "*.*"
End If
With strm
'文字セットにUTF-8を設定する
.Charset = "UTF-8"
'streamを開く
.Open
'ファイルからデータを読み込む
.LoadFromFile readFile
'読み込んだデータを、行ごとに配列に格納する(vbNewLine(改行文字)を区切り文字とし、1行ずつ配列に格納)
readFileStr = Split(.ReadText(-1), vbNewLine)
'Streamを閉じる
.Close
End With
For cnt = 1 To UBound(readFileStr)
' 出力ファイル名を決定する
If cnt Mod maxRows = 1 Then
'cntをmaxRowsで割って余りが1になる場合
'ファイル名用カウンタ変数を1増やす
fnCnt = fnCnt + 1
'出力するファイル名の接頭文字列とファイル名用カウンタ変数の値、そして拡張子「.csv」を結合してファイル名を生成し、outputFileNMに格納する
outputFile = OutputPath & newFilePrefxStr & fnCnt & ".csv"
'文字セットにUTF-8を設定する
strm.Charset = "UTF-8"
'streamを開く
strm.Open
End If
With strm
'行データに改行コードを結合させて出力ファイルに書き込む
.WriteText readFileStr(cnt - 1) & vbNewLine
If cnt Mod maxRows = 0 Or cnt = UBound(readFileStr) Then
'①データを読み込んだ回数が分割する1ファイルの最大行数と同じ場合
'②データを読み込んだ回数と、読み込み元のデータ行が同じ場合
'→①②のどちらかの条件を満たす場合
'Streamオブジェクトの現在の位置を0に設定する
.Position = 0
'Stream内のデータの型をバイナリデータに設定する
.Type = adTypeBinary
'(バイナリデータ後)Streamオブジェクトの現在の位置を3に設定する(3バイト目から読み取る)
.Position = 3
'一時的に配列tmpDataに格納する
tmpData = .Read
'Streamを一旦閉じる
.Close
'再度Streamを開く
.Open
'Streamに書き込む
.Write tmpData
'ファイルを保存する
.SaveToFile outputFile, adSaveCreateOverWrite
'Stream内のデータの型をテキストデータに設定する(戻す)
.Type = adTypeText
'streamを閉じる
.Close
End If
End With
DoEvents
Next cnt
'後処理
Set fso = Nothing
Set folder = Nothing
Set strm = Nothing
End Sub
注目すべきコード①
最初に見て頂きたいのは18行目です。
'出力するファイル名の接頭文字列
Const newFilePrefxStr As String = "Output"
コードの説明
以上のコードは、出力するファイルの名前の先頭の文字列を取得するコードです。
今回のサンプルでは、出力するファイルの名前の先頭の文字列を「Output」に指定しています。
出力されるファイルの名前のイメージは下の画像の通りです。
出力されたファイル名の先頭に「Output」の文字列が付けられています。
注目すべきコード②
次に見て頂きたいのは48行目から51行目です。
'データを読み込む用のバイナリ データまたはテキストのストリームのインスタンスを生成する
Set strm = New ADODB.stream
'FileSystemObjectのインスタンスを生成する
Set fso = New FileSystemObject
コードの説明
このコードは、データを読み込む用のバイナリ データまたはテキストのストリームのインスタンスと、FileSystemObjectのインスタンスを生成を生成するコードです。
コードの詳細
48行目のデータを読み込む用のバイナリ データまたはテキストのストリームのインスタンスは、CSVファイルのデータを読み込んだり、書き込んだりするのに必要です。
51行目のFileSystemObjectのインスタンスは、ファイルの存在チェックとファイルを削除するのに必要です。
注目すべきコード③
次に見て頂きたいのは65行目から82行目です。
With strm
'文字セットにUTF-8を設定する
.Charset = "UTF-8"
'streamを開く
.Open
'ファイルからデータを読み込む
.LoadFromFile readFile
'読み込んだデータを、行ごとに配列に格納する(vbNewLine(改行文字)を区切り文字とし、1行ずつ配列に格納)
readFileStr = Split(.ReadText(-1), vbNewLine)
'Streamを閉じる
.Close
End With
コードの説明
以上のコードは、分割元のCSVファイルを配列readFileStrに読み込む処理のコードです。
コードの詳細
68行目のコードは、Streamの文字セットにUTF-8を設定する処理です。
71行目のコードは、Streamを開く処理です。
74行目のコードは、CSVファイルからデータを全てStreamに読み込む処理です。
77行目のコードは、読み込んだCSVファイルのデータを、改行文字で区切って配列readFileStrに格納している処理です。
80行目のコードは、Streamを閉じる処理です。
注目すべきコード④
次に見て頂きたいのは84行目です。
For cnt = 1 To UBound(readFileStr)
コードの説明
以上のコードは、読み込んだCSVファイルのデータ分処理を繰り返すFor文です。
注目すべきコード⑤
次に見て頂きたいのは87行目から103行目です。
' 出力ファイル名を決定する
If cnt Mod maxRows = 1 Then
'cntをmaxRowsで割って余りが1になる場合
'ファイル名用カウンタ変数を1増やす
fnCnt = fnCnt + 1
'出力するファイル名の接頭文字列とファイル名用カウンタ変数の値、そして拡張子「.csv」を結合してファイル名を生成し、outputFileNMに格納する
outputFile = OutputPath & newFilePrefxStr & fnCnt & ".csv"
'文字セットにUTF-8を設定する
strm.Charset = "UTF-8"
'streamを開く
strm.Open
End If
コードの説明
以上のコードは、出力するファイルの名前とそのファイルの文字セットの設定、そしてStreamを開く処理です。
読み込んだCSVファイルのデータを出力するためのCSVファイルを新たに作成するために、以上のコードを実行します。
コードの詳細
87行目のIF文は、CSVファイルのデータの読み込んだ行位置から、ExcelのA5のセルで設定した行数を割った値が1かどうかを判定するIF文です。
ExcelのA5のセルで設定した行数の次、例えば501行目、1001行目、1501行目…のタイミングで新たなCSVファイルを生成し、そのCSVファイルに対して読み込んだCSVファイルのデータを書き込むので、値が1かどうかを判定します。
92行目では、出力するファイル名に項番を含ませるため、ファイル名用カウンタ変数を1増やします。
95行目のコードでは、出力するファイル名の接頭文字列とファイル名用カウンタ変数の値、そして拡張子「.csv」を結合してファイル名を生成し、outputFileNMに格納します。
98行目のコードでは出力するファイルの文字セットにUTF-8を設定し、101行目ではstreamを開きます。
これで読み込んだCSVファイルのデータを、新たに生成するCSVファイルに書き込み準備が整いました。
注目すべきコード⑥
次に見て頂きたいのは108行目です。
'行データに改行コードを結合させて出力ファイルに書き込む
.WriteText readFileStr(cnt - 1) & vbNewLine
コードの説明
以上のコードは、読み込んだCSVファイルのデータを、WriteTextを実行してStreamに書き込む処理のコードです。
読み込んだCSVファイルのデータには改行コードを結合させて、CSVファイルが1つなぎで延々と書き込まれないよう改行させます。
注目すべきコード⑥
次に見て頂きたいのは110行目から146行目です。
If cnt Mod maxRows = 0 Or cnt = UBound(readFileStr) Then
'①データを読み込んだ回数が分割する1ファイルの最大行数と同じ場合
'②データを読み込んだ回数と、読み込み元のデータ行が同じ場合
'→①②のどちらかの条件を満たす場合
'Streamオブジェクトの現在の位置を0に設定する
.Position = 0
'Stream内のデータの型をバイナリデータに設定する
.Type = adTypeBinary
'(バイナリデータ後)Streamオブジェクトの現在の位置を3に設定する(3バイト目から読み取る)
.Position = 3
'一時的に配列tmpDataに格納する
tmpData = .Read
'Streamを一旦閉じる
.Close
'再度Streamを開く
.Open
'Streamに書き込む
.Write tmpData
'ファイルを保存する
.SaveToFile outputFile, adSaveCreateOverWrite
'Stream内のデータの型をテキストデータに設定する(戻す)
.Type = adTypeText
'streamを閉じる
.Close
End If
コードの説明
以上のコードは、読み込んだCSVファイルのデータの件数を判定する条件の処理と、Streamに書き込まれたCSVファイルのデータを、新たに生成したCSVファイルに保存する処理のコードです。
なお、保存したCSVファイルがBOM付きにならないように回避する処理を行っています。
コードの詳細
110行目のIF文は、以下の条件を満たしているかを判定しています。
- データを読み込んだ回数を分割する1ファイルの最大行数で割った時に0の場合
- データを読み込んだ回数と、読み込み元のデータが格納された配列の要素数が同じ場合
まずは1つ目の条件ですが、例えばExcelのA5のセルでに500が設定されている場合、データを読み込んだ回数が500になった時に1つ目の条件に合致します。
500以降も、1000回目、1500回目…、と500で割れる回数の場合にこの条件に合致します。
500行のデータでファイル生成、次の500行のデータでファイル生成、次の500行のデータでファイル生成…といったイメージです。
次に2つ目の条件ですが、データの最後まで読み込んだ場合にこの2つ目の条件に合致します。
例えば元データが1000件ある場合にExcelのA5のセルに800を指定していたとすると、800行分データを読み込んだ時点で先ほどお話した1つ目の条件に合致します。
この時点で800件までデータが読み込まれたので、200件のデータが残っています。
次は残りの200件を読み込みたいので200件全てを読み込みます。
残り200件が全て読み込まれると、800＋200件で合計1000件までデータを読みまれたことになります。
データを読み込んだ回数が1000で、読み込み元のデータが格納された配列の要素数も同じく1000なので2つ目の条件に合致します。
117行目のコードは、Streamオブジェクトの現在の位置を0に設定しています。
0に設定しているのは、UTF-8形式のデータがBOM付きになるのを回避するためです。
BOM付きを回避させるためにまずは120行目のコードで、一旦Stream内のデータの型をバイナリデータに設定し、123行目でStream内のデータの3バイト目に移動させます。
3バイト目に移動することで、先頭の3バイトを読み込ませないようにする(スルーさせる)ことができます。
ちなみに先頭の3バイトというのは「0xEF」「0xBB」「0xBF」のことで、このデータが先頭の3バイトに追加されているとBOM付きになるので、この3バイトを回避させる必要があります。
126行目では、一時的に配列tmpDataにStreamのデータ(先ほどの3バイト回避させたデータ)を格納し、129行目でStreamを閉じます。
再度132行目でStreamを開き、配列tmpDataに格納されたデータを135行目でStreamに書き込みます。
138行目では、StreamのデータをCSVファイルに保存します。
ここまででCSVファイルに保存し終わったので、次のデータを繰り返しCSVファイルに書き出すために141行目でStrem内のデータの型をバイナリ形式からテキスト形式に戻します。
バイナリ形式に設定したのはあくまでBOM付きの回避策として行ったのであって、このままテキスト形式に戻さないとStreamの文字セットを設定したりデータを書き込むなどの処理を行うことができません。
例えば98行目の文字セットにUTF-8を設定する処理を行うのにバイナリ形式のまま実行するとエラーになります。必ずテキスト形式に戻すのを忘れないようにします。
'文字セットにUTF-8を設定する
strm.Charset = "UTF-8"
144行目では、CSVファイルへの書き込みが終わったのでStreamを閉じます。
動作確認
今回は次のExcelファイルを用意しました。
300行でファイルを分割してCSVファイルを生成します。
なお、分割元のCSVファイルには1000行データが存在しており、CSVファイルはUTF-8でBOMなしです。
マクロ実行後
マクロを実行すると、CSVファイルが4つ生成されました。
300行ごとにCSVファイルが生成されるので、1000行のデータから300行+300行+300行+100行で分割されてCSVファイルが生成されています。
また、BOM無しでCSVファイルが生成されています。
【注意】参照設定が必要です
一つ注意点があるのですが、先ほどのコードを動かすには参照設定が必要です。
参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「OK」ボタンをクリックします。
- Microsoft ActiveX Data Objects 2.8 Library(msado28.tlb)
- Windows Script Host Object Model(wshom.ocx)
なぜ必要かというと、Excelのマクロのコードの9行目の「adodb.stream」というオブジェクトが「msado28.tlb」というファイルを、10行目の「FileSystemObject」というオブジェクトが「wshom.ocx」というファイルを参照するからです。
Dim strm As adodb.stream 'バイナリ データまたはテキストのストリームのインスタンス用変数
Dim fso As FileSystemObject 'FileSystemObjectのインスタンス用変数
この参照設定をしないと下の画像のエラーが出ますので必ず行う必要があります。
ここでは「msado28.tlb」とは何者かについては記事の本題から逸れてしまうので詳細は割愛しますが、マクロで「adodb.stream」「FileSystemObject」というオブジェクトを使う場合は参照設定しないと動かない、程度に思って頂ければと思います。
最後に
この記事では、ExcelのマクロでCSVファイルを複数のファイルに分割する方法についてご説明しました。
UTF-8形式のCSVファイルを分割させたい時は本記事を参考にしてみてくださいね。
プログラミングのスキルを習得するなら
プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。
プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。
