スポンサードリンク

Excelでビッグデータを乗りこなせ(4.ADODBでCSV取り込みの準備)

前回でSQLiteCSVファイルを取り込む方法をADODBで行うことを決定した。

www.excellovers.com

今回からADODBを利用したCSV取り込みの方法を紹介します。

会計ソフトの代わりに家計簿ソフト

今回のコードは会社で使用している会計ソフトから出力したCSVファイルを取り込むために使用しているのだが、さすがにデータを持って変えるわけにもいかないので、かわりに自宅で使用している家計簿ソフトがCSVファイルをエクスポート出来るのでこのデータで検証してみます。

ちなみに

うきうき家計簿と言うソフトです。

www.eases.jp

データの入力自体はこのソフトに入力して、CSVを出力してExcelに貼り付けしてBS/PLを作成しています。

職場の場合でもやりたいことはほぼ同じで、会計ソフトからCSVを出力して部門別のPLを簡単に集計しようとしています。

この自宅環境を擬似職場環境として、ロジックを作っていきたいと思います。

CSVデータ構成

うきうき家計簿から出力されるデータは下記のとおりです。

項目名
日付 2019/06/01
項目 医療・衛生
内訳 衛生消耗品
品名 トイレットペーパー
お店 スギ薬局
収入
支出 289
口座 現金
メモ

これらのデータをSQLiteに取り込みます。


ADODBの使用準備

参照設定

ExcelからADODBを使用するには参照設定が必要です。

VBEを開いて上部メニューから

ツール⇛参照設定で「Microsoft ActiveX Data Objects 2.8 Library」にチェックを入れる。

2.8の部分は使用環境によって異なるかもしれませんが、表示されているものを選択すれば大丈夫と思います。
f:id:ExcelLover:20190602130544j:plain

テキストデータの場合はschema.iniの準備

ADODBでデータベースやExcelファイルを操作する場合は上記の参照設定さえ行えば操作できるのですが、テキストファイルの場合はもう一手間必要になります。


それは「schema.ini」ファイルの準備です。


データベースやExcelファイルであれば、そのファイル自体に列の属性を持っていますが、テキストファイルはそのような属性を持っていません。

そのため各列がどのような列名でどのような属性を持っているかを明示するものがschema.iniファイルになります。

作成方法は簡単です。メモ帳などのテキストエディタで新規ファイルを作成します。

今回の家計簿ソフトの例では次のように記載します。

[家計一覧1904.csv]
ColNameHeader = True
CharacterSet = ANSI
Format = CSVDelimited
Col1=日付     Char
Col2=項目     Char
Col3=内訳     Char
Col4=品名     Char
Col5=お店     Char
Col6=収入     Integer
Col7=支出     Integer
col8=口座     Char
col9=メモ     Char
項目名 内容
ファイル名 CSVファイル名を[ ]で挟んで記載します。
colNameHeader 1行目をフィールド名として扱う場合はTrueを指定
CharacterSet 文字セットの指定
Format 区切り文字を指定します。
・カンマ区切り→CSVDelimited
・タブ区切り→TabDelimited
・任意の区切り文字→Delimited(区切り文字)
・固定長→FixedLength
フィールド定義 Col列番号 = フィールド名 データ型
データ型
・bit
・byte
・short
・Long
・Currency
・Single
・Double
・Datetime
・Text
・Memo
等がある

SQL文でのフィールド名の記載はこのSchema.iniに記載されているフィールド名を使用する。


まとめ

今回は取り込む用のCSVの準備から、ADODBでテキストファイルを扱う場合に必要なschema.iniの準備までを行いました。

次回以降は取り込み用のSQLite3データベースの作成と実際にSQLite3データベースへCSVファイルの取り込みを検証したいと思います。


schema.iniについては下記を参考

docs.microsoft.com


ADODBについては下記の書籍で勉強しました。未だによく見ます。
仕事に役立つExcel&Accessデータベース連携テクニック

仕事に役立つExcel&Accessデータベース連携テクニック

仕事に役立つExcel&Accessデータベース連携テクニック