お世話になります。 アクセスを利用したテーブルのデータに連番をつける
方法を教えてください。
質問カテゴリが違っていましたらご容赦ください。
バージョンは、アクセスは2003を利用しています。
質問の内容として、データをインポートした下記のようなテーブルがあります。
列A 列B 列C 列4D 列E ・・・・・・・
1 品名 10,000 500
2 品名 20,000 1,000
3 品名 50,000 2000
4 AAA
5 品名 20,000 500
6 品名 80,000 1,000
7 BBB
8 品名 50,000 500
9 品名 30,000 1,000
上記のようなデータが数万件あります。
これを 列B・列Cに連番(+1)をつけたいとおもってます。
列A 列B 列C 列4D 列E ・・・・・・・
1 品名 1 1 10,000 500
2 品名 2 2 20,000 1,000
3 品名 3 3 50,000 2000
4 品名 4 1 20,000 500
5 品名 4 2 80,000 1,000
6 品名 5 1 50,000 500
7 品名 5 2 30,000 1,000
自分なりに調べたりもしたのですが、うまくできませんでした。
あまり、VBAに詳しくないこともあり、ご面倒かけ申し訳ありませんが、
方法を教えていただけますでしょうか
ご面倒かけますが、よろしくお願いいたします。
No.9ベストアンサー
- 回答日時:
#8です
> うまくいったらで良いので、余力あればですが、
> 2)の CSV を別名でコピーして、品名部分を数値にした取り込みがうまくいくか・・・
私の確認漏れです。
AAA 部分が Null 扱いされる場合があるようですね。
> If (Left(rsFrom(0), 3) = "AAA") Then
↓
If ((IsNull(rsFrom(0))) Or (Left(rsFrom(0), 3) = "AAA")) Then
に変更してみて、確認してみてください。
これで不都合なければ良いのですが、
上記以外に CSV の内容を定義する schema.ini を使う手もあります。
この回答への補足
お世話になります。
確かにNullになることがありました。
If ((IsNull(rsFrom(0))) Or (Left(rsFrom(0), 3) = "AAA")) Then
上記に変更後実行しましたが、問題なく実行されていました。
希望通りの結果が返ってきてなんとかなりそうです。
忙しいところ何度も確認をさせてしましまして申し訳ありませんでした。
ありがとうございました。m(__)m
No.8
- 回答日時:
#7です
原因わかっていません。
> iRecCnt 0 Variant/Long
の Variant が気になりますが・・・
#6で提示した NumSet3 は、
コピー&貼り付けした後、テーブル名、★★、■■ の3か所を変更しただけでしょうか。
以下2点確認していただけませんか。
1)エラーとなる CSV を対象に以下を実行してみてください。
Public Sub RecDump()
Dim rsFrom As New ADODB.Recordset
Dim i As Long
i = 20
rsFrom.Source = "SELECT * FROM [★★] IN " _
& "'■■'[Text;FMT=Delimited;HDR=YES;IMEX=1;];"
rsFrom.Open , CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
Do While (Not rsFrom.EOF)
Debug.Print rsFrom(0), rsFrom(1), rsFrom(2), rsFrom(3), rsFrom(4)
i = i - 1
If (i <= 0) Then Exit Do
rsFrom.MoveNext
Loop
rsFrom.Close
End Sub
上記を標準モジュールに記述・変更部分は変更して実行してみてください。
対象の CSV 先頭20行をイミディエイトウィンドウに表示します。(表示例は後述)
特に1行目の一番左の表示(左3文字)は AAA ですか。
5~6行で良いんで結果を貼り付けてもらえませんか。
2)以下を CSV ファイルとして作成し、上記を実行してみます。
列A,列B,列C,列4D,列E
AAA 1
1
品名,,,10000,500
AAA 1
2
品名,,,20000,500
品名,,,80000,1000
AAA 2
2
品名,,,50000,500
品名,,,30000,1000
4
品名,,,30000,1000
品名,,,70000,2000
品名,,,50000,1500
品名,,,40000,1700
で、これを表示してみた結果は以下のようになると思います。
(QA上、表示が崩れたらごめんなさい)
AAA 1 Null Null Null Null
1 Null Null Null Null
品名 Null Null 10000 500
AAA 1 Null Null Null Null
2 Null Null Null Null
品名 Null Null 20000 500
品名 Null Null 80000 1000
AAA 2 Null Null Null Null
2 Null Null Null Null
品名 Null Null 50000 500
品名 Null Null 30000 1000
4 Null Null Null Null
品名 Null Null 30000 1000
品名 Null Null 70000 2000
品名 Null Null 50000 1500
品名 Null Null 40000 1700
もし、表示が同じなら、NumSet3 を使ってこの CSV データを取り込んでみます。
うまくいったらで良いので、余力あればですが、
2)の CSV を別名でコピーして、品名部分を数値にした取り込みがうまくいくか・・・
以上 確認の程よろしくお願いします。
この回答への補足
出先に出ていた為、ご回答がおくれました。
申し訳ありませんでした。
標準モジュールを実行した結果としては、
イミディエイトウィンドウの表示は同じでした。
CSVデータをNumSet3で、改めて数字データに変換後の
実行結果としては、うまくいきました。
正直???って感じですが・・・
とりあえず、結果をご連絡いたします。
No.7
- 回答日時:
#6です
> 'CLng 関数を使って値を長整数型 (Long) に変換
> 'AAAでなければ、処理するレコード数を覚える
> iRecCnt = CLng(rsFrom(0))
>
> 上記箇所でエラーになりました。
>
> 何が原因か分かりますでしょうか
とのことですが、何のエラーになりますか。
エラーになった時の rsFrom(0) の内容は何でしょうか。
また、iRecCnt / iNum1 / iNum2 の値はどうなっているのでしょうか。
なお、こちらで確認した CSV の内容ですが、
#5の補足で提示された 1 ~ 16 をコピーし続け、131,072 件に加工し、
提示した NumSet3 で生成された件数は 73,728
73728 ÷ 9 * 16 = 131072
と件数は一致し、また読み込み順も崩れることはありませんでした。
どこが異なるんですかね
ルールに抜けはないでしょうか。
この回答への補足
実行時エラーとして、エラー94
Nullの使い方が不正です。と表示されます。
Variant型に対してのエラーのように見受けられます。
他の値ですが、下記のようにローカルウィンドウに表示されていました。
式 値 型
iNum1 0 Long
iNum2 0 Long
iRecCnt 0 Variant/Long
ここからはこんなことがあるかの確認とそちらでも同じように
なるかどうかの確認なのですが
rsFrom(0) CSVの列Aに対して、確認を取っていたんですが
NULLはありませんでした。
但し、全てをひらがなや漢字に変更すると実行可能でした
例) (1)あああ (2)ああ あ (3)う うう (4) えええ
※間が空いているのはスペースです。
これを数字のみに変更すると、同様の位置でエラーが発生しました。
AAA 1
1
111,,,1000,2000
たとえば上記のような状態でコードを実行するとエラーが発生しました。
No.6
- 回答日時:
#5です
AAA のレコードから始まることを前提に、以下でどうなりますか。
(折り返し記述が面倒だったので、変な折り返し表示になるかもしれません)
Public Sub NumSet3()
Dim rs As New ADODB.Recordset
Dim rsFrom As New ADODB.Recordset
Dim iNum1 As Long, iNum2 As Long
Dim iRecCnt As Long
Dim i As Long
Dim sSql As String
Const sTable As String = "テーブル名"
iNum1 = 0
iRecCnt = 0
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 (iRecCnt = 0) Then
If (Left(rsFrom(0), 3) = "AAA") Then
iNum1 = iNum1 + 1
iNum2 = 1
Else
iRecCnt = CLng(rsFrom(0))
End If
Else
rs.AddNew
For i = 0 To rsFrom.Fields.Count - 1
Select Case i
Case 1
rs(1) = iNum1
Case 2
rs(2) = iNum2
Case Else
rs(i) = rsFrom(i)
End Select
Next
rs.Update
iRecCnt = iRecCnt - 1
iNum2 = iNum2 + 1
End If
rsFrom.MoveNext
Wend
rsFrom.Close
rs.Close
End Sub
新設した変数は iRecCnt で、列A に記述しているレコード数を管理。
iRecCnt = 0 なら 列A の内容を確認する。
・左3文字が AAA なら、カウンタの更新
・AAA でなければ、処理するレコード数を覚える
品名を処理したら iRecCnt = iRecCnt -1
iRecCnt = 0 になった時には、 AAA が来るか、レコード数の数値部分になるはず。
※※ 列A には必ずデータがあるものとしてました。
また、AAA は固定された文字としていました。
この回答への補足
度々のご回答感謝です。
頂いた内容を実行してみました。
結果として
'CLng 関数を使って値を長整数型 (Long) に変換
'AAAでなければ、処理するレコード数を覚える
iRecCnt = CLng(rsFrom(0))
上記箇所でエラーになりました。
何が原因か分かりますでしょうか
No.5
- 回答日時:
#3です
サンプルデータ・結果がどうなっているのか補足からわかりませんでした。
1 ~ 12 のものと、6 ~ 17 のものが、結果 1 ~ 6 になるのか???
どの様なルールになっているのでしょうか。
単に 列B に数字があった時にカウンタ操作をする。
ただし、列A が line の時に限られる・・・・であれば
> 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
部分を
If (Len(Nz(rsFrom(1), "")) > 0) Then
If (Nz(rsFrom(0), "") = "line") Then
If (iNum1 = 0) Then
iNum1 = iNum2
Else
iNum1 = iNum1 + 1
End If
iNum2 = 1
End If
Else
とすれば良いと思いますが・・・・
列A が line の時だけカウンタの操作をして、それ以外で 列B があるのは単に読み飛ばし・・・
それとも、列B に数字があるわけではなく、列A として line 2 とかが存在するのでしょうか。
> 列A 列B 列C 列4D 列E ・・・・・・・
> 1 品名 1 1 50,000 500
> 2 品名 2 2 50,000 500
> 4 品名 2 3 50,000 500
では
列A 列B 列C 列4D 列E ・・・・・・・
1 品名 1 1 50,000 500
2 品名 2 1 50,000 500
4 品名 2 2 50,000 500
レコードの2以降の 列C はどちらが正解なのでしょう。
また、列4D 部分の 20,000 80,000 はどこに行ったのでしょうか。
ルールをもう一度整理して、説明してもらえませんか。
この回答への補足
改めて説明させていただきます。
度々ご面倒かけて申し訳ありません。
改めてルールを整理します。
列A 列B 列C 列4D 列E ・・・・・・・
1 AAA 1
2 1
3 品名 10,000 500
4 AAA 1
5 2
6 品名 20,000 500
7 品名 80,000 1,000
8 AAA 2
9 2
10 品名 50,000 500
11 品名 30,000 1,000
12 4
13 品名 30,000 1,000
14 品名 70,000 2,000
15 品名 50,000 1,500
16 品名 40,000 1,700
⇒
列A 列B 列C 列4D 列E ・・・・・・・
1 品名 1 1 10,000 500
2 品名 2 1 20,000 500
3 品名 2 2 80,000 1,000
4 品名 3 1 50,000 500
5 品名 3 2 30,000 1,000
6 品名 3 3 30,000 1,000
7 品名 3 4 70,000 2,000
8 品名 3 5 50,000 1,500
9 品名 3 6 40,000 1,700
ルールとしては、下記のようなルールとなります。
1. 全データが対象
2. AAAからのデータが対象となり、AAAから連番を開始したい
3. AAA 2 とあれば、AAAデータが2データあることになります。
2・5・9・12行目のデータの横にある数字は、何行あるか表しています。
※ 同様に、AAA 3とあれば、データが3つあることになります。
3が一番ややこしいんですが、希望としては、AAAの対象レコードの数字を
読み飛ばして連番を付与したいということになります。
4. 連番付与後は、AAA・数字データとも表示をなくしたい
※ AAA・数字データとも列Aに存在しています。
※ 品名にも数字で始まっているものがあります。但し5桁以上です。
ルールとしては以上となります。
No.4
- 回答日時:
そもそもキー情報がない状態のレコードなのでインポート後の処理は無理。
上から順にインポートされ、なおかつ同時にオートナンバーなどでキー情報を追加できたのならプログラムで何とかなるだろうが、Excel や CSV エディタなどで連番を振るのが一番エネルギーも時間も掛けないで解決する方法だと思う。
No.3
- 回答日時:
#1です
> テーブルを開いた時、提示されたフィールド・レコード順で操作できるものとします。
やっぱりこれじゃ無理がありましたか・・・・
#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 の記述変更で対応できると思います。
※ インポート元が複数・・・には対応してませんので・・・・
失礼しました。
この回答への補足
30246kiku 様
何度も申し訳ないのですが、私の勘違いがあり
2点程 確認させて頂けないでしょうか
下記のように質問させて頂いた内容ですが
列A 列B 列C 列4D 列E ・・・・・・・
1 品名 10,000 500
2 品名 20,000 1,000
3 品名 50,000 2000
4 AAA
5 品名 20,000 500
6 品名 80,000 1,000
7 BBB
8 品名 50,000 500
9 品名 30,000 1,000
実際には下記のような内容でした。
列A 列B 列C 列4D 列E ・・・・・・・
1 品名 10,000 500
2 品名 20,000 1,000
3 品名 50,000 2000
4 AAA 1
5 line 2
6 品名 20,000 500
7 品名 80,000 1,000
8 AAA 2
9 line 3
10 品名 50,000 500
11 品名 30,000 1,000
12 品名 50,000 1,000
訂正店
(1) 列B にあった AAA・BBB ⇒ Nullが含まれていた為
勘違いして、列Bに記載してしまっていました。
正しくは、AAAのみとなり、その横に 数字が記載されています。
(2) AAAの横に、2のように記載があった場合
6 AAA 1
7 Line 1
8 品名 50,000 500
9 AAA 2
10 Line 2
12 品名 50,000 500
13 品名 30,000 1,000
14 Line 3
15 品名 50,000 500
16 品名 30,000 1,000
17 品名 50,000 500
⇒
列A 列B 列C 列4D 列E ・・・・・・・
1 品名 1 1 50,000 500
2 品名 2 2 50,000 500
4 品名 2 3 50,000 500
5 品名 2 4 30,000 1,000
6 品名 2 5 50,000 500
※ AAA 5 というデータがあった場合、同様に、Line 五つ分のLineデータを連番付ける
必要があります。
これも私の不注意になるのですが、データでAAA 数字となっているのが
1しか確認できていなかった為、Lineについてはクエリ等で削除をすればいいと
いう認識でした。
実際には、AAA 2や3のようなデータがありました。
質問をしているにも関わらず、誤った情報を記載した点もあり
心苦しいのですが、ご助力頂けると幸いです。
ご面倒かけ申し訳ございませんが、よろしくお願いいたします。
お礼が遅れましてすいませんでした。
回答いただいた内容でほぼ対応できました。
また、分かりやすい説明ありがとうございます。
ご協力感謝いたします。
あとは自分なりに、一人でも記述できるようになれるよう
勉強したいと思います。(先は長そうですが・・・)
ありがとうございました。
No.2
- 回答日時:
仮にインポート前のオリジナルデータが下記の様であったとして
インポートした際にレコードの並び順は保証されないと思います。
また、Recordsetの並び順の保証も。
http://www.accessclub.jp/bbs/0025/beginers11174. …
1 品名 10,000 500
2 品名 20,000 1,000
3 品名 50,000 2000
4 AAA
5 品名 20,000 500
6 品名 80,000 1,000
7 BBB
8 品名 50,000 500
9 品名 30,000 1,000
↑オリジナルにこのようなユニークなカラムがあれば並び替えに使えるとおもいます。
上記のようなカラムが無い場合には、
オリジナルのテキストファイル?を一行ずつ読み込んで
Accessのテーブルに追加するときに、連番フィールドのようなものに書き加えるか
オリジナルがExcelファイルなら、そちらで対処するとか・・が必要かと思います。
回答ありがとうございます。
おっしゃるとおり、1の回答では、並び順がおかしくなってました。
3で回答いただいた内容で、希望に近いことができました。
少ない説明で対応頂き、ありがとうございました。
No.1
- 回答日時:
テーブルを開いた時、提示されたフィールド・レコード順で操作できるものとします。
以下を標準モジュールに記述し、テーブル名を設定しなおして実行します。
(データは元には戻せなくなるので、テスト用の環境で)
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 空文字 を前提にしています。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- その他(Microsoft Office) Excelで該当しない項目(#N/Aの商品名)を簡単に表示・抽出させる方法についてです 1 2022/08/25 22:12
- Excel(エクセル) ある文字を含む際に、値を返す数式についてです 5 2022/08/28 16:58
- Visual Basic(VBA) 【VBA】複数行あるカンマ区切りのデータを全て縦に一列に並べたい 5 2022/04/13 17:03
- Visual Basic(VBA) VBAで最新のデータを別シートに転記する方法をお教えください。 3 2022/04/07 19:20
- Excel(エクセル) エクセルの条件付き書式 個人シートを参照して集計シートに色付けしたい 1 2023/06/22 00:39
- Visual Basic(VBA) 重複データをまとめて合計を合算する 4 2022/10/25 20:25
- Excel(エクセル) 【VBA】元のシート内の文字列を別シートと比較し、一致したら元のシートの別のセルへ転記する方法。 3 2023/03/23 17:30
- Excel(エクセル) Excel での関数についての質問です。 例えばA列に商品名を、B列に個数をそれぞれ入力しています。 7 2023/05/13 10:51
- Excel(エクセル) エクセルでフィルタ後の列の重複を回避したい 6 2022/10/13 12:50
- Excel(エクセル) SUMIFSと日付変換 10 2023/04/16 15:38
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
文字列の結合を空白行まで実行
-
Worksheets メソッドは失敗しま...
-
Excelで、あるセルの値に応じて...
-
vba 2つの条件が一致したら...
-
【VBA】2つのシートの値を比較...
-
B列の最終行までA列をオート...
-
Changeイベントでの複数セルの...
-
VBAを使って検索したセルをコピ...
-
エクセルでフォームのチェック...
-
VBA 値と一致した行の一部の列...
-
VBAで、特定の文字より後を削除...
-
VBAで10行おきにセルの下に罫線...
-
VBAコンボボックスで選択した値...
-
データグリッドビューの一番最...
-
マクロ 最終列をコピーして最終...
-
VBAのFind関数で結合セルを検索...
-
rowsとcolsの意味
-
空白セルをとばして転記
-
追記する列を増やしたい 2つの...
-
【VBA】複数行あるカンマ区切り...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Worksheets メソッドは失敗しま...
-
Cellsのかっこの中はどっちが行...
-
VBAのコードを教えてください
-
VBAを使って検索したセルをコピ...
-
B列の最終行までA列をオート...
-
エクセルvbaについて
-
vba 2つの条件が一致したら...
-
Excelで、あるセルの値に応じて...
-
VBA UserFormからの転記で
-
VBAのFind関数で結合セルを検索...
-
文字列の結合を空白行まで実行
-
IIF関数の使い方
-
VBA 何かしら文字が入っていたら
-
マクロ 最終列をコピーして最終...
-
Changeイベントでの複数セルの...
-
エクセルVBAにて =A1=B1とすれ...
-
【VBA】2つのシートの値を比較...
-
データグリッドビューの一番最...
-
VBマクロ 色の付いたセルを...
-
VBAで指定範囲内の空白セルを左...
おすすめ情報