MySQL for Excel - MySQL Community Downloads
Contents
1. 16 5 4 Importing a View or Procedure 18 5 5 Adding Summary Fields idee e da 19 5 6 Creating PivotT ables LUE e ERE e EE s 21 6 Append Excel Data into MySQL sssssssssssessssseenenenenen 31 7 Export Excel Data into MySQL 35 8 What Is New In MySQL for 39 8 1 What Is New In MySQL for Excel 1 3 nnne nnns 39 8 2 What Is New In MySQL for Excel 1 2 ssssssssssesesseeeeenenee nennen nnns 39 9 MySQL for Excel Frequently Asked Questions eene eene 41 Chapter 1 Introduction MySQL for Excel enables you to work with a MySQL database from within Microsoft Excel MySQL data can be imported into Excel Excel data can be exported into MySQL as a new table or appended to a current table and MySQL for Excel enables you to edit the MySQL data directly from within Excel External Resources That Supplement the Documentation Release Notes for notes detailing the changes in each release of MySQL for Excel see MySQL for Excel Release Notes Support Forum visit the MySQL for Excel forum for additional MySQL for Excel help and support2. Review SQL Script Review the SQL script to be applied on the MySQL database Creating Table actor copy Inserting 200 row s with the following SQL statement s CREATE TABLE test actor copy actor id Integer not null first name Varchar 12 null last name Varchar 12 null last update Varchar 25 null PRIMARY KEY aactor id X INSERT INTO est actor copy actor id first name last name last update VALUES 1 PENELOPE GUINESS 15 02 2006 04 34 33 a m INSERT INTO test actor copy actor_id first_name last_name last update VALUES 2 NICK WAHLBERG 15 02 2006 04 34 33 a m INSERT INTO test actor copy actor id first_name last_name last_update VALUES 3 ED CHASE 15 02 2006 04 34 33 a INSERT INTO test actor copy actor first name last name last update VALUES 4 JENNIFER DAVIS 15 02 2006 04 34 33 a m INSERT INTO test actor copy actor_id first name last last update VALUES 5 JOHNNY LOLLOBRIGIDA 15 02 2006 04 34 33 a m INSERT INTO test actor copy actor id first_name last_name last update VALUES 6 BETTE NICHOLSON 15 02 2006 04 34 33 a m INSERT INTO test actor copy actor_id first_name last_name last update VALUES 7 GRACE MOSTEL 15 02 2006 04 34 33 m INSERT INTO test actor copy actor id first_name last_name last_update VALUES 8 MATTHEW JOHANSS
3. MySQL for Excel allows you to work with the MySQL Database right from within the MS Office Excel application Excel is a powerful tool for data analysis and editing an Open a MySQL Connection Double Click a Connection to Start Local Connections J X Delete Connection Edit Connection N Refresh Connections New Connection Add a new Database Connection Manage Connections 3 Launch MySQL Workbench About MySQL For Excel The MySQL connection edit dialog is similar to the edit dialog in MySQL Workbench Configure the changes and click OK to save your changes 10 Delete MySQL Connections Figure 3 9 MySQL for Excel Editing a MySQL Connection Connection Name amp Type a name for the connection Connection Method Standard TCP IP v Method to use to connect to the RDBMS Connection Status Unknown Parameters Hostname localhost Name or IP address of the server host TCP IP port Username root Name of the user to connect with Password e The user s password stored in a secured vault Default Schema The default schema leave blank to select it later Advanced C Use ANSI quotes to quote identifiers If enabled this option overwrites the server side settings C Use compression protocol Select this option for WAN connections Test Connection Editing MySQL Connections in MySQL Workbench Optionally you can edit your MySQL f
4. os fr nes v o 1 rating gt spedal_festures last update gt release year 2006 MySQL For Excel X PivotTable Fields X 2 P6 Deleted Scenes Behind the Scenes 15 02 2006 05 03 42 a m tool gt 36 Trailers Deleted Scenes 15 02 2006 05 03 42 4 m Row Labels Sumofrental rate ix User root St tocamos 4 NCI Trailers Deleted Scenes 15 02 2006 05 03 42 a m G 514 22 Ei schema alita lim id slo Commentaries Behind the Scenes 15 02 2006 05 03 42 619 2 te 66 Deleted scenes 15 02 2006 05 03 42 m LA 92 06 mi p nnd 2 description 06 Deleted scones 15 02 2006 05 03 42 m ro 626 7 2 eleme yest Y 90 13 Trailers Deleted scones 15 02 2006 05 03 42 m 5R snas language i oR Trailers 15 02 2006 05 03 42 m Grand Total 2980 Select Database Objects Jd PG 13 Trailers Deleted Scenes 15 02 2006 05 01 42 a m 11 NC 17 Trailers Deleted Scenes 15 02 2006 05 03 42 m a J 26 Commentaries Behind the Scenes 15 02 2006 05 03 42 a m Commentanes Deleted Scenes 15 02 2006 05 03 42 a m Deleted Scenes Behind the Scenes 15 02 2006 05 03 42 m LL 15 NO17 Trailers Deleted Scenes Behind the Scenes 15 02 2006 05 03 42 a m LN NC17 Trailers Commentaries Behind the Scenes 15 02 2006 05 03 42 a NC 17 Trailers Commentaries 15 02 2006 05 03 42 a m Sm Drag feidh between areas below wR Trailers Behind the Scenes 15 02 2006 05 03 42 a m a ner 19 PG 13 Traile
5. Column Options Column Name C Create Index 7 Primary Key C Exclude Column Data Type Integer C Unique Index C Allow Empty _txport data v coner Advanced Export options Several advanced options enables you to tweak the exported data The advanced options dialog looks like so 35 Advanced Export options Figure 7 2 Exporting Excel data to MySQL Advanced options Advanced Export Options Column Datatype Options Usethefirst 100 24 Excel data rows to preview and calculate datatypes Y Analyze and try to detect correct datatype based on column field contents Y Add additional buffer to Varchar length round up to 12 25 45 125 255 V Automatically check the Index checkbox for Integer columns Y Automatically check the Allow Empty checkbox for columns without an index E Show all available MySQL data types in the Data Type drop down list Field Data Options V Use formatted values Queries Options V Create table s secondary indexes after data has been exported to speed up rows insertion Reset to Defaults Accept Cancel Column Datatype Options Use the first 100 default Excel data rows to preview and calculate data types This determines the number of rows that the preview displays and the values that affect the automatic mapping feature Analyze and try to detect correct datatype based on column field contents Attempts to analyze the data and determine the data type
6. Set Routine Parameters routine might need parameters to be set Please p film id int set a value for all parameters Then press the Call button to execute the routine p store id int p film count int 2 Import Options routine might retum more than one result set Import Selected Result Set Please choose which result set to import or how to import several result sets V Include Column Names as Headers Reading Sand 5 5 Adding Summary Fields Summary fields are calculated fields and this option adds summary related functions to each of the imported columns These fields are added to the last row of the imported table data and the dropdown of choices includes Average Sum Min and Max Note Y This feature was added in MySQL for Excel 1 3 0 The Add Summary Fields option disabled by default is listed on the import dialog 19 Adding Summary Fields Figure 5 6 The Add Summary Fields option Import Data from MySQL Choose Columns to Import Click on column headers to exclude include them when importing the MySQL table data in Excel Table Name actor Row Count 201 1 2 3 4 5 6 7 8 9 Options V include Column Names as Headers Limit to gt Rows and Start with Row C Create a PivotTable with the imported data 0 Add Summary Fields Enabling this option adds a row of summary fields for the appr
7. Data into Excel Table of Contents 5 1 Choosing Columns To Export 15 5 2 Importing Table Ln 15 5 3 Import Advanced Options visi nia leds 16 5 4 Importing a View or Procedure 18 5 5 Adding Summary FleldS comica pede eher 19 5 6 Creating PivotTables eda i pi dae aiiidia 21 Data be imported from MySQL into a Microsoft Excel spreadsheet by using the Import MySQL Data option after selecting either a table view or procedure to import 5 1 Choosing Columns To Export By default all columns are selected and will be imported Specific columns may be selected or unselected using the standard Microsoft Windows method of either Control Mouse click to toggle the selection of individual columns or Shift Mouse click to select a range of columns The background color of a column shows the status of each column The color white means that the column has been selected and therefore it will be imported Conversely a gray background means that the column will not be imported Right clicking anywhere in the preview grid opens a context menu with either a Select None Select 11 option depending on the current status 5 2 Importing a Table The dialog while importing a table includes the following options Include Column Names as Headers Enabled by default this inse
8. Is New In MySQL for Excel 1 2 ssssssssssssssssssseeeenee nennen eene enhn enn nnns nena 39 This section summarizes how MySQL for Excel progressed with each minor and major release 8 1 What Is New In MySQL for Excel 1 3 Most of the new features added to MySQL for Excel 1 3 x involve improvements to the Data Import functionality You can now refresh imported data from the source MySQL database by clicking Refresh from the context menu or Refresh All from the navigation menu These check for changes in the source MySQL database and update your imported MySQL data accordingly Use case A colleague sends you a MySQL Excel spreadsheet with data exported from a MySQL database You open the file several days later and worry that the data is outdated so you click Refresh A new Refresh To Defaults button was added to the options pages It changes each option to the default value and you then confirm or cancel the application of these changes Enabling the new Add Summary Fields for Numeric Columns option adds a summary field to the end of each numeric column in Excel From here you choose the desired function for the column Such as total or average You may now import data from multiple objects in a single operation Use Control or Shift to select multiple objects tables and or views from the MySQL for Excel panel and click Import to open the new dialog for selecting additional objects that have direct relationships to the obj
9. Video Introduction for a short introductory to MySQL for Excel video see Video MySQL for Excel Introduction on the official MySQL YouTube channel Developer Blog developers of MySQL for Excel post related blog posts on the official MySQL on Windows Blog Chapter 2 Installation MySQL for Excel is a product for Windows and it is installed with MySQL Installer And typically you will not be required to install or configure additional tools to use MySQL for Excel MySQL for Excel Requirements The MySQL Installer installation process will check if these requirements are met or notify you if further action is required before proceeding with the installation NET Framework 4 0 Client or Full Profile Microsoft Office Excel 2007 or greater for Microsoft Windows Visual Studio 2010 Tools for Office Runtime and MySQL Installer may install this for you Note Y This requirement is different than Office Developer Tools for Visual Studio which is not a substitute An available MySQL Server connection MySQL for Excel Download Either install MySQL for Excel using the MySQL Installer for Windows a system that manages installations and updates for all MySQL products on Windows or download and execute the standalone file The download links are as follows MySQL Installer Download and execute the MySQL Installer MSI file Select the MySQL for Excel product and then proceed with the installation See the MySQL Installe
10. for the column The column type is defined as VARCHAR if it contains multiple types Add additional buffer to VARCHAR length round up to 12 25 45 125 255 When the data type is automatically detected and is set to VARCHAR then it calculates the maximum length for all rows within the column and rounds up the maximum length to one of the defined lengths above If disabled then the VARCHAR length is set to the length of the longest entry in the Excel spreadsheet Automatically check the Index checkbox for Integer columns If enabled default columns with an Integer data type will have the Create Index option enabled by default Automatically check the Allow Empty checkbox for columns without an index If enabled default columns without the Create Index checkbox checked will automatically enable the Allow Empty configuration option Show all available MySQL data types in the Data Type drop down list By default only the most commonly used data types are displayed Enable disabled by default this setting to see a list of all MySQL data types Note Y This option was added in MySQL for Excel 1 3 0 Field Data options 36 Additional Notes Use formatted values When enabled default the data from Excel is treated as Text Double Or Date When disabled data is never treated as a Date type so for example this means that a date would be represented as a number Other options Create table s secondar
11. s data at the current cell 22 Edit MySQL Data Append Excel Data to Table _25 Add data to an existing MySQL Table The background color represents the status of each cell and there are four distinct colors that are used while editing table data Note Y The Green and Blue colors were switched in MySQL for Excel 1 2 0 13 Table 4 1 Background cell colors Color Description White Default color for all cells This is either the original data or the data after Refresh from DB is clicked Gee Cells that were committed with success Blue Cells that were modified but have not yet been committed Cells that generated an error when a commit was attempted An error dialog is also displayed while the commit is attempted Cells that had a commit attempted but the commit failed due to detected changes from external sources For example a different user made a change to a field after it was imported into Excel This is a feature of Optimistic Updates Yellow Cells that accept new data Data entered here is inserted into the MySQL table In our example the green Drama field was changed and then committed first then the blue Gaming field was changed but not committed and then Auto Commit was enabled before changing the 9 to a 10 in column 10 which generated an error because this commit would have added a duplicate value as primary key 14 Chapter 5 Import MySQL
12. set either as a preference or toggled per session The Append Data dialog will now notify you of incompatible types with visual warnings when mapping source Excel columns to target MySQL columns If a mismatch is discovered then the column in the source grid that contains the mapped Excel data turns red and selecting this column displays a warning with text explaining that the source data is not suitable for the mapped target column s data type New preview preferences allow you to enable one of the following three options Preview SQL statements before they are sent to the Server View and optionally edit the MySQL UPDATE INSERT statements before they are committed Show executed SQL statements along with the results View the statements after they are committed which is the current behavior Do not show the MySQL statements Only show summary information such as number of affected rows and not MySQL statements This is enabled by default Create Table The Data Export feature now has the option to only create the table without inserting the data To execute toggle the Export Data button to Create Table and then click The selected schema name is now displayed on top of the MySQL for Excel Database Object Selection panel The Advanced Options dialogs opened from the Import Export and Append Data windows now immediately apply the option changes when before the Advanced Options dialog had to be reopened before the ch
13. 2706 0 x HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW DEVELOPER LOAD TEST Javier Trevi o n e f a E Connections EN Y Y Clear A E Flash Fill 8 0 Consolidate 98 Group A ALZ El m 3m ES 27 Properties Y Reapply Remove Duplicates l What If Analysis f Ungroup Get External Refresh i Sort Filter o y n g Data gt All Edit Link Y Advanced Columns 25 Data Validation AG Relationships eB Subtotal Connections Sort amp Filter Data Tools Outline 5 a M f v A B c D E F G H a 1 inventory id Y p film count MySQL For Excel vr 1 4 Local instance MySQL56 3 2 N User root IP localhost 4 3 tee Schema sakila 5 4 6 7 Click in this area to work with the 8 1 PivotTable report akila ilt po ggg Select Database Objects Use CTRL or SHIFT for multiple selectior 10 Click in this area to work with the PivotTable report Q 12 m list tal staff list 24 LM vw Procedures 15 8 rewards report E 17 O lt gt 18 v e 28 film not in stock a v 19 20 777 Import MySQL Data BES Add object s data at the currer 21 22 23 24 25 Sheet r gt Options Back Close READY In our example we selected to import All Result Sets Arranged Horizontally Because the Create a PivotTable with the imported data option was also checked a PivotTable was created for each returned result set 25 Creating PivotTables Figure 5 15 PivotTable Example Arrange
14. 8 A 4 femen Catcutation 2 0 nmm 3 Hort 5 A gnmert 3 Nacer A fh tating hi v T 2 MySQL For Excel Xx Local instance MySQLSS User Poca b Ed Creste a new table and fl t mth data de y Select a Database Object en dick on an action below v Tables Escape test values that stat with Excel does nct treat them as formulas ie 2 sese Excel Table Options Ze Y Creste an Excel table for the emported MySQL table data u address Une style PwotstyeDart wo forthe new Excel table B cuyo EZ Excel tables with the following bet MySQL Bo D omy R Column Names Headers Ouma 1 T Rows and Stat with Row B A Import MySQL Data UB sc at the coment cot gamme Open a nam wheat to edt table data A vence Cancel ij Aopen Data to Table 544 date to mm exiting MySQL Table a Back Chose Excel Table Options Create an Excel table for the imported MySQL table data Enabled by default Use style for the new Excel table Defaults to MySqiDefault Prefix Excel tables with the following text Disabled by default Importing a table displays a dialog similar to the following 17 Importing a View or Procedure Figure 5 4 Importing table data with MySQL for Excel IDEO REN gt cea Import Da
15. A Awe Inspiring Reflection of Pastry Chef And a Teacher who must Overcome Sumo Wrestler in A U Boat 499 DI armer ARABIA DOGMA 09 A Touching Epistle of a Madman And a Mad Cow who mest Defeat a Student in Nigeria 099 uj e y 4 ARK RIDGEMONT 099 gt ALUES A Beautiful Yarn of Pioneer And a Monkey who must Pursue a Explorer in The Sahara Desert 0 mon ova ARTIST COLDALOOOED 19 pr pro A Stunning Reflection of a Robot And a Moose who must Challenge a Woman in California i gp 2 00 om we T BABY MALL 499 woe Y L A Boring Character Study of A Shark And Girl who must Outrace Feminist in A Abandoned Mine Shaft A 5 BALLOON HOMEWARD 2 5 Options lt Back Core Deler Layout We can also do this with data coming from a MySQL view or stored procedures The only difference is that for stored procedures we can create a PivotTable for each of the imported result sets returned by the procedure s call Take the following screenshot as an example where we have the film in stock stored procedure selected we configured its input parameter values and we called the procedure You can see the procedure returned one result set Result and the OutAndReturnValues table always present if the procedure has output parameters or a return value 24 Creating PivotTables Figure 5 14 PivotTable Example Stored Procedure HHS Book Excel
16. Column Mappings Rename Delete Unmapped Columns Mapped Columns Cancel Advanced Options Store Mapping Append Cancel The advanced Mapping Options Perform an automatic mapping when dialog opens Automatically attempt to map the target and source when the Append Data dialog is opened This feature is enabled by default Automatically store the column mapping for the given table Stores each mapping routine after executing the Append operation The mapping routine is saved using the tablenameMapping dbname tablename format This may also be performed manually using the Store Mapping button It is enabled by default and this feature was added in MySQL for Excel 1 1 0 e Reload stored column mapping for the selected table automatically Ifa stored mapping routine exists that matches all column names in the source grid with the target grid then it is automatically be loaded This is enabled by default and this feature was added in MySQL for Excel 1 1 0 The advanced Field Data Options Use the first 100 default Excel data rows to preview and calculate data types This determines the number of rows that the preview displays and the values that affect the automatic mapping feature Use formatted values The data from Excel is treated as Text Double or Date This is enabled by default When disabled data is never treated as a Date type so for example this means that a date would be represented as a number The adva
17. MySQL for Excel Abstract This is the MySQL for Excel Reference Manual It documents MySQL for Excel 1 3 through 1 3 6 Much of the documentation also applies to the previous 1 2 series For notes detailing the changes in each release see the MySQL for Excel Release Notes For legal information see the Legal Notices For help with using MySQL please visit either the MySQL Forums or MySQL Mailing Lists where you can discuss your issues with other MySQL users For additional documentation on MySQL products including translations of the documentation into other languages and downloadable versions in variety of formats including HTML and PDF formats see the MySQL Documentation Library Document generated on 2015 12 16 revision 45894 Table of Contents e OTi EE SA men Ug IM ID Ius trig 1 2 Installation 22 82 ET 3 3 Configuration au Her a rea 5 3 1 Global Options and Preferences 5 3 2 Managing MySQL Connections 8 4 Edit MySQL Data im EXG6l Joni eve ea e vue LO dou NS Vie Ld arid Pe VR ra 13 5 Import MySQL Data into Excel nennen nne rh amare sn nsns 15 5 1 Choosing Columns To Export rriena aiani i i a a N aa ia 15 5 2 Importing a Table 3 crt aii 15 5 3 Import Advanced Options
18. ON 15 02 2006 04 34 33 a m INSERT INTO test actor copy actor id first_name last_name last update VALUES 9 JOE SWANK 15 02 2006 04 34 33 a m gt lt gt Global Options and Preferences From here you can modify the SQL statements before they are executed which also enables the Original Query button If clicked it will revert all modifications to the script to restore the SQL to its original form when the dialog was first opened This option is disabled by default Show executed SQL statements along with their results When enabled SQL statements are first executed and then the information dialog includes both the results and the executed statements This is helpful when reviewing the recently executed queries when comparing the results This option is disabled by default Edit Session Options Restore saved Edit sessions when opening an Excel workbook Enabled by default Reuse Excel worksheets matching their names with the session table names Enabled by default Create new Excel worksheets for the restored Edit sessions Disabled by default Manage Stored Import and Edit Connections See a list of saved Excel files with linked MySQL connections Figure 3 4 MySQL for Excel Manage Stored MySQL Connections r Manage Import Edit Connections Information i MySQL for Excel Import and Edit connection information maintenance Choose Edit Impor
19. WAHLBERG 2 15 2006 4 34 AM 3 4 eo CHASE 1 27 2014 1 29 PM E DAVIS 11 27 2014 1 29 PM OMORRIGIDA 1 77 20 5 pp 6 E 18 19 20 E 28 29 E El aa Columns LJ Mapped Columns IFR General Mapping Information It is common to tweak the column mappings A few notes about the manual mapping process 31 Mapping Methods Manual mapping is performed by dragging a column from the upper source grid Excel spreadsheet and dropping it into the lower target column MySQL table grid Click anywhere within the column to initiate this dragging routine The color of the header field for each column defines the current mapping status of the column The colors include Green A source column is mapped to a target column Red A target column is not mapped Gray A source column is not mapped A source column may be mapped to multiple target columns although this action generates a warning dialog Right clicking a target column shows a context menu with options to either Remove Column Mapping for a single column or to Clear All Mappings for all columns Dragging a target column outside of the grid removes the mapping Mapping Methods The three mapping methods are described below Automatic The automatic mapping method attempts to match the Excel source column names with the MySQL target table column names It is then possible to manually tweak the mapping afterwards If the au
20. amp TM df Format Printer 2 4 E 7 Merge Formatting Tabie t lO ee Abgnesent mt tamg os f nes v o p MySQL For Excel PivotTable Field gt x release yer 2006 g MySQL For Exce ivotTable Fields Local instance MySQLS6 noose fadi to add to report Row Labels Sum of rental rate aana Anann 6 1422 c im jd ee _ ns pias 6 ADAPTATION HOLES 2m amp v description y A Astounding Reflection of Lumberjack And Car who must Sink a Lumberjack in A Baloon Factory tome T ALADOIN CALENDAR Aw angusge id A Action Packed Tale of a Man And a Lumberjack who mast Reach a Feminist in Ancient China 49 quy euet Dutsbuse espinal ungesge id 7 ALICE FANTASIA 099 rental daston A Emotional Drama of A Shark And a Database Administrator who must Vanquish a Psoewer in Soviet Georgia 059 a cate 3 A Brilliant Drama of a Cat And a Mad Scientist who must Battle Feminist in A MySQL Convention 299 u J SALLEY EVOLUTION 2 1 Saar e img A Fast Paced Drama of a Robot And a Composer who east Battle a Astronaut in New Orleans 299 29 ANONYMOUS HUMAN 099 y Amazing Reflection of a Database Administrator And a Astronaut who must Outrace a Database Administrator in A Shark Tank 0 99 2 um Drag feldi Between areas below ANTITRUST TOMATOES 29 Dim A Fateful Yarn of a Womanizer And a Feminist who must Succumb a Database Administrator in Ancient India 299 APACHE DIVINE 49 LI
21. anges could be previewed Edit Data sessions can now be saved Using the new Edit Session preferences these sessions were automatically closed after closing an Excel workbook This data such as the Workbench connection ID MySQL schema and MySQL table name can now be preserved if the Excel workbook is saved to disk and available when the Excel workbook is reopened Excel tables are automatically created for any data imported from MySQL to an Excel worksheet with a name like Schema DB Object name The DB object name can be a MySQL table view or stored procedure Options for this feature are listed under Import Data Advanced Options The newly created Excel tables can be referenced for data analysis in Pivot Tables or reports 40 Chapter 9 MySQL for Excel Frequently Asked Questions Questions 9 1 41 When I m using Excel to edit data from a live MySQL database will my changes overwrite changes made by other users For example maybe they used MySQL Workbench to edit the same data at the same time 9 2 41 I installed the MySQL for Excel plugin but can t find it in Microsoft Excel How do start it 9 3 41 I click on Edit Data and after importing the table data into Excel can t sort or move columns around How can do that 9 4 41 When editing a MySQL table s data the Excel worksheet where the data is dumped is protected How can unprotect it 9 5 41 The MySQL Workbench connections that use SSH t
22. ata into Excel Figure 4 1 Editing table data with MySQL for Excel Bookl Compatibility Mode Microsoft Excel Home Insert Page Layout Formulas Data Review View Oracle UCM Es 5 connections al DH F Clear Em Data Validation gt Group 7 r Y i id f Properties Y Reapply Fa Consolidate Ungroup Get External Refresh 2 J Sort Filter Tetto Remove De Data Ally gt Edit Links X7 Advanced Columns Duplicates 22 What If Analysis Subtotal Connections Sort amp Filter Data Tools Jl Outline MySQL For Excel gt 1 Acti mn 4 46 ion ah Auto C NN User root IP localhost 3 Animation 2 15 2006 4 46 MySQL for Excel El Auto Commit Revert Data Commit Changes X Export Excel Data to New Tabl emu 5 4 Classics 2 15 2006 4 46 X Create a new table and fill it with data 6 5 Comedy 2 15 2006 4 46 T T 6 Documentary 2 15 2006 4 46 8 7Drama 2 15 2006 4 46 Ey Select a Database Object Then click on an action item below EN 8 Family 2 15 2006 4 46 10 B Foreign 2 15 2006 4 46 11 10 Gaming 2 15 2006 4 46 v Tables 2 12 11 Horror 2 15 2006 4 46 29 actor a 13 12 Music 2 15 2006 4 46 1 14 13 New 2 15 2006 4 46 E address a 16 15 Sports 2 15 2006 4 46 z 17 16 Travel 2 15 2006 4 46 d cy 18 a country 19 20 Wl Import MySQL Data 21 Add object
23. ccept Saves option changes to your host and preserves these changes across all sessions and future Excel instances Clicking Reset to Defaults Resets all option values on the current options window to their default settings Click Accept to save the changes A set of global options affect the entire plugin as described here Figure 3 1 The MySQL for Excel configuration Global Options MySQL for Excel Options Global Options Connection Options Wait 15 seconds for a connection to the server before timing out Wait 60 seconds for a database query to execute before timing out SQL Queries Options V Use optimistic updates on all Edit Data sessions Do not show SQL statements sent to the server 5 Preview SQL statements before they are sent to the server Show executed SQL statements along with their results Edit Sesssion Options V Restore saved Edit sessions when opening an Excel workbook O Reuse Excel worksheets matching their names with the session table names Create new Excel worksheets for the restored Edit sessions Manage Stored Import and Edit Connection Reset to Defaults Cancel Connection Options Wait seconds for a connection to the server before timing out Defaults to 15 Wait seconds for a database query to execute before timing out Defaults to 60 Global Options and Preferences SQL Queries Options Use optimistic updates on all Edit Data se
24. cel REVIEW VIEW Y Header Row Total Row C2 Banded Rows M dastupdate 15002720086 05 09 42 TABLE TOOLS m DX DEVELOPER LOADTEST DESIGN Javier Trevi o n First Column Y Filter Button Y z Last Column Quick Banded Columns Styles Table Style Options Table Styles v o MySQL For Excel vx 160272006 05 0342 m 1500272006 050342 15 02 2006 06 03 42 m 1500272006 06 03 42 1500272006 050342 15 02 2006 05 0342 1500202006 05 03 42 1500202006 05 03 42 16 02 2006 05 0342 1500202006 07 03 42 m 15 02 2006 05 03 42 1600272006 05 0342 1500202006 05 03 42 15 02 2006 05 0342 1500272006 0 03 42 m 1500272006 05 09 42 1600272006 05 03 42 m 1570212006 05 03 42 m 1500272008 05 09 42 15 02 2006 05 03 42 1500202006 05 03 42 1506212006 05 09 42 a m 1500202006 05 0342 m 1500202006 05 09 42 a m 1500272006 05 03 42 1500272006 05 03 42 1502 2006 05 09 42 1600202006 05 03 42 a m 1500272006 050342 15 02 2006 05 0342 a m 1500202006 05 03 42 m 1500202006 05 03 42 m 1500272006 05 0242 a m 1500272006 07 03 42 m 1502 2006 05 0342 m 1502 2006 05 03 42 m 1500272006 05 03 42 m 15 02 2006 05 0342 m 1500202006 05 03 42 m 1502 2006 05 09 42 100202006 50 142 a m 4 Cosmo Click i
25. created This is because this type of PivotTable requires that the model tables are related among each other Our next example uses all tables in our schema You can manually choose each table or use Control A in the database objects list to select them all When clicking Import Multiple Tables and Views the Import Data dialog appears as shown below We need to check the Create a PivotTable option which by default its drop down is set to for all the tables in the data model Keep that value 27 Creating PivotTables Figure 5 18 Importing All Tables and Views Import Data from MySQL z Selected Tables and Views g Pick Related Tables z 5 Tables and views selected below will be imported to individual 5 Related tables not in the original selection can be selected Excel worksheets based on their relationships Selected Tables and Views 23 Selected related Tables 0 Table View Related to Table Related to T3 actor film actor actor info T address city customer staff store 1 category film category country address city customer address store payment rental F customer list Di film language film actor film category inve a film_actor actor film T film category category film film list BD film text inventory film store rental T language film U Add Summary Fields __ Advanced Options When clicking Import the data in all of the selecte
26. d Horizontal Import Data from MySQL 1 Set Procedure Parameters film in stock A procedure might need parameters to be set Please p film id set a value for all parameters Then press the Call m id button to execute the procedure e um p film count 2 Import Options 3 A procedure might return more than one result set Import All Result Sets Arranged Horizontal v Please choose which result set to import or how to import several result sets V include Column Names as Headers Create a PivotTable for each imported result set Add Summary Fields An important use case for PivotTables is when we create it for multiple related tables as typically a single table does not contain all of the data needed by a PivotTables report You can create a single PivotTable tied to the data in the current Excel Data Model that contains fields from several related tables That way you can use the data in a single report for an entire MySQL schema if needed However you can only do this in Excel 2013 and later where the Excel Data Model is available In Excel versions before Excel 2013 only a PivotTable for each imported table or view can be created This is because a single PivotTable for the entire Excel Data Model requires that the tables are related to each other If Excel relationships cannot be created then this type of PivotTable cannot be created So in these cases the Import Data dialog looks like the following
27. d tables is imported to Excel its Data Model and Excel relationships are created and a new worksheet is created that contains a PivotTable with all of the tables that were imported This is demonstrated in the screenshot below and notice that all tables are listed in the PivotTable Fields panel 28 Creating PivotTables Figure 5 19 Importing All Tables and Views Listing DH s EG HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW DEVELOPER LOAD TEST Book Excel E Ta 2 Connections mm Y Flash Fil B Consolidate ia ra zu Properties y Remove Duplicates E What If Analysis Get External Refresh E P Sort Filter Texto em ur lys Data All 3 Edit Links gt Y Advanced Columns Ex Data Validation Relationships Connections Sort amp Filter Data Tools Al fe PIVOTABLE TOOLS MH ANALYZE nx DESIGN Javier Tre n Group ES y S ur for Excel Outline Fa MySQL X i Yx 1 MySQL For Excel PivotTable Fields 2 Multa Local instance MySQLS6 ACTIVE ALL 3 User root IP localhost Es Choose fields to add t t 4 Tobuilda report choose ie Schema sakila eon QUPD PN 5 fields from the PivotTable b ES sakila actor 6 Field List 7 EX sakila address 8 b E sakila category 9 5 Select Database Objects 10 Use CTRL or SHIFT for multiple selection b F3 sakila city x r E sakila country 12 v v Tabl
28. ects you selected Each object opens in its own Worksheet From this new dialog you may also generate a Relationships model in Excel This functionality requires Excel 2013 or higher or Excel 2010 with the PowerPivot add in A new Create a PivotTable with the Imported Data option was added This creates a Pivot Table in Excel All options now have descriptive tooltips Hover over an option preference to view helpful information about its use You may now specify a collation for created schemas The collation type defaults to Server Default These statements can be reviewed before execution MySQL data types are now available when performing Data Export operations By default only the most commonly used data types are listed which was only behavior in previous versions of MySQL for Excel You may still type in a type instead of selecting it 8 2 What Is New In MySQL for Excel 1 2 Edit Connections MySQL connections can now be edited from within the MySQL for Excel plugin by right clicking and choosing Edit Connection Before these connections could only be edited with MySQL Workbench Optimistic Updates Previously only Pessimistic Updates were used which means that pressing Commit Changes would overwrite changes performed outside of MySQL for Excel for the edited cells 39 What Is New In MySQL for Excel 1 2 Both options remain available today and optimistic updates are enabled by default This update type can be
29. en importing the MySQL table data in Excel Table Name aaa_sampletable Row Count 7 Advanced Import Options id country money formula Afghanistan 45464 000000000000000000000000 C2 16 General Options Algeria 456 00000000000000000000000000 C3 16 Usethefirst 4 Ey rows to preview the MySQL table s data American Samoa 23157 000000000000000000000000 C4 16 V Escape text values that start with so Excel does not treat them as formulas Angola 546554 00000000000000000000000 CS 16 Table Options V Create an Excel table for the imported MySQL table data Use style PrvotStyleDark1 w forthenew Excel table Mi Prefix Excel tables with the following tet MySQL Options Include Column Names as Headers O Limitto 1 Rows and Start with Row Import Cancel Escape text values that start with so Excel does not treat them as formulas and is enabled by default This option may not reflect any differences in the preview because itis only applied after the data is imported into the Excel Worksheet 16 Import Advanced Options Figure 5 3 MySQL for Excel Escape z formulas Bias 0 1 ice re Meme Papelaput Fomm Review View Tem Oen e cen Kos Can n gt zw Genara M fee ap Tnt a dcum y X A Qa Aa nd aj Dm a ret premarin 2 EE RI mean
30. es 13 b ES sakila customer 14 15 Drag fields between areas below 16 Y FILTERS COLUMNS 17 18 19 Y 20 Import Multiple Tables and Views 21 Add each object s data to new sheets 7 ROWS X VALUES 22 23 24 25 a c sales by store T gt Optico Bad lose Defer Layout Update READY 01 m 34 100 You can also configure the Create a PivotTable drop down list the for each imported table or view which in turn will create a PivotTable for each of the imported tables or views as opposed to creating a single PivotTable for all of them 29 Creating PivotTables Figure 5 20 Importing Each Imported Table or View Import Data from MySQL E Selected Tables and Views Pick Related Tables z Tables and views selected below will be imported to individual Related tables not in the original selection can be selected Excel worksheets based on their relationships Selected Tables and Views 23 Selected related Tables 0 Table View Related to Table Related to actor film actor Z actor info T address city customer staff store Di category film category city country address E country city customer address store payment rental customer list film language film actor film category inve Di film actor actor film film category category film I film list film text inventory language Add Summary Fields 30 Chapter 6 Append Excel Data into MySQL Data
31. filing a bug report bugs mysql com or attempt to reinstall the plugin 9 3 I click on Edit Data and after importing the table data into Excel can t sort or move columns around How can I do that In order to maintain the mapping of rows and columns in the Excel Worksheet against the rows and columns in the MySQL table no alteration is permitted on the worksheet i e sorting deleting rows deleting columns If you need to alter the data there you can do that by right clicking the Edit Data window and selecting Exit Edit Mode 9 4 When editing a MySQL table s data the Excel worksheet where the data is dumped is protected How can unprotect it The Excel worksheet is protected to not allow alterations to the order of rows and columns The password used for the protection is a GUID auto generated at runtime so that the protection is not violated in any way If you wish to unprotect the worksheet to manipulate your data you can do that by right clicking the Edit Data window and selecting Exit Edit Mode 9 5 The MySQL Workbench connections that use SSH tunneling appear grayed out disabled in MySQL for Excel How can use a SSH connection 41 This is a known limitation of MySQL for Excel MySQL for Excel uses MySQL Connector NET to connect and communicate with MySQL databases Connector NET does not have SSH support so the behavior will change if Connector NET supports it in the future 42
32. from a Microsoft Excel spreadsheet can be appended to a MySQL database table by using the Append Excel MySQL Data to Table option Column Mappings Mapping the Excel columns to the MySQL columns can be executed automatically default manually or by using a stored mapping routine An automatic mapping routine is the default and can be can be tweaked if every column cannot be matched automatically The following screenshot shows two columns of Excel data and the preview dialog after choosing Append Excel Data to Table Figure 6 1 Appending Excel data to MySQL Automatic mapping Bookl Sein patbir Meds OSEE Es connections 4 K Clear E Data Validation gt ig Properties at 2001 y Reapply ES Fa Consolidate Al Edit Links Yo Advanced Usi De ES What If Analysis Sort amp Filter 201 Paul Hinz 2 15 2012 4 34 202 Jon Ellison 2 15 2012 4 34 203 Suki Allen Append Data to MySQL Table 1 Choose Column Mapping Method 7 2 Manually Adjust Column Mapping Hn Select how the Excel columns should be mapped to the E Manually change the column mapping if needed Click MySQL table columns a column in the upper table with the mouse and drag it onto a column in the lower table Mapping Maho E First Row Contains Column Names Hinz 2 15 2012 4 34 00 AM Ellison 2 15 2012 4 34 00 AM 2 15 2012 4 34 00 AM actorid first name last name last update 1 PENELOPE GUINESS 2715 2006 4 34 AM 2 NICK
33. hind the Scenes 299 46 3039 NCH Trailers Commentaries Behind the Scenes 299 180 2399 NO M Tralors Commentaries v 099 82 4939 Trailers Behind the Scenes LJ 039 57 2799 PGA Trailers Behind the Scenes 20 099 2099 PG Commentaries Deleted Scenes Behind the Scenes a 439 73 2399 R Commen aies Deleted Scenes Behind the Scenes 22 439 129 17 99 R Commentaries Behind the Soenez 23 299 85 039 G Comment aries Behind the Scenes 24 099 92 39 R Trailers Deleted Scenes 25 299 el 1399 R Trailers Behind the Scenes 26 239 74 539 Trailers a 039 86 15 99 G Commentaries Deleted Scenes 26 099 179 1299 NC Deleted Scenes Behind the Scenes 23 99 5 1639 PGO Deleted Scenes Behind the Scenes 30 299 168 199 NO I Trailers Commentaries Deleted Scones gt 239 82 2799 R Trailers Deleted Scenes Behind the Scenes 92 438 92 3639 NCI Commentaries Deleted Scenes Behind the Scenes 33 499 ns 199 R Trallers Comement aries 233 5 1539 FG 13 Trailers Commentaries Deleted Scenes Behind the Sc 95 099 62 2939 17 Commentaries Deleted Scenes 36 29 7 2499 PG 13 Trailers Deleted Scenes Behind the Scenes 3 039 127 1299 1 Trailers Commentaries 38 299 121 2899 PG Trailers Deleted Scenes 099 68 2539 NC 17 Trailers Comment wies Deleted Scenes Behind the Sor 40 099 33 099 G Trailers 099 2299 R Trallers Commwent aries 42 099 n7 1739 PG Trailers Deleted Scenes Behind the Scenes an 298 m 30139 MC Trailers Rebind the Scenes Sheet Ex
34. l M 4 M Sheeti Sheet2 Sheet3 2 From Excel click New Connection to open the new connection dialog as demonstrated in the following partially filled screenshot Figure 3 7 MySQL for Excel Add a New MySQL Connection Dialog Connection Name My New Connection Name Type a name for the connection Connection Method Standard TCP IP v Method to use to connect to the RDBMS Connection Status Unknown Parameters Hostname localhost Port 3306 Name or IP address of the server host TCP IP port Username Name of the userto connect with Password The user s password stored in a secured vault Default Schema sakila The default schema leave blank to select it later Advanced Use ANSI quotes to quote identifiers Tf enabled this option overwrites the server side settings Use compression protocol Select this option for WAN connections A Fill out the connection details click Test Connection to confirm the MySQL connection is valid and click OK to save the new connection Editing MySQL Connections Editing MySQL Connections Editing MySQL Connections in MySQL for Excel To edit a MySQL connection right click the connection you want to modify and select Edit Connection from the context menu like so Figure 3 8 MySQL for Excel Choosing a MySQL Connection to Edit MySQL For Excel XS T Welcome to MySQL for Excel for Excel
35. n this area to work Local instance MySQL56 with the PivotT able report User root IP localhost jee fe J Schema sakila Select Database Objects se CT a IC i film actor Si film category 2 film text E inventory 777 Import MySQL Data x Edit MySQL Data t table data Options lt Back Close Clicking the PivotTable opens a PivotTable Fields panel to next to the MySQL for Excel panel and from here you can select fields you want to summarize in the PivotTable report Drag and drop fields from the list to any of the FILTERS COLUMNS ROWS or VALUES areas depending on the visualizations you want in the report The report is completely dynamic meaning that you can change the views by moving fields around the areas until you see the visualization you need for your PivotTable report Below is an example PivotTable report using the sakila film table we imported in our previous example This report includes a filter by release_year and it summarizes the rental_rate values while also grouping the data by values in the rating column 23 Creating PivotTables Figure 5 12 PivotTable Example Film Ratings 08 o Boot Excel PNOTUBU TOOLS bi oo Data smew VEW DEVELOPER LOADTEST ANALYZE DESIGN EY e amp sal Cain au 2 Reve General k DY normal bad j B DX x v pP BEN lo LLLI Eee coto EE ELE Breed Sm forme
36. nced SQL Queries Options Disable table indexes to speed up rows insertion This option is disabled by default since you must make sure that if unique indexes are present that the data mapped to that column does not contain duplicate data This option was added in MySQL for Excel 1 2 1 The Stored Column Mappings is a list of saved column mappings that were saved with the Automatically store the column mapping for the given table feature or manually with the Store Mapping option 33 34 Chapter 7 Export Excel Data into MySQL Data from a Microsoft Excel spreadsheet can be exported to a new MySQL database table by using the Export Excel Data to New Table option Exporting data looks like so Figure 7 1 Exporting Excel data to MySQL Bookl Compatibility Mode MyLocalMySQL YN User root IP localhost Entel Schema sakila Export Excel Data to New Table Create a newtable and fill it with data Export Data Sheetl A1 C5 Export Data to MySQL 1 Set a Table Name 2 Pick a Primary Key The selected data will be stored in a MySQL table Each row of data needs to hold a unique number Please specify a unique name for the table that is used as the Primary Key Name food Add a Primary Key column food id 3 Specify Column Options J Click the header of a column to specify options like column name and a datatype V First Row Contains Column Names This is a sr
37. oolbar to add a new and empty MySQL schema Figure 3 5 MySQL for Excel Adding a New Schema MySQL for Excel Qe New Schema Name Please enter a name for the new schema for Schema Name MyFriends Collation Server Default X 3 2 Managing MySQL Connections MySQL for Excel shares its MySQL connections with MySQL Workbench although it is optional to have MySQL Workbench installed Creating and editing MySQL connections in either application will edit the MySQL connection information for both applications Adding MySQL Connections You can use MySQL for Excel or MySQL Workbench to add new MySQL connections Adding MySQL Connections Figure 3 6 MySQL for Excel Add a New MySQL Connection Bookl Compatibility Mode Microsoft Excel Page Layout Formulas Data Review View Oracle UCM Team MySQL for Excel MySQL for Excel allows you to MySQL for Exh MySQL Database right from w MySQL Datat h Office Excel application Excel i Office Excel aC tool for data analysis and edit tool for data t e Opena MySQL Connection Create a new MySQL Double Click a Connection to Start connection in MySQL for Excel v Local Connections e First Connection v Remote Connections Optionally use MySQL Workbench to add your new New Connection MySQL connection Add a new Database Connection ES Manage Connections E Launch MySQL Workbench About MySQL For Exce
38. opriate columns in your imported data Notice the newly created row on the bottom Figure 5 7 The new Add Summary Fields the new row Select the row to reveal a down arrow and click it to display a set of summary options 20 Creating PivotTables Figure 5 8 The Add Summary Fields row choices 4581 4580 1000 2 2 15 2006 05 09 4582 4581 1000 2 2 15 2006 05 09 4583 4581 4584 4585 4586 Count Numbers Max 4587 Min 4588 Sum 4589 4590 For example choosing Sum Figure 5 9 The Add Summary Fields row sum example More Functions Adjust each summary field accordingly 5 6 Creating PivotTables A PivotTable can be created from imported MySQL tables views stored procedures or the entire Excel Data Model Note Y This feature was added in MySQL for Excel 1 3 0 An Excel PivotTable report summarizes and provides a visual representation of data in many different ways It is a native Excel feature see PivotTable reports 101 for additional information about Excel PivotTables Our example covers a simple use case where an empty PivotTable is created from an imported MySQL table This example uses the film table of the Sakila database To create the PivotTable select the film table from the database object s selection panel and then click Import MySQL Data On the Import Data dialog check the Create a PivotTable before pressing OK to execute the operation 21 Creating PivotTable
39. or Excel MySQL connections using MySQL Workbench To do this open MySQL Workbench edit a MySQL connection and then refresh the connection list in MySQL for Excel For information about editing MySQL connections in MySQL Workbench see the MySQL Workbench documentation titled MySQL Connections Delete MySQL Connections MySQL connections can be deleted from MySQL for Excel or MySQL Workbench Note Y MySQL connections cannot be deleted if MySQL Workbench is open To remove connections you must first close MySQL Workbench To delete an edit or import connection from MySQL for Excel to a particular Excel worksheet click Options Manage Stored Import and Edit Connection check the desired worksheets and then click Apply to execute the delete action 11 12 Chapter 4 Edit MySQL Data in Excel MySQL for Excel enables you to load and edit MySQL data directly from Microsoft Excel Changes are immediately committed if the Auto Commit option is enabled or done manually by pressing Commit Changes The example below uses the category table of the example 1 database but the screen will look the same for any table Within MySQL for Excel Open a MySQL Connection click the sakila schema Next select the category table click Edit MySQL Data then choose Import to import the data into a new Microsoft Excel worksheet for editing Note N For additional information about the importing procedure see Chapter 5 Import MySQL D
40. r manual for additional details This is the recommended approach Standalone Download and execute the MySQL for Excel standalone MSI file MySQL for Excel is loaded and executed by selecting the Data menu tab in Excel and then choosing the MySQL for Excel Database icon This opens a new Excel sidebar with the available MySQL for Excel options The navigation bar with the MySQL for Excel icon is shown in the following screenshot MySQL for Excel Download Figure 2 1 The MySQL for Excel navigation bar Export Excel Data to New Table Create a new table and fill it with data Select a Database Object Then dick on an action item below v Tables Fr address category a country mmm MySQL Data Add object s data at the current cell Ei Edit MySQL Data Open a new sheet to edit table data Append Excel Data to Table Add data to an existing MySQL Table Chapter 3 Configuration Table of Contents 3 1 Global Options and Preferences 3 2 Managing MySQL Connections enne nennen enne nnn nennt This section is divided into two overlapping topics configuring the global options and managing the MySQL connections 3 1 Global Options and Preferences Each action such as Import MySQL Data has its own set of options The buttons on these pages include Actions include Clicking A
41. rs Behind the Scenes 15 02 2006 05 03 42 a m Y Pers 20 PG Commentaries Deleted Scenes Behind the Scenes 15 02 2006 05 03 42 m 2 gt T 1R Commentaries Deleted Scenes Behind the Scenes 15 02 2006 05 03 42 a m aoe 2 R Commentaries Behind the Scenes 15 02 2006 05 03 42 3 m 26 Commentaries Behind the Scenes 15 02 2006 05 03 42 m 3 MR Trailers Deleted scenes 15 02 2006 05 03 42 m ges 51 Trailers Bened the scenes 15 02 2006 05 01 42 m u oet men om TENE 6 Trailers 15 02 2006 05 03 42 m mol cual lo Commentaries Deleted Scenes 15 02 2006 05 03 42 rn Ome le NC 17 Deleted Scenes Behind the Scenes 15 02 2006 05 03 42 m des nguon 9 P6 19 Deleted Scenes Behind the Scenes 15 02 2006 05 03 42 e gy Mot See Com to Trailers Comenennaries Deleted Scenes Sheet 15 02 2006 05 03 42 a m Options lt Back Cove Expanding one of the groups reveals its values from the title and description columns Figure 5 13 PivotTable Example Expanded Group o 60041 Excel PIVOTTABUE TOOLS 73 85 x Ka INSERT PAGE LAYOUT FORMULAS DATA view DEVELOPER LOAD TEST ANALYZE DESIGN as Tomo n 2 e n Be P Wrap Text General Y Normal Bad fum x us Zum A A gt Tra Pee amp h A E We se mere center gt 82 Conditional format os Good imen Format Sot
42. rts the column names at the top of the Microsoft Excel spreadsheet as a headers row Limitto Rows and Start with Row Disabled by default this limits the range of imported data The Limit to option defaults to 1 and defines the number of rows to import The Start with Row option defaults to 1 the first row and defines where the import begins Each option has a maximum value of COUNT rows in the table Add Summary Fields Disabled by default this option adds a summary field to each column For additional information see Section 5 5 Adding Summary Fields 15 Import Advanced Options 5 3 Import Advanced Options Figure 5 1 Importing table data with MySQL for Excel Advanced options Advanced Options Advanced Import Options General Options Usethefirst 10 rowsto preview the MySQL table s data Escape text values that start with so Excel does not treat them as formulas Excel Table Options Create an Excel table for the imported MySQL data forte new liable Prefix Excel tables with the following text MySQL General Options Use the first 1 0 rows to preview the MySQL tables data This affects the preview step in the import process and defaults to 10 Figure 5 2 MySQL for Excel Preview Import Data aaa sampletable Import Data from MySQL Choose Columns to Import Click on column headers to exclude include them wh
43. s Figure 5 10 Option Create a PivotTable with the imported data Import Dat Import Data from MySQL ac Choose Columns to Import Click on column headers to exclude include them when importing the MySQL table data in Excel Table Name film Row Count 1000 film_id title description release year language id original language id rental d ACADEMY DINOSAUR Epic Drama of a Feminist And a M ACE GOLDFINGER Astounding Epistle of a Database ADAPTATION HOLES A Astounding Reflection of a Lumb AFFAIR PREJUDICE Fanciful Documentary of a Frisbe AFRICAN EGG A Fast Paced Documentary of a Past AGENT TRUMAN Intrepid Panorama of a Robot An AIRPLANE SIERRA A Touching Saga of a Hunter And a AIRPORT POLLOCK A Epic Tale of a Moose And a Girl w ALABAMA DEVIL A Thoughtful Panorama of Datab ALADDIN CALENDAR Action Packed Tale of a Man And 1 2 3 4 5 6 7 8 9 ni w o Include Column Names as Headers L Limitto 1 Rows and Start with Row Create a PivotTable with the imported data C Add Summary Fields Advanced Options When the Create a PivotTable with the imported data option is checked an empty PivotTable or a PivotTable placeholder is inserted just to the right of the imported data The PivotTable name follows the same naming rules used for Excel tables created from tje imported da
44. sample screenshot 26 Creating PivotTables Figure 5 16 Disabled Create Excel relationships option before Excel 2013 Import Data from MySQL z Selected Tables and Views z Pick Related Tables 4 Tables and views selected below will be imported to individual Related tables not in the original selection can be selected Excel worksheets based on their relationships Selected Tables and Views 23 Selected related Tables 0 Table View Related to Table Related to actor film actor 77 actor info T address city customer staff store category film category country address city customer address store payment rental I customer list film language film actor film category inve a film_actor actor film film_category category film I film list T film text inventory film store rental language film Add Summary Fields Advanced Options Clicking Why is this option disabled displays an information dialog with an explanation of the disabled controls Figure 5 17 Disabled Create Excel relationships option description Excel Relationships cannot be created Excel Model Relationships are not supported in Excel versions lower than 2013 The PivotTable type drop down has been disabled to only allow the creation of PivotTables for each imported table or view Since Excel relationships cannot be created a single PivotTable for the whole Excel data model cannot be
45. ssions This option helps prevent unintentional data overwrite in that it checks for external edits before committing your changes For example between the time you loaded the data into Excel made changes in Excel and committed a different user could have edited the same cells elsewhere in MySQL using MySQL Workbench or some other means The optimistic updates feature checks for these changes and notifies you accordingly Optimistic updates can also be configured at runtime for all Edit Sessions or for a specific edit session by right clicking the Edit Session floating dialog and choosing the desired Use Optimistic Update option as demonstrated below Figure 3 2 Optimistic Updates Configuring at Runtime T MySQL for Excel Auto Commit Commit Changes Exit Edit Mode gt Use Optimistic Update gt For this session For all sessions This option is enabled by default Do not show SQL statements sent to the server When enabled SQL statements are now displayed and only their results are displayed in the information dialog Enabled by default Preview SQL statements before they are sent to the server When enabled it adds an extra step to the Create Schema Export Data Append Data and Edit Data operations before a statement is committed to the server It enables the Review SQL Script dialog as shown below for an Export Data operation Figure 3 3 The MySQL for Excel configuration Preview Option
46. t connection information to be deleted d Select entries that no longer will be used to be erased from storage definitively Type Reference Last Access C Users pholson Documents Bookl xlsx 7 Import world country 02 25 2015 22 16 45 C Users pholson Desktop MyBook1 xlsx 7 Import world city 02 25 2015 22 30 33 Wl current Workbook Bl Workbook not found 7 Delete automatically connection information where Workbook is no longer found Select connection information entries that have not been accessed in the last 30 gt days EM This lists the connected Excel worksheets that are known to MySQL for Excel From here you can view these connections and optionally delete them By default clicking Apply will delete connections to missing worksheets but this behavior is configurable Additionally clicking the Select connection Adding a New Schema information entries link checks selects for deletion books that you have not accessed for n days where n defaults to 30 Note N This option was added in MySQL for Excel 1 3 0 Note N The options to automatically delete missing connections or delete connections not accessed for n days were added in MySQL for Excel 1 3 4 For additional information about managing MySQL connections using MySQL for Excel see Section 3 2 Managing MySQL Connections Adding a New Schema Select aMySQL connection and then click Create New Schema from the MySQL for Excel t
47. ta but PivotTables can be created with or without enabling the Create an Excel table for the imported MySQL data advanced option That means a PivotTable can be created from an imported Excel range if the aforementioned advanced option is off or from an imported Excel table if the option is on Click Import to dump the film table s data to an active Excel Spreadsheet and this also creates a PivotTable for that data 22 Creating PivotTables Figure 5 11 PivotTable Example Empty PivotTable GES s Book HOME INSERT PAGE LAYOUT FORMULAS DATA Table Name Pal Summarize with PivotTable F ES L E n sakila film FA Remove Duplicates 3 Ta Insert Export Refresh T gt Resize Table 5 Convert to Range Slicer Properties Tools External Table Data Al M i f Y film id I J t rental lengi replacement rath features 099 86 20 99 PG Deleted Scenes Behind the Scenes 3 439 48 12996 Trailerz Deleted Scenes 4 299 50 1839 NC I7 Tralers Deleted Scenes 5 299 2639 G Commentaries Behind the Scenes 6 299 130 2299 G Deleted Scenes r 299 169 1739 PG Deleted Scenes 438 2 28 99 PG 3 Trailers Deleted Scenes 499 5 639 R Traers 299 m 2199 PG 13 Traiers Deleted Scenes 438 63 2439 NCA Trailers Deleted Scenes u 099 126 99 Commentaries Behind the Scenes n 039 196 2239 PG Commentaries Deleted Scenes 438 150 2199 PG Deleted Scenes Behind the Scenes e 039 9 2339 NC 17 Trailers Deleted Scenes Be
48. ta from MySQL Choose Columns to Import Click on column headers to exclude include them when importing the MySQL table data in Excel Table Name film actor Row Count 5462 Je I Options Include Column Names as Headers Limitto 1 5 Rows and Start with Row 1 E Create a PivotTable with the imported data 7 Add Summary Fields 5 4 Importing a View or Procedure Importing a view or procedure displays a similar dialogue but with the following options Include Column Names as Headers Enabled by default this will insert the column names at the top of the Excel spreadsheet as a headers row Import Because a procedure might return multiple result sets the import options include Selected Result Set Imports the selected tab sheet This is the default behavior All Result Sets Arranged Horizontally Imports all result sets into the Excel Worksheet horizontally and inserts one empty column between each result set All Result Sets Arranged Vertically Imports all result sets into the Excel Worksheet vertically and inserts one empty row between each result set For example a dialogue like the following is displayed after importing a procedure and pressing the Call button to invoke the stored procedure 18 Adding Summary Fields Figure 5 5 Importing called stored procedure data with MySQL for Excel 1
49. tomatic process finds zero columns to match then a simple 1 to 1 matching routine is attempted Meaning SourceColumn 1 to TargetColumn 1 SourceColumn 2 to TargetColumn 2 and so on Manual The source column names are manually dragged matched with the target column names Manual dragging can also be performed after the Automatic method is selected Stored Manual mapping styles may be saved using the Store Mapping button which will also prompt for a name and then save it using a name dbname tablename naming scheme The saved mapping style will then be available alongside the Automatic and Manual options Stored mappings may be deleted or renamed within the Advanced Options dialog Append Advanced Options There are several advanced options that are configured and stored between sessions for each Excel user The advanced options are 32 Append Advanced Options Figure 6 2 Appending Excel data to MySQL Advanced Options Append Data aaa sampletable A4 E16 Append Data to MySQL Table Advanced Append Data Options This will affect the number of rows in the preview window bs pp pu MySQL table columns Mapping Options Mapping Method Automatic C First Row Contains Column Names American Samoa 23157 Angola 546554 Use the first 100 2 Excel data rows to preview and calculate datatypes Sets the format ifthe A Use formatted values column has one Anguilla Argentina Armenia Australia Stored
50. unneling appear grayed out disabled in MySQL for Excel How can use a SSH connection Questions and Answers 9 1 When I m using Excel to edit data from a live MySQL database will my changes overwrite changes made by other users For example maybe they used MySQL Workbench to edit the same data at the same time The optimistic updates feature checks for external edits and notifies you of their existence before committing any changes If an external edit is discovered you can then choose whether or not to overwrite their changes This option is enabled by default and can be disabled to use pessimistic updates Disabling this option means external changes will always be overwritten In other words the choice is yours 9 2 l installed the MySQL for Excel plugin but can t find it in Microsoft Excel How do start it The MySQL for Excel plugin is automatically added to Microsoft Excel s data menu when it is installed Look for the MySQL for Excel icon by default it will be listed on the right side of the main menu If it s not there then you might have to reinstall the plugin But before doing so first check if it s listed under Add Remove Programs in Microsoft Windows If not then it has not been installed Next check the Excel Add Ins list For Office 2007 this is found by clicking the Office logo in Excel top left corner click Excel Options then select Add Ins Is MySQL for Excel listed as a COM Add in If so then consider
51. y indexes after data has been exported to speed up rows insertion This saves disk I O for bulk inserts thousands of rows since re indexing will not happen every time a row is inserted but only once at the end of the data insertion This option is enabled by default Note Y This option was added in MySQL for Excel 1 2 1 Note Y The following option was Removed in MySQL for Excel 1 2 1 Now the default behavior is to always remove empty columns from the calculations Remove columns that contain no data otherwise flag them as Excluded If enabled columns without data in Excel are removed and not shown in the preview panel If disabled default these columns will exist but have the Exclude Column option checked Additional Notes Entering 0 into a date column Entering the value 0 into an Excel date column will convert the value to 12 30 1899 in MySQL This is because Excel first translates the value to the minimum date in Excel which is 1 0 1900 because dates are internally stored in Excel as numbers the days that have passed since 1 0 1900 However because 1 0 1900 is not a valid date the committed value to MySQL will change to 12 30 1899 because the NET MySQL connector automatically converts 1 0 1900 to 12 30 1899 which is the closest valid date 37 38 Chapter 8 What Is New In MySQL for Excel Table of Contents 8 1 What Is New In MySQL for Excel 1 3 enne nne nhe nennen nena 39 8 2 What
Download Pdf Manuals
Related Search