12
返回列表 上一主題 發帖

[發問] excel vba (插入資料)insert 進 sqlite 緩慢問題

koshi0413

帖子
55 
主題
1 
精華
0 
積分
81 
點名
0  
作業系統
win 
軟體版本
10 
閱讀權限
20 
註冊時間
2016-5-15 
最後登錄
2018-11-1 
1# 跳轉到 »
發表於 2016-12-25 21:23 | 顯示全部帖子
如果大大還有更好的方法，請回文讓小弟多學習，這資料可是花了很久的時間才突破的
joey0415 發表於 2013-8-7 16:10


小弟引用你上述的方式，一直跑到第500筆即失敗
以下是小弟的方法，速度好像還好，範列 4000筆，有比你原來的一筆一筆快，但應該比你的一次500筆慢
  1. Sub SQLite_insert()

  3. Sheets("SQL JOIN").Select
  4. SQLName = "D:\Dropbox\SQLite3\Stock.sqlite"

  6.     insert = "insert into Stock values"
  7.     ret = ""
  8.     ret = insert
  9.    
  10.     et = 113 '導入的標題數
  11.     rcnt = Range("A1").CurrentRegion.Rows.Count '計算所有的行
  12.     xa = 3 '從第三行開始計算

  14.             For j = xa To rcnt - 2
  15.                 zc = ""
  16.                     For i = 1 To et
  17.                         za = Cells(j, i)
  18.                             If za = "" Then '空格轉換
  19.                                 za = 0
  20.                                 
  21.                             ElseIf i = 2 Then '日期轉換
  22.                                 If (Len(Split(za, "/")(1)) = 1) And (Len(Split(za, "/")(2)) = 1) Then
  23.                                     za = Split(za, "/")(0) & "-0" & Split(za, "/")(1) & "-0" & Split(za, "/")(2)
  24.                                 ElseIf Len(Split(za, "/")(1)) = 1 Then
  25.                                     za = Split(za, "/")(0) & "-0" & Split(za, "/")(1) & "-" & Split(za, "/")(2)
  26.                                 ElseIf Len(Split(za, "/")(2)) = 1 Then
  27.                                     za = Split(za, "/")(0) & "-" & Split(za, "/")(1) & "-0" & Split(za, "/")(2)
  28.                                 Else
  29.                                     za = Split(za, "/")(0) & "-" & Split(za, "/")(1) & "-" & Split(za, "/")(2)
  30.                                 End If
  31.                                 
  32.                             End If
  33.                         zc = zc & "'" & za & "',"
  34.                     Next i
  35.                 zc = Left(zc, Len(zc) - 1)  '刪除最後一個","
  36.                 rez = "(" & zc & ")"
  37.                 ret = ret & rez & ","
  38.             Next j
  39.         ret = Left(ret, Len(ret) - 1)  '刪除最後一個","
  40.         SqlImport = ret
  41.         
  42. Set cn = CreateObject("adodb.connection")
  43.     cn.Open ("Driver={SQLite3 ODBC Driver};database=" & SQLName) '開啟sqlite指定資料庫，路徑與檔案名稱要對
  44.     cn.Execute (SqlImport)
  45.     cn.Close
  46. Set cn = Nothing
  47. End Sub
複製代碼

TOP

koshi0413

帖子
55 
主題
1 
精華
0 
積分
81 
點名
0  
作業系統
win 
軟體版本
10 
閱讀權限
20 
註冊時間
2016-5-15 
最後登錄
2018-11-1 
2#
發表於 2016-12-25 21:54 | 顯示全部帖子
回復 6# koshi0413

剛才試了　７９８３筆，１分鐘
python的 for 好像比較快  哈哈
可是execl 還是有方便計算的地方

TOP

koshi0413

帖子
55 
主題
1 
精華
0 
積分
81 
點名
0  
作業系統
win 
軟體版本
10 
閱讀權限
20 
註冊時間
2016-5-15 
最後登錄
2018-11-1 
3#
發表於 2016-12-28 12:44 | 顯示全部帖子
回復 8# PKKO

P大
小弟的例子就是用跟您一下的方法
恩   vba跑迴圈是不是比較慢呢？
一次500筆很快
一次7999筆，一分鐘
然後一次性丟入 sqlite3

j大的批次方式，好像就是 7999/500 ，分段的一次性丟入
只是小弟分段丟入都出門，7999/3000 ，也一樣丟不進，
很奇怪，不知道是不是sqlite3反應時間的問題，下次有空, 中間丟個等待好了
不過這樣一來，就跟一次性7999迴圈差不多時間？
要試試~~

TOP

koshi0413

帖子
55 
主題
1 
精華
0 
積分
81 
點名
0  
作業系統
win 
軟體版本
10 
閱讀權限
20 
註冊時間
2016-5-15 
最後登錄
2018-11-1 
4#
發表於 2016-12-31 14:31 | 顯示全部帖子
本帖最後由 koshi0413 於 2016-12-31 14:34 編輯

回復 10# PKKO

可否請p大協助一下，看小弟的語法是不是有問題  
因為在跑  For j = 3 To rcnt  << 最多會 3 to 10000 迴圈許久
za = cell(j,i) 是為了空格&字串判別，這好像不這樣的話導入日期都錯誤，還是可以這樣  str("2016/03/03") 這樣轉換成字串

至於導入sqlite3 是這行，開關一下就結束了，目前關卡在 迴圈太久 orz.....
Set cn = CreateObject("adodb.connection")
    cn.Open ("Driver={SQLite3 ODBC Driver};database=" & SQLName)
    cn.Close
Set cn = Nothing

ps:小弟是程式白痴，全自學的， python比較順手
  1. Sub SQLite_insert()

  3. Sheets("SQL JOIN").Select
  4. SQLName = "D:\Dropbox\SQLite3\StockAll.sqlite"

  6.     insert = "insert into StockAll values"
  7.     ret = ""
  8.     ret = insert
  9.         et = 114 '導入的標題數
  10.         rcnt = Range("A1").CurrentRegion.Rows.Count '計算所有的行
  11.             For j = 3 To rcnt
  12.                 zc = ""
  13.                     For i = 1 To et
  14.                         za = Cells(j, i)
  15.                             If za = "" Then '空格轉換
  16.                                 za = 0
  17.                                 
  18.                             ElseIf i = 2 Then '日期轉換
  19.                                 If (Len(Split(za, "/")(1)) = 1) And (Len(Split(za, "/")(2)) = 1) Then
  20.                                     za = Split(za, "/")(0) & "-0" & Split(za, "/")(1) & "-0" & Split(za, "/")(2)
  21.                                 ElseIf Len(Split(za, "/")(1)) = 1 Then
  22.                                     za = Split(za, "/")(0) & "-0" & Split(za, "/")(1) & "-" & Split(za, "/")(2)
  23.                                 ElseIf Len(Split(za, "/")(2)) = 1 Then
  24.                                     za = Split(za, "/")(0) & "-" & Split(za, "/")(1) & "-0" & Split(za, "/")(2)
  25.                                 Else
  26.                                     za = Split(za, "/")(0) & "-" & Split(za, "/")(1) & "-" & Split(za, "/")(2)
  27.                                 End If
  28.                                 
  29.                             End If
  30.                         zc = zc & "'" & za & "',"
  31.                     Next i
  32.                 zc = Left(zc, Len(zc) - 1)  '刪除最後一個","
  33.                 rez = "(" & zc & ")"
  34.                 ret = ret & rez & ","
  35.                 DoEvents
  36.             Next j
  37.         'MsgBox "跑完迴圈"
  38.         ret = Left(ret, Len(ret) - 1)  '刪除最後一個","
  39.         SqlImport = ret
  40.         
  41. Set cn = CreateObject("adodb.connection")
  42.     cn.Open ("Driver={SQLite3 ODBC Driver};database=" & SQLName)
  43.     cn.Close
  44. Set cn = Nothing
  45. End Sub
複製代碼

TOP

koshi0413

帖子
55 
主題
1 
精華
0 
積分
81 
點名
0  
作業系統
win 
軟體版本
10 
閱讀權限
20 
註冊時間
2016-5-15 
最後登錄
2018-11-1 
5#
發表於 2016-12-31 15:30 | 顯示全部帖子
p大，
('Bob','SHIT','TWN','SHIT2','SHIT3',2016-01-01," & 100 + i & ")"  << 這行是小弟看不懂的地方
恩~~為何看不懂，是因為小弟要逐行120個儲存格皆寫入，
還是要寫成  strSQL = "INSERT INTO customer VALUES (Range("A" & i),Range("B" & i),Range("C" & i),.......共120個.......," & 100 + i & ")"
我好像觀念怪怪的@@
  1. Sub WRITE_SQL() '寫入資料
  2. conn_connect'這是呼叫開啟SQL
  3. For i = 1 To 10000
  4.     strSQL = "INSERT INTO customer VALUES ('Bob','SHIT','TWN','SHIT2','SHIT3',2016-01-01," & 100 + i & ")" '每一列的語法
  5.     If i = 1 Then
  6.            strSQL2 = strSQL
  7.     Else
  8.            strSQL2 = strSQL2 & ";" & strSQL'將語法連接
  9.     End if
  10. Next
  11. Conn.Execute (strSQL2)'執行SQL
  12. End Sub
複製代碼

TOP

koshi0413

帖子
55 
主題
1 
精華
0 
積分
81 
點名
0  
作業系統
win 
軟體版本
10 
閱讀權限
20 
註冊時間
2016-5-15 
最後登錄
2018-11-1 
6#
發表於 2016-12-31 21:39 | 顯示全部帖子
回復 13# PKKO

哇~~新年快樂，沒想到跨年夜還回文

非常謝謝唷，小弟先改來試一下，如果還慢，在上來請教~~~

所以 cells(i,j) 速度 > Range("A" & i)   這點小弟倒是不知道，還以為一樣呢

TOP

koshi0413

帖子
55 
主題
1 
精華
0 
積分
81 
點名
0  
作業系統
win 
軟體版本
10 
閱讀權限
20 
註冊時間
2016-5-15 
最後登錄
2018-11-1 
7#
發表於 2016-12-31 23:42 | 顯示全部帖子
回復 13# PKKO

P大，小弟單獨弄了個迴圈來試，一樣要一分鐘也？
是小弟弄錯了嘛

for i = 1 to 10000
cells(i.2) = i
next i

要如何改成陣列呢？

TOP

koshi0413

帖子
55 
主題
1 
精華
0 
積分
81 
點名
0  
作業系統
win 
軟體版本
10 
閱讀權限
20 
註冊時間
2016-5-15 
最後登錄
2018-11-1 
8#
發表於 2017-1-1 01:19 | 顯示全部帖子
回復 15# koshi0413
找到教學了，謝謝P大指點^_^
https://dotblogs.com.tw/yc421206/archive/2008/12/20/6470.aspx

TOP

koshi0413

帖子
55 
主題
1 
精華
0 
積分
81 
點名
0  
作業系統
win 
軟體版本
10 
閱讀權限
20 
註冊時間
2016-5-15 
最後登錄
2018-11-1 
9#
發表於 2017-1-1 14:31 | 顯示全部帖子
回復 13# PKKO

p大，今天試了一下，也不知道對不對
一樣 80秒，好像都是卡在迴圈，是不是小弟用法弄錯啦，
https://dotblogs.com.tw/yc421206/archive/2008/12/20/6470.aspx  <<但是照這個網頁的範例跑是非常快的(16秒)，疑問

如果可以的話，可以指導一下嘛？您說的 za=cells(j,i) 改成陣列，實在是試不出來
  1.         myCols = Range("A2").CurrentRegion.Columns.Count '計算所有的列
  2.         myRows = Range("A1").CurrentRegion.Rows.Count    '計算所有的行
  3.         ReDim myArray(1 To myRows, 1 To myCols)          '動態陣列
  4.             For j = 3 To myRows
  5.                 zc = ""
  6.                     For i = 1 To myCols
  7.                         myArray(j, i) = Cells(j, i)
  8.                         za = myArray(j, i)
  9.                             If za = "" Then '空格轉換
  10.                                 za = 0
  11.                                 
  12.                             ElseIf i = 2 Then '日期轉換
  13.                                 za = Replace(za, "/", "-")

  15.                             End If
  16.                         zc = zc & "'" & za & "',"
  17.                     Next i
  18.                 zc = Left(zc, Len(zc) - 1)  '刪除最後一個","
  19.                 rez = "(" & zc & ")"
  20.                 ret = ret & rez & ","
  21.                 DoEvents
  22.             Next j
複製代碼

TOP

koshi0413

帖子
55 
主題
1 
精華
0 
積分
81 
點名
0  
作業系統
win 
軟體版本
10 
閱讀權限
20 
註冊時間
2016-5-15 
最後登錄
2018-11-1 
10#
發表於 2017-1-1 23:46 | 顯示全部帖子
回復 18# PKKO

果然用法錯了@@
謝謝 p大指導，今天一氣之下弄了python來導入了，約10秒
明天再來試vba陣列，非常感謝唷!!!!!!

TOP

        靜思自在 : 我們最大的敵人不是別人．可能是自己。
12
返回列表 上一主題