エクセル マクロでCSVファイルを下から指定した行数だけ表示したい

どなたかご経験のある方教えていただけないでしょうか。

かなり行数の多いcsvファイルから指定した行数だけ
エクセルのシートに展開したいのですが、
どのようにマクロで記述してよいかわからず困っています。

使い方のイメージとしては

①A1のセルに読み込む行数を記入。
②「展開」ボタンをクリック
③ファイル読み込み（マクロファイルと同じフォルダのCSVファイル）
④A1のセルで指定した行数分を2行目以降に転記
　（例：A1セル：「5」の場合、最後の5行分のテキストが「2行目」以降に転記）

よろしくお願いいたします。

質問者からの補足コメント

• みなさん
  ご回答頂きありがとうございます。
  
  補足コメントします。
  読み込む対象のファイルは「.csv」のファイルです。
  データとしては、カンマ区切りで、数字のみとなっています。
  
  処理イメージとしては、
  Linuxでの「tail -n」コマンドで、ファイルの末尾何行を表示するのと同等な処理を
  エクセル上で行いたいです。
  
  よろしくお願いいたします。

    補足日時：2018/07/08 14:32

No.11 回答者： めぐみん_ 回答日時： 2018/07/09 13:43

No.10です。

最後の方で抜けてる部分がありました。

Set objRS = Nothing
Set objCn = Nothing
End Sub
　　　↓
objRS.Close
objCn.Close
Set objRS = Nothing
Set objCn = Nothing
End Sub

２行追加です。

No.10 回答者： めぐみん_ 回答日時： 2018/07/09 04:26

No.8です。

一例です。
ただし日付は元々文字列のようですが読み込んだ際にシリアル値に変わってしまいます。（見た目は修正してますが）
不備になるようであれば飛ばして下さい。

Sub megu()
Dim objCn As Object
Dim objRS As Object
Dim strSQL As String
Dim F_path As String, F_name As String
Dim n As Long

F_path = ThisWorkbook.Path
F_name = "test.csv" '★検証用のCSVファイル名
n = Range("A1").Value

If n < 1 Then Exit Sub

Set objCn = CreateObject("ADODB.Connection")
Set objRS = CreateObject("ADODB.Recordset")

With objCn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Properties("Extended Properties") = "Text;HDR=NO"
.Open F_path & "\"
End With

strSQL = ""
strSQL = strSQL & " SELECT * FROM ("
strSQL = strSQL & " SELECT TOP " & n & " * FROM [" & F_name & "]"
strSQL = strSQL & " ORDER BY F1 DESC,F2 DESC)"
strSQL = strSQL & " ORDER BY F1 ,F2;"

Set objRS = objCn.Execute(strSQL)

Range("A2", Cells(Rows.Count, "A")).EntireRow.ClearContents
Range("A2").CopyFromRecordset objRS '★ 抽出したデータを貼り付け

Range("A2").Resize(n).NumberFormat = "yyyy.mm.dd" '★Ａ列は文字列ではなくシリアル値になりますが。
Range("B2").Resize(n).NumberFormat = "h:mm"


Set objRS = Nothing
Set objCn = Nothing
End Sub

あと項目行は書き出してません。
やっぱ必要でしたかね？

No.9 回答者： ママチャリ 回答日時： 2018/07/08 21:40

ネットで使えそうなものを見つけたので、紹介します。

「FileSystemObject」の「OpenTextFile」で、Iomode「ForAppending」でオープンするとファイルの末尾のライン位置が取得できるようです。その値をQueryTables オブジェクトの「TextFileStartRow」に設定して、CSVファイルを取り込んでみました。こんな感じです。
QueryTables.Addの命令は、マクロの記録そのままなので、素人丸出しですが、参考になれば幸いです。

Sub sample()
Dim FSO As Object
Dim Loc As Long
Const Target As String = "C:\Users\xxxxxxxx\sample.txt"
Set FSO = CreateObject("Scripting.FileSystemObject")
With FSO.OpenTextFile(Target, 8)
Loc = .Line - Range("A1").Value
.Close
End With
Set FSO = Nothing

Rows("2:" & Rows.Count).Delete
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & Target, Destination:=Range("$A$2"))
.Name = "sample"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 932
.TextFileStartRow = Loc
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

この回答へのお礼

ご回答ありがとうございます。
思い通りの動きでした。実行速度も問題ありません。
非常に参考になりました。ありがとうございました！！

お礼日時：2018/07/08 23:16

No.8 回答者： めぐみん_ 回答日時： 2018/07/08 18:51

No.5です。

＞データ例は以下の通りです。今のところは最大で1万行くらいのデータです。
＞2018.07.08,14:40,108.5,108.6,108.5,108.6,2304,0.09,0.06,54.34

データが1つなので憶測でしかないですが、これって１列目は日付で2列目は時間という事は最終行が最新データで、
そこから遡ってn個のデータを抜き出したいという事ではないでしょうか？

仮にそうなら日付と時間を元に降順で並び替えをしてTOP句を用いてn個のデータを取り出し、更に昇順に並び換えし直せば良いのかな？
と思えたのですが・・・・

そう言うデータ群ではないですかね？

この回答へのお礼

何度もお世話になります。

＞データが1つなので憶測でしかないですが、これって１列目は日付で2列目は時間という事は最終行が最新データで、
＞そこから遡ってn個のデータを抜き出したいという事ではないでしょうか？
はい。そうです。

そのままの並びで。に固執していましたが、
シンプルに考えればそのようにできますね。ありがとうございます。参考になります。

お礼日時：2018/07/08 23:11

No.7 回答者： siffon9 回答日時： 2018/07/08 15:58

一案として

tail -n と同等のWindows PowerShellコマンドを実行するという方向で考えてみました。

Sub test()
Dim cmd As String
' 下行は一行で記載
cmd = "powershell -executionpolicy bypass -command ""Get-Content 'C:\\xxx\\input.csv' | Select-Object -Last 3 | Out-File -FilePath 'C:\\xxx\\output.csv'"""
' 上行は一行で記載
Shell cmd

' ここにoutput.csv読み取る処理を書く

End Sub

C:\xxx\input.csvを読み取り
ラスト3行を、C:\xxx\output.csvに出力します。

csvのパス名、残す行数は変数を使用するなどして適宜修正、作成されたoutput.csvを利用下さい。

この回答へのお礼

ご回答ありがとうございます。

外部のコマンドを使用する事ができるんですね。
初めて知りました。大変勉強になりました。

お礼日時：2018/07/08 23:04

No.6 回答者： tatsu99 回答日時： 2018/07/08 15:31

以下のモジュールを標準モジュールに登録してください。

ざっとしか試験してませんので不具合があれば、その旨補足ください。
Const csvfile As String = "TEST.csv"
はCSVファイル名です。あなたの環境にあわせて適切に設定して下さい。
----------------------------------------------------------
Option Explicit

Dim lines() As String
Public Sub CSV読み込み()
Dim no_line As Long '最後のデータの表示件数
Const csvfile As String = "TEST.csv"
Dim lctr As Long
Dim fileno As Long
Dim fname As String
Dim ix As Long
Dim i As Long
Dim row As Long
no_line = Cells(1, 1).Value
If no_line < 1 Then Exit Sub
Rows("2:" & Rows.Count).ClearContents 'Sheetの2行以降をクリア
ReDim lines(no_line - 1)
fileno = FreeFile
fname = ThisWorkbook.Path & "\" & csvfile
Open fname For Input As #fileno
lctr = 0
'全件読み込み（linesにはサイクリックに格納）
Do Until EOF(fileno)
ix = lctr Mod no_line
Line Input #fileno, lines(ix)
lctr = lctr + 1
Loop
Close #fileno
row = 2
'データが表示件数以内
If no_line >= lctr Then
For i = 0 To lctr - 1
Call expand_line(row, i)
Next
MsgBox ("完了")
Exit Sub
End If
'データが表示件数を超えている
ix = lctr Mod no_line
'途中から最後までの部分
For i = ix To no_line - 1
Call expand_line(row, i)
Next
'最初から途中の手前までの部分
For i = 0 To ix - 1
Call expand_line(row, i)
Next
MsgBox ("完了")
End Sub
'指定行へ1行分の全項目を格納
Public Sub expand_line(ByRef row As Long, ByVal ix As Long)
Dim items As Variant
Dim i As Long
items = Split(lines(ix), ",")
For i = 0 To UBound(items)
Cells(row, i + 1).Value = items(i)
Next
row = row + 1
End Sub

この回答へのお礼

ありがとうございます！！
思った通りの動きを確認できました。
ここまで完全なソースを提供いただいて、
本当に助かりました。

お礼日時：2018/07/08 23:02

No.5 回答者： めぐみん_ 回答日時： 2018/07/08 13:51

No.2です。

＞そしてDBではありませんので、SQL発行では対応できません。

につきましては以前
https://oshiete.goo.ne.jp/qa/10466706.html
このような回答もさせて貰ってます。
ただ質問者さんの環境で実際にやってダメだったというのであれば、その言葉は正解なのでしょう。
そうでない場合ですと『ラスト5行』を決める何かがなければ頭から読み込んでいくしかないでしょう。（No.3さんの回答のように）

Linuxの件については『5行だけのCSVファイルを作成』し、それを読み込めば可能ではないか？
と言うだけの事です。
ただし読み込める行数（データ容量）に限界があるかも知れないので場合によっては無理ってだけの事です。

とにかくデータがどうなっているのかが不明ではどうしようもないかな？

でもNo.3さんのパターンBもAと同じ方法を取れない物なのかな？（ここは経験ないので推測です）

この回答へのお礼

先ほどのご回答に引き続きありがとうございます。
リンク先を確認しました。
そのようなやり方があるとは知らず、大変失礼いたしました。

データ項目は
年月日,時間,項目1,項目2,項目3,項目4,項目5,項目6,項目7,項目8,項目9,項目10
となっています。

データ例は以下の通りです。今のところは最大で1万行くらいのデータです。
2018.07.08,14:40,108.5,108.6,108.5,108.6,2304,0.09,0.06,54.34

SQLのwhere句で絞り込むのはむずかしく、今回は他の方法で実装しようと思います。
ですが、SQLを用いた検索方法は大変参考になりました。
ありがとうございました。

お礼日時：2018/07/08 14:45

No.4 回答者： siffon9 回答日時： 2018/07/08 12:37

> そしてDBではありませんので、SQL発行では対応できません。

VBAでCSVファイルに対してSQLを用いて検索する方法はありますよ。

もし案件がSQL使用で対応可能でしたら
excel vba sql adodb csv　あたりをキーワードにぐぐってください。

この回答へのお礼

教えて頂きありがとうございました。
このようなやり方があることを初めて知りました。
検索してみます。

お礼日時：2018/07/08 14:35

No.3 回答者： tatsu99 回答日時： 2018/07/08 11:56

補足要求です。

①CSVファイルの拡張子は".csv"であってますか。
②1行のデータですが、以下の様な単純なカンマ区切りでしょうか。
遠藤蘭,男,20,大阪市中央区瓦町,グッズショップTORA・・・・・・・・パターンA
それとも、ダブルクオートで括られたカンマ区切りでしょうか。
"遠藤蘭","男","20","大阪市中央区瓦町","グッズショップTORA"・・・・パターンB

パターンAであればLine　Inputで1行読み込んで、各項目をカンマで分割すればよいので問題ありません。
また、１００万行でも時間はかかりますが問題なく処理可能です。（データはA1の件数分内部メモリに保持します）

パターンBの場合は、単純にカンマで分割できないので、Workbooks.Openを使用しExcelファイルとして読み込んだ方が簡単に分割できます。又、データ中にカンマとかダブルクオートがあってもexcelが正しく各項目単位に分割してくれます。
但し、この場合、データが１００万行とかあるとexcelの限界を超えますので、処理できません。５万行程度以内なら可能かと。

どちらの方法で行うかは、データ量とデータ形式できまるかと。

この回答へのお礼

ご回答ありがとうございます。情報が不足しており申し訳ありません。

補足コメントにも記載しましたが、以下の通りです。

＞①CSVファイルの拡張子は".csv"であってますか。
はい。CSVです。

＞②1行のデータですが、以下の様な単純なカンマ区切りでしょうか。
＞遠藤蘭,男,20,大阪市中央区瓦町,グッズショップTORA・・・・・・・・パターンA
＞それとも、ダブルクオートで括られたカンマ区切りでしょうか。
＞"遠藤蘭","男","20","大阪市中央区瓦町","グッズショップTORA"・・・・パターンB
対象となるインプットデータは単純なカンマ区切りです。

イレギュラーなデータのない、数字のみのデータなので、
Line Inputでの読み取りなのかなと思っています。

あとはNo.1でご回答いただいた通りに、全体を空読みして指定行まで読み飛ばすのが今のできる範囲なのかなと思っています。

お礼日時：2018/07/08 14:28

No.2 回答者： めぐみん_ 回答日時： 2018/07/08 08:18

『かなり行数が多い』って曖昧な表現ですと1000行なのか100万行なのかでも違うでしょ。

それにどんな感じのデータがあるのかでも変わりますし。
例えば重複しないインデックスを最初の列に持っているなら、Max値からMax値-4までが目的の5行になりますし。
ここはSQL文のSELECT句で対応かな？

経験の有無より状況に応じて考えるんじゃないかな？と思いますよ。
そして状況を知る上での情報が重要かと。

例で言えばWinにLinux環境を備えていたら
tail -n 5 abc.csv
で最後から5行の値をコマンドプロンプトに表示できます。（abc.csvの文字数によっては難しいかもですが）
この結果をダミーファイルに書き込んでそれを読み込むって方法もあり得るかもですが、Linuxのコマンドが使える環境になってないと無理ですけど。

この回答へのお礼

ご回答ありがとうございました。
あいまいな表現で失礼しました。
ただ、100行でも1000行でも100万行でもやりたい事は変わらず最後の○○行をエクセル上に展開したいのです。

そしてDBではありませんので、SQL発行では対応できません。

また、エクセル上で読み込んだファイルをそのファイルのシート上に展開したいので、
Linuxコマンドが使えたとしても要件としては合致しません。

ご回答頂き、ありがとうございました。

お礼日時：2018/07/08 10:44