FC2ブログ

スポンサーサイト 


上記の広告は1ヶ月以上更新のないブログに表示されています。
新しい記事を書く事で広告が消せます。

--/--/--

Category: スポンサー広告

TB: --  /  CM: --

top △

言い訳 


以下の様な質問があって回答したのですが

インポートした以下の様なテーブルがある
列A列B列C列4D列E
品名  10000500
品名  200001000
品名  500002000
 AAA   
品名  20000500
品名  800001000
 BBB   
品名  50000500
品名  300001000
データは数万件

列B、列C を以下の様な連番に振り直したい
列A列B列C列4D列E
品名1110000500
品名22200001000
品名33500002000
品名4120000500
品名42800001000
品名5150000500
品名52300001000

あまり、VBAに詳しくないこともあり・・・・

列B、列C でのカウンタ処理をメインにした方が良いのか・・・
・・・・レコードの並びがそうなっているもの・・・・として回答してみました。

なお、今回サンプルファイルはありません。
記述してあるものをコピー&貼り付けすれば、確認は容易にできると思います。

CSVをレコードセットとして扱う・・・
CSVから読み込む際に、連番を振る処理例も記述しています。
(ExcelでCSVに連番を振る例も・・・)
 
まず、動作確認を容易にするためのデータを用意します。
以下を「tst1.csv」名で保存し、テーブル「T1」としてインポートします。
列A,列B,列C,列4D,列E
品名,,,10000,500
品名,,,20000,1000
品名,,,50000,2000
,AAA,,,
品名,,,20000,500
品名,,,80000,1000
,BBB,,,
品名,,,50000,500
品名,,,30000,1000
※ 列C、列4D、列E は長整数、その他はテキスト型にて

で、回答した内容は以下

テーブルを開いた時、提示されたフィールド・レコード順で操作できるものとします。

以下を標準モジュールに記述し、テーブル名を設定しなおして実行します。
(データは元には戻せなくなるので、テスト用の環境で)

rs(0) は 列A、rs(1) は 列B、rs(2) は 列C で、
iNum1 は 列B 用のカウンタ、iNum2 は 列C 用のカウンタ になってます。
難しいことはしていないので、説明はいらないかと・・・・
(必要なら補足いただければと)

おそらく 列B はテキスト型だと思いますが、数字は文字扱いに・・・
列C は、?

Public Sub NumSet()
  Dim rs As New ADODB.Recordset
  Dim iNum1 As Long, iNum2 As Long
  Const sTable As String = "テーブル名"

  iNum1 = 0
  iNum2 = 1
  rs.Open sTable, CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic
  While (Not rs.EOF)
    If ((Len(Nz(rs(0), "")) = 0) And (Len(Nz(rs(1), "")) > 0)) Then
      If (iNum1 = 0) Then
        iNum1 = iNum2
      Else
        iNum1 = iNum1 + 1
      End If
      iNum2 = 1
      rs.Delete
    Else
      If (iNum1 = 0) Then
        rs(1) = iNum2
      Else
        rs(1) = iNum1
      End If
      rs(2) = iNum2
      rs.Update
      iNum2 = iNum2 + 1
    End If
    rs.MoveNext
  Wend
  rs.Close
End Sub

※ なお、列A、列B の空白部分は、Null or 空文字 を前提にしています。

上記の "テーブル名" 部分を、"T1" に変更して確認してみます。

カウンタの扱いは、列B が現れるまでは 列C のカウンタをそのまま使用します。
列B が現れたら、両方のカウンタを扱うように・・・・
あまり説明する部分はありませんね・・・

今回、Nz の第2引数(第1引数が Null だったら置換えるもの)を使ってみました。
Nz の動きとしては、第2引数が指定されなかった時、Null なら Empty が返ってくるみたい。
変数に代入する時には、あまり問題にならないのかな・・・
Len に渡す時に ""(空文字)として扱ってくれるよね・・・間違っても 0 じゃないよね・・・
悩まないようにしましょうか・・・・って指定した程度のことですけど・・・・

テーブルを開いた時、提示されたフィールド・レコード順で操作できるものとします。

これが問題なんですね。
レコード件数が少なければ少ないほど、正常に処理できる頻度は上がってくると思います。
ただ、やろうとしている件数は数万件・・・・

レコードの順を指定できるものが無ければ、順は保証されない。

沈黙のご指摘がありました。
ごもっともと思います。

じゃここで、オートナンバのフィールドを追加してみたらどうだろう・・・
事後だから・・・順が保証されていない状況で・・・・インポート順に・・・・・
これは、たぶん、無理でしょう・・・

という事で、インポートしながら連番を振る例を提示してみました。
何故って、
・連番を振って・・・・
・インポートして・・・・
・レコード全件処理しまくる・・・
私には面倒・・・・どの道全件処理するんだったら、そのままのものを取り込む際に、順に・・・
必要なものだけを・・・・連番振りながら・・・・が、楽じゃないの・・・・・・・ ・・・・・

インポート元がCSV、Excel どちらの例にしよう・・・・
Excel だったらバージョンによって記述が変わってくるし・・・・
CSVなら1つ覚えておけばいいか・・・・ Excel から CSV への変換も楽だし・・・・

> テーブルを開いた時、提示されたフィールド・レコード順で操作できるものとします。

やっぱりこれじゃ無理がありましたか・・・・

#2さんに1票


動作環境を限定した例を以下に(雰囲気で)

・インポート元は CSV ファイル1つ
 その CSV にはヘッダがある(列A,列B,・・・・)
・テーブルは既に出来上がっており、CSV のヘッダの並び順でフィールドが定義してある。

以下を標準モジュールに記述して実行してみます。
基本的な動きは #1 のものになります。

★★ は CSV ファイル名、■■ は CSV ファイルがあるフォルダまでのパス
E:\Hoge\Test.csv なら、★★は Test.csv  ■■は E:\Hoge

Public Sub NumSet2()
  Dim rs As New ADODB.Recordset
  Dim rsFrom As New ADODB.Recordset
  Dim iNum1 As Long, iNum2 As Long
  Dim i As Long
  Dim sSql As String
  Const sTable As String = "テーブル名"

  iNum1 = 0
  iNum2 = 1
  sSql = "DELETE * FROM " & sTable & ";"
  CurrentProject.Connection.Execute sSql
  rs.Open sTable, CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic
  rsFrom.Source = "SELECT * FROM [★★] IN " _
      & "'■■'[Text;FMT=Delimited;HDR=YES;IMEX=1;];"
  rsFrom.Open , CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
  While (Not rsFrom.EOF)
    If ((Len(Nz(rsFrom(0), "")) = 0) And (Len(Nz(rsFrom(1), "")) > 0)) Then
      If (iNum1 = 0) Then
        iNum1 = iNum2
      Else
        iNum1 = iNum1 + 1
      End If
      iNum2 = 1
    Else
      rs.AddNew
      For i = 0 To rsFrom.Fields.Count - 1
        Select Case i
          Case 1
              If (iNum1 = 0) Then
                rs(1) = iNum2
              Else
                rs(1) = iNum1
              End If
          Case 2
              rs(2) = iNum2
          Case Else
              rs(i) = rsFrom(i)
        End Select
      Next
      rs.Update
      iNum2 = iNum2 + 1
    End If
    rsFrom.MoveNext
  Wend
  rsFrom.Close
  rs.Close
End Sub

※ たぶん CSV の rsFrom レコード順は維持されていたような・・・
違っていたらごめんなさい。(提示された物での確認レベルです)
(2007 での確認結果は、添付図のように)
(テーブル内の、列B、列C は数値型/長整数で定義してました)

※ インポート元が Excel 1シートなら、
rsFrom.Source の記述変更で対応できると思います。

※ インポート元が複数・・・には対応してませんので・・・・

まず、上記のコードを確認してみます。
テーブル「T1」の構造のみを「T2」としてコピー後、列B をテキスト型から長整数へ変更します。
(テキスト型なのは、列Aだけ)
で、
上記のテーブル名を「T2」に、
★★を tst1.csv に、
■■を tst1.csv を置いたフォルダのパス
に変更して実行してみます。

CSVをレコードセットで扱えるようにしてからやると、結構簡単にできるのかな・・・
テキストとして、1行読んで・・・区切りを解釈して・・・型変換して・・・
・・・私には面倒すぎて・・・


※ たぶん CSV の rsFrom レコード順は維持されていたような・・・
違っていたらごめんなさい。(提示された物での確認レベルです)

っていう記述がありますが、実際には7万件位のは確認してました。
ただ、この件数で十分なのか・・・・チョッと不安があったので


件数を多くした確認はすぐできるので、以下に紹介しておきます。
・ベースとなる CSV ファイル「tst1.csv」を「tst2.csv」名でコピーします。
・「tst2.csv」を Excel で開きます。
・以下を標準モジュールに記述し、実行します。
Public Sub MakeMany()
  Dim iRow As Long
  Dim iCol As Long

  iCol = Cells(1, Columns.Count).End(xlToLeft).Column
  iRow = Cells(Rows.Count, 1).End(xlUp).Row
  While (iRow < 30000)
    Range(Cells(2, 1), Cells(iRow, iCol)).Copy Cells(iRow + 1, 1)
    iRow = Cells(Rows.Count, 1).End(xlUp).Row
  Wend

  With Range(Cells(2, iCol + 1), Cells(iRow, iCol + 1))
    .Formula = "=Row()-1"
    .Value = .Value
  End With
  Cells(1, iCol + 1) = "列F"
End Sub
これは、2行目からデータのある最後の行までをコピーし続け、最後の行が3万以上でコピーをやめる。
レコードは、倍、倍・・・になっていきます。
(30000 で切っておけば、2000/2003 でも動くかな:最大でも6万弱)
(2007 で確認していた時には、50000 にしてたら7万件強に)
項目を1つ増やして(今回は 列F )2行目から連番を振る。

レコードを複製する必要が無ければ、While 部分はいりません。
連番を振る必要が無ければ、With 以降いりません。

で、「tst2.csv」に上書き保存し、閉じます。
(上記のコードは消えます。捨てコードという位置づけになるのかも・・・)
・テーブル「T2」の構造のみ「T3」名でコピーします。
・「T3」のデザインで、「列F」を長整数、「an」をオートナンバで後ろに追加します。
・Public Sub NumSet2() 内のテーブル名、★★部分を「T3」「tst2.csv」に変更し実行します。

ここで「T3」の内容が出来上がるので、以下のクエリで順を確認してみます。
SELECT Q1.*
FROM T3 AS Q1 INNER JOIN T3 AS Q2 ON (Q1.列F<=Q2.列F) AND (Q1.an-1=Q2.an)
ORDER BY Q1.an;
やっている事は、
オートナンバ「an」の1つ前のもので、列F の値が大きいものがありますか・・・・
実行の結果は、何も表示されません。
つまり、CSV の内容は順に処理されていた・・・・と判断できるかと・・・・


 その CSV にはヘッダがある(列A,列B,・・・・)

という記述してましたが、実際の処理を見て頂くとわかると思いますが、
・ヘッダがあるだけ
しか意味がありません。現状では・・・・フィールド名が何であるか自体、見ていません。
これ、この回答以降に発展するかもしれない・・・・・布石というものになるかも・・・・

例えば、
・CSV の項目以上にインポートしたいテーブルには多くの項目がある・・・・とか
・逆に、CSV にある項目の内、インポート項目を限定したい・・・とか

そういった時に使えるものになります。
前者の場合、以下の様な処理を組み込めば良いですね。
  iNum1 = 0
  iNum2 = 1
  sSql = "DELETE * FROM " & sTable & ";"
  CurrentProject.Connection.Execute sSql
  rsFrom.Source = "SELECT * FROM [★★] IN " _
      & "'■■'[Text;FMT=Delimited;HDR=YES;IMEX=1;];"
  rsFrom.Open , CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
  sSql = ""
  For i = 0 To rsFrom.Fields.Count - 1
    sSql = sSql & ", [" & rsFrom.Fields(i).Name & "]"
  Next
  rs.Source = "SELECT " & Mid(sSql, 3) & " FROM " & sTable & ";"

  rs.Open , CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic
先に CSV 側のレコードセットを作っておいて、テーブル側をそのフィールド順を指定した SELECT に・・・


※ インポート元が複数・・・には対応してませんので・・・・

これも対応はしやすいかと・・・・

Public Sub NumSet2() の引数として CSV ファイルのフルパスを指定できるようにして、
中では分離して・・・・出来上がったテーブルを1次テーブルと解釈。
本テーブルに1次テーブルを追加する際、列B 部分に細工。
本テーブルの 列B の最大値を、1次テーブル 列B に加算して一気に追加(加算は追加SQL内で)
・・・・


注意していこう・・・・・っと
関連記事

2012/11/03

Category: 注意しよ

TB: --  /  CM: 0

top △

この記事に対するコメント

top △

コメントの投稿

Secret

top △


上記広告は1ヶ月以上更新のないブログに表示されています。新しい記事を書くことで広告を消せます。