ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] excel vba (´¡¤J¸ê®Æ)insert ¶i sqlite ½wºC°ÝÃD

¦^´_ 5# joey0415


¤p§Ì¥ÎEXCEL¼g¤Jmysql ©M mssql ¥H«e¤]¹J¨ì¬Û¦Pªº°ÝÃD
¦ý¤p§Ì¸Ñ¨Mªº¤è¦¡¤£¬O®¼Àu,±z¤£¶û±óªº¸Ü¥i¥H¸Õ¸Õ¬Ý

¥Î³s±µ²Å¸¹=";"  
§â»yªk³s±µ°_¨Ó,EXCUTEªº°Ê§@µ¥°j°é¶]§¹¤§«á,¦Aµ¹¥L¤@¦¸EXCUTE´N¦n¤F

±zªº»yªk¥D­n¬OºC¦b¼Æ¶q¦h,§A¨C¤@¦C³£¥´¶}¸ê®Æ®w³sµ²¤@¦¸,¦]¦¹³t«×ºC¤F
³s±µ¦b¤@°_´N¥u­n¥´¶}¸ê®Æ®w¤@¦¸,¤@¦¸©ñ¤J¤­¦Êµ§¸ê®Æ,´N«D±`§Ö¤F!
  1. Sub WRITE_SQL() '¼g¤J¸ê®Æ
  2. conn_connect'³o¬O©I¥s¶}±ÒSQL
  3. For i = 1 To 10000
  4.     strSQL = "INSERT INTO customer VALUES ('Bob','SHIT','TWN','SHIT2','SHIT3',2016-01-01," & 100 + i & ")"'¨C¤@¦Cªº»yªk
  5.     If i = 1 Then strSQL2 = strSQL Else strSQL2 = strSQL2 & ";" & strSQL'±N»yªk³s±µ
  6. Next
  7. Conn.Execute (strSQL2)'°õ¦æSQL
  8. End Sub
½Æ»s¥N½X
PKKO

TOP

¥»©«³Ì«á¥Ñ PKKO ©ó 2016-12-28 21:48 ½s¿è

¦^´_ 9# koshi0413


   excel ¤£·|ºC¨ì­þÃä¥h®@
§Úªº³£¬O¤@¬í¦h´Nµ²§ô¤F(¤@¬í¬O¦]¬°­n¶}±Ò©MÃö³¬¸ê®Æ®w)
±zªº³t«×ºC¬O¦]¬°©p¥Î¤FZA=CELLS(I,J)

excel­n§Ö³Ì°ò¥»ªº­n¥ó´N¬O¥ýÂର°}¦C
¨Ò¦p
RNG=[A1].RESIZE(100,10).VALUE'¨ú¥X100¦C*10Äæ¦ìªº¸ê®Æ
±µµÛ§â©pªºcells´«¦¨RNG´N¥i¥H¤F
excelŪ¨úÀx¦s®æ¬O«D±`ºCªº³t«×
Ū¨ú°}¦C¬O«D±`§Öªº,©ñ¸ê®Æªº®É­Ô¤]¤@¼Ë®@,¤£¯à¤@­Ó¤@­Ó©ñ,­n¤@¦¸©ñ¤@¾ã­Ó°}¦C¶i¥h®@!

¥H¤Î¤W¤è¥ý©ñ¤J¹Bºâªºµ{¦¡,ÁקK¨C¤@­Óif³£­n°õ¦æ¤@¦¸SPLIT
PKKO

TOP

¦^´_ 12# koshi0413


    ZA=cells(i,j)

¥ý§âCELLS´«¦¨°}¦C
«á­±ªºZA³£¤£¥Î°Ê,´N¤w¸g§Ö«Ü¦h¤F
·N«ä¬O¤@¼Ëªº

VBA±j¨îÂà´«¤å¦rªº«ü¥O¬OCSTR(XXX)
100¦h­ÓÀx¦s®æ¤]¬O¤@¼Ë
¥ý¤@¦¸©ÊÂର°}¦C
µM«á¶]¦^°é
©p¦pªG¨S¦³Âà°}¦C¥Îrange("A" & i) ¤j·§·|ºC¨ì·Q­n¥´¤H§a!
PKKO

TOP

¦^´_ 17# koshi0413


    ¥Îªk¿ùÅo,§A¤@¼Ë¬OŪ¨ú¤Fcellsªº­È

rng=[a1].resize(r,c).value'r=¦C¼Æ,C=Äæ¦ì¼Æ¶q
¤§«á§âCELLSªº³¡¤À¨ú¥N¬°rng´N¥i¥H¥Î°}¦C¤F

¨Ò¦p­ì¥»­n¨ú¥Îcells(2,1) ´NÅܦ¨ rng(2,1)
·N«ä§¹¥þ¬Û¦P,¦ý¤£¬O¨ú¥ÎÀx¦s®æ¦Ó¬O¨ú¥Î°}¦C,³t«×ª½±µ¤j¼W®@!
PKKO

TOP

¦^´_ 20# koshi0413


  §Ú¨S¦³«Ü²`¤Jªº¬Ý±z²Ó¸`ªºµ{¦¡½X
¦ý¥Ø«e¥u¦³¬Ý¨ì¨â­Ó¦a¤è¥i¥Hµy·L§Ö¤@ÂI,§A¥i¥H¸Õ¸Õ¬Ý³o­Ó³¡¤À

²Ä¤@:za=RNG(J,I)  =>³o­Ó³¡¤À¥i¥H¤£¥Î,¥u±µ§â©Ò¦³ªºza¨ú¥N¬°RNG(J,I)§Y¥i=>¦ý³o­Ó¦a¤è¼vÅT¤£¤j
²Ä¤G:±zªº°j°é¤§¤º¨Ï¥Î¤F¤j¶qªºSPLIT,À³¸Ó¦b°j°é¶}©lªº®É­Ôª½±µ±NAR=Split(za, "/")
µM«áIF AR(0)=1 OR LEN(AR(1))=1 THEN
³o¼Ëªº¸Üµ{¦¡´N¤£¥Î¨C¤@¦æ³£°õ¦æ¤@¦¸©î¶}ªº°Ê§@
¥H¤W±z¸Õ¸Õ¬Ý
PKKO

TOP

        ÀR«ä¦Û¦b : ¤f»¡¦n¸Ü¡B¤ß·Q¦n·N¡B¨­¦æ¦n¨Æ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD