webentwicklung-frage-antwort-db.com.de

Tricks zum Generieren von SQL-Anweisungen in Excel

Haben Sie Tricks zum Generieren von SQL-Anweisungen, hauptsächlich INSERTs, in Excel für verschiedene Datenimportszenarien?

Ich habe es wirklich satt, Formeln mit "Gefällt mir" zu schreiben

="INSERT INTO Table (ID, Name) VALUES (" & C2 & ", '" & D2 & "')"

18
David Vidmar

Das Semikolon muss innerhalb des letzten doppelten Anführungszeichens mit einer schließenden Klammer stehen. Denken Sie beim Hinzufügen von einfachen Anführungszeichen um eine Zeichenfolge daran, diese außerhalb der ausgewählten Zelle hinzuzufügen.

(Leerzeichen für Sichtbarkeit hinzugefügt - vor dem Einfügen entfernen)

=CONCATENATE("insert into table (id, name) values (",C2,",' ",D2," ');")

Hier ist eine andere Ansicht:

=CONCATENATE("insert into table (id, date, price) values (",C3,",'",D3,"',",B3,");")

31
Jason V

Früher habe ich die String-Verkettungsmethode verwendet, um SQL-Einfügungen in Excel zu erstellen. Es kann gut funktionieren, kann aber auch etwas zeitaufwändig und "fummelig" sein.

Ich habe ein Excel-Add-In erstellt, das das Generieren von Inserts aus Excel vereinfacht:

(siehe Video am Ende der Seite) http://www.howinexcel.com/2009/06/generating-sql-insert-statements-in-Excel.html

http://www.querycell.com/SQLGenerator.html

http://www.oneclickcommissions.com/Excel-statement.html

2
SamH

Manchmal benutze ich ersatzweise, um Muster im SQL-Befehl zu ersetzen, anstatt zu versuchen, den SQL-Befehl aus Verkettung zu erstellen. Angenommen, die Daten befinden sich in den Spalten A und B. Fügen Sie eine obere Zeile ein. Platzieren Sie in Zelle C1 den SQL-Befehl mit dem Muster:

insert into table t1 values('<<A>>', '<<B>>')

Dann platzieren Sie in Zeile 2 die Excel-Formel:

=SUBSTITUTE(SUBSTITUTE($C$1, "<<A>>", A2), "<<B>>", B2)

Beachten Sie die Verwendung der absoluten Zellenadressierung $C$1, um das Muster zu erhalten. Besonders schön, wenn Sie mit char oder varchar arbeiten und die Anführungszeichen in Anführungszeichen setzen müssen. Vergleichen mit:

=concatenate("insert into table t1 values '", A2, "', '", B2, "')"

Eine andere Sache, die mich mehr als einmal gebissen hat, ist der Versuch, einige numerische Zeichen oder Varchars mit Excel zu verarbeiten, mit Ausnahme von führenden Nullen wie 007. Excel konvertiert in 7.

2

der VBA-Ansatz lautet: deklarieren Sie Ihre Zeichenfolge und weisen Sie Ihre SQL-Anweisung folgendermaßen zu

dim SqlString as String
SqlString = "SELECT * FROM %1 WHERE (var=%2)"
SqlString = Replace("%1", "Table_Name")
SqlString = Replace("%2", ""value"")

Der Excel-Ansatz ist ähnlich, verwendet jedoch die Funktion SUBSTITUTE.

Ich bevorzuge diesen Ansatz, weil er den SQL-Text lesbarer macht und alle lästigen und verketteten Probleme vermeidet. Ja, es braucht eine zusätzliche Zelle, aber es lohnt sich für den Audit-Trail.

1
Peter Carswell

Ich kenne diesen Schmerz. Ich landete schrieb darüber in meinem Blog ... zweimal .
Ich habe eine UDF erstellt, die einen Zellbereich mit mehreren Optionen verkettet. Dabei werden die Werte immer durch Kommas getrennt, es können jedoch bei Bedarf auch einzelne Anführungszeichen und/oder Klammern hinzugefügt werden.

Sie schreiben also den einfachen Teil der SQL-Anweisung.

INSERT INTO table
VALUES /*string that we don't want to type by hand*/

oder 

SELECT *
FROM table
WHERE foo IN (/*another string I don't want to type out*/)

Und die benutzerdefinierte Excel-Funktion unten macht aus Werten in einem Tabellenkalkulationsbereich eine Nizza-Zeichenfolge.

Function SQLConcat(rng As Range, Optional quoted As Boolean = False, Optional parenthesis As Boolean = False) As String
' ***************************************************************
' * Returns a comma separated list for use in SQL IN statements *
' * Params *
' * - rng: Range of cells to concatenate *
' * - quoted: True/False. If true, values are placed inside *
' * of single quotes. Default of false *
' * - parenthesis: Boolean. *
' * Useful for INSERT INTO tbl VALUES(53),(90),(397) *
' * *
' * Author: Christopher J. McClellan *
' * Published under Creative Commons Attribution-Share Alike *
' * http://creativecommons.org/licenses/by-sa/3.0/ *
' * You are free to change, distribute, and pretty much do *
' * whatever you like with the code, but you must give credit *
' * to the original author and publish any derivitive of this *
' * code under the same license. *
' ***************************************************************

Dim tmp As String 'temporary string
Dim row As Long 'first cell is special case
row = 0 'initalize row count
Dim c As Object 'cell
Dim txtwrapperLeft As String, txtwrapperRight As String

If quoted = True And parenthesis = False Then
 txtwrapperLeft = "'"
 txtwrapperRight = "'"
ElseIf quoted = True And parenthesis = True Then
 txtwrapperLeft = "('"
 txtwrapperRight = "')"
ElseIf quoted = False And parenthesis = True Then
 txtwrapperLeft = "("
 txtwrapperRight = ")"
Else
'quoted = false and parenthesis = false
 txtwrapperLeft = ""
 txtwrapperRight = ""
End If

For Each c In rng.Cells
 If row = 0 Then
 tmp = txtwrapperLeft & c.Value & txtwrapperRight
 Else
 tmp = tmp & "," & txtwrapperLeft & c.Value & txtwrapperRight
 End If
 row = row + 1
 Debug.Print tmp
Next c

'return
SQLConcat = tmp
End Function
1
RubberDuck

Das Exportieren einer Excel-Datei als CSV-Datei kann eine Alternative sein (siehe Beitrag von Bill Krawin - als neues Poster kann ich noch keinen Kommentar hinzufügen). Beachten Sie jedoch, dass Sie wahrscheinlich die Formatierung Ihrer Datumsfelder in yyyy-mm-dd ändern müssen. Andernfalls wird in allen Datumsspalten 00/00/00 angezeigt. Dies liegt daran, dass MySQL einen anderen Datumscode als Microsoft Excel verwendet. Alternativ können Sie OpenOffice verwenden, um die CSV-Datei zu speichern.

0
DBMarcos99

Für Einfügen von Daten in die SQL-Tabelle 

=CONCATENATE("INSERT INTO `database_name`.`table_name`(`Column_Name`,`Column_Name`) VALUES ( '",A1,"',",B1,"); ")
0
Vivek Gajbhiye

Ich habe das gestern gemacht, und ja, es ist ärgerlich, die Zitate richtig zu machen. Eine Sache, die ich tat, war eine benannte Zelle, die nur ein einzelnes Zitat enthielt. Geben Sie in A1 ="'" (gleich, doppelte Anführungszeichen, einfache Anführungszeichen, doppelte Anführungszeichen) ein und benennen Sie diese Zelle dann "QUOTE", indem Sie diese in das Feld links neben der untersten Symbolleiste eingeben.

0
WW.

Wie wäre es mit dem Abfragen und Einfügen der Daten aus der Excel-Arbeitsmappe in die Quelle mit ACE/Jet (a.k.a. Access) SQL? Dies erfordert einen ACE/Jet, bei dem es sich um eine andere Excel-Tabelle handeln kann. Hier ein kurzes Beispiel:

INSERT INTO 
   [ODBC;Driver={SQL Server};SERVER=MYSERVER;DATABASE=MyDatabase;UID=sa;Pwd=mypassword;].MyTable (ID, Name)
SELECT F1, F2
  FROM 
   [Excel 8.0;HDR=NO;IMEX=1;Database=C:\db.xls;].[Sheet1$A1:B4];
0
onedaywhen

Warum generieren Sie SQL in Excel? Es ist einfacher und schneller, ein Arbeitsblatt als CSV-Datei zu exportieren und dann mit einem Tool diese Datei in eine SQL-Datenbank zu importieren. Zum Beispiel die MySQL-Anweisung LOAD DATA INFILE .

Ich entschuldige mich dafür, dass Sie Ihre Frage nicht direkt beantwortet haben, und ich weiß, dass dies keine Lösung für alle Umstände ist.

0
Bill Karwin

Beim Erstellen von SQL-Inserts scheint dies manchmal der einfachste Weg zu sein. Aber Sie werden schnell müde, und ich glaube nicht, dass es "intelligente" Wege gibt (außer vielleicht Makros/VBA-Programmierung).

Ich möchte Sie bitten, Excel zu meiden und einige andere Ideen zu erkunden:

  • verwenden Sie Access (großer csv-Importfilter, verknüpfen Sie dann mit der DB-Tabelle und lassen Sie den Einfügevorgang von Access übernehmen)
  • tOAD verwenden (noch bessere Importfunktion, da Sie damit Spalten mischen und anpassen und sogar aus der Zwischenablage importieren können)
  • verwenden Sie SQL Loader (etwas kompliziert zu bedienen, aber schnell und ziemlich flexibel).
0
Thorsten