Hiljuti tekkis mul vajadus võtta Excelist andmed ja sisestada need andmebaasi. Lihtne oleks see asi olnud siis kui kõik Exceli failides olevad andmed oleksid ühessuguse asetusega sest siis saaksin kasutada SQL Serveri importimise tööriista. Kuna minul olid väärtused laiali erinevates tulpades ja erinevates ridades tuli midagi kavalamat väljamõelda.
Esimene samm…
Excelist andmebaasile ligipääsemiseks on vaja luua andmebaasi ühendus ja VbScript’is käib see järgnevalt:
Sub Andmebaasi ()
'--Andmebaasi ühenduse objekt--'
Set oConn = CreateObject("ADODB.Connection")
'--Andmebaasi ühenduse connection string--'
sConnectionString = "Provider=SQLOLEDB.1;Data Source=SERVER\ANDMEBAASIMOOTOR;Initial Catalog=TEST"
'--Loome andmebaasi ühenduse--'
oConn.Open sConnectionString, "kasutajanimi", "parool"
'--Lihtne käsklus--'
oConn.Execute "INSERT INTO TestTabel (tulp1,tulp2) VALUES ('Esimene','Teine')"
'--Sulgeme ühenduse--'
oConn.Close
Set oConn = Nothing
End Sub
Olulised punktid
- Rida 6 kus me määrame, millise andmebaasiga ühenduda. Näiteks masinas nimega TEST kus on SQL Server 2008 Express installeeritud on Data Source väärtuseks “TEST\sqlexpress”. Samamoodi on oluline valida õieti Initial Catalog, mis määrab andmebaasi nime kuhu soovitakse ühenduda s.t hetkel TEST
- Rida 9 et õieti saaksid märgitud andmebaasiga ühendumiseks vajalikud kasutajanimi ja parool
Teine samm …
Valitud teksti andmebaasi lisamiseks võib käituda mitut moodi, aga mina otsutstasin selle kasuks, et lasen skriptil For tsükkli abil oma valikutest iga rea kohta insert lause teha.
Pilt 1 - Excel data
Ülaltoodud pildil on näidisandmed ja sealt on mul vaja sisestada andmebaasi kas Tulp1-Tulp2 andmed või Tulp1-Tulp3 andmed nagu toodud Piltidel 2 ja 3
Pilt 2. - Valikuvõimalus 1
Pilt 2 - Valikuvõimalus 2
Selleks, et vajalikud andmed kätte saada kirjutasin järgmised read
'--Insert lause algus--'
sInsertValues = "Insert into TestTabel(Number1,Number2) values "
If Selection.Areas.Count Mod 2 = 0 Then '--Kui valikualasi on rohkem kui üks ja need on paaris--'
'--Käib läbi kõikide valikualade paaride--'
For j = 1 To Selection.Areas.Count Step 2
'--Käib läbi kõikide valikualade paaride väärtuste ja loob insert lause ning saadab selle andmebaasi---'
For i = 1 To Selection.Areas(j).Cells.Count
oConn.Execute = sInsertValues & "('" & Selection.Areas(j).Cells(i).Value & "','" & Selection.Areas(j + 1).Cells(i).Value & "')"
Next
Next
ElseIf Selection.Areas.Count > 1 Then
MsgBox "Wrong relations count"
Else
'--Kui tegemist on kõrvuti asetsevate tulpadega mis on ühes valikualas--'
For Each codeRow In Selection.Rows
oConn.Execute = sInsertValues & "('" & codeRow.Columns(1).Value & "','" & codeRow.Columns(2).Value & "')"
Next
End If
Selles koodis toimub küll palju segast, aga põhimõtteliselt on nii, et Exceli valikuala/d saab kätte Selection attribuudi alt. Kui on valitud mitte kõrvuti asuvad read või tulbad siis tuleb kasutada Areas atribuuti, mis võimaldab erinevatel valikualadel vahet teha.
Olulised punktid
- Antud koodi miinuseks on see, et kui näiteks Pilt 1 toodul andmetest valida väärtused A2 ja A4 ning siis B2 ja B4 ei pane ta kokku mitte A2 ja B2 vaid A2 ja A4, sest valimise järjekord on siin oluline.
Kolmas samm…
Paneme loodud koodijupid kokku.
Sub Andmebaasi ()
'--Andmebaasi ühenduse objekt--'
Set oConn = CreateObject("ADODB.Connection")
'--Andmebaasi ühenduse connection string--'
sConnectionString = "Provider=SQLOLEDB.1;Data Source=SERVER\ANDMEBAASIMOOTOR;Initial Catalog=TEST"
'--Loome andmebaasi ühenduse--'
oConn.Open sConnectionString, "kasutajanimi", "parool"
'--Insert lause algus--'
sInsertValues = "Insert into TestTabel(Number1,Number2) values "
If Selection.Areas.Count Mod 2 = 0 Then '--Kui valikualasi on rohkem kui üks ja need on paaris--'
'--Käib läbi kõikide valikualade paaride--'
For j = 1 To Selection.Areas.Count Step 2
'--Käib läbi kõikide valikualade paaride väärtuste ja loob insert lause ning saadab selle andmebaasi---'
For i = 1 To Selection.Areas(j).Cells.Count
oConn.Execute = sInsertValues & "('" & Selection.Areas(j).Cells(i).Value & "','" & Selection.Areas(j + 1).Cells(i).Value & "')"
Next
Next
ElseIf Selection.Areas.Count > 1 Then
MsgBox "Wrong relations count"
Else
'--Kui tegemist on kõrvuti asetsevate tulpadega mis on ühes valikualas--'
For Each codeRow In Selection.Rows
oConn.Execute = sInsertValues & "('" & codeRow.Columns(1).Value & "','" & codeRow.Columns(2).Value & "')"
Next
End If
'--Sulgeme ühenduse--'
oConn.Close
Set oConn = Nothing
End Sub
Sedasi sain mina omad asjad tehtud …
Logiblogi 