Access (VBA)

Access VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 7 Professional : Access 2003)
テキストファイルの行を削除して詰める
投稿日時: 18/09/11 10:02:12
投稿者: かきぬま

お世話になります。
CSVファイルの日付が昇順で登録されています。
ACCESS−VBAでCSVファイルを読み込んで
今日から10日以前のデータを削除して詰める方法はねいでしょうか。

回答
投稿日時: 18/09/11 10:51:41
投稿者: よろずや

かきぬま さんの引用:
お世話になります。
CSVファイルの日付が昇順で登録されています。
ACCESS−VBAでCSVファイルを読み込んで
今日から10日以前のデータを削除して詰める方法はねいでしょうか。
読み込んでどうするのでしょう?
テーブルに入れたいのか、CSVファイルとして出力したいのか?
日付は、CSVファイルの先頭項目ですか?
また、どんな形式で入ってますか?
CSVファイルには、ヘッダーはありますか?

回答
投稿日時: 18/09/11 12:08:37
投稿者: Suzu

こんにちは。
 
テーブルに取りこんで、ご質問にある通り、
10日以前のデータを削除クエリを使って削除する。
 
では、何か問題があるのでしょうか。
 
VBAで一行づつ日付を判定して削除を行う方法は処理時間を考えると非効率的と思いますよ。

投稿日時: 18/09/11 13:00:20
投稿者: かきぬま

みなさんお忙しい中すいません。
やりたい事はCSVファイルの古いデータを削除して前詰めにたいんです。
CSVファイルの中はヘッダーは無く1件目からデータが入っています。
現在は、毎日4〜6000件くらいCSVファイルにデータが追加されて
古いデータは手作業で削除している状態です。
なんとかボタン1つの操作で古いデータを自動で削除できないか考えています。

回答
投稿日時: 18/09/11 14:50:25
投稿者: Suzu

CSV って ただの テキストファイルです。
 
それを、EXCEL や ACCESSと同じ様に考えてはいけません。
テキストファイルは基本シーケンシャルファイルで、
1行目から順に1行づつ読み書きを行います。行毎に「削除」という概念自体ありません。
 
出来るのは、 編集(上書き)、最後尾への追加 です。
 
【CSVファイルを読み込む】
http://www.moug.net/tech/exvba/0060086.html
 
なので、
1.必要な部分だけ新規ファイルに追加
2.旧ファイルの削除、新ファイルの名称を旧ファイル名に変える
の手順になります。
 
 
結局、Access と その CSVの絡みは無いのですよね。
要は、Access の VBAを 使用し CSV のファイル編集をしたいだけ なのですよね?
 
Access を使うのであれば、テーブルを使っては? と言う部分はお気に召さないのですよね?

投稿日時: 18/09/11 17:06:51
投稿者: かきぬま

Suzuさんレスありがとうございます
おっしゃる通り、Access の VBAを 使用し CSV のファイル編集をしたいだけです。
 
あの手この手を考えて試行錯誤しているところですが
なかなかうまくいっていません。
 
いまテスト用にコーディングしているのは下記の通りですが
まだ動作の確認は取れていません。
 
もう少し頑張ってみます。
 
        Dim Fso As Object
        Dim Fds As Object
        Dim Fl As Object
        Dim wk_date As Date
        Dim wk_YYYYMMDD As String '今日より10日前の日付
 
        '今日より10日前の日付を求める
        wk_date = DateAdd("d", -10, Date)
        wk_YYYYMMDD = Format(wk_date, "YYYYMMDD")
         
        'T_TEMPのF1という項目は、日付(YYYYMMDD)が入っている
 
        DoCmd.SetWarnings False
    'インポート先のテーブルを初期化する
        DoCmd.RunSQL "Delete From T_TEMP;"
        'アクセスmdbに全件インポートする。
        DoCmd.TransferText acImportDelim, , "T_TEMP", "C:\TEST\TESTDATA.CSV"
        '念のためF1に数値以外のデータがあったら削除する(これは無いはず)。
        DoCmd.RunSQL "DELETE FROM T_TEMP WHERE F1 Like '*[!0-9]*'= true;"
        '10日以上前のデータを消す。
        DoCmd.RunSQL "Delete FROM T_TEMP WHERE F1 <" & wk_YYYYMMDD & ";"
        DoCmd.SetWarnings True
         
        '古いCSVファイルを消す
        Set Fso = CreateObject("Scripting.FileSystemObject")
        Set Fds = Fso.GetFolder("C:\TEST")
        For Each Fl In Fds.Files
            If Fl.Name = "TESTDATA.CSV" Then
               Fl.Delete ' ファイルを削除
            End If
        Next
        Set Fso = Nothing
         
        '削除後のデータをエクスポートする。
        DoCmd.TransferText acExportDelim, , "T_TEMP", "C:\TESTDATA.CSV"

回答
投稿日時: 18/09/11 18:52:20
投稿者: Suzu

引用:
'T_TEMPのF1という項目は、日付(YYYYMMDD)が入っている

引用:
wk_YYYYMMDD = Format(wk_date, "YYYYMMDD")

引用:
DoCmd.RunSQL "Delete FROM T_TEMP WHERE F1 <" & wk_YYYYMMDD & ";"

 
・CSVファイルの 日付 の書式はどうなっていますか?エクセル でなく、メモ帳にて開き確認してください。
・F1 は 日付時刻型のデータ型ですか? それとも、数値型?、テキスト型?
 
上記2件で、単にTransferTextメソッドでインポートできるかが変わります。
 
 
CSV の日付のデータが、20180911 の様なデータの場合を前提に。。
10日前 以降のデータをテーブル作成クエリのSQLにて直接、CSVファイルを作成する。
 
【既存CSVファイル名.csv】
1,20180810
2,20180811
   :
9,20180818
   :
23,20180901
24,20180902
25,20180903
   :
33,20180911
34,20180912
 
の時
 
【新作成CSVファイル名.csv】
23,20180901
24,20180902
    :
33,20180911
34,20180912
 
を作成します。
 
 
既存CSVファイル名 と、同じフォルダに、
 
【schema.ini】 を作成します。
 
[既存CSVファイル名.csv]
ColNameHeader=False
Format=CSVDelimited
MaxScanRows=25
CharacterSet=ANSI
Col1=ID LONG
Col2=日付 LONG

[新作成CSVファイル名.csv]
ColNameHeader=False
Format=CSVDelimited
CharacterSet=ANSI
Col1=ID LONG
Col2=日付 LONG

 
を書き込み保存。
Col1、・・は フィールド名 データ型 を合せて下さい。
 
 
 
Private Sub MakeNewCSV()
  Dim dbEng As Object
  Dim ws    As Object
  Dim db    As Object

  Set dbEng = CreateObject("DAO.DBEngine.36")
  Set ws = dbEng.CreateWorkspace("JET", "Admin", "", dbUseJet)
  Set db = ws.OpenDatabase("C:\DATA\マクロ\", False, False, "TEXT;HDR=NO;")

  If Dir("C:\DATA\マクロ\新作成CSVファイル名.csv") <> "" Then
    Kill "C:\DATA\マクロ\新作成CSVファイル名.csv"
  End If

  db.Execute _
    "SELECT * INTO [新作成CSVファイル名.csv] " & _
    "FROM [既存CSVファイル名.csv] " & _
    "WHERE 日付>=" & Format(DateDiff("d", -10, Date), "yyyymmdd") & ";"

  db.Close: Set db = Nothing

  Kill "C:\DATA\マクロ\既存CSVファイル名.csv"
  Name "C:\DATA\マクロ\新作成CSVファイル名.csv" As "C:\DATA\マクロ\既存CSVファイル名.csv"
End Sub

投稿日時: 18/09/11 23:27:58
投稿者: かきぬま

Suzuさん親切丁寧にありがとうございます。
 
CSVファイルの日付の書式は数値型で(YYYYMMDD)です。
※拡張子をテキストに変更して開いて内容を確認しています。
 
書いて頂いた手順としては
1.schema.iniという定義ファイルを作る
2.条件付きでCSVファイルを読み込んで新ファイルを作る
3.既存ファイルは消してしまい
4.新ファイルをリネームして完成というわけですね!
 
流れの雰囲気はなんとなくわかるものの
自分が思いも付かなかった方法でドキドキしています。
 
明日になってしまいますがコーディングを真似て
動作させて報告させて頂こうと思います。

回答
投稿日時: 18/09/12 10:15:26
投稿者: Suzu

ミソとしては、テーブル作成クエリ(SELECT INTO) にて、CSVファイルを作っている事。
 
Excel でも、CSVを含めたテキストファイルでも、データの編集や削除はできないですが
参照、テーブル(ファイル)作成は、SQLで可能。
SQL を使用する為に今回は、DAO を使用。
 
フォルダがデータベース、CSVファイルがテーブル の様なイメージになります
なので、OpenDatabase にて、フォルダを指定。
テーブル の所に、CSVファイル を指定。
 
 
その時のデータ型は、schema.ini にて設定可能。
(日付型の時には、Format関数等を用い、出力する書式 yyyymmdd,yyyy/m/d とかに合せる必要あり)
 
 
あとは、ファイルが別になりますので、その為の操作ですね。

投稿日時: 18/09/12 11:55:07
投稿者: かきぬま

Suzuさんありがとうございます。
期待する結果が得られました。
それに処理速度が凄く速くて驚きです!
こんなに早期に解決できると思っていませんでした。
 
ただ私は、schema.iniの設定内容がわかっていないので
これから学習するところです。
まだまだいろんなパラメーターがあるのでしょうね楽しみうです。
 
ColNameHeader=False
Format=CSVDelimited
MaxScanRows=25 ←今はここがわかっていない
CharacterSet=ANSI

回答
投稿日時: 18/09/12 13:52:22
投稿者: Suzu

かきぬま さんの引用:

それに処理速度が凄く速くて驚きです!

 
ワークテーブルを使わない分、早いかもしれませんね。
今回の場合は、テーブルが CSVファイルですね。
 
ただし、気を付けなけらばならないのが
Jetの場合、テーブルとして制限が決まっています。
そのサイズ超えるCSVファイルの場合、
エラーや意図した結果が得られない事が考えられます。
 
【Access の仕様】
https://support.office.com/ja-jp/article/access-%E3%81%AE%E4%BB%95%E6%A7%98-0cf3c66f-9cf2-4e32-9568-98c1025bb47c
 
テスト確認していませんが フォルダがデータベース 扱い と言いました。
という事は、フォルダサイズにも制限があるかもしれません。
(その辺りは必要ならテストを行う等、確認ください)
 
 
 
引用:
MaxScanRows=25 ←今はここがわかっていない

テキストファイルの場合、各フィールドのデータ型を 自動で決定する仕様です。
それを、25行目まで データを参照し、決めます という設定です。
 
今回の様にCol〜〜で指定を行う場合は必要なかったですね。

投稿日時: 18/09/12 19:16:45
投稿者: かきぬま

Suzuさん
お陰さまで「MaxScanRows」の意味がやっとわかりました。
調べてみても意味が理解できず困っていました。
 
処理結果を検証していたのですが
良く見てみると小数第1位までしか無いはずのデータが
なぜか小数第2位に、無条件に0が付いているのです。
 
既存データは必ず小数第1位まで固定で入っているので
schema.iniファイルの項目に、Singleと入れたのですが
それだけでは足りないのではと思い調べている所です。
 
既存の値は、123.4
新作成の値は、123.40
 
新規作成の値を何とか既存の値と同じ小数第1位までに
したく探し続けていますが未だに辿りつけず。
もう少し頑張ってみます。
 
 
schema.iniの内容
  
[既存CSVファイル名.csv]
ColNameHeader=False
Format=CSVDelimited
MaxScanRows=25
CharacterSet=ANSI
Col1=作業日 Long
Col2=規格下限値 Single →小数第1位までが固定で入っている 
Col3=規格上限値 Single →小数第1位までが固定で入っている
Col4=測定値 Single   →小数第1位までが固定で入っている
Col5=判定 Text
 
[新作成CSVファイル名.csv]
ColNameHeader=False
Format=CSVDelimited
CharacterSet=ANSI
Col1=作業日 Long
Col2=規格下限値 Single →なぜか小数第2位に0が付与される 
Col3=規格上限値 Single →なぜか小数第2位に0が付与される
Col4=測定値 Single →なぜか小数第2位に0が付与される
Col5=判定 Text

回答
投稿日時: 18/09/12 19:50:47
投稿者: Suzu

【Schema.ini ファイル (テキスト ファイル ドライバー】
https://docs.microsoft.com/ja-jp/sql/odbc/microsoft/schema-ini-file-text-file-driver?view=sql-server-2017
 

引用:
NumberDigits    数値の小数部分の 10 進数字の数を示します。

 
判りづらい日本語訳ですが、小数点以下何桁にするかの設定です。
 
引用:
エントリを省略すると、Windows コントロール パネルの 既定値が使用されます。

の通り、コントロールパネル 「地域と言語」-「追加の設定」-「小数点以下の桁数」の値が使われます。
 
なので、新作成CSVファイル名.csv 側に
NumberDigits=1

を追加してみてください。
 
 
或いは、単に転記するだけのフィールドであれば、
数値型や、日付時刻型ではなく、書式もそのまま持っていける TEXT にしてしまっても良いでしょう。

投稿日時: 18/09/13 06:23:19
投稿者: かきぬま

Suzuさん何度も何度もありがとうございます。
 
新規作成側に「NumberDigits=1」を追加したのですが
エラーになってしまいどうもうまく行かないので、
結局「TEXT」ににしました。
 
新規側の処理結果には、"124.4"と""が付いてしまいますが
参照するだけなので問題はありません。
これで大丈夫です。
 
Suzuさん本当にありがとうございました。
Suzuさんのように人に指導できるスキルを身に着けられるように
頑張ります!