The mystical magic of Excel

Hoe het verhaal verder gaat? Nou, een heel dapper iemand neemt inderdaad de tijd om alles over te typen. Weliswaar via copy-past, maar toch. Andere zoeken hun heil in het omzetten van de lijst naar een Comma Seperated File en importeren het in de database. Weer andere proberen een externe dataconnectie op te zetten om het zo in de database te krijgen. Enkele hebben een optie in de database om de lijst te importeren, maar niemand gebruikt de verborgen magie van Excel.

Nu wil ik wel zeggen, dat het exporteren naar een CSV zeker een goede methode is! Ook het opzetten van de dataconnectie is heel goed bedacht! Dat zijn ook wel de methodes zoals het eigenlijk zou moeten. Toch wil ik ingaan op een makkelijkere oplossing. De reden waarom ik hierop in wil gaan is ook een beetje omdat het in andere situaties ook goed van pas zou kunnen komen.

Goed, to the point! Wat wil ik? Ik wil de lijst in mijn database. Eigenlijk ongeacht welke database en ook niet te veel tijd besteden aan het werkend krijgen van allerlei connecties ed.

Een voorbeeld.

Hier staan automerken en de verschillende modellen erbij.

Het eerste wat we gaan doen is er een ‘echte’ tabel van maken. We hebben al kolomnamen, dus die gaan we ook gelijk gebruiken. Ga naar Format as Table om er een tabel van te maken. Kies de kleuren die je wilt, ik gebruik hier zwart wit grijs.

Ik kies ervoor om de namen van de kolommen te gebruiken.

We hebben nu gelijk meer mogelijkheden die we kunnen gebruikt, bijvoorbeeld makkelijk sorteren.

Nu hebben we een tabel, maar hoe krijgen we de data in een database. Normaal gesproken ondersteund de database altijd INSERT statements. Extra kan een import functie zijn, maar even van de simpelste database uitgaan, zal het INSERT ondersteunen. Dat ga ik dan ook maken. Althans, genereren, want ik ben lui en ga niet voor elke regel een INSERT maken… Dat doet Excel voor mij!

Ik maak een nieuwe kolom aan genaamd SQL. Ik type gewoon de naam, enter en voila, kolom erbij.

Nu gaan we de kolom vullen met de SQL code en wel de INSERT’s per regel. Dit kunnen we doen aan de hand van een formule. Het handige is dat als het eerste veld wordt gevuld met een formule, de rest automatisch ook wordt gevuld. Een formule kan verwijzen naar een veld in de regel en dus zullen alle regels een regelspecifieke INSERT krijgen.

Hoe werkt het. Ik wil dit krijgen;

INSERT INTO Auto (Merk, Model) VALUES ('ASTON MARTIN', 'DB9 COUPE')

Nu zien we twee regelspecifieke waardes, namelijk in de VALUES ‘ASTON MARTIN‘ en ‘DB9 COUPE‘. In het eerste veld in de SQL kolom zet ik dan het volgende;

="INSERT INTO Auto (Merk, Model) VALUES ('"& Table1[[#This Row];[Automerk]] & "', '"& Table1[[#This Row];[Model]] & "')"

Wat zien we hier staan?

  • De formule het begint met =. De ‘=’ houdt in dat er niet een waarde in komt maar een formule. Iets specifieker; een berekening dus.
  • We zien dan een dubbele quote ‘”‘ staan. Ik begin hier een string, een string zijn characters en dat geven we aan met een dubbele quote ‘”‘. Overigens moet de string ook altijd met een dubbele quote eindigen. Bijvoorbeeld; “INSERT”
  • Daarna komt de rest van de string “INSERT INTO Auto (Merk, Model) VALUES (‘”. Let op dat ik eindig met een enkele quote en een dubbele quote. De dubbele quote geeft het einde van de string aan. De enkele valt binnen de string, dus hoort het gewoon bij de string zelf (zie het resultaat).
  • Het &-teken geeft aan dat er iets op string niveau wordt samengevoegd. Een + had betekend dat ik iets op numeriek niveau samenvoeg, een som dus. Het &-teken plakt dus twee strings aan elkaar.
  • Echter zien we niet een standaard string staan, maar een verwijzing naar een string in een ander veld op dezelfde regel. Table1[[#This Row];[Automerk]]; ik heb mijn tabel geen naam gegeven, dus heet deze nog Table1. [#This Row] verwijst naar, ja ja, deze rij. [Automerk] Verwijst naar de kolomnaam. En het geheel staat tussen rechtehaken.
  • Daarna gaan we weer verder met een string, namelijk een enkele quote, een comma en weer een enkele quote.
  • En weer voegen we een veld uit dezelfde rij toe.
  • Het geheel sluiten we af met een enkele quote, een haak sluiten en om de string weer te sluiten een dubbele quote.

Het resultaat Op het moment dat we op enter drukken wordt voor elke rij het veld gevuld met de SQL code.

Hier wordt gelijk duidelijk waarom we de enkele quote hebben gebruikt, namelijk om in de string van SQL code aan te geven dat het een string van SQL betreft.

Een copy-paste van de kolom SQL in mijn SQL Management Tool geeft mij alle INSERT’s en na het runnen van de code staat alle data in mijn database.

Tot zover deze Blog over hoe Excel kan helpen met het maken van SQL code…

  1 comment for “The mystical magic of Excel

  1. 15 april 2012 at 17:38

    Zeer SMART bedacht :)

Laat een reactie achter bij Daniel van Dorp Reactie annuleren

Het e-mailadres wordt niet gepubliceerd. Verplichte velden zijn gemarkeerd met *

Zoals de meeste websites gebruiken we cookies om een meer persoonlijke en snelle service te bieden.

Wij gebruiken cookies zodat onze website meer efficiƫnt kan functioneren, om de prestaties te verbeteren en, eventueel, om op maat reclame van onze partners aan te bieden. Als u doorgaat gaan we ervan uit dat u akkoord gaat alle cookies te krijgen van onze website.

To accept cookies please Click To Continue