返回列表 上一主題 發帖

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

如果大大還有更好的方法,請回文讓小弟多學習,這資料可是花了很久的時間才突破的
joey0415 發表於 2013-8-7 16:10


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

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

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

  11.             For j = xa To rcnt - 2
  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.             Next j
  36.         ret = Left(ret, Len(ret) - 1)  '刪除最後一個","
  37.         SqlImport = ret
  38.         
  39. Set cn = CreateObject("adodb.connection")
  40.     cn.Open ("Driver={SQLite3 ODBC Driver};database=" & SQLName) '開啟sqlite指定資料庫,路徑與檔案名稱要對
  41.     cn.Execute (SqlImport)
  42.     cn.Close
  43. Set cn = Nothing
  44. End Sub
複製代碼

TOP

回復 6# koshi0413

剛才試了 7983筆,1分鐘
python的 for 好像比較快  哈哈
可是execl 還是有方便計算的地方

TOP

回復 8# PKKO

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

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

TOP

本帖最後由 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()

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

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

TOP

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

回復 13# PKKO

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

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

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

TOP

回復 13# PKKO

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

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

要如何改成陣列呢?

TOP

回復 15# koshi0413
找到教學了,謝謝P大指點^_^
https://dotblogs.com.tw/yc421206/archive/2008/12/20/6470.aspx

TOP

回復 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, "/", "-")

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

TOP

回復 18# PKKO

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

TOP

        靜思自在 : 能善用時間的人,必能掌握自己努力的方向。
返回列表 上一主題