Excel Tabelle an SQL Server schicken

Informationen und Beispiele zu den hier genannten Dialog-Elementen:
MsgBox
Bild

Betrifft: Excel Tabelle an SQL Server schicken
von: Hannes Respondek
Geschrieben am: 12.08.2015 14:04:07

Hallo Zusammen,
ich muss eine Excel Tabelle(6 Spalten * 8500 Zeilen) an SQL Schicken.
Die Verbindung stelle ich mit ADODB her.+
Ich kann mich bereits mit den SQL Server Verbinden und dort eine neue Tabelle anlegen. In diese Tabelle soll nun die Daten aus den Excel Sehht geschrieben werden.
Leider Bekomme ich folgenden Fehler:
"In der INSERT-Anweisung sind weniger Spalten angegeben als Wertein der VALUES-Klausel"
Anscheinend funktioniert es nicht das er die Werte aus den Zellen in die INSERT übernimmt.
Hat jemand vielleicht eine IDEE oder elegantere Variante für mich?
Hier mein Code:

Sub senden_an_SQL()
'variablen deklarieren
Dim connection As New ADODB.connection
Dim rs As New ADODB.recordset
Dim ConnectionString As String
Dim SQLAbfrage As String
Dim DMC As String
Dim zeile As Long
zeile = 2
'Connection String herichten
ConnectionString = "Provider=SQLOLEDB.1;Password=asdf;Persist Security Info=True;User ID=sa; _
Initial Catalog=XMLDATA;Data Source=C517L0039801437"
'Verbindung zu Datenbank herstellen
connection.Open ConnectionString
Sheets("SQL").Select
DMC = "111"
   
''Tabelle mit DMC erstellen und Spalten definieren
SQLAbfrage = "CREATE TABLE id_" & DMC & "(time int, DV int, U int, I int, PG int, SG int)"
rs.Open SQLAbfrage, connection
''use tabelle
'SQLAbfrage = "USE id_" & DMC & ""
'rs.Open SQLAbfrage, connection
'Werte eintragen
'1.Recordset mit Daten füttern
Do While Cells(zeile, 1) <> ""
SQLAbfrage = "INSERT INTO id_" & DMC & "(time , DV, U, I, PG, SG) VALUES( " & _
Cells(zeile, 1).Value & ", " & _
Cells(zeile, 2).Value & ", " & _
Cells(zeile, 3).Value & ", " & _
Cells(zeile, 4).Value & ", " & _
Cells(zeile, 5).Value & ", " & _
Cells(zeile, 6).Value & ")"
rs.Open SQLAbfrage, connection
zeile = zeile + 1
Loop
'aufräumen
connection.Close
Set connection = Nothing
Set rs = Nothing
End Sub

Bild

Betrifft: AW: Excel Tabelle an SQL Server schicken
von: Michael
Geschrieben am: 12.08.2015 14:55:56
Hi Hannes,
alle Variablen sind als int definiert; könnte es sein, daß irgendein Wert in der Excel-Tabelle doch ein zusätzliches Komma enthält (z.B. bei time?) - das würde von SQL als weiterer Feldtrenner interpretiert werden.
Wirf mal das Schreiben "in den Server" raus und ersetze es durch
debug.print SQLAbfrage
dann kannst Du Dir die übergebenen Werte (naja, vielleicht nicht alle 8000!) mal im Direktfenster (im Editor unter Ansicht) ansehen, was Du Denn eigentlich übergibst.
Oder Du durchläufst die komplette Schleife und zählst alle "," im String SQLAbfrage: das müssen ja immer gleich viel sein - falls nicht, gibst Du ne msgbox aus, etwa so:


kommas=Len(SQLAbfrage) - Len(Replace(SQLAbfrage, ",", ""))
if kommas> 10 then 'oder wieviele auch immer
  msgbox "Kommas: " & kommas & " Zeile: " & zeile
endif
Schöne Grüße,
Michael

Bild

Betrifft: AW: Excel Tabelle an SQL Server schicken
von: Hannes Respondek
Geschrieben am: 13.08.2015 07:57:02
Danke für die fixe Antwort Michael,
Ja da sind in jeder Variable Kommas enthalten. Ich geb dir mal einen Ausschnit aus der Tabelle welche eingelesen werden soll.


121,1	8,68	35,6	336	999,6	        470,0109
121,13	8,68	35,6	338	999,8625	470,0312
121,14	8,68	35,6	338	1000,125	470,0109
121,16	8,68	35,6	338	999,8187	469,9906
Manchmal steht auch eien 0 drinnen.
Wie muss ich ejtzt die Variablen der SQL deklarieren? Oder wie kann ich das Problem lösen?

Bild

Betrifft: AW: Excel Tabelle an SQL Server schicken
von: Hannes Respondek
Geschrieben am: 13.08.2015 11:34:28
Entschuldigung, Harken vergessen bei "Offenener Beitrag..."

Bild

Betrifft: AW: Excel Tabelle an SQL Server schicken
von: Hannes Respondek
Geschrieben am: 13.08.2015 11:45:06
AHA!
Danke Michael ich habe jetzt zumidnest das Problem erkannt!
Das steht ind er debug.print
Und da werden ja die kommas von der einzulesenden Variablen auch genommen, sit ja völlig logisch weshalb dann mehr VALUES als angegeben übergeben werden.
INSERT INTO id_1212s1(time , DV, U, I, PG, SG) VALUES( 0, 8,68, 39,6, 0, 1000,65, 520,0203)
INSERT INTO id_1212s1(time , DV, U, I, PG, SG) VALUES( 0, 8,68, 39,6, 0, 1000,956, 520,0406)
INSERT INTO id_1212s1(time , DV, U, I, PG, SG) VALUES( 0, 8,68, 39,6, 0, 1000,737, 520,0406)
Wie kann ich jetzt die Kommas so wie gewüsncht an SQL schicken so dsa VALUES auch stimmt.

Bild

Betrifft: AW: Excel Tabelle an SQL Server schicken
von: Hannes Respondek
Geschrieben am: 13.08.2015 11:55:38
Ich hab die Lösung!
ich hab Cells(zeile,x).Value in eine Str() gepackt. Dadruchw andelt er Kommas in Punkte um.
Somit passt alles wieder!

Do While Cells(zeile, 1) <> ""
    SQLAbfrage = "INSERT INTO id_" & DMC & "(time , DV, U, I, PG, SG) VALUES( " & _
    Str(Cells(zeile, 1).Value) & ", " & _
    Str(Cells(zeile, 2).Value) & ", " & _
    Str(Cells(zeile, 3).Value) & ", " & _
    Str(Cells(zeile, 4).Value) & ", " & _
    Str(Cells(zeile, 5).Value) & ", " & _
    Str(Cells(zeile, 6).Value) & ")"
    
    rs.Open SQLAbfrage, connection
    zeile = zeile + 1
Loop


Bild

Betrifft: freut mich
von: Michael
Geschrieben am: 13.08.2015 13:30:12
Hi Hannes,
freut mich, wenn es fehlerfrei läuft.
Oder zu laufen *scheint*: ich bin mir nicht sicher, ob die Daten "sauber" in Deiner SQL-DB ankommen.
Du definierst int, und das sind Ganzzahlen, Dezimalwerte werden anders definiert.
Ich habe kaum Praxis mit SQL, aber ein Blick in
https://de.wikibooks.org/wiki/Einf%C3%BChrung_in_SQL:_Datentypen
zeigt, daß es für Dezimalzahlen Typen wie float oder double gibt, je nach Größe der Werte bzw. Anzahl der signifikanten Stellen.
Ich weiß nicht, was Dein Server macht, wenn Du float in int schreibst: schlimmstenfalls schneidet er schlicht die Nachkommastellen ab.
Schöne Grüße,
Michael

Bild

Betrifft: Nachtrag
von: Michael
Geschrieben am: 13.08.2015 13:49:49
Ach so, "in Str() verpackt": Du übergibst ja sowieso einen bzw. den String SQLAbfrage, und Teile davon sind immer STRINGs.
VBA macht bei
Str(Cells(zeile, 2).Value) & ", " & _
automatisch eine Typkonvertierung von Zahl (ob mit oder ohne Komma) nach String.
Sieh Dir mal den Code an:

Sub test()
Dim i As Long     ' langes integer
i = 5
' Range("C2").Value enthält 123,456
MsgBox TypeName(i)
MsgBox TypeName(Range("C2").Value)
If TypeName(Range("C2").Value) = "Double" Then
  MsgBox "r: " & Range("C2") & vbLf & _
         "r: " & Replace(Range("C2"), ",", ".") & vbLf & _
         "r: " & Format(Range("C2"), "0.00")  ' zwei Nachkommastellen
End If
End Sub

Die Konvertierung mit Format liefert trotz . im Format ein Komma zurück - hier geht es um Ländereinstellungen.
Replace arbeitet immer richtig.
Schöne Grüße,
M.

Bild

Betrifft: AW: Excel Tabelle an SQL Server schicken
von: Hannes Respondek
Geschrieben am: 13.08.2015 14:41:26
Ach stimmt ja. INT sit ja Ganzzahl. Danke für die Richtigstellung meiens Denkfehlers.
Verwende jetzt decimal(9,2) snstatt int. Muss mal in die DB schauen was die da für werte stehen hat.
Besten DAnk!

Bild

Betrifft: gerne, vielen Dank für die Rückmeldung owT
von: Michael
Geschrieben am: 13.08.2015 18:29:20


 Bild

Beiträge aus den Excel-Beispielen zum Thema "Excel Tabelle an SQL Server schicken"