スポンサーサイト 


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

--/--/--

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 の第２引数（第１引数が Null だったら置換えるもの）を使ってみました。
Nz の動きとしては、第２引数が指定されなかった時、Null なら Empty が返ってくるみたい。
変数に代入する時には、あまり問題にならないのかな･･･
Len に渡す時に ""（空文字）として扱ってくれるよね･･･間違っても 0 じゃないよね･･･
悩まないようにしましょうか････って指定した程度のことですけど････

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

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

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

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

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

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

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

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

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

＃２さんに１票


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

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

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

★★ は 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 １シートなら、
rsFrom.Source の記述変更で対応できると思います。

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

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

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


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

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


件数を多くした確認はすぐできるので、以下に紹介しておきます。
・ベースとなる 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
これは、２行目からデータのある最後の行までをコピーし続け、最後の行が３万以上でコピーをやめる。
レコードは、倍、倍･･･になっていきます。
（30000 で切っておけば、2000/2003 でも動くかな：最大でも６万弱）
（2007 で確認していた時には、50000 にしてたら７万件強に）
項目を１つ増やして（今回は 列F ）２行目から連番を振る。

レコードを複製する必要が無ければ、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」の１つ前のもので、列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 ファイルのフルパスを指定できるようにして、
中では分離して････出来上がったテーブルを１次テーブルと解釈。
本テーブルに１次テーブルを追加する際、列B 部分に細工。
本テーブルの 列B の最大値を、１次テーブル 列B に加算して一気に追加（加算は追加SQL内で）
････


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

2012/11/03

Category: 注意しよ

TB： --  /  CM： 0

top △

« 理不尽
帳票フォームで固定行スクロール »

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

top △

コメントの投稿

Secret

top △

| h o m e |


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