VBAを使用して、CSVファイルの内容をすばやく読み込んで、EXCELテーブルにインポートする

数日前、何十個の商品のQRコードデータファイルをEXCELにインポートし、フォーマット変換した後にデータベースにインポートするという任務が任せられました。得られた元のテーブルは、CSV形式のほぼ100個のテキストファイルであり、各ファイルには数万から数十万行のデータが含まれています。このような大量のデータを手動で処理するには時間がかかりすぎるため、VBAを使用してスクリプトを作成してインポートしたいと思います。また、VBAでCSVファイルをテーブルに読み込むことも一般的な操作です。FileSystemObjectオブジェクトの使い方を身につける限り、Scripting.FileSystemObjectオブジェクトを使用して操作できます。

実際、Excelには、データ検索とインポート機能があるため、複数の形式のデータ(構造化データ)を取得できます。この機能をVBAスクリプトと組み合わせて使用し、ファイルをすばやくインポートできますか？検証した結果は実行可能です。

データを取得する

まずはExcelファイルを作成します。できるだけ少ない手動操作でデータを読み込みます。最初には、ファイルの数と各ファイルの名前を知る必要があります。これはVBAスクリプトを使用すべきです。 ALT+F11を使用してVBAエディターを開き、新しいモジュールを作成し、ファイル名を読み取るプロセスを作成します。

Sub sfiles()
Dim s As FileSearch '検索オブジェクトであるファイルを定義する
Dim fpath As String 'ファイルパスを定義する
Dim fl As Worksheet
Set fl = ThisWorkbook.Worksheets("filelist")
fpath = ThisWorkbook.Path
fpath = fpath & "\ファイルを読み取るフォルダー\"
n = 1
myFile = Dir(fpath & ".")
Do While myFile <> ""
fl.Cells(n, 1) = myFile
myFile = Dir '次のファイルを検索する
n = n + 1
Loop
End Sub

このスクリプトを実行すると、最初のシートに読み込むファイル名を書き込むことができます。次には、ファイル名に従ってファイルの内容を現在のExcelファイルに順番に読み込み、各ファイルのデータを新しいシートに保存します。

ExcelにデータをインポートするVBAの書き方に精通している場合は、スクリプトを直接記述できます。慣れていない場合にも大丈夫です。最初にマクロを記録し、訂正すればいいです。プロセスは次のとおりです。

Sub importDatafromCsv()
'CSVファイルからテーブルにデータをバッチで読み取り
'
Dim filelists As Worksheet
Dim fileNum As Integer
Dim aimFileName As String
Dim aimFile As String
Dim aimFileFullPath As String
Dim sheetNum As Integer
Dim i As Integer
Set filelists = ThisWorkbook.Worksheets("filelist")
fileNum = filelists.[A1].CurrentRegion.Rows.Count '行数を取得する
For i = 1 To fileNum
'aimFile = Selection.Value ‘ファイル名を選択してインポートする。または以下のループを押してすべてのファイルをインポートする
aimFile = filelists.Cells(i, 1).Value
aimFileName = Split(aimFile, ".", , vbTextCompare)(0)
aimFileFullPath = ThisWorkbook.Path & "\インポートするファイルがあるフォルダ\" & aimFile
sheetNum = ThisWorkbook.Sheets.Count
ThisWorkbook.Sheets.Add(After:=Sheets(sheetNum)).Name = "new" & sheetNum + 1
Range("A1").Select
ActiveWorkbook.Queries.Add Name:=aimFileName, Formula:= _
"let" & Chr(13) & "" & Chr(10) & " ソース = Csv.Document(File.Contents(""" & aimFileFullPath & """),[Delimiter="","", Columns=5, Encoding=936, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & " 変更するタイプ = Table.TransformColumnTypes(ソース,{{""Column1"", Int64.Type}, {""Column2"", type text}, {""Column3"", type text}, {""Column4"", Int64.Type}, {" & _
"""Column5"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " 変更するタイプ" & _
""
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & aimFileName & ";Extended Properties=""""", Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [" & aimFileName & "]")
.rownumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = aimFileName
.Refresh BackgroundQuery:=False
End With
ActiveSheet.ListObjects(aimFileName).Unlink
Range("A1").Select
Next
End Sub

上記のスクリプトを実行し、数分間の程度で、ほぼ100個のファイル、約100万行のデータがExcelのテーブルにインポートされ、すごく便利です。

同じ状況に遭う場合は、上記の方法で解決を試みてください。これより、時間が大幅に節約され、効率も向上します。より良い、より効率的な方法があれば、コメントをよろしくお願いいたします。