1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47
| dim asql, ssql, sdb, suser, spwd, shost, sconn, oconn, ocmd, i
asql=array("drop table if exists tmp;" _
, _
"create table tmp (" & vbcrlf & vbtab & _
"valeur1 varchar(255) NOT NULL," & vbcrlf & vbtab & _
"valeur2 varchar(255) NOT NULL," & vbcrlf & vbtab & _
"valeur3 varchar(255) NOT NULL," & vbcrlf & vbtab & _
"valeur4 varchar(45) NOT NULL," & vbcrlf & vbtab & _
"valeur5 varchar(255) NOT NULL," & vbcrlf & vbtab & _
"valeur7 varchar(255) NOT NULL," & vbcrlf & vbtab & _
"PRIMARY KEY (valeur1)" & vbcrlf & _
") ENGINE=InnoDB DEFAULT CHARSET=utf8;" _
, _
"LOAD DATA INFILE 'C:/TEMP/HOTLINE2/valeur17_valeur113_ANSI.txt' INTO TABLE tmp FIELDS TERMINATED BY ';';" _
, _
"insert into tablet_info (tblt_cd,tblt_nm,tblt_barcode,tblt_type,use_atm_yn,tblt_barcode_list)" & vbcrlf & vbtab & _
"select valeur5,valeur2,valeur5,valeur3,'N',valeur1 from tmp where valeur5 not in (select tblt_cd from tablet_info);" _
, _
"delete from tmp where valeur5 in (select tblt_cd from tablet_info);" _
, _
"update tablet_info,tmp set tblt_nm=valeur2,tblt_type=valeur3,tblt_barcode_list=valeur1 where tblt_cd=valeur5;" _
, _
"DROP TABLE tmp;" _
, _
"update tablet_info set tblt_barcode=tblt_cd;" _
)
'sconn connection-string pour mysql, je ne donne qu'une version possible et il vous faut s'adapter
sdb="databasename"
suser="username"
spwd="xxxxxx"
shost="localhost" 'ou autre
sconn="Driver={MySQL ODBC 5.3 Unicode Driver};Server=" & shost & ";Database=" & sdb & ";UID=" & suser & ";PWD=" & spwd & ";"
set oconn=createobject("adodb.connection")
oconn.open sconn
set ocmd=createobject("adodb.command")
set ocmd.activeconnection=oconn
ocmd.commandtype=1 'adCmdText=1
for i=0 to ubound(asql)
ssql=asql(i)
ocmd.commandtext=ssql
ocmd.execute
next
set ocmd=nothing
set oconn=nothing |
Partager