Natuurlijk wordt Excel gebruikt voor spreadsheets, maar wist u dat u Excel kunt verbinden met externe gegevensbronnen? In dit artikel gaan we bespreken hoe u een Excel - spreadsheet koppelt aan een MySQL -databasetabel en de gegevens in de databasetabel gebruikt om onze spreadsheet te vullen. Er zijn een paar dingen die je moet doen om je voor te bereiden op deze verbinding.

Voorbereiding^{(Preparation)}

Eerst moet u het meest recente ODBC -stuurprogramma ( Open Database Connectivity ) voor MySQL downloaden . Het huidige ODBC -stuurprogramma voor MySQL is te vinden op :

https://dev.mysql.com/downloads/connector/odbc/

Zorg^(Make) ervoor dat u na het downloaden van het bestand de md5-hash van het bestand vergelijkt met de hash die op de downloadpagina wordt vermeld.

Vervolgens moet u de zojuist gedownloade driver installeren.  Dubbelklik^(Double) op het bestand om het installatieproces te starten. Zodra het installatieproces is voltooid, moet u een databasebronnaam^{(Database Source Name)} ( DSN ) maken om met Excel te gebruiken .

De DSN maken^{(Creating the DSN)}

De DSN bevat alle verbindingsinformatie die nodig is om de MySQL- databasetabel te gebruiken. Op een Windows -systeem moet u op Start klikken , vervolgens op Configuratiescherm^{(Control Panel)} , vervolgens op Systeembeheer^{(Administrative Tools)} en vervolgens op Gegevensbronnen (ODBC)^{(Data Sources (ODBC))} . U zou de volgende informatie moeten zien:

Let^(Notice) op de tabbladen in de afbeelding hierboven. Een gebruikers-DSN^{(User DSN)} is alleen beschikbaar voor de gebruiker die deze heeft gemaakt. Een systeem-DSN^{(System DSN)} is beschikbaar voor iedereen die op de machine kan inloggen. Een bestands-DSN^{(File DSN)} is een .DSN-bestand dat kan worden getransporteerd naar en gebruikt op andere systemen waarop hetzelfde besturingssysteem en dezelfde stuurprogramma's zijn geïnstalleerd.

Klik op de knop Toevoegen^(Add) in de rechterbovenhoek om door te gaan met het maken van de DSN .

U zult waarschijnlijk naar beneden moeten scrollen om het MySQL ODBC 5.x-stuurprogramma^{(MySQL ODBC 5.x Driver)} te zien . Als het niet aanwezig is, is er iets misgegaan met het installeren van het stuurprogramma in het gedeelte Voorbereiding^{(Preparation)} van dit bericht. Om door te gaan met het maken van de DSN , zorg ervoor dat MySQL ODBC 5.x Driver is gemarkeerd en klik op de knop Voltooien^(Finish) . U zou nu een venster moeten zien dat lijkt op het onderstaande venster:

Vervolgens moet u de informatie verstrekken die nodig is om het bovenstaande formulier in te vullen. De MySQL -database en -tabel die we voor dit bericht gebruiken, bevinden zich op een ontwikkelmachine en worden slechts door één persoon gebruikt. Voor "productie"-omgevingen wordt aangeraden om een ​​nieuwe gebruiker aan te maken en de nieuwe gebruiker alleen SELECT- privileges toe te kennen. In de toekomst kunt u indien nodig extra rechten toekennen.

Nadat u de details voor uw gegevensbronconfiguratie hebt opgegeven, moet u op de knop Testen^(Test) klikken om te controleren of alles naar behoren werkt. Klik vervolgens op de knop OK . U zou nu de naam van de gegevensbron moeten zien die u hebt opgegeven op het formulier in de vorige set in het venster ODBC-gegevensbronbeheerder :^{(ODBC Data Source Administrator)}

De spreadsheetverbinding maken

Nu u met succes een nieuwe DSN hebt gemaakt , kunt u het venster ODBC-gegevensbronbeheerder sluiten en ^{(ODBC Data Source Administrator)}Excel openen . Nadat u Excel hebt geopend , klikt u op het lint Gegevens . ^(Data)Voor nieuwere versies van Excel klikt u op Gegevens ophalen^{(Get Data)} , vervolgens op Van andere bronnen^{(From Other Sources)} en vervolgens op Van ODBC^{(From ODBC)} .

In oudere versies van Excel is het een beetje meer een proces. Ten eerste zou je zoiets als dit moeten zien:

De volgende stap is om op de koppeling Verbindingen^{(Connections)} te klikken die zich direct onder het woord Gegevens^(Data) in de lijst met tabbladen bevindt. De locatie van de Connections -link is rood omcirkeld in de bovenstaande afbeelding. U zou het venster Werkmapverbindingen^{(Workbook Connections)} moeten zien :

De volgende stap is om op de knop Toevoegen^(Add) te klikken. U krijgt dan het venster Bestaande verbindingen te zien:^{(Existing Connections)}

Het is duidelijk dat u niet aan een van de vermelde verbindingen wilt werken. Klik daarom op de knop Browse for More… . U krijgt nu het venster Gegevensbron selecteren:^{(Select Data Source)}

Net als in het vorige venster Bestaande verbindingen^{(Existing Connections)} , wilt u de verbindingen in het venster Gegevensbron^{(Select Data Source)} selecteren niet gebruiken . Daarom wilt u dubbelklikken op de map +Connect to New Data Source.odcAls u dit doet, zou u nu het venster Data Connection Wizard moeten zien:^{( Data Connection Wizard)}

Gezien de vermelde gegevensbronkeuzes, wilt u ODBC DSN markeren en op Volgende^(Next) klikken . In de volgende stap van de wizard Gegevensverbinding^{(Data Connection Wizard)} worden alle ODBC -gegevensbronnen weergegeven die beschikbaar zijn op het systeem dat u gebruikt.

Hopelijk, als alles volgens plan is verlopen, zou u de DSN moeten zien die u in eerdere stappen hebt gemaakt, vermeld bij de ODBC -gegevensbronnen.  Markeer^(Highlight) het en klik op Volgende^(Next) .

De volgende stap in de wizard Gegevensverbinding^{(Data Connection Wizard)} is opslaan en voltooien. Het veld voor de bestandsnaam moet automatisch voor u worden ingevuld. U kunt een beschrijving aanleveren. De beschrijving die in het voorbeeld wordt gebruikt, is vrij duidelijk voor iedereen die het zou kunnen gebruiken. Klik vervolgens op de knop Voltooien^(Finish) rechtsonder in het venster.

U zou nu terug moeten zijn in het venster Werkmapverbinding^{(Workbook Connection)} . De gegevensverbinding die u zojuist hebt gemaakt, moet worden weergegeven:

De tabelgegevens importeren^{(Importing the Table Data)}

U kunt het venster Werkmapverbinding^{(Workbook Connection)} sluiten . We moeten op de knop Bestaande verbindingen^{(Existing Connections)} klikken in het gegevenslint^(Data) van Excel . De knop Bestaande verbindingen^{(Connections)} moet zich links op het gegevenslint^(Data) bevinden .

Als u op de knop Bestaande verbindingen^{(Existing Connections)} klikt, krijgt u het venster Bestaande verbindingen te zien. ^{(Existing Connections)}Je hebt dit venster in eerdere stappen gezien, het verschil is nu dat je dataverbinding bovenaan moet staan:

Zorg^(Make) ervoor dat de gegevensverbinding die u in de vorige stappen hebt gemaakt, is gemarkeerd en klik vervolgens op de knop Openen^(Open) . U zou nu het venster Gegevens importeren moeten zien:^{(Import Data)}

Voor de doeleinden van dit bericht gaan we de standaardinstellingen gebruiken in het venster Gegevens importeren . ^{(Import Data)}Klik vervolgens op de knop OK . Als alles voor u is gelukt, zou u nu de MySQL -databasetabelgegevens in uw werkblad moeten krijgen.

Voor dit bericht had de tabel waarmee we werkten twee velden. Het eerste veld is een auto-increment INT - veld genaamd ID. Het tweede veld is VARCHAR (50) en heet fname. Onze uiteindelijke spreadsheet ziet er als volgt uit:

Zoals je waarschijnlijk hebt gemerkt, bevat de eerste rij de namen van de tabelkolommen. U kunt ook de vervolgkeuzepijlen naast de kolomnamen gebruiken om de kolommen te sorteren.

Inpakken^(Wrap-Up)

In dit bericht hebben we besproken waar u de nieuwste ODBC-stuurprogramma's voor MySQL kunt vinden , hoe u een DSN kunt maken, hoe u een spreadsheetgegevensverbinding kunt maken met behulp van de DSN en hoe u de spreadsheetgegevensverbinding kunt gebruiken om gegevens in een Excel - spreadsheet te importeren. Genieten van!

 

Connecting Excel to MySQL

Ѕure Excel is used for spreadsheets, but did you know you can соnnect Excel to external data sources? In this artіcle we’re going to dіscuss hоw to connect an Excel sprеadsheet to a MySQL database table and use the data in the database table to populate our spreadsheet.  There аre a few things you need to do in order to prepare for this connection.

Preparation

First, you must download the most recent Open Database Connectivity (ODBC) driver for MySQL.  The current ODBC driver for MySQL can be located at

https://dev.mysql.com/downloads/connector/odbc/

Make sure after you download the file that you check the file’s md5 hash against that listed on the download page.

Next, you will  need to install the driver you just downloaded.  Double click the file to start the install process.  Once the install process is complete you will need to create a Database Source Name (DSN) to use with Excel.

Creating the DSN

The DSN will contain all of the connection information necessary to use the MySQL database table.  On a Windows system, you will need to click on Start, then Control Panel, then Administrative Tools, then Data Sources (ODBC).  You should see the following information:

Notice the tabs in the image above.  A User DSN is only available to the user that created it.  A System DSN is available to anyone that can log into the machine.  A File DSN is a .DSN file that can be transported to and used on other systems that have the same OS and drivers installed.

To continue creating the DSN, click on the Add button near the top right corner.

You will probably have to scroll down to see the MySQL ODBC 5.x Driver.  If it’s not present, something went wrong with installing the driver in the Preparation section of this post.  To continue creating the DSN, make sure MySQL ODBC 5.x Driver is highlighted and click on the Finish button. You should now see a window similar to the one listed below:

Next you will need to supply the information necessary to complete the form shown above.  The MySQL database and table we’re using for this post is on a development machine and is only used by one person. For “production” environments, it is suggested you create a new user and grant the new user SELECT privileges only.  In the future, you can grant additional privileges if necessary.

After you have supplied the details for your data source configuration, you should click on the Test button to make sure everything is in working order.  Next, click on the OK button. You should now see the data source name you supplied on the form in the previous set listed on the ODBC Data Source Administrator window:

Creating the Spreadsheet Connection

Now that you have successfully created a new DSN, you can close the ODBC Data Source Administrator window and open Excel.  Once you have opened Excel, click on the Data ribbon.  For newer versions of Excel, click on Get Data, then From Other Sources, then From ODBC.

In older versions of Excel, it’s a bit more of a process. Firstly, you should see something like this:

The next step is to click on the Connections link located right under the word Data in the tab list. The location of the Connections link is circled in red in the above image. You should be presented with the Workbook Connections window:

The next step is to click on the Add button.  This will present you with the Existing Connections window:

Obviously you don’t want to work on any of the connections listed.  Therefore, click on the Browse for More… button. This will present you with the Select Data Source window:

Just like the previous Existing Connections window, you do not want to use the connections listed in the Select Data Source window.  Therefore, you want to double click on the +Connect to New Data Source.odc folder.  In doing so, you should be now see the Data Connection Wizard window:

Given the data source choices listed, you want to highlight ODBC DSN and click Next.  The next step of the Data Connection Wizard will display all of the ODBC data sources available on the system you are using.

Hopefully, if all as gone according to plan, you should see the DSN that you created in previous steps listed among the ODBC data sources.  Highlight it and click on Next.

The next step in the Data Connection Wizard is to save and finish.  The file name field should be auto filled for you.  You can supply a description.  The description used in the example is pretty self explanatory for anyone that might use it.  Next, click on the Finish button in the lower right of the window.

You should now be back at the Workbook Connection window.  The data connection you just created should be listed:

Importing the Table Data

You can close the Workbook Connection window.  We need to click on the Existing Connections button in the Data ribbon of Excel.  The Existing Connections button should be located to the left on the Data ribbon.

Clicking on the Existing Connections button should present you with the Existing Connections window.  You’ve seen this window in previous steps, the difference now is that your data connection should be listed near the top:

Make sure the data connection you created in the previous steps is highlighted and then click on the Open button.  You should now see the Import Data window:

For the purposes of this post, we are going to use the default settings on the Import Data window.  Next, click on the OK button.  If everything worked out for you, you should now be presented with the MySQL database table data in your worksheet.

For this post, the table we were working with had two fields.  The first field is an auto-increment INT field titled ID.  The second field is VARCHAR(50) and is titled fname.  Our final spreadsheet looks likes like this:

As you’ve probably noticed, the first row contains the table column names.  You can also use the drop down arrows next to the column names to sort the columns.

Wrap-Up

In this post we covered where to find the latest ODBC drivers for MySQL, how to create a DSN, how to create a spreadsheet data connection using the DSN and how to use the spreadsheet data connection to import data into an Excel spreadsheet. Enjoy!

 

Related posts

• Hoe lege regels in Excel te verwijderen

• De functie voor het uitspreken van cellen in Excel gebruiken

• Een Excel-werkblad invoegen in een Word-document

• De What-If-analyse van Excel gebruiken

• Hoe een rij in Excel te repareren

• Snel meerdere rijen invoegen in Excel

• Wanneer Index-Match gebruiken in plaats van VERT.ZOEKEN in Excel?

• Een Excel-bestand delen voor eenvoudige samenwerking

• Hoe te zoeken in Excel

• Hoe maak je een cirkeldiagram in Excel

• CSV of TSV invoegen in een Excel-werkblad

• Snel getallen spellen in Word en Excel

• Hoe een IF-formule / verklaring in Excel te schrijven?

• Afhankelijkheden traceren in Excel

• Hoe gebruik je Flash Fill in Excel?

• Hoe dubbele rijen in Excel te verwijderen

• 3 manieren om een ​​cel in Excel te splitsen

• Excel's What-If Analysis Goal Seek Tool gebruiken

• Centreer uw werkbladgegevens in Excel om af te drukken

• Hoe If en Geneste If-statements in Excel te gebruiken?