- ベストアンサー
Excel2016のVBAでCSVデータを取得・編集しTXTに保存する方法
- Excel2016のVBAを使用して、特定のCSVファイルのデータを取得・編集し、テキストファイルに保存する方法について説明します。
- CSVファイルをダイアログで開き、Excelの最大行数を超えないようにテキストファイルで開きます。その後、データを取得・編集します。
- 取得したデータは、年月日と時刻、最初の数値、最大値、最小値、最後の数値、1分間のデータの個数に編集されます。最後にテキストファイルとして保存します。
- miya_HN
- お礼率100% (1683/1683)
- Excel（エクセル）
- 回答数7
- ありがとう数7
- みんなの回答 （7）
- 専門家の回答
質問者が選んだベストアンサー
なるほど、入力ファイルが２つあるということですか。 これで、「１件目のデータを読み込む」の意味も、2015.12.31 21:18が0 になる理由もわかりました。 しかし、そうなるとマッチング処理が必要で、プログラムが複雑になります。やはり無償で引き受けることはできません。他にもそんな人はいないと思います。有償でも構わないのであれば、私がこのまま引き受けてもかまわないし、プログラム開発を請け負うサイトがあるので、そこに相談するのもいいと思います。 お金が出せないのであれば、自分で開発するしかありません。
その他の回答 (6)
- SI299792
- ベストアンサー率47% (774/1618)
もう疲れました。 １件目は無視すると書いてあったので、そのようにしたら、1件目のデータを読み込むというし、データの個数の数え方がいまだにわかりません。 無償ではこれ以上できません。 おろさせていただきます。
お礼
回答ありがとうございます。 データの個数に関しては、大変申し訳ありません。質問する前にはっきり決めるべきでした。 他に聞きたいことがあったのですが非常に残念ですが、ありがとうございました。 ただ、一つだけ。 自分は、説明下手で注意されてもなかなか直りません。 そして、回答者様に「１件目は無視すると書いてあったので、そのようにしたら、1件目のデータを読み込むというし～」という誤解を与えてしまいました。 2つ目の質問で、自分は「1分単位とミリ秒単位のデータの2つのデータをプログラムで処理すればできるのではないかと思いました。」と書き、作成手順の「■1」と「■2」も書きました。 これらから、2つのファイルのデータが必要であることを理解していただけたのではないかと思い、「１件目は無視する」ということではないのですが・・・。 どの部分の記述で、そのような誤解を与えてしまったのでしょうか？ ここで終わってしまうのは残念ですが、回答者様の回答のおかげで処理速度も含めVBAで、データの個数や日本時間に直すなど最終的なデータの作成のほとんどのことが可能であるということが証明できました。 本当に感謝しております。 ありがとうございました。
- SI299792
- ベストアンサー率47% (774/1618)
(1)スペースができるのを消したい。 CSV なのでスペースがあっても問題はないと思っていました。 (2) 2015.12.31 21:17:00,120.184,120.184,120.18,120.18,6.99 2015.12.31 21:18:00,120.18,120.18,120.18,120.18,0 2015.12.31 21:19:00,120.181,120.182,120.18,120.18,11.5 これでどうして、 2015.12.31 21:18,0 になるのですか？データはあるので1 ではないのですか？ データの無い場合0 データを作ると、膨大な量になると思うのですが。 (3)「ダイアログで開くように指定し、保存先を決め」私はこれを見落としていました。 「最後のデータの日にち・最後のデータの時間」はファイルの最後を読まないと決定しません。 ファイルの最後を先に読むという、トリッキーなことをしなければなりません。 ' Option Explicit ' Sub Macro1() ' Dim InpFile As String Dim OutFile As String Dim Idx As Long Dim FileData As String Dim NowDate As String * 10 Dim NowTime As String * 5 Dim NowDateTime As Long Dim OldDateTime As Long Dim Volume As Integer ' 入力ファイル指定 InpFile = Application.GetOpenFilename("CSV ファイル,*.csv") If InpFile = "False" Then End End If Open InpFile For Input As #1 ' Idx = InStrRev(InpFile, "\") ChDir Left(InpFile, Idx - 1) ' ２件目のデータを読む Line Input #1, FileData Line Input #1, FileData InpFile = Mid(InpFile, Idx + 1, 6) & "_" & Left(FileData, 10) & "." & Mid(FileData, 12, 2) & Mid(FileData, 15, 2) ' 最終データを読む、下の数字は最後のレコードの長さより大きくする。大きければ確実だか、出力ファイルの指定が遅くなる。 Idx = LOF(1) - 100 Seek #1, 1 - Idx * (Idx > 0) Do Until EOF(1) Or FileData = "" Line Input #1, FileData ' If FileData = "" Then Exit Do End If NowDate = FileData NowTime = Mid(FileData, 12) Loop ' 出力ファイル指定 OutFile = InpFile & "-" & NowDate & "." & Mid(NowTime, 1, 2) & Mid(NowTime, 4, 2) & ".txt" OutFile = Application.GetSaveAsFilename(OutFile, "TXT ファイル,*.txt") If OutFile = "False" Then Close End End If Seek #1, 1 ' Open OutFile For Output As #2 Line Input #1, FileData ' Do Until EOF(1) Line Input #1, FileData ' If FileData = "" Then Exit Do End If NowDate = Replace(FileData, ".", "/") NowTime = Mid(FileData, 12) NowDateTime = DateValue(NowDate) * 1440 + TimeValue(NowTime) * 1440 ' If OldDateTime > 0 Then FWrite OldDateTime, NowDateTime, Volume End If Volume = Volume + 1 OldDateTime = NowDateTime Loop FWrite NowDateTime, NowDateTime + 1, Volume Close End Sub ' Sub FWrite(OldDateTime As Long, NowDateTime As Long, Volume As Integer) ' Dim Wrk As Long ' For Wrk = OldDateTime To NowDateTime - 1 Print #2, Format(Wrk / 1440, "YY.MM.DD hh:mm") & "," & Volume Volume = 0 Next Wrk End Sub
お礼
回答ありがとうございます。 データの個数で0か1にするかは正直迷っています。 1分単位のデータにはデータはあるが、ミリ秒単位のデータにはデータがない、矛盾したものだからです。 回答者様のおっしゃる通り、1にした方がよいかもしれません。 それと、回答者様のプログラムで2件目のデータを読み込む（ミリ秒単位のCSVファイルのデータ）はあるのですが、1件目のデータを読み込む（1分単位のCSVファイルのデータ）プログラムがないため、思うような結果が得られないため、それを教えてください。 また、その他にも聞きたいことがあるのですが、それは後にします。 また、この質問と直接関係ないのですが、1分単位のデータ（「1分間のデータの個数」含む）を日本時間に直し、VBAで作成する、というのを回答者様の最初のプログラムを基に、途中まで作ってみたのですが結構満足いく処理時間で処理できました（データが全て合っているかまでは分からないのですが・・・） 最終的にはExcelの関数で処理しなければいけないかもしれませんが、処理時間も満足に足りるため、ほとんどVBAで作れそうです。 お手数をお掛けしますが、再度回答よろしくお願いします。
- SI299792
- ベストアンサー率47% (774/1618)
新しく質問されたことに気が付きませんでした。せっかく作ったので載せておきます。 ワークシートを一切使っていないので、VBA にするのも無駄ですが、他に環境がないもので。 ' Option Explicit ' Sub Macro1() ' Dim FileName As String Dim IdX As Integer Dim FileData As String Dim NowDateTime As String * 16 Dim OldDateTime As String Dim ASplit As Variant Dim First As Double Dim Low As Double Dim High As Double Dim Last As Double Dim Volume As Integer ' FileName = Application.GetOpenFilename("CSV ファイル,*.csv") ' If FileName = "False" Then End End If IdX = InStrRev(FileName, "\") Open FileName For Input As #1 ChDir Left(FileName, IdX - 1) FileName = Mid(FileName, IdX + 1, 6) Open "Output.csv" For Output As #2 Line Input #1, FileData ' Do Until EOF(1) Line Input #1, FileData ' If FileData = "" Then Exit Do End If NowDateTime = FileData ' If Len(FileName) < 7 Then FileName = FileName & "_" & Left(FileData, 10) & "." & Mid(FileData, 12, 2) & Mid(FileData, 15, 2) End If ASplit = Split(FileData, ",") ' If NowDateTime <> OldDateTime Then ' Stop ' If OldDateTime > "" Then Print #2, OldDateTime; ","; First; ","; High; ","; Low; ","; Last; ","; Volume End If OldDateTime = NowDateTime First = ASplit(2) High = 0 Low = 1E+308 Volume = 0 End If ' Last = ASplit(2) High = WorksheetFunction.Max(High, Last) Low = WorksheetFunction.Min(Low, Last) Volume = Volume + 1 Loop Print #2, OldDateTime; ","; First; ","; High; ","; Low; ","; Last; ","; Volume FileName = FileName & "-" & Left(OldDateTime, 10) & "." & Mid(OldDateTime, 12, 2) & Mid(OldDateTime, 15, 2) & ".csv" Close On Error Resume Next Kill FileName On Error GoTo 0 Name "Output.csv" As FileName End Sub ’ 新しい質問の方は「日本時間に直して」、とありますが、どこの時間を日本時間に直すのかわかりません。 また、曜日の計算など難しいことが絡むので無料ではちょっと、という感じです。 もし、良かったら１度試してください。実行速度がどれくらいかかるかわかりませんが、もし、満足できる速度ならやってみてください。実行結果はレコード数が減るので、うまくゆけばExcel で取り込めるかもしれません。そうすれば抽出で不要なレコードを取り除けるかもしれません。
お礼
お礼が遅くなり、大変申し訳ありません。 回答ありがとうございます。 新しい質問も見てくれたんですね。 回答者様のプログラムを走らせたところ、1年分という長期間にも関わらず非常に満足できる処理速度でファイルが作成できました。 実は、データ取得のソフトはミリ秒単位だけではなく、1分単位で取得できるようになっています。 そこで、回答者様のプログラムのFirst（最初の数値）、High（最大値）、Low（最小値）、Last（最後の数値）の計算を省かせていただきました。 1分単位でデータを取得した場合、「日時」、「最初の数値」、「最大値」、「最小値」、「最後の数値」を取得できるようになっています。 そして、「1分間のデータの個数」はミリ秒単位でしか取得できません。 本来の目的は、1分単位の「日時」、「最初の数値」、「最大値」、「最小値」、「最後の数値」、「1分間のデータの個数」をある法則の形式にして日本時間とし1行にまとめ、時系列にすることです。 この重要なことを記載せず申し訳ありません。 それで、「日時」、「最初の数値」、「最大値」、「最小値」、「最後の数値」、「1分間のデータの個数」を組み合わせるには、1分単位とミリ秒単位のデータの2つのデータをプログラムで処理すればできるのではないかと思いました。 ■1. ダイアログを表示し、1分単位のCSVファイルのデータを取得する。 1分単位のCSVファイルのデータをテキストファイルで開くと次のようになっています。 1行目（文字） 2015.12.31 15:00:00,120.288,120.296,120.287,120.296,59.82 2015.12.31 15:01:00,120.295,120.298,120.295,120.297,94.74 2015.12.31 15:02:00,120.297,120.297,120.289,120.296,95.27 ～ 最終行は改行 2行目で言うと、 2015.12.31 15:00:00（日時）,120.288（最初の値）,120.296（最大値）,120.287（最小値）,120.296（最後の値）,59.82（その他の数値） ・1行目は単なる文字でデータではないため、2行目から日時のデータを取得します。 ・保存するときのファイル名のため、CSVファイルの左から6文字までと、日時の最初と最後を取得します。 ■2. ダイアログを表示し、ミリ秒単位のCSVファイルのデータを取得・編集する。 ミリ秒単位のCSVファイルのデータをテキストファイルで開くと最初の質問と同じで、次の通りです。 1行目（文字） 2015.12.31 15:00:00.078,120.293,120.288,1.87,1 2015.12.31 15:00:00.619,120.293,120.289,1.12,1 2015.12.31 15:00:01.160,120.293,120.288,1,1 ～ 最終行は改行 プログラムは「日時」と「1分間のデータの個数」の取得のみで構いません。 ■3. 1分単位で取得した日時と、ミリ秒単位で取得した日時が一致したとき、その日時の行に「1分間のデータの個数」を入れます。 例えば、1分単位で取得した日時が「2015.12.31 15:00:00」で「2」の工程でミリ秒単位の日時と一致したとき、 2015.12.31 15:00,34 のようにします（「34」が「1分間のデータの個数」です。）。 回答者様のプログラムで気になったのは空白のスペースができたことです。 次の□（四角）の部分がその空白のスペースです。 2015.12.31 15:00,□34□ このスペースをなくしたいのですが。 もちろんExcelで簡単になくすことはできるのですが、できる限り数式を減らしたいんです。 詳細は下記の「補足」を参照してください。 また、気を付けていただきたいのは例えば次のデータは1分単位で取得したデータです。 2015.12.31 21:17:00,120.184,120.184,120.18,120.18,6.99 2015.12.31 21:18:00,120.18,120.18,120.18,120.18,0 2015.12.31 21:19:00,120.181,120.182,120.18,120.18,11.5 そして次に、回答者様のプログラムで作成したデータです。 2015.12.31 21:17, 6 2015.12.31 21:19, 10 2015.12.31 21:20, 14 「2015.12.31 21:18:00」のデータがないため、このようにデータが飛ぶのですが、この場合その日時の「1分間のデータの個数」を「0」としていただきたいのです。 データの基準を、1分単位の日時としてほしいのです。 ■4. ダイアログを表示し、自動的にファイル名が表示され、ファイルの保存先を指定し、テキストファイルとして保存する。 ファイル名とは質問に記載した、 ******_2003.05.04.2100-2003.05.30.1459 のことです。 保存先を指定するために「Application.GetSaveAsFilename」の「InitialFileName」を試すなどしてみたのですがうまくいきませんでした。 これらのプログラムを考えていただきたいのですがいかがでしょうか？ ■補足 補足として、以前使用していたソフトでは1分単位として「日時」、「最初の数値」、「最大値」、「最小値」、「最後の数値」、「1分間のデータの個数」のデータを取得できていました。 以前のソフト用に日本時間に変更するなど数式が分からなかったため、いろいろなことを想定し質問で数式を教えていただき、Excelファイルを作成することができました。 しかし、現在ではそのソフトではデータが取得不可能になってしまいました。 そこで新しいソフトを発見し、それが現在のソフトで、取得したデータが上記のものになります。 ただ、1分単位のデータ取得では「1分間のデータの個数」のデータがありません。 そこで、ミリ秒単位から「1分間のデータの個数」を取り出したかったんです。 そして、以前のソフト用に使用していたExcelファイルの数式を現在のソフト用に変え、結構コンパクトになったのですがそれでもファイルを開くのに数分かかったり、メモリが結構消費します。 「3」の工程で、できる限り数式を減らしたいと言ったのはそのためです。 また、新しい質問の中で、「日本時間に直す」などと言いましたがちょっと調べてみました。 2006年までや2007年からの一定期間内での月曜日から土曜日までの時刻を調べると、データを取得した段階で取り除くデータがなく、計算する必要がないことが分かりました。 これも重要な情報なんですが、記載せず申し訳ありません。 日本時間に直すには、「2015.12.31 21:18:00」などの日時に＋9時間にするだけで日本時間となります。 最終的に日本時間に直し、 「年4桁」「.」「月2桁」「.」「日にち2桁」「,」「時間2桁」「:」「分2桁」「,」「最初の数値」「,」「最大値」「,」「最小値」「,」「最後の数値」「,」「1分間のデータの個数」をスペースがない状態にし、これを1行とし時系列にすることです。 これが法則の形式です。 例： 2016.01.04,07:00,120.188,120.204,120.183,120.204,11 こんな感じになります。 できるならVBAで全てやりたいのですが、プログラムが分かりませんし処理時間もどの程度かかるか分かりません。 また、自分が考えた方法より、より効率的な方法があれば教えてください。 大変申し訳ありませんが、再度回答よろしくお願いします。
- IDii24
- ベストアンサー率24% (1597/6506)
http://www.microsoft.com/ja-JP/download/details.aspx?id=52679 https://creativeweb.jp/fc/ テーブルにCSVをインポート。制限は１ファイル5ギガまで。これを超えるなら複数のテーブルを作成。一つのテーブルでやりたいなら有料版。あるいはMySQLなどフリーを使う。 SQLの実行 select convert(時間,120) As 時間分単位、MIN(3桁目) AS 最小,MAX(3桁目) AS 最大, Count(*) AS 分個数 from ImportTB group by convert(時間,120) order by convert(時間,120) これだけでは？ Accessでもできるけど構文は多少違うので調べる。
お礼
回答ありがとうございます。 本当はもう少し聞きたいことがあるんですが、当初のVBAの質問と離れてしまったため、新規に質問しようと思います。 大変参考になりました。 ありがとうございました。
- IDii24
- ベストアンサー率24% (1597/6506)
なぜExcelでやりたいのか？Excelにこだわる理由があるなら仕方ないですが、VBAでやるには一行ずつ読んで一行ずつ比較して大きいか小さいかを判断し変数に保管し、一分の切れる箇所で吐き出すって書き方をします。ここでは書ききれないので書きません。 読み込むときには一気に読み込むのではなくファイルを一行ずつメモリに読み込みます。 こうすることでメモリ不足をなくします。ただし遅いはずです。出来ますがすごく遅いと思ったほうが良いでしょう。なにしろExcelですから。 こういうものはPowerShellなどのスクリプトで書くべきでは？当然VBとかC#ならなおいです。Linqも使えますし。 で、根本的なこと言いますとAccessとかSQLサーバーの無料版に取り込んで、SQLで処理した方が全然早いし簡単なんですが？ 時間は分以下を切り捨てればグループ化出来ますし、その中でMAXとMINで一気に最大値と最小値が取れますよね。おそらくやりたいことは一行のSQL文で終わります。
お礼
回答ありがとうございます。 ExcelのVBAではメモリを食ったり、遅くなってしまうんですね。 それでは、無料（無期限）を前提として、そのようなソフトというようなもので、仕様制限などいろいろとありそうなので質問では記載されていない必要な情報があれば提示してください。 その後、具体的なプログラムを教えてください。 大変お手数をお掛けしますが、再度回答よろしくお願いします。
- msMike
- ベストアンサー率20% (364/1804)
》 Excelの最大行数1,048,576行を軽く超えてしまうため、 》 テキストファイルで開きます そういうことなら、此処 Excel の部屋で質問された理由は何ですか？
お礼
回答ありがとうございます。 >そういうことなら、此処 Excel の部屋で質問された理由は何ですか？ 自分の考えでは、他に方法が見当たらないからです。 また、そのような大量なデータを扱うソフトの購入は金銭的に余裕がありませんし。 可能ならば教えてください。 大変お手数をお掛けしますが、再度回答よろしくお願いします。
お礼
お礼が大変遅くなり申し訳ありません。 回答ありがとうございます。 ご理解いただけて感謝いたします。 前回、回答者様のプログラムを基に自分でプログラムを作ってみたと言いましたが、若干データが合わない箇所があったため修正してみたところ全て一致させることができました。 かなりの件数があったため、確認するのに時間がかかりましたが成功しました。 このプログラムは、回答者様の回答がなければできませんでした。 これは全て回答者様のおかげです。 ありがとうございました。