Access VBAでCSVファイルをインポートするのは、DoCmd.TransferText。これでうまく動けば問題なし。
通常の操作でCSVファイルをインポートする場合は、「外部データ」ー「テキストファイル」ー「テキストインポートウィザード」で行います。左下の設定ボタンから、区切り記号の指定、各項目のデータ型の指定など、詳細設定が出来ます。これをマクロで記録すると、「DoCmd.TransferText」というメソッドが出てくるはずです。
DoCmd.TransferText TransferType:=acImportDelim, TableName:="Daily_KPI", _
FileName:="20180726_daily.csv", HasFieldNames:=True
TransferType:=acImportDelim →区切りテキストファイルのインポートを指定
TableName:="Daily_KPI" →インポート先のテーブル名を指定
FileName:="20180726_daily.csv" →インポート元のファイル名を指定
HasFieldNames:=True →CSV１行目に項目名が有る場合はTrueを指定
インポート先のテーブルに正しくデータが入っていれば、問題ありません。しかし、インポートエラーテーブルが出来てしまったり、ある行だけ列ズレしてしまう場合は、工夫が必要になります。
CSVのセル内改行でレイアウトが崩れる問題 Layout collapses due to line feed in a cell
列がズレている、ある行が不完全なデータになっているばあい、CSVのセル内に余計な改行が入っていることが原因と考えられます。基本的にCSVなので、セル内に改行コードは無く、行の最後だけに有るのが本来の姿ですが、吐き出すアプリによってはセル内LFは許容、最後にCRLFを入れてくる場合もあります。CSVファイルをExcelで開くとＯＫ、AccessのインポートではＮＧになるのはこのためです。Excelではセル内改行をちゃんと読み取って表示してくれるのです。また、全角コンマが入っている場合も、列ズレが起きますが、Excelで開くとＯＫです。ということで、一度CSVをExcelで読み込み、改行やコンマを消した後、CSVで保存するという、非常に汚いやり方で対応することにしました。コードは煩雑ですが、Excelのワークシート上で綺麗にしておけば、保存するときに正しくコンマと改行を入れてくれることを利用するのです。
Accessと同じ場所にExcelファイルを置き、Accessから起動してExcel VBAを実行することにします。これで、Excelが裏で動いているところは見えません。
Excelファイルには、標準モジュールとしてひとつの関数を入れるだけです。
Public Function replace_extra_character(site_csv_path)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim book_csv As Workbook, sheet_csv As Worksheet
Set book_csv = Workbooks.Open(Filename:=site_csv_path, Local:=True)
Set sheet_csv = book_csv.Worksheets(1)
sheet_csv.Cells.Replace vbLf, "", xlPart 'セル内改行
sheet_csv.Cells.Replace vbCr, "", xlPart 'セル内改行
sheet_csv.Cells.Replace vbCrLf, "", xlPart 'セル内改行
sheet_csv.Cells.Replace ",", "", xlPart '半角コンマ
sheet_csv.Cells.Replace "、", "", xlPart '全角コンマ
book_csv.Close SaveChanges:=True
End Function
この関数をAccessから実行するには、Excel.Applicationオブジェクトを使用します。関数は引数付きで実行できますが、'（シングルクォーテーション）でパスを囲ってやり、その後ろの!に関数名、コンマで区切って引数を指定する必要があります。
Dim my_excel_app As New Excel.Application
my_excel_app.Visible = False
my_excel_app.UserControl = False
my_excel_app.Run "'" & Application.CurrentProject.Path & _
"\access_import_tool.xlsm'!replace_extra_character", csv_folder & "\Sites.csv"
my_excel_app.Quit
データ型の自動識別がうまくいかない問題 Automatic identification of data types fails
インポート後にインポートエラーテーブルが出来て、内容が「型変換できませんでした」となるのは、自動でデータ型を識別できなかった場合です。Accessの仕様を調べてみると、ファイルの上から数十行のデータを読み込んでデータ型を決めているようです。何という仕様！！例えば数値と文字列が混在しているため、文字列でインポートすべき項目が、並び順によってどちらの型にもなってしまうという。
これの回避の為、まずは手動でCSVをインポートして、エラーが発生しないような「インポート定義」を作成します。
外部データの取り込みーテキストファイル「テキストインポートウィザード」の定義ボタンから、「インポート/エクスポートの定義」へ進み、項目ごとのデータ型を指定して、名前を付けて保存します。この定義を使って一度手動インポートし、エラーが発生しないことを確認します。その後、DoCmd.TransferTextのSpecificationName引数に、作った定義名をダブルクォーテーションで囲んで指定します。
DoCmd.TransferText TransferType:=acImportDelim, SpecificationName:="Sites_Import", _
TableName:="Sites", FileName:="Sites.csv", HasFieldNames:=True
SpecificationName:="Sites_Import" →定義Sites_Importを使ってインポートする。スペルミス・大文字小文字に注意。
元々自動でうまくいくファイルの場合は、引数を省略すると自動でインポートされます。
後で特定の項目のデータ型を指定したい場合 specify the data type of a specific item later
CSVファイルをインポートしたテーブルや、テーブルからクエリで新たに作った新テーブルで、項目のデータ型を任意に指定したい場合があります。これは、アクションクエリを使うと一発です。DoCmd.RunSQL "ALTER TABLE KPI ALTER COLUMN TIME_ID LONG;"
→TIME_IDという項目をLONG型に変更
ファイルサイズが肥大化する問題 Oh, no... the file size grows steadily with every operation
インポートを繰り返すと、いつの間にかaccdbのファイルサイズが大きくなり、2GB以上になると壊れてしまうことがあります。これ、残念ですがどちらもAccessの仕様です。酷い仕様ですが、テーブルを消してもデータ量は減らず、「データベースの最適化」を行って初めてゴミが消えるのです。最適化は（これもまた酷い仕様）Accessの処理上、空データベースにテーブルを全コピーして元テーブルを上書きする形になります。よって、VBAのコマンドでは実行できません。他ファイル（他のAccessやExcel VBA、Windos Script）からは実行可能です。若しくは、メニューから最適化するようにメッセージを出すか、閉じる時に最適化する設定にするしかありません。メニューからは「データベースツール」ー「データベースの最適化」、Accessのオプションからは「現在のデータベース」ー「閉じるときに最適化」です。
また、余計なテーブルを削除するコードもサンプルに載せましたので、参考にしてください。
コード一覧（標準モジュール）
Option Compare DatabasePublic Sub import_csv()
Dim csv_folder As String
If MsgBox("設定テーブルのデフォルトのフォルダからCSVをインポートしますか", vbYesNo) = vbYes Then
csv_folder = Application.DLookup("setting_value", "my_setting", "setting_item='csv_folder'")
Else
csv_folder = get_folder("フォルダを指定")
End If
If csv_folder = "" Then Exit Sub
DoCmd.SetWarnings False
'search exported csv files
Dim search_target, search_targets
search_targets = Array("Sites.csv", "long.csv", "short.csv", "volte.csv", "3gcalls.csv")
'Sites.csvのセル内改行を置換
Dim my_excel_app As New Excel.Application
my_excel_app.Visible = False
my_excel_app.UserControl = False
my_excel_app.Run "'" & Application.CurrentProject.Path & _
"\access_import_tool.xlsm'!replace_extra_character", csv_folder & "\Sites.csv"
my_excel_app.Quit
'import csv
Dim error_message As String
For Each search_target In search_targets
Dim searched_filename As String
searched_filename = Dir(csv_folder & "\" & search_target)
If searched_filename <> "" Then
If search_target = "Sites.csv" Then
DoCmd.TransferText TransferType:=acImportDelim, SpecificationName:="Sites_Import", _
TableName:=search_target, FileName:=csv_folder & "\" & searched_filename, HasFieldNames:=True
Else
DoCmd.TransferText TransferType:=acImportDelim, _
TableName:=search_target, FileName:=csv_folder & "\" & searched_filename, HasFieldNames:=True
End If
Else
error_message = error_message & search_target & "が見つかりませんでした。"
End If
Next
'Query correct data from csv files
DoCmd.OpenQuery "get_KPI"
'型変換
DoCmd.RunSQL "ALTER TABLE KPI ALTER COLUMN TIME_ID LONG;"
'delete extra tables except (Systemテーブル、Temporaryテーブル、必要テーブル)
Dim keep_table_list
keep_table_list = Array("MSys", "TMP", "KPI", "設定")
Dim database As database, table
Set database = CurrentDb
For Each table In database.TableDefs
If is_include_string(table.Name, keep_table_list) = False Then
DoCmd.DeleteObject acTable, table.Name
End If
Next
'ending process
DoCmd.SetWarnings True
If error_message = "" Then
MsgBox "正常にインポートしました。" & vbCrLf & "データベースの最適化を行ってください。"
Else
MsgBox "エラーが発生しました。エラー：" & error_message
End If
End Sub
'dialog for select folder
Private Function get_folder(title) As String
Dim Shell, myPath
Set Shell = CreateObject("Shell.Application")
Set myPath = Shell.BrowseForFolder(&O0, title, &H1 + &H10)
If Not myPath Is Nothing Then
get_folder = myPath.Items.Item.Path
Else
get_folder = ""
End If
Set Shell = Nothing
Set myPath = Nothing
End Function
0 件のコメント:
コメントを投稿