123
返回列表 上一主題 發帖

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

koshi0413

帖子
55 
主題
1 
精華
0 
積分
81 
點名
0  
作業系統
win 
軟體版本
10 
閱讀權限
20 
註冊時間
2016-5-15 
最後登錄
2018-11-1 
11# 跳轉到 »
發表於 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 
12#
發表於 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

PKKO
　暱稱: PKKO

高中生Rank: 3Rank: 3

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

論壇榮譽勳章論壇贊助勳章
13#
發表於 2016-12-31 16:27 | 只看該作者
回復 12# koshi0413


    ZA=cells(i,j)

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

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

TOP

koshi0413

帖子
55 
主題
1 
精華
0 
積分
81 
點名
0  
作業系統
win 
軟體版本
10 
閱讀權限
20 
註冊時間
2016-5-15 
最後登錄
2018-11-1 
14#
發表於 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 
15#
發表於 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 
16#
發表於 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 
17#
發表於 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

PKKO
　暱稱: PKKO

高中生Rank: 3Rank: 3

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

論壇榮譽勳章論壇贊助勳章
18#
發表於 2017-1-1 23:14 | 只看該作者
回復 17# koshi0413


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

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

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

TOP

koshi0413

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

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

TOP

koshi0413

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

        靜思自在 : 心中常存善解、包容、感思、知足、惜福。
123
返回列表 上一主題