VBA エクセルからSQL実行(SQLite)

最近では専門家ではなくてもデータ分析ができたほうがいいという話をよく聞きます。そういう話の中で利用されているソフトはだいたい専用ソフトかエクセルといった感じでしょうか。

ただ数は少ないですがSQLを使おうというのもあるようです。実際SQLは非常に便利でエクセルより多少敷居は高いですが、少し使えればエクセルでは面倒な処理も簡単にできたりします。経験上、ワークシート関数、VBA、SQLを使い分ければほとんどの処理ができると思います。

ですが、いざSQLを使おうとするとどのソフトを使ったらいいのか迷うと思います。一般的にはAccess、SQL Server、MySQL(MariaDB)、SQLiteあたりがお勧めされていますが、もしPCにAccessが入っているならAccessが一番手軽に始められるのではないかと思います。今回はSQLiteを採用していますが、私も最初はAccessを使っていました。

中小企業で働いているとデータ分析やITの専門家がいない場合も多いので、自分たちで少しでもデータ分析ができると役に立つことが多いです。

追記

ちょっと原因は分からないけど、SQLite標準のカラムのデータ型(textやinteger)でテーブルを作成するとCopyFromRecordsetで取得できなくなる。

varchar(255)をカラムのデータ型に指定しているとCopyFromRecordsetで問題なく取得できる。

CopyFromRecordsetはテーブル作成時の型の影響を受けているらしい。とりあえずCopyFromRecordsetを使わないパターン

 

SQLiteメモ

準備

sudo apt-get update
sudo apt-get install sqlite3

基本

DBへの作成、接続
sqlite3 xxx

情報確認
.schema

終了
.exit

指定できる型
TEXT
NUMERIC
INTEGER
REAL
NONE

キーワード

PRIMARY KEY、ROWID、AUTOINCREMENT

INTEGER PRIMARY KEY
と設定しても自動で増加するが、以前使った値を再度使う。

INTEGER PRIMARY KEY AUTOINCREMENT
とすると、以前の値を使わない。

create table xxx (id integer primary key autoincrement)
のような感じ。

NOT NULL、UNIQUE、DEFAULT、CHECK

テーブルの作成、削除は標準
create,drop等。

例)
create table news (
id integer not null primary key autoincrement,
title text not null,
slug text not null,
text text not null
);

xamppでの利用

php.iniの以下コメント外す
extension=php_sqlite3.dll

application\config\database.php
の以下を修正

‘database’ => APPPATH.’sqlite/db’,
‘dbdriver’ => ‘sqlite3’,

CodeIgniter + SQLite + mod_rewriteの使い方メモ

SQLiteメモ

DBへの作成、接続
sqlite3 xxx

情報確認
.schema

終了
.exit

指定できる型
TEXT
NUMERIC
INTEGER
REAL
NONE

キーワード

PRIMARY KEY、ROWID、AUTOINCREMENT

INTEGER PRIMARY KEYと設定しても自動で増加するが、以前使った値を再度使う。
INTEGER PRIMARY KEY AUTOINCREMENTとすると、以前の値を使わない。

create table xxx (id integer primary key autoincrement)
のような感じ。

NOT NULL、UNIQUE、DEFAULT、CHECK

テーブルの作成、削除は標準
create,drop等。

例)
create table news (
id integer not null primary key autoincrement,
title text not null,
slug text not null,
text text not null
);

xampp+SQLiteでの設定

php.iniの以下コメント外す
extension=php_sqlite3.dll

application\config\database.php
の以下を修正

‘database’ => APPPATH.’sqlite/db’,
‘dbdriver’ => ‘sqlite3’,

SQLiteのみ別記事へ

CodeIgniterの使い方(ユーザガイド参考)

コントローラ

application/controllers/pages.php
コントローラクラス名とファイル名が同じ。

ルーティングはこのような感じ。
/コントローラクラス/コントローラクラスメソッド/引数
例)
http://localhost/c/index.php/pages/view/news

定義はこのような感じ
class Pages extends CI_Controller {
public function view($page = ‘home’){}
}

CI_Controllerを継承しているので、
system/core/Controller.php
を利用できる。

テンプレート

application/views/templates/header.php

以下のような感じ。
<html>
<head>
<title>CodeIgniter Tutorial</title>
</head>
<body>
<h1><?php echo $title; ?></h1>

静的なページ

application/views/pages/
home.phpやabout.php等作成。

コントローラからの呼び出し

public function view($page = ‘home’)
{
if ( ! file_exists(APPPATH.’views/pages/’.$page.’.php’))
{
show_404();
}
$data[‘title’] = ucfirst($page); // 頭文字を大文字に
$this->load->view(‘templates/header’, $data);
$this->load->view(‘pages/’.$page, $data);
$this->load->view(‘templates/footer’, $data);
}

モデルの作成

application\models
News_model.phpを作成し、以下を記述

<?php
class News_model extends CI_Model {
public function __construct()
{
  $this->load->database();
}
public function get_news($slug = FALSE)

{
if ($slug === FALSE)
{
$query = $this->db->get(‘news’);
return $query->result_array();
}
$query = $this->db->get_where(‘news’, array(‘slug’ => $slug));
return $query->row_array();
}
}

モデルを呼び出すためのコントローラ

<?php
class News extends CI_Controller {
public function __construct()
{
parent::__construct();
$this->load->model(‘news_model’); //モデルのロード
$this->load->helper(‘url_helper’);
}
public function index()
{
$data[‘news’] = $this->news_model->get_news();
//モデル->モデルメソッド
}
public function view($slug = NULL)
{
$data[‘news_item’] = $this->news_model->get_news($slug);
}
}

変数

コントロール内の$data[‘title’]のビュー内の$title

ルーティング

application/config/routes.php

$route[‘default_controller’] = ‘pages/view’;
default_controllerはURIに何もない場合、
コントローラ/メソッドになるがメソッドを省略するとindexを呼ぶ。

$route[‘(:any)’] = ‘news/$1’;
//このルーティングを使うと、
//http://localhost/c/index.php/create
//とアクセスすると、
//http://localhost/c/index.php/news/create
//実際は上記にアクセスしたことになる。

index.phpを消す。

httpd.confのmod_rewriteが有効かどうか確認。(通常有効)

ルートに.htaccessを作成。(localhost/c/.htaccess)

RewriteEngine on
RewriteCond $1 !^(index\.php|images|robots\.txt)
RewriteRule ^(.*)$ /c/index.php/$1 [L]
と記述。

.htaccess(mod_rewrite)について

RewriteEngine On
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule ^(.*)$ index.php/$1 [L]

RewriteCondは
RewriteRuleを実行するための条件。IF文のようなもの。

以下記述例。

RewriteCond %変数名(テスト文字列) 条件パターン(正規表現) [フラグ]
RewriteRule ここにRewriteRuleのURL書換&転送設定を記述

フラグは何も記述しないとANDで、ORの場合[OR]と記述。
[OR]を使わずに空白行を入れてもOR扱い。
[NC]と記述すると大文字/小文字の区別をしない。
ふたつ同時の場合。[OR,NC]

WordPress等のmod_rewrite

<IfModule mod_rewrite.c>
RewriteEngine On
RewriteBase /

# Rewrite処理後にベースとなるURL
# 記述がないとhtaccessの設置ディレクトリが選択
# RewriteRuleが相対パスのみ適用される。

RewriteCond %{REQUEST_FILENAME} !-f

# %{REQUEST_FILENAME} が !-f だったという条件式。
# ! と -f であり、
# -f はファイルかどうか。! は真偽の反転。

RewriteCond %{REQUEST_FILENAME} !-d

# -d はディレクトリかどうか

RewriteRule . /index.php [L]

# URLが(.)だったら、/index.phpを参照。
# 全てのアクセスが/index.phpを参照ということ。
# [L]は最後という意味。

</IfModule>

フラグは
[NC] 大小区別無し
[R] リダイレクト
[R=301] リダイレクト

C# SQLiteへ接続

PHP SQLiteに接続