アクセス 取り込み時に、桁数（テキスト型）を合わせたい

質問

CSVファイル（１ファイルあたり、２～４万件のデータ）をアクセスに取り込んで、作業しています。
桁数が異なる列が２箇所あるので、取り込み時に桁数を合わせたいと思います。
テーブルのフィールド・プロパティで、（テキスト型→数値型、書式→"000")として試してみましたが、
元のデータがアルファベット混じりのテキスト型のためか、上手くいきませんでした。

エクセルに出力して、text関数を使う方法もあるのですが、なんせデーター量が多いので、
出来ればアクセスで作業してしまいたいと思います。
マクロが設定されていますので、どこかに関数を入れればよいのかと思いますが、
アクセスのマクロは経験がないので、よくわかりません。

何か良い方法があれば、ご教授下さい。よろしくお願い致します。

CSVファイル：
53列　１ファイルあたり、２～４万件のデータ
桁数が異なる列：
(1)O列（8桁と11桁（アルファベット含む）が混在）
→８桁のものの頭に「000」を付け加えて、テキスト型で11桁に揃えたい
(2)Z列　（空欄と、２桁、３桁が混在）
→頭に空欄は「000」、２桁は「0」を付け加えて、テキスト型で３桁に揃えたい

マクロ：KMM**.csv　複数データを取り込み、トータルテーブル[T_KMM]を作成
→[T_KMM]を使用したクエリ[Q_KMM]にてデータ抽出→エクセルへ出力

Option Compare Database

Private Sub TableLinkUpdate_Click()

Dim TableName As String
Dim ImportDef As String
Dim CsvName As String
Dim Fpath As String
Dim Epath As String
Const TableKMMs As String = "KMM"
Const UKMM As String = "T_KMM"

Set db = CurrentDb()

DoCmd.OpenQuery "KMM削除"

Fpath = Me.フォルダパス

n = db.TableDefs.Count - 13

For i = 1 To n
DoCmd.DeleteObject acTable, TableKMMs & i
Next i

TableName = TableKMMs
ImportDef = "KMMimport"
CsvName = "KMM.csv"

With Application.FileSearch
.LookIn = Fpath
.NewSearch
.FileName = CsvName
If .Execute > 0 Then
i = .FoundFiles.Count
Else
MsgBox CsvName & "ファイルが見つかりません。", vbOKOnly, "エラー"

End If

For n = 1 To i
Fpath = .FoundFiles(n)
DoCmd.TransferText acLinkDelim, ImportDef, TableName & n, Fpath, False

strSQL = ""
strSQL = "INSERT INTO " & UKMM
strSQL = strSQL & " SELECT *"
strSQL = strSQL & "FROM " & TableName & n
db.Execute strSQL

Next n

End With

Epath = Me.フォルダパス
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Q_KMM", Epath & "\費用.xls", True

DoCmd.Close acForm, "MAIN"

MsgBox "終わりました。", vbOKOnly, "更新・出力完了"

End Sub

回答No.6

x0000x · ベストアンサー

#1です。

回答の補足を頂いていたのに、確認が遅くなりました。
既に解決していた場合には、無視してください。

>クエリ[Q_KMM]にてデータ抽出…
>DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Q_KMM", Epath & "\費用.xls", True

クエリ"Q_KMM"の抽出時にRight関数で処理できます。
Right("0000" & 列,桁) as 列

意味は、抽出列に前ゼロ文字列を付加し、右から必要な桁数を取得。
Right関数で処理した列には、名前が無いので、元の名前を"As 列"で命名してます。

（１）クエリ"Q_KMM"を選択後、コンテキストメニューから「デザイナー」を選択します。
（２）クエリーデザイナーのテーブル表示ペインでコンテキストメニューの「ＳＱＬビュー」を開きます。
（３）O列(TBL.O列で示します）を以下に変更します。
　　　SELECT ～ ,TBL.O, ～
　　　　↓
　　　SELECT ～ ,Right("00000000000" & TBL.O,11) AS O, ～
（４）Z列も同様に変更します。
　　　SELECT ～ ,TBL.Z, ～
　　　　↓
　　　SELECT ～ ,Right("000" & TBL.Z,3) AS O, ～

（５）クエリ"Q_KMM"を保存します。

※念のため、クエリ"Q_KMM"のbackupコピーを作成の上、お試しください。

right("000"&T11.c,11) as c, right("0"&T11.d,3)

回答No.5

t-ka

正直言ってVBAを理解してないと厳しいです。個人の開発者（SOHO）さんに依頼してみてはいかがでしょう。

回答No.4

imogasi

＞アルファベット混じりのテキスト型のためか
この数例を具体的に上げていただけませんか
アクセスのFormat関数を使えば済む問題ではないですか。
これだけのコードをお書きになる力の方なので、そんなことはわかっている、部類かもしれませんが、
＞エクセルに出力して、text関数を使う方法もあるのですが
といわれると、上記の可能性がありそうで、気になります。
＞O列（8桁と11桁（アルファベット含む）が混在）
→８桁のものの頭に「000」を付け加えて、テキスト型で11桁に揃えたい
簡単な場合だと下記でできそうですが、一筋縄で行かない天は？
テーブルのフィールド
コード2
12345678
asd123456789
sdf12345
123456789012
式
コード２: IIf(Len([体重表]![コード2])=8,"000" & [体重表]![コード2],[体重表]![コード2])
結果
コード２
00012345678
asd123456789
000sdf12345
123456789012

補足

ご回答ありがとうございます。

No.1の補足でも触れましたが、このマクロは私が書いたものではありません。
（それどころか、アクセスのマクロは初めてなのです）
＞エクセルに出力して、text関数を使う方法もあるのですが
が一番簡単で良いと考えたのですが、データを扱う人がご年配で、
しかもデータの更新が頻繁にあるため、どうしてもアクセスで自動的に出力する必要があるのですが、正直お手上げです。（苦笑）

アクセスのFormat関数＞はクエリに設定なので良いのでしょうか？

式1: IIf(Len([コード2])=8,"000" & [コード2],[コード2])
（上記のように試しにやってみたのですが、「式で型が一致しません」とエラーが出てしまいました）
どこか間違っているところがあるのでしょうか？

実際のデータは下記のようなものです。
＞O列（8桁と11桁（アルファベット含む）
例：12345678　00234567895　C5244300067　
＞Z列　（空欄と、２桁、３桁が混在）
例：　45　045　975　（空欄）

すみませんんが、もしお判りでしたら教えてください。
お願い致します。

回答No.3

nicotinism

登録されていないようなのでもう一度

置換に関しては
replace(format("a1jd3","@@@@@@@@")," ","0")
で
000a1jd3
となります。
@の数で調整してみてください。
※Access2000以降で値にスペースがない場合に限ります。
（2000でもクエリだと）
http://support.microsoft.com/kb/225956/ja
など

回答No.2

nicotinism

置換に関しては
replace(format("a1jd3","@@@@@@@@")," ","0")
で
000a1jd3
となります。
@の数で調整してみてください。
※Access2000以降で値にスペースがない場合に限ります。
（2000でもクエリだと）
http://support.microsoft.com/kb/225956/ja
など

補足

ご回答ありがとうございます。

No.1の補足にも記入しましたが、恥ずかしながら、私自身はマクロは超初心者です。
取り込みの際に置換する…というのは理解できるのですが、
このコードは、マクロのどこに書き込めばよいのかが
わからないのです（汗）

※Access2000以降で値にスペースがない場合に限ります。＞
という条件はクリアしています。

回答No.1

x0000x

MS-ACCESSのユーザ定義関数を作成し、QUERYから呼び出す事で可能です。

>strSQL = "INSERT INTO " & UKMM
>strSQL = strSQL & " SELECT *"
>strSQL = strSQL & "FROM " & TableName & n
の[SELECT * "FROM " & TableName & n] を　[SELECT A列,B列,C列,…,subO(O列) as O,…,subZ(z列),…　FROM " & TableName & n]
の様に列毎のSELECT文に再定義します。
処理が必要な列では、列値を引数として、変換後の値を戻すFunctionを定義し、変換後の値でINSERTします。
上記の場合"subO(),subZ()"です。

これらのFunctinは、標準モジュールでPublicで宣言します。

Public Function subO(Byval colO as string) AS string
'(1)O列（8桁と11桁（アルファベット含む）が混在）
'→８桁のものの頭に「000」を付け加えて、テキスト型で11桁に揃えた結果値を求める。

subO = 処理結果値
End Function

同様に、subZも定義します。
AccessのSql文が解析される際に、subO(),subZ()が1行毎に処理されます。

”未定義関数…”の実行時エラーが発生した場合は
(1)Accessのコンパイル
(2)データベース最適化／修復
などを行なった後に再度チャレンジしてください。

まずは、試験的に定義関数をの動作を確認した後、実装されては如何でしょう！

補足

ご回答ありがとうございます。

ただ…すみません
このマクロは前任者が作ったファイルを流用しているので、
私自身には、なんのことやらさっぱりわからないのです。

メインフォームにボタンが設置されていてクリックしたら実行
（指定したフォルダーから読み込み、
同じフォルダー内に出力するようにされています。）
マクロもフォームに書かれています。

これらのFunctinは、標準モジュールでPublicで宣言します。＞
ということは、新たに標準モジュールを作る必要があるのでしょうか？
（補足質問が的を得ていないかもしれませんが…）