言い訳
以下の様な質問があって回答したのですが
インポートした以下の様なテーブルがある
データは数万件
列B、列C を以下の様な連番に振り直したい
あまり、VBAに詳しくないこともあり・・・・
列A | 列B | 列C | 列4D | 列E |
---|---|---|---|---|
品名 | 10000 | 500 | ||
品名 | 20000 | 1000 | ||
品名 | 50000 | 2000 | ||
AAA | ||||
品名 | 20000 | 500 | ||
品名 | 80000 | 1000 | ||
BBB | ||||
品名 | 50000 | 500 | ||
品名 | 30000 | 1000 |
列B、列C を以下の様な連番に振り直したい
列A | 列B | 列C | 列4D | 列E |
---|---|---|---|---|
品名 | 1 | 1 | 10000 | 500 |
品名 | 2 | 2 | 20000 | 1000 |
品名 | 3 | 3 | 50000 | 2000 |
品名 | 4 | 1 | 20000 | 500 |
品名 | 4 | 2 | 80000 | 1000 |
品名 | 5 | 1 | 50000 | 500 |
品名 | 5 | 2 | 30000 | 1000 |
あまり、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 は長整数、その他はテキスト型にて品名,,,10000,500
品名,,,20000,1000
品名,,,50000,2000
,AAA,,,
品名,,,20000,500
品名,,,80000,1000
,BBB,,,
品名,,,50000,500
品名,,,30000,1000
で、回答した内容は以下
テーブルを開いた時、提示されたフィールド・レコード順で操作できるものとします。
以下を標準モジュールに記述し、テーブル名を設定しなおして実行します。
(データは元には戻せなくなるので、テスト用の環境で)
rs(0) は 列A、rs(1) は 列B、rs(2) は 列C で、
iNum1 は 列B 用のカウンタ、iNum2 は 列C 用のカウンタ になってます。
難しいことはしていないので、説明はいらないかと・・・・
(必要なら補足いただければと)
おそらく 列B はテキスト型だと思いますが、数字は文字扱いに・・・
列C は、?
※ なお、列A、列B の空白部分は、Null or 空文字 を前提にしています。
以下を標準モジュールに記述し、テーブル名を設定しなおして実行します。
(データは元には戻せなくなるので、テスト用の環境で)
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
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
※ たぶん CSV の rsFrom レコード順は維持されていたような・・・
違っていたらごめんなさい。(提示された物での確認レベルです)
(2007 での確認結果は、添付図のように)
(テーブル内の、列B、列C は数値型/長整数で定義してました)
※ インポート元が Excel 1シートなら、
rsFrom.Source の記述変更で対応できると思います。
※ インポート元が複数・・・には対応してませんので・・・・
やっぱりこれじゃ無理がありましたか・・・・
#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
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万以上でコピーをやめる。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
レコードは、倍、倍・・・になっていきます。
(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;
やっている事は、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 に・・・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
※ インポート元が複数・・・には対応してませんので・・・・
これも対応はしやすいかと・・・・
Public Sub NumSet2() の引数として CSV ファイルのフルパスを指定できるようにして、
中では分離して・・・・出来上がったテーブルを1次テーブルと解釈。
本テーブルに1次テーブルを追加する際、列B 部分に細工。
本テーブルの 列B の最大値を、1次テーブル 列B に加算して一気に追加(加算は追加SQL内で)
・・・・
注意していこう・・・・・っと
- 関連記事
2012/11/03
Category: 注意しよ
TB: -- /
CM: 0
« 理不尽
帳票フォームで固定行スクロール »
この記事に対するコメント
| h o m e |