12
返回列表 上一主題 發帖

excel vba 查詢 Sqlite資料庫(設定與語法)範例

joey0415
　暱稱: joey0415

中學生Rank: 2

帖子
361 
主題
57 
精華
0 
積分
426 
點名
0  
作業系統
win7 
軟體版本
2003,2010 
閱讀權限
20 
性別
男 
註冊時間
2010-5-13 
最後登錄
2022-12-8 
1# 跳轉到 » 倒序看帖
打印
tT
發表於 2013-7-31 20:05 | 只看該作者

excel vba 查詢 Sqlite資料庫(設定與語法)範例

本帖最後由 joey0415 於 2013-7-31 20:16 編輯

自己試了很久，終於成功了，網路上以vba操作sqlite的文章非常少，所以發個文來紀錄一下

不能傳附件，附上dorpbox連結下載

sqlite odbc driver要裝
http://www.ch-werner.de/sqliteodbc/

附上練習資料庫與excel vba 檔

https://dl.dropboxusercontent.com/u/40393708/sqlite/sqlite3.zip
  1. Sub select輸出至sheet1中()
  2.     Dim cn, rs, f
  3.    
  4.     Set cn = CreateObject("adodb.connection")
  5.     '開啟sqlite指定資料庫，路徑與檔案名稱要對
  6.     cn.Open ("Driver={SQLite3 ODBC Driver};database=E:\joey.sqlite")
  7.     '寫出SQL查詢語法
  8.     Set rs = cn.Execute("SELECT  *  FROM  home ")
  9.     Sheet1.Cells.Delete
  10.     For f = 0 To rs.fields.Count - 1
  11.         Sheet1.Cells(1, f + 1).Value = rs.fields(f).Name
  12.     Next
  13.     Sheet1.Cells(2, 1).CopyFromRecordset (rs)
  14.     cn.Close
  15. End Sub
複製代碼
  1. Sub insert()

  3.     Dim x
  4.     Dim sql_command As String
  5.     Dim connection_string As String
  6.    
  7.         Set x = CreateObject("adodb.recordset")
  8.         sql_command = "insert into home(name, age) values('mary','50') "
  9.         connection_string = ("Driver={SQLite3 ODBC Driver};database=E:\joey.sqlite")
  10.         x.Open sql_command, connection_string
  11.         Set x = Nothing
  12.    
  13. End Sub
複製代碼
收藏 分享

koshi0413

帖子
55 
主題
1 
精華
0 
積分
81 
點名
0  
作業系統
win 
軟體版本
10 
閱讀權限
20 
註冊時間
2016-5-15 
最後登錄
2018-11-1 
2#
發表於 2016-9-26 19:39 | 只看該作者
回復 1# joey0415

想請問 joey0415 大，小弟剛好也在試sqltie3
目前小弟整理使用sqltie3 ，整理資料如下圖。
因為考慮到未來如果要多表整合的情況（目前查詢還不太熟練）
所以想請教一下~~

A。stock_每日更新.db內丟進 1000~9000各股成為表格 （單一檔案會很大）
B。每股代號各為.db，然後用附加數據的方式導入stocktest.db（單一檔案小，但會不會查詢麻煩？）

A vs B   未來查詢哪個比較方便呢？
因為目前還在初期規畫，所以想了解一下 joey0415 ，看小弟是否能跟進~~

TOP

joey0415
　暱稱: joey0415

中學生Rank: 2

帖子
361 
主題
57 
精華
0 
積分
426 
點名
0  
作業系統
win7 
軟體版本
2003,2010 
閱讀權限
20 
性別
男 
註冊時間
2010-5-13 
最後登錄
2022-12-8 
3#
發表於 2016-9-26 20:40 | 只看該作者
回復 2# koshi0413

所有個股股價資料放進單一檔案，例如stock.sqlite中，再透過select 選出你要的股票資料才對，因為放進同一個資料，才可以選所有指定區間的股票與股價等…
，這樣不用幾秒，透過程select的語法，還可以指定區間轉檔單週或單月所有股票週或月資料，一定不用30秒，我的資料庫六年日週月所有股票與集保，檔案才800mb
沒有什麼問題

TOP

koshi0413

帖子
55 
主題
1 
精華
0 
積分
81 
點名
0  
作業系統
win 
軟體版本
10 
閱讀權限
20 
註冊時間
2016-5-15 
最後登錄
2018-11-1 
4#
發表於 2016-9-26 21:07 | 只看該作者
回復 3# joey0415

多謝 joey0415 大願意說明^^
小弟提個疑問 Stock.db >> 會有 mian >> 才有 Table
然後Table是分類，還是各股呢？

小弟的想法為：
1.Table=每日行情
2.Table=每日資券
3.Table=每日法人持股
4.Table=每日券商進出
5.Table=集保庫存
6.Table=每月營收
7.Table=每季財報
8.Table=每年財報
但 Table 之下好像無法包含個股？

所以有個想法，如果改成 Stock.db >>  mian >>  Table
mian 為附加數據庫
1. Stock.db 附加 每日行情.db >>  Table為1000~9000個股
2.Stock.db 附加 每日資券.db >>  Table為1000~9000個股
3.Stock.db 附加 每日法人持股.db >>  Table為1000~9000個股
4.Stock.db 附加 每日券商進出.db >>  Table為1000~9000個股
5.Stock.db 附加 集保庫存.db >>  Table為1000~9000個股
6.Stock.db 附加 每月營收.db >>  Table為1000~9000個股
7.Stock.db 附加 每季財報.db >>  Table為1000~9000個股
8.Stock.db 附加 每年財報.db >>  Table為1000~9000個股
不知道這樣會不會好查詢呢？

還是您為的型式為：（單一Table即1000~9000）
1.Table=每日行情
2.Table=每日資券
3.Table=每日法人持股
4.Table=每日券商進出
5.Table=集保庫存
6.Table=每月營收
7.Table=每季財報
8.Table=每年財報

ps: 目前法人持股+每日資券 二年期就120mb了@@

TOP

PKKO
　暱稱: PKKO

高中生Rank: 3Rank: 3

帖子
549 
主題
152 
精華
0 
積分
691 
點名
0  
作業系統
WIN7 
軟體版本
OFFICE 2010 
閱讀權限
50 
性別
男 
註冊時間
2013-8-10 
最後登錄
2022-9-7 

論壇榮譽勳章論壇贊助勳章
5#
發表於 2016-9-26 21:30 | 只看該作者
回復 1# joey0415


     joey大大您好,
非常感謝您的大力分享
想向您請教Sqlite幾個問題

一、與MSsql的一般操作法相同嗎?例如輸入日期、數字、文字的時候
二、與Access一樣都是檔案方式的資料庫,其資料庫大小、連線人數、連線的執行速度，不曉得大大是否知悉?
三、Sqlite本身要如何備份?
PKKO

TOP

joey0415
　暱稱: joey0415

中學生Rank: 2

帖子
361 
主題
57 
精華
0 
積分
426 
點名
0  
作業系統
win7 
軟體版本
2003,2010 
閱讀權限
20 
性別
男 
註冊時間
2010-5-13 
最後登錄
2022-12-8 
6#
發表於 2016-9-26 21:43 | 只看該作者
回復 4# koshi0413

你看圖就知道了

120mb很少，sqlite容量最大是2T，很夠用

資料抓得到，會用SQL  語法可以解法很多問題，它的運算比VBA陣列等都還快上許多

TOP

joey0415
　暱稱: joey0415

中學生Rank: 2

帖子
361 
主題
57 
精華
0 
積分
426 
點名
0  
作業系統
win7 
軟體版本
2003,2010 
閱讀權限
20 
性別
男 
註冊時間
2010-5-13 
最後登錄
2022-12-8 
7#
發表於 2016-9-26 21:49 | 只看該作者
回復 5# PKKO

您好！

一、應該一樣才對，資料都是進抓來就先處理好(資料格式)
二、ACCESS最大2G, sqlite 2T，連線人數我沒試過，速度一樣快
三、它是單一檔案，直接複製貼上即可，差異備份好條沒有

TOP

koshi0413

帖子
55 
主題
1 
精華
0 
積分
81 
點名
0  
作業系統
win 
軟體版本
10 
閱讀權限
20 
註冊時間
2016-5-15 
最後登錄
2018-11-1 
8#
發表於 2016-9-26 21:56 | 只看該作者
回復 6# joey0415

看了 joey0415大的圖就了解了，原來是方法三，這是小弟一開始的作法xd
型式為：（單一Table即1000~9000個股）
1.Table=每日行情
2.Table=每日資券
3.Table=每日法人持股
4.Table=每日券商進出
5.Table=集保庫存
6.Table=每月營收
7.Table=每季財報
8.Table=每年財報

二星期前剛接觸sql，還不知道搜尋語法可以算這麼快速，所以才改為各別檔案
最近利用上班時間常常在找sql資料，原來索引、雙查詢也是很重要的（剛開始真的不懂@@）
結果本來查一筆資料要25s，加上索引後就變3s了，然後在改一下就變0.08s!!!
所以這二天的認真思考，怎樣的整理方式比較好，本身沒碰過這類的東西，語法也看得辛苦，就是範本一直試一直試~~
還好  joey0415大 可以幫忙解惑，非常感謝您:victory:

TOP

koshi0413

帖子
55 
主題
1 
精華
0 
積分
81 
點名
0  
作業系統
win 
軟體版本
10 
閱讀權限
20 
註冊時間
2016-5-15 
最後登錄
2018-11-1 
9#
發表於 2016-9-26 22:00 | 只看該作者
回復 5# PKKO

備份小弟目前是用 gdrive 每天上傳，可以有備份記錄 不過只有 15gb

另外有別的軟體，可以設定每天備份，只不過備份出來的是單一檔份

如果要同步備份到別的資料夾or硬碟，可以上網查.bat，寫一個簡單的同步檔即可，設定開機啟動之類的

TOP

c_c_lai

帖子
2035 
主題
24 
精華
0 
積分
2031 
點名
0  
作業系統
Win7 
軟體版本
Office2010 
閱讀權限
100 
性別
男 
註冊時間
2012-3-22 
最後登錄
2024-2-1 
10#
發表於 2016-9-27 07:59 | 只看該作者
回復 1# joey0415
請教一下有關 Sqlite 要如何正確安裝？
我已經下載瞭一些套件如圖：

我執行了 sqliteodbc.exe 以及 sqliteodbc_w64.exe
但是接下來要如何應用？
從未接觸過它還請多多指教！

TOP

        靜思自在 : 欣賞別人就是莊嚴自己。
12
返回列表 上一主題