返回列表 上一主題 發帖

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

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

回復 12# koshi0413


    ZA=cells(i,j)

先把CELLS換成陣列
後面的ZA都不用動,就已經快很多了
意思是一樣的

VBA強制轉換文字的指令是CSTR(XXX)
100多個儲存格也是一樣
先一次性轉為陣列
然後跑回圈
妳如果沒有轉陣列用range("A" & i) 大概會慢到想要打人吧!
PKKO

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

回復 17# koshi0413


    用法錯囉,你一樣是讀取了cells的值

rng=[a1].resize(r,c).value'r=列數,C=欄位數量
之後把CELLS的部分取代為rng就可以用陣列了

例如原本要取用cells(2,1) 就變成 rng(2,1)
意思完全相同,但不是取用儲存格而是取用陣列,速度直接大增哦!
PKKO

TOP

回復 18# PKKO

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

TOP

回復 18# PKKO

P大,哎呀呀,抱歉咧
應該又試錯了,一樣80秒@@

改成下面這樣是72秒,所以 if 內日期轉換需 8秒
myCols = 123
myRows = 7923
        Rng = [A3].Resize(myRows, myCols).Value  
            For j = 1 To (myRows - 2)
                zc = ""
                    For i = 1 To myCols
                        za = Rng(j, i)
                        zc = zc & "'" & za & "',"
                    Next i
                zc = Left(zc, Len(zc) - 1)
                rez = "(" & zc & ")"
                ret = ret & rez & ","
                DoEvents
            Next j
  1.         
  2. Application.ScreenUpdating = False
  3. StartTime = Timer
  4.         myCols = Range("A2").CurrentRegion.Columns.Count '計算所有的列
  5.         myRows = Range("A1").CurrentRegion.Rows.Count    '計算所有的行
  6.         Rng = [A3].Resize(myRows, myCols).Value         
  7.             For j = 1 To (myRows - 2)
  8.                 zc = ""
  9.                     For i = 1 To myCols
  10.                         za = Rng(j, i)
  11.                             If za = "" Then '空格轉換
  12.                                 za = 0
  13.                                 
  14.                             ElseIf i = 2 Then '日期轉換
  15.                                 If (Len(Split(za, "/")(1)) = 1) And (Len(Split(za, "/")(2)) = 1) Then
  16.                                     za = Split(za, "/")(0) & "-0" & Split(za, "/")(1) & "-0" & Split(za, "/")(2)
  17.                                 ElseIf Len(Split(za, "/")(1)) = 1 Then
  18.                                     za = Split(za, "/")(0) & "-0" & Split(za, "/")(1) & "-" & Split(za, "/")(2)
  19.                                 ElseIf Len(Split(za, "/")(2)) = 1 Then
  20.                                     za = Split(za, "/")(0) & "-" & Split(za, "/")(1) & "-0" & Split(za, "/")(2)
  21.                                 Else
  22.                                     za = Split(za, "/")(0) & "-" & Split(za, "/")(1) & "-" & Split(za, "/")(2)
  23.                                 End If
  24.                                 
  25.                             End If
  26.                         zc = zc & "'" & za & "',"
  27.                     Next i
  28.                 zc = Left(zc, Len(zc) - 1)  '刪除最後一個","
  29.                 rez = "(" & zc & ")"
  30.                 ret = ret & rez & ","
  31.                 DoEvents
  32.             Next j
  33. EndTime = Timer
  34. MsgBox Format(EndTime - StartTime, "00.00") & "秒"
  35. Application.ScreenUpdating = True
複製代碼

TOP

        靜思自在 : 每天無所事事,是人生的消費者,積極、有用才是人生的創造者。
返回列表 上一主題