MySQL for Excel 1.3.7 has been released
Dear MySQL users, The MySQL Windows Experience Team is proud to announce the release of MySQL for Excel version 1.3.7. This is a maintenance release for 1.3.x. It can be used for production environments. MySQL for Excel is an application plug-in enabling data analysts to very easily access and manipulate MySQL data within Microsoft Excel. It enables you to directly work with a MySQL database from within Microsoft Excel so you can easily do tasks such as: * Importing MySQL Data into Excel * Exporting Excel data directly into MySQL to a new or existing table * Editing MySQL data directly within Excel MySQL for Excel is installed using the MySQL Installer for Windows. The MySQL Installer comes in 2 versions - Full which includes a complete set of MySQL products with their binaries included in the download. - Web (network install) which will just pull the MySQL for Excel over the web and install it when run. You can download MySQL Installer from our official Downloads page at http://dev.mysql.com/downloads/installer/ The MySQL for Excel product can also be downloaded by using the product standalone installer found at this link http://dev.mysql.com/downloads/windows/excel/ Changes in MySQL for Excel 1.3.7 (2017-05-24) Functionality Added or Changed * The way MySQL for Excel shares data-editing sessions among users and between computers was improved. (Bug #25509085, Bug #73314) * The Append Excel Data to Table operation was updated with new advanced options to manage the behavior of rows containing unique key values that are duplicates of those in the database. (Bug #25479653, Bug #83801) * Added a new global option that specifies how to format spatial data as text: Well-Known Text, Keyhole Markup Language, Geography Markup Language, or GeoJSON. (Bug #22081263) * Enhanced the logic that migrates stored MySQL connections to the MySQL Workbench connections.xml file. In previous versions, the migration was offered and if not done at that moment, the dialog to migrate was shown every time MySQL for Excel was launched. There was no way to choose to continue storing MySQL connections in the MySQL for Excel configuration folder, instead of merging them with MySQL Workbench. Now, the Connections Migration dialog offers a way to postpone the migration by one hour, one day, one week, one month, or indefinitely. If the migration is postponed, the dialog is shown again after that time elapses. If the migration is postponed indefinitely, then an option is added to the Options dialog that permits the migration of connections to be done manually, as long as MySQL Workbench is installed. * Support for MySQL Fabric was removed. Bugs Fixed * SSL connections when created with MySQL Workbench should be inactive within MySQL for Excel, which does not support SSL connections. (Bug #25962564) * Selecting a schema containing at least one stored procedure for a MySQL 8.0 or 8.1 connection emitted an error. (Bug #25962347) * Empty string values within Excel column data that were used in an export or append-data operation caused the generated SQL queries to have no value, instead of an empty value corresponding to the data type of the target column (for example: 0 for Integer; false for Bool if the column does not allow NULL values, or NULL otherwise). (Bug #25509312, Bug #84851) * MySQL data could not be refreshed or edited directly in an Excel worksheet by different users or from different computers, which reduced the ability to share data-editing sessions among users or between computers. This fix alters the way connection information is stored by migrating the connection details for related import and edit-data operations from the user settings file to the XML parts of a workbook when the workbook is opened, and if the workbook supports XML parts and the connection information related to that workbook is found in the user settings file. (Bug #25509085, Bug #73314) * User-selected data types that replaced the detected values of a column were lost when the First Row Contains Column Names check box was selected or deselected in preparation for an export-data operation. This fix retains the selected value when the data type is set manually to override the automatically detected type and the check box is selected or deselected. It further adds a new action to reset the column back to automatic detection. (Bug #25492772, Bug #84802) * A portion of the preview area that should be displayed during import, export, and append data operations was concealed by other fields. (Bug
MySQL for Excel 1.3.6 has been released
Dear MySQL users, The MySQL Windows Experience Team is proud to announce the release of MySQL for Excel version 1.3.6. This is a maintenance release for 1.3.x. It can be used for production environments. MySQL for Excel is an application plug-in enabling data analysts to very easily access and manipulate MySQL data within Microsoft Excel. It enables you to directly work with a MySQL database from within Microsoft Excel so you can easily do tasks such as: * Importing MySQL Data into Excel * Exporting Excel data directly into MySQL to a new or existing table * Editing MySQL data directly within Excel MySQL for Excel is installed using the MySQL Installer for Windows. The MySQL Installer comes in 2 versions - Full (150 MB) which includes a complete set of MySQL products with their binaries included in the download. - Web (1.5 MB - a network install) which will just pull the MySQL for Excel over the web and install it when run. You can download MySQL Installer from our official Downloads page at http://dev.mysql.com/downloads/installer/ The MySQL for Excel product can also be downloaded by using the product standalone installer found at this link http://dev.mysql.com/downloads/windows/excel/ Changes in MySQL for Excel 1.3.6 (2016-01-07) * Functionality Added or Changed * Bugs Fixed Functionality Added or Changed * A new Generate an INSERT statement for each data row option was added to the Advanced Options dialog for Append and Export Data operations. This is disabled by default. A single INSERT statement is generated that inserts all affected rows, unless this option is enabled. When checked, an INSERT statement for each data row being appended or exported is generated in the resulting SQL query sent to the MySQL server. When unchecked, a single INSERT statement is generated as a bulk data operation, which performs better than multiple INSERT statements. While row-by-row inserts are slower, for debugging errors it could help determine which row causes an error, since the MySQL Server will process them row by row. * Added column properties to the Export Data dialog to support the definition of Auto Increment and Default Value for new MySQL tables where the data will be exported. Added a context menu to declare a numeric field to be Unsigned or to have Zero-Fill, accessible by right-clicking the Data Type combo box. Added the TIMESTAMP data type to the full list of valid data types, as it was not being shown before. Added help tool tips to fields in the Export Data dialog that explain in detail how the column properties work in a MySQL database. Bugs Fixed * The Pivot Table feature was updated to work with Excel 2016. (Bug #22387425) * Closing the plugin via the Close button in the panel would not toggle the MySQL for Excel checkbox in the Data ribbon. (Bug #22374026, Bug #79655) * Fixed the content detector for the active Excel cell after importing data and after loading database objects when a database is opened. Now, the data options are not enabled until a table is selected. (Bug #22372915) * Append and Export operations would not append/export boolean data. (Bug #22138966) * The MySQL for Excel Refresh All function would throw an unhandled exception. (Bug #22110627, Bug #78941) * With Office 2007, data would not auto-save after closing Excel. (Bug #22081313, Bug #78910) * Numeric data stored in text columns is now correctly formatted by Excel as text, and not as the "General" format that automatically formats cells as numbers when the data is stored in a text object. This fixes the issue where text columns were formatted as numbers, which stripped leading zeros. (Bug #20905284, Bug #76738) * Changed settings for containers and forms to let all UI components automatically scale on different DPI settings. Also removed the restriction to resize the MySQL for Excel's pane, so in case a different DPI setting is used and the fonts look too big, the pane can be resized to see contents as best as possible. (Bug #18099454, Bug #71389) Quick links: MySQL for Excel documentation: http://dev.mysql.com/doc/en/mysql-for-excel.html. Inside MySQL blog (NEW blog home): http://insidemysql.com/ MySQL on Windows blog (OLD blog home): http://blogs.oracle.com/MySQLOnWindows. MySQL for Excel forum: http://forums.mysql.com/list.php?172. MySQL YouTube channel: http://www.youtube.com/user/MySQLChannel. Enjoy and thanks for the support! The MySQL on Windows team at Oracle. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
MySQL for Excel 1.3.5 has been released
Dear MySQL users, The MySQL Windows Experience Team is proud to announce the release of MySQL for Excel version 1.3.5. This is a maintenance release for 1.3.x. It can be used for production environments. MySQL for Excel is an application plug-in enabling data analysts to very easily access and manipulate MySQL data within Microsoft Excel. It enables you to directly work with a MySQL database from within Microsoft Excel so you can easily do tasks such as: * Importing MySQL Data into Excel * Exporting Excel data directly into MySQL to a new or existing table * Editing MySQL data directly within Excel MySQL for Excel is installed using the MySQL Installer for Windows. The MySQL Installer comes in 2 versions - Full (150 MB) which includes a complete set of MySQL products with their binaries included in the download. - Web (1.5 MB - a network install) which will just pull the MySQL for Excel over the web and install it when run. You can download MySQL Installer from our official Downloads page at http://dev.mysql.com/downloads/installer/ The MySQL for Excel product can also be downloaded by using the product standalone installer found at this link http://dev.mysql.com/downloads/windows/excel/ Changes in MySQL for Excel 1.3.5 (2015-10-23) Functionality Added or Changed * Added support for the JSON data type that was added in MySQL Server 5.7. The JSON data type is now selectable from the Export Data column dialog, if the advanced option to show all data types is enabled. All data operations (Import, Export, Append and Edit) now function with the JSON data type. * Added a new advanced option to Show column data types above column names. When checked, the Append Data dialog grid will show data type information for columns on source and target grids. This is especially helpful when manually mapping columns. * A new Append Data advanced option was added, titled Confirm column mapping overwriting. When enabled, dragging and dropping source columns over target columns that were already mapped (in other words, they already contained a mapped source column) now triggers a confirmation dialog asking if the overwrite should be executed. This option is disabled by default as data overwrite is a common operation. (Bug #16239058) Bugs Fixed * Attempting to save data to a column where the data can not be written (incompatible data type) now generates an error. (Bug #21898699, Bug #78550) * Changed the way data type related warnings are linked to grid columns in the Append Data dialog. Now the warnings are linked to the target (lower) grid view columns, because a single source column can be mapped to multiple target columns, and the warnings are about source data not being suitable for the target column's data type. (Bug #21898678, Bug #78549) * When scrolling the target MySQL table grid horizontally, the upper column headers that contained names of the mapped source columns were not rendering text properly. The text moved while scrolling, and the column separators disappeared when scrolling to the right. (Bug #21898210, Bug #78548) * Change the data type detection logic for a column's data that was failing when only the first row of data contained data, when the rest of the rows were empty. (Bug #21799680, Bug #78358) * Connection information created after the last save was cleared from memory when an Excel Workbook was closed, thus causing the connection information to not write to disk. Now the connection cleanup happens after a Workbench is saved, thereby saving the new connection information. (Bug #21172751, Bug #21089790, Bug #76475, Bug #73467) * Stopped using hidden defined names to store formulas that transform date values into a date format used by MySQL. These defined names were being detected by Excel as macros or user functions that could not be saved in macro-free workbooks, thus generating bogus warnings every time a Workbook was saved when the Excel plugin was active. (Bug #19358961, Bug #73467) * Updated the data connection library to Connector/NET v6.9.8, which fixes the problem of accessing a schema that contained Unicode (non-ASCII) characters in its name. These databases now successfully open. (Bug #17409832) * Deleted stored mappings would remain in the stored mapping method list. (Bug #16493255) * Enhanced the custom grid view control used in the Append Data dialog to automatically adjust the width of the grid columns. This adjustment considers the grid's cell contents, the base column header text, and also the contents of the additional
MySQL for Excel 1.3.4 has been released
Dear MySQL users, The MySQL Windows Experience Team is proud to announce the release of MySQL for Excel version 1.3.4. This is a maintenance release for 1.3.x. It can be used for production environments. MySQL for Excel is an application plug-in enabling data analysts to very easily access and manipulate MySQL data within Microsoft Excel. It enables you to directly work with a MySQL database from within Microsoft Excel so you can easily do tasks such as: * Importing MySQL Data into Excel * Exporting Excel data directly into MySQL to a new or existing table * Editing MySQL data directly within Excel MySQL for Excel is installed using the MySQL Installer for Windows. The MySQL Installer comes in 2 versions - Full (150 MB) which includes a complete set of MySQL products with their binaries included in the download. - Web (1.5 MB - a network install) which will just pull the MySQL for Excel over the web and install it when run. You can download MySQL Installer from our official Downloads page at http://dev.mysql.com/downloads/installer/ The MySQL for Excel product can also be downloaded by using the product standalone installer found at this link http://dev.mysql.com/downloads/windows/excel/ Changes in MySQL for Excel 1.3.4 (2015-03-06) Functionality Added or Changed * Two new options were added to the Manage Import/Edit connection information maintenance dialog. The first is used to automatically remove stored connection information related to Excel Workbooks that are no longer found where they were originally saved. The second new option is used to select all connection information entries that have not been used in the last n days, where n is configurable. (Bug #19789907, Bug #74304) Bugs Fixed * For Excel 2007, a pop-up warning message incorrectly stated that some features could not be saved in a macro-free workbook would appear when closing a Workbook that was not saved. (Bug #20540331, Bug #75900) References: See also Bug #19358961. * The Refresh All action did not function on pivot tables. Each pivot table had to be individually updated. (Bug #20524883, Bug #75565) * When a named table in Excel was filtered using the standard Excel filtering option, this filter was not taken into account when the append function of the MySQL for Excel plugin was called. All rows of the table were inserted, not just the remaining (filtered) ones. (Bug #20433602, Bug #75635) * While the Export Data operation automatically assembles the SET declaration when detecting values suitable for a SET data type (such as lists of values separated by commas), the SET declaration assembled by MySQL for Excel was incorrect and did not correctly merge all of the values nor did it remove the duplicates when generating a single list of suitable values. (Bug #20426374, Bug #75660) * An error similar to Exception from HRESULT: 0x800A03EC was generated when importing data with dates older than January 1, 1900. Dates older than this are now converted to text, as to prevent the COM exception. (Bug #20340588, Bug #75450) * For Excel 2007, only .xls files were available in the Save As dialog. Now, all formats supported by Excel 2007 are available. (Bug #20296618, Bug #75389) * Executing Export Data to a New Table with the Preview SQL statements before they are sent to the server option enabled would report an incorrect (off by -1) number of affected rows under the Review SQL Script dialog. (Bug #20286897, Bug #75342) * During Data Export, if the number of rows to be exported was higher than the number of previewed rows, the export operation was restricted to the number of previewed rows. A workaround was to increase the number of previewed rows to the number of rows being exported. (Bug #20077608, Bug #74942) * Connection information entries could not be deleted. (Bug #19789907, Bug #74304) * During an Edit Data session, and after data was added to a new row (and the row turned blue to indicate uncommitted changes), copying a value from a cell that lacked a status color (not modified) and pasting it into the new blue row did not retain the blue color. (Bug #19783737, Bug #74285) * The Import Data, Append Data and Edit Data action labels were not disabled after a DB Object was deselected from the list. (Bug #18323840) * Excel cells selected before MySQL for Excel was launched were not recognized, which caused the Export Data action label to remain disabled unless cells were selected again after MySQL for Excel was opened. (Bug #18323840) * When performing an Export Data operation
MySQL for Excel 1.3.3 has been released
Dear MySQL users,
The MySQL Windows Experience Team is proud to announce the release of
MySQL for Excel version 1.3.3. This is a maintenance release for 1.3.x.
It can be used for production environments.
MySQL for Excel is an application plug-in enabling data analysts to very
easily access and manipulate MySQL data within Microsoft Excel. It
enables you to directly work with a MySQL database from within Microsoft
Excel so you can easily do tasks such as:
* Importing MySQL Data into Excel
* Exporting Excel data directly into MySQL to a new or existing table
* Editing MySQL data directly within Excel
MySQL for Excel is installed using the MySQL Installer for Windows.
The MySQL Installer comes in 2 versions
- Full (150 MB) which includes a complete set of MySQL products with
their binaries included in the download.
- Web (1.5 MB - a network install) which will just pull the MySQL for
Excel over the web and install it when run.
You can download MySQL Installer from our official Downloads page at
http://dev.mysql.com/downloads/installer/
The MySQL for Excel product can also be downloaded by using the product
standalone installer found at this link
http://dev.mysql.com/downloads/windows/excel/
Changes in MySQL for Excel 1.3.3 (2014-10-27)
This section documents all changes and bug fixes applied to MySQL
for Excel since the release of 1.3.2. Several new features were
added the 1.3.x branch, for more information see What Is New In
MySQL for Excel 1.3
(http://dev.mysql.com/doc/mysql-for-excel/en/mysql-for-excel-what-is-new-1-3.html).
Known limitation:
Upgrading from versions MySQL for Excel 1.3.2 and lower is not
possible due to a bug fixed in MySQL for Excel 1.3.3. In that
scenario, the old version (MySQL for Excel 1.3.2 or lower) must be
uninstalled first. Upgrading from version 1.3.3 works correctly.
Bugs Fixed
* The MySQL for Excel MSI was not replacing the registry keys on
an upgrade, in that registry keys from previous versions
remained and were not upgraded. (Bug #19783949, Bug #74286)
* Using semicolons (';') in values like schema names, column
names or text values caused SQL queries sent to the MySQL
server to fail. Internally, MySQL for Excel treated the
semicolon as a separator for SQL statements. (Bug #19680607,
Bug #74057)
* Committing 20+ cell changes and new rows at the same time
would fail to commit. (Bug #19639669, Bug #73911)
* The Preview SQL statements before they are sent to the
server option is now overridden when the Auto-Commit
checkbox in the Edit Data dialog is checked. Before, each edit
(that was auto committed) would first show the preview dialog.
(Bug #19607260, Bug #73900)
* The Export Data dialog's preview grid would display time data
as a standard decimal numbers.
Also, exported data with time values were not properly wrapped
in single quotes. (Bug #19607195, Bug #73899)
* Importing a table with a row count that exceeded the number of
rows below the cursor would generate a fatal High severity
error and fail to import the data. Now, the import succeeds
but the Import will be truncated since it exceeds the
available worksheet space. (Bug #19588933, Bug #73866)
* When selecting a non-primary column that only contains integer
values, the Create Index option is automatically checked,
and the Allow Empty option is unchecked. This is the
expected behavior with the advanced Automatically check the
Allow Empty checkbox for columns without an index option
enabled. (Bug #19503820, Bug #73719)
* For consistency, all references to Varchar were changed to
VarChar. (Bug #19501346, Bug #73712)
* The Allow Empty checkbox only had an effect the first time the
SQL query was previewed. (Bug #19467535, Bug #73646)
* Changed the way NULL and zero dates are handled. Previously,
MySQL zero dates (-00-00 00:00:00) were imported into
Excel as the minimum valid date allowed by .NET
(DateTime.MinValue), which was then converted into a text
representation where the cell's value was no longer recognized
as a date. Now, zero dates are always treated as NULL. (Bug
#19423952, Bug #73541)
* Exporting data that used a comma as the decimal separator
would fail to export. The commas are now converted to periods,
as already done when appending data. (Bug #19403063, Bug
#73293)
* When the number of mapped columns was less than the number of
columns in the target table, and when the last MySQL column
contained NULL values, append operations would insert NULL
values for all fields in the aforementioned last column
instead of inserting the values defined in Excel. (Bug
MySQL for Excel 1.3.2 GA has been released
Dear MySQL users, The MySQL Windows Experience Team is proud to announce the release of MySQL for Excel version 1.3.2. This is a GA release for 1.3.x. It can be used for production environments. MySQL for Excel is an application plug-in enabling data analysts to very easily access and manipulate MySQL data within Microsoft Excel. It enables you to directly work with a MySQL database from within Microsoft Excel so you can easily do tasks such as: * Importing MySQL Data into Excel * Exporting Excel data directly into MySQL to a new or existing table * Editing MySQL data directly within Excel As this is a GA version the MySQL for Excel product can be downloaded at this link http://dev.mysql.com/downloads/windows/excel/ Changes in MySQL for Excel 1.3.2 (2014-09-22, General Availability) This section documents all changes and bug fixes applied to MySQL for Excel since the release of 1.3.1. Several new features were added the 1.3.x branch, for more information see What Is New In MySQL for Excel 1.3 (http://dev.mysql.com/doc/mysql-for-excel/en/mysql-for-excel-what- is-new-1-3.html). Known limitation: Upgrading from versions MySQL for Excel 1.2.0 and lower is not possible due to a bug fixed in MySQL for Excel 1.2.1. In that scenario, the old version (MySQL for Excel 1.2.0 or lower) must be uninstalled first. Upgrading from version 1.2.1 works correctly. Functionality Added or Changed * Excel tables created from stored procedures can now be refreshed. (Bug #19129583, Bug #73151) * Several fields next to checkboxes were updated to automatically be focused and selected when the corresponding checkbox or radio button is checked. * A new global option called Preview MySQL table data before an Edit Data session is opened was added. When checked (default), the data of a selected MySQL table to be edited is shown in a preview dialog before the Edit Data session is opened. If the option is unchecked, no preview takes place and the Edit Data session is simply opened. A new context menu option called Preview Data was also added, and it is available when right-clicking a MySQL table or view that is listed in the DB Objects selection panel. The option opens the Preview Data dialog and shows the first 10 (by default) rows of the selected table or view. In previous versions, the Import Data dialog was always displayed when an Edit Data was started, the Preview Data dialog replaces it to avoid confusion about the type of operation being performed. * MySQL for Excel now handles the managed Fabric connection type that was introduced in MySQL Workbench 6.2. New icons were introduced for SSH and managed Fabric connections. Connections that are not of the type native, native_socket, or native_sshtun, will not be listed in the connections listing under the Welcome Panel. * A single PivotTable can now be created for the entire Excel data model when importing multiple MySQL tables, which allows analyzing the related Excel tables within a single PivotTable. The Excel data model contains all Excel tables at the time the single PivotTable is created, so if previous imports were already performed, the data model may show more tables than the imported ones in the last multiple import operation. The option to create a PivotTable for each imported table is still present, but single PivotTable creation is selected by default. * The generated summary row added by the Add Summary Fields option when importing MySQL data is now bold and includes a thick border, as to differentiate from actual data. * When importing multiple database objects, the ability to add related tables to the directly related tables (i.e. indirectly related tables) was added by right-clicking a table in the related tables list (at the right side of the dialog) and choosing Add Related Tables from the context menu. * The Schema Selection panel now includes collation information for the listed schemas that can be shown by right-clicking the Schemas list and choosing Display Schema Collations from the context menu. * Changed the way stored procedure result sets are laid out in a Excel worksheet when all of them are imported horizontally or vertically, and when PivotTables are created for each result set. Instead of skipping one row or column, the necessary rows and columns are skipped so the created PivotTables for the result sets do not collide. Bugs Fixed * Duplicate edit session connection information would be saved to the settings.config file, which generated a Editing not possible error message after using the Work Offline option. Closing an unsaved workbook now opens an Excel Save As dialog. (Bug #19347634) * The Append Excel Data to Table operation failed when the number of columns did not match, even when the data
MySQL for Excel 1.3.1 RC has been released
Dear MySQL users, The MySQL Windows Experience Team is proud to announce the release of MySQL for Excel version 1.3.1. This is a RC release for 1.3.x. MySQL for Excel is an application plug-in enabling data analysts to very easily access and manipulate MySQL data within Microsoft Excel. It enables you to directly work with a MySQL database from within Microsoft Excel so you can easily do tasks such as: * Importing MySQL Data into Excel * Exporting Excel data directly into MySQL to a new or existing table * Editing MySQL data directly within Excel As this is a RC version the MySQL for Excel product can be downloaded only by using the product standalone installer at this link http://dev.mysql.com/downloads/windows/excel/ Changes in MySQL for Excel 1.3.1 (Not yet released, RC) This section documents all changes and bug fixes applied to MySQL for Excel since the release of 1.3.0. Several new features were added the 1.3.x branch, for more information see What Is New In MySQL for Excel (http://dev.mysql.com/doc/refman/5.6/en/mysql-for-excel-what-is-new.html). Known limitations: * Upgrading from versions MySQL for Excel 1.2.0 and lower is not possible due to a bug fixed in MySQL for Excel 1.2.1. In that scenario, the old version (MySQL for Excel 1.2.0 or lower) must be uninstalled first. Upgrading from version 1.2.1 works correctly. * PivotTables are normally placed to the right (skipping one column) of the imported data, they will not be created if there is another existing Excel object at that position. Functionality Added or Changed * When reopening a saved Excel workbook that contains imported MySQL data, MySQL for Excel attempts to reconnect the Excel tables with the corresponding MySQL databases where the imported MySQL data was imported from. If the MySQL connections are not found, then a new dialog prompts you to either ignore, create, or delete the missing connections. Ignore allows you to work offline, and the dialog will open the next time the Excel workbook is opened. (Bug #18961860, Bug #72966) * Control + A now selects all tables and views in the Database Objects selection panel. (Bug #18961544, Bug #72963) Bugs Fixed * Data could not be imported from a remote MySQL server. (Bug #19012634, Bug #73040) * The Edit Connection dialog was not populated with information from the selected MySQL connection. (Bug #18968777, Bug #72990) * In Excel 2013, importing the same table twice in the same Excel worksheet failed after deleting the Excel table linked to in the first report. (Bug #18962670, Bug #72976) * Data could not be imported to the left of previously imported data if the new data intersected with the existing import. (Bug #18962617, Bug #72975) * After importing data, clicking Refresh All from the Data tab would fail to refresh the data, and it generated an Initialization of the data source failed error. (Bug #18962538, Bug #72974) * When exporting data, a duplicate entry was sent to the MySQL server when data was exported to a new table with the First Row Contains Column Names checkbox deselected. The generated error was similar to Error while inserting rows... MySQL Error 1062: Duplicate entry '1' for key 'PRIMARY'. (Bug #18962470, Bug #72973) * With the Create a Pivot Table with the imported data option enabled, Data Import failed to create a PivotTable for tables that contained a large data set. (Bug #18962371, Bug #72972) * When importing tables, the Pick related Tables or Views feature did not function in Excel 2007 or Excel 2010, but did in Excel 2013. (Bug #18962354, Bug #72971) * For new columns, the Export Data dialog would not accept manually typed in data types that contained a parenthesis, such as ENUM. (Bug #18962214, Bug #72970) * Line and LinearRing were removed from the list of valid data types, so they no longer appear in the Data Export data type form. (Bug #18962184, Bug #72969) * Hovering over elements in the MySQL connecting and object listing could cause the text of each item to be bold. (Bug #18962130, Bug #72968) * Imported data would not refresh if the table name was changed in Excel during the import. The table name is no longer used to verify the MySQL connection. (Bug #18961806, Bug #72964) * Edit Sessions would fail to restore with sessions that contained tables with zero DATETIME values. Edit Sessions would also fail to restore when MySQL for Excel was started on a currently opened Excel workbook. (Bug #18889495, Bug #72842) * Attempting to import a table with ENUM values would fail with an Input string was not in a correct format. error. (Bug #18728748, Bug #72434) References: See also Bug #18962214, Bug #72970. Quick links: You can access the MySQL for Excel documentation at http://dev.mysql.com/doc/en
MySQL for Excel 1.3.0 Beta has been released
Dear MySQL users, The MySQL Windows Experience Team is proud to announce the release of MySQL for Excel version 1.3.0. This is a beta release for 1.3.x. MySQL for Excel is an application plug-in enabling data analysts to very easily access and manipulate MySQL data within Microsoft Excel. It enables you to directly work with a MySQL database from within Microsoft Excel so you can easily do tasks such as: * Importing MySQL Data into Excel * Exporting Excel data directly into MySQL to a new or existing table * Editing MySQL data directly within Excel As this is a beta version the MySQL for Excel product can be downloaded only by using the product standalone installer at this link http://dev.mysql.com/downloads/windows/excel/ Changes in MySQL for Excel 1.3.0 (2014-06-06, Beta) This section documents all changes and bug fixes applied to MySQL for Excel since the release of 1.2.1. Several new features were added, for more information see What Is New In MySQL for Excel (http://dev.mysql.com/doc/refman/5.6/en/mysql-for-excel-what-is-new.html). Known limitations: Upgrading from versions MySQL for Excel 1.2.0 and lower is not possible due to a bug fixed in MySQL for Excel 1.2.1. In that scenario, the old version (MySQL for Excel 1.2.0 or lower) must be uninstalled first. Upgrading from version 1.2.1 works correctly. Control + A cannot be used to select all database objects. Either shift + Arrow or Control + click must be used instead. Excel tables created from external data sources may not refresh if the MySQL for Excel add-in is loaded. PivotTables are normally placed to the right (skipping one column) of the imported data, they will not be created if there is another existing Excel object at that position. Functionality Added or Changed * Imported data can now be refreshed by using the native Refresh feature. Fields in the imported data sheet are then updated against the live MySQL database using the saved connection ID. * Functionality was added to import data directly into PivotTables, which can be created from any Import operation. * Multiple objects (tables and views) can now be imported into Excel, when before only one object could be selected. Relational information is also utilized when importing multiple objects. * All options now have descriptive tooltips. Hovering over an option/preference displays helpful information about its use. * A new Export Data, Advanced Options option was added that shows all available data types in the Data Type combo box, instead of only showing a subset of the most popular data types. * The option dialogs now include a Refresh To Defaults button that resets the dialog's options to their defaults values. Each option dialog is set individually. * A new Add Summary Fields for Numeric Columns option was added to the Import Data dialog that automatically adds summary fields for numeric data after the last row of the imported data. The specific summary function is selectable from many options, such as Total and Average. * A new collation option was added for the schema and table creation wizards. The default schema collation is Server Default, and the default table collation is Schema Default. Collation options may be selected from a drop-down list of all available collations. Quick links: You can access the MySQL for Excel documentation at http://dev.mysql.com/doc/en/mysql-for-excel.html. You can find our team's blog at http://blogs.oracle.com/MySQLOnWindows. You can also post questions on our MySQL for Excel forum found at http://forums.mysql.com/list.php?172. You can follow our videos on our YouTube channel found at http://www.youtube.com/user/MySQLChannel. Enjoy and thanks for the support! The MySQL on Windows team at Oracle. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
MySQL for Excel 1.2.1 has been released
The MySQL Windows Experience Team is proud to announce the release of MySQL for Excel version 1.2.1. This is a maintenance release for 1.2.x. MySQL for Excel is an application plug-in enabling data analysts to very easily access and manipulate MySQL data within Microsoft Excel. It enables you to directly work with a MySQL database from within Microsoft Excel so you can easily do tasks such as: · Importing MySQL Data into Excel · Exporting Excel data directly into MySQL to a new or existing table · Editing MySQL data directly within Excel MySQL for Excel is installed using the MySQL Installer for Windows. The MySQL installer comes in 2 versions · Full (150 MB) which includes a complete set of MySQL products with their binaries included in the download · Web (1.5 MB - a network install) which will just pull the MySQL for Excel over the web and install it when run. You can download MySQL for Excel 1.2.1 from our official Downloads page at http://dev.mysql.com/downloads/windows/excel as a standalone MSI, and it will be available shortly in the MySQL Installer for Windows that can be downloaded from http://dev.mysql.com/downloads/installer/. MySQL for Excel 1.2.1 introduces the following features: · When MySQL Workbench is not installed, MySQL connections are now automatically created for discovered MySQL services. If MySQL Workbench is later installed, these connections are not migrated over to MySQL Workbench as MySQL Workbench also creates connections for discovered MySQL services. o Bug #16238788 - CONNECTIONS FOR FOUND MYSQL SERVICES SHOULD BE AUTOMATICALLY CREATED Known limitation / requirement: · You must uninstall any current installation of MySQL for Excel before performing this upgrade, due to a bug that prevented upgrades from previous versions. This applies to both the standalone MSI and MySQL Installer installation methods, and the fix allows for simpler upgrades in the future. Also this release contains the following bug fixes: · With Microsoft Excel 2013, closing an Excel window containing an open MySQL for Excel plugin did not properly dispose of the link to the plugin, which caused a delay when opening the plugin inside additional Excel windows. o Bug #18392674 - SEVERAL CLICKS NEEDED ON THE MYSQL FOR EXCEL RIBBON BUTTON TO START/CLOSE ADD-IN · Attempts to upgrade MySQL for Excel would sometimes fail. The Installer would attempt to add Registry keys that were already present from a previous installation. A workaround was to uninstall and then reinstall the plugin o Bug #18354533 - MYSQL FOR EXCELL ADDIN 1.2.0 (STAND ALONE) IS NOT ABLE TO UPDATE AN OLD VERSION · Executing Export Data while the first data column was not numeric would cause an AutoPK column (Automatically create a Primary Key) to be created as the first column in the table. This column was also created if the user manually selected the Add a Primary Key column radio button. The generated CREATE TABLE statement did not contain the AutoPK column. o Bug #18269654 - EXPORT DATA - ERROR ON CHANGING PRIMARY KEY OPTIONS · The Export Data operation failed when checking the Exclude Column checkbox for any column. The generated CREATE TABLE query did not show the excluded columns, but the generated INSERT statements did (incorrectly) include them. o Bug #18269602 - EXPORT DATA - EXCLUDING COLUMNS CAUSE THE EXPORT DATA TO FAIL · The Remove columns that contain no data, otherwise just flag them as Excluded option was removed from the Export Data Advanced Options preference panel. Now, the default behavior is to always remove empty columns from the calculations. o Bug #18113057 - EXPORT DATA - UNHANDLED EXCEPTION WHEN SELECTING THE WHOLE SPREADSHEEET'S DATA · A MySQL database with unique keys would sometimes fail to import, when executing Import MySQL Data. o MySQL Bug #71004, Bug #17891357 - ONLY ONE TABLE FROM A DATABASE WON'T IMPORT · The creation of named tables did not function in all cases during an import, and could generate an exception. o MySQL Bug #70925, Bug #17806468 - IMPORTING TABLES WITH GUID TO EXCEL - EXCEPTION · After creating a new stored mapping in the Append Data dialog, the default selection for the Stored Mapping drop-down list was a blank value. The new default is the newly created stored mapping. o Bug #17665496 - APPEND DATA - NEW STORED MAPPING NOT APPLIED AFTER CREATION · Exporting large table (250,000+ rows) would not function, as MySQL for Excel would disconnect from the MySQL Server before completion. The MySQL for Excel plugin was optimized to help with these use cases, and these changes include: o Optimized the way SQL statements are sent to the server, compared to statements sent in version 1.2.0 a 30-35% time improvement was achieved. o Optimized the way SQL
career advice - Excel Expert and MySQL SQL specialist
Hi I passed Oracle SQL Expert and working on MySQL 5.6 developer exam. I work 5% of my job with SQL but I am searching for a full time SQL job. http://shop.oreilly.com/product/0790145363466.do Is the Excel exam (Microsoft Office Specialist - MOS) useful for getting a junior SQL pro job? What do you think? Lukas
Using Excel to query the database
Hi, I have a fairly standard set of queries that I do then I save the resultset as a CSV and send it off to a couple dozen folks. My understanding is that I can build an Excel .iqy file that can do the queries (one for each query) and that I need send that file but once to the users. Does anyone have any sample Excel .iqy files for MySQL access that they would be willing to share (with passwords removed, of course)? Thanks. Rob Tanner UNIX Services Manager Linfield College, McMinnville Oregon ITS will never ask you for your password. Please don’t share yours with anyone! [cid:74641041-0AC7-459B-AA5C-2BDE34206F14]
Re: from excel to the mySQL
Thanks for your information, we are sorry about this. - Original Message - From: Martin Gainty To: pagong...@gmail.com ; haidarpes...@gmail.com Cc: mysql@lists.mysql.com Sent: Wednesday, August 04, 2010 6:20 AM Subject: RE: from excel to the mySQL Vaz lecture start please do not insert bogus display names into the email display name (such as Vaz when your real name is haidarpesebe) most responsible mail-admins wont tolerate this type of spoofing or any type of email spoofing and if your name is VAZ use the email address created for VAZ instead of haidarpesebe lecture concluded Martin __ Verzicht und Vertraulichkeitanmerkung Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Date: Tue, 3 Aug 2010 19:40:58 +0100 Subject: Re: from excel to the mySQL From: pagong...@gmail.com To: haidarpes...@gmail.com CC: mysql@lists.mysql.com Hi, Ive always used navicat for mysql and it does wonders for import/export (wether it be from excel or any other file type) and also general database management. Actually, too many features to list here, check the website: http://www.navicat.com/en/products/navicat_mysql/mysql_overview.html Pag On Tue, Aug 3, 2010 at 10:23 AM, HaidarPesebe haidarpes...@gmail.comwrote: Dear All, I'm needs a way to upload data from excel to the mySQL database. Dear all, I need help is how to upload data from excel columns and load into mysql database using php? Thanks HaidarPesebe __ Information from ESET Smart Security, version of virus signature database 5338 (20100803) __ The message was checked by ESET Smart Security. http://www.eset.com
Re: from excel to the mySQL
Hi! Data Wizard for MySQL allows you to import data from Excel (as well as from CSV, DBF, XML and text files) in a few mouse clicks. The app also provides a flexible task scheduler. http://www.sqlmaestro.com/products/mysql/datawizard/ - Original Message - From: HaidarPesebe haidarpes...@gmail.com To: MySQL Lists mysql@lists.mysql.com Sent: Tuesday, August 03, 2010 3:23 PM Subject: from excel to the mySQL Dear All, I'm needs a way to upload data from excel to the mySQL database. Dear all, I need help is how to upload data from excel columns and load into mysql database using php? Thanks HaidarPesebe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: from excel to the mySQL
Thanks for your information, we have tried it and solve my problems. Thanks. - Original Message - From: Miguel Vaz To: HaidarPesebe Cc: MySQL Lists Sent: Wednesday, August 04, 2010 1:40 AM Subject: Re: from excel to the mySQL Hi, Ive always used navicat for mysql and it does wonders for import/export (wether it be from excel or any other file type) and also general database management. Actually, too many features to list here, check the website: http://www.navicat.com/en/products/navicat_mysql/mysql_overview.html Pag On Tue, Aug 3, 2010 at 10:23 AM, HaidarPesebe haidarpes...@gmail.com wrote: Dear All, I'm needs a way to upload data from excel to the mySQL database. Dear all, I need help is how to upload data from excel columns and load into mysql database using php? Thanks HaidarPesebe
from excel to the mySQL
Dear All, I'm needs a way to upload data from excel to the mySQL database. Dear all, I need help is how to upload data from excel columns and load into mysql database using php? Thanks HaidarPesebe
Re: from excel to the mySQL
I'm needs a way to upload data from excel to the mySQL database. Dear all, I need help is how to upload data from excel columns and load into mysql database using php? http://www.sqldbu.com/eng/sections/tips/mysqlimport.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: from excel to the mySQL
visit this site : http://www.daniweb.com/forums/thread119197.html --- On Tue, 8/3/10, Marc Guay marc.g...@gmail.com wrote: From: Marc Guay marc.g...@gmail.com Subject: Re: from excel to the mySQL To: HaidarPesebe haidarpes...@gmail.com Cc: MySQL Lists mysql@lists.mysql.com Date: Tuesday, August 3, 2010, 7:34 AM I'm needs a way to upload data from excel to the mySQL database. Dear all, I need help is how to upload data from excel columns and load into mysql database using php? http://www.sqldbu.com/eng/sections/tips/mysqlimport.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=samc...@yahoo.com
RE: from excel to the mySQL
hi HaidarPesebe, one way to do this is : 1. save the excel to xxx.csv 2. then use the mysql SQL statement LOAD DATA INFILE for example : LOAD DATA INFILE 'c:\\test\\xxx.csv' INTO TABLE tbl_name FIELDS TERMINATED BY ',' best regards liuyann From: haidarpes...@gmail.com To: mysql@lists.mysql.com Subject: from excel to the mySQL Date: Tue, 3 Aug 2010 16:23:22 +0700 Dear All, I'm needs a way to upload data from excel to the mySQL database. Dear all, I need help is how to upload data from excel columns and load into mysql database using php? Thanks HaidarPesebe
Re: from excel to the mySQL
Hi, Ive always used navicat for mysql and it does wonders for import/export (wether it be from excel or any other file type) and also general database management. Actually, too many features to list here, check the website: http://www.navicat.com/en/products/navicat_mysql/mysql_overview.html Pag On Tue, Aug 3, 2010 at 10:23 AM, HaidarPesebe haidarpes...@gmail.comwrote: Dear All, I'm needs a way to upload data from excel to the mySQL database. Dear all, I need help is how to upload data from excel columns and load into mysql database using php? Thanks HaidarPesebe
RE: from excel to the mySQL
Vaz lecture start please do not insert bogus display names into the email display name (such as Vaz when your real name is haidarpesebe) most responsible mail-admins wont tolerate this type of spoofing or any type of email spoofing and if your name is VAZ use the email address created for VAZ instead of haidarpesebe lecture concluded Martin __ Verzicht und Vertraulichkeitanmerkung Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Date: Tue, 3 Aug 2010 19:40:58 +0100 Subject: Re: from excel to the mySQL From: pagong...@gmail.com To: haidarpes...@gmail.com CC: mysql@lists.mysql.com Hi, Ive always used navicat for mysql and it does wonders for import/export (wether it be from excel or any other file type) and also general database management. Actually, too many features to list here, check the website: http://www.navicat.com/en/products/navicat_mysql/mysql_overview.html Pag On Tue, Aug 3, 2010 at 10:23 AM, HaidarPesebe haidarpes...@gmail.comwrote: Dear All, I'm needs a way to upload data from excel to the mySQL database. Dear all, I need help is how to upload data from excel columns and load into mysql database using php? Thanks HaidarPesebe
Re: Mysql - Tables Export to Excel!
Vikram A wrote: Hi, I would like to export my table structure from MYSQL from a particular db. Is there any tool for doing this? There are several ways to get structure information from within MySQL: the SHOW COLUMNS... command the SHOW INDEXES... command the SHOW CREATE TABLE... command you can also write queries against the tables in the INFORMATION_SCHEMA You can also use an external utility such as mysqldump. Use the --no-data option to get just a dump of your table definitions. If you also want to see triggers and events and stored procedures, you also need to use the --triggers, --events, and --procedure options, too. Details are in the fine manual: http://dev.mysql.com/doc/refman/5.1/en/show.html http://dev.mysql.com/doc/refman/5.1/en/information-schema.html http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html I guess the final answer depends on which information are you looking for and in what format you want to see it. Got any details you want to share? -- Shawn Green MySQL Principle Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Mysql - Tables Export to Excel!
use can use mysqldump with option *-no-data* eg. *mysqldump -u user -ppassword wordpress user --no-data Dumpdata.txt * where wordpress is my database and user is my table. Thanks On Sun, Apr 18, 2010 at 9:48 PM, Shawn Green shawn.l.gr...@oracle.comwrote: Vikram A wrote: Hi, I would like to export my table structure from MYSQL from a particular db. Is there any tool for doing this? There are several ways to get structure information from within MySQL: the SHOW COLUMNS... command the SHOW INDEXES... command the SHOW CREATE TABLE... command you can also write queries against the tables in the INFORMATION_SCHEMA You can also use an external utility such as mysqldump. Use the --no-data option to get just a dump of your table definitions. If you also want to see triggers and events and stored procedures, you also need to use the --triggers, --events, and --procedure options, too. Details are in the fine manual: http://dev.mysql.com/doc/refman/5.1/en/show.html http://dev.mysql.com/doc/refman/5.1/en/information-schema.html http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html I guess the final answer depends on which information are you looking for and in what format you want to see it. Got any details you want to share? -- Shawn Green MySQL Principle Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: Mysql - Tables Export to Excel!
Note : if you table name it will export all tables from given database; eg:* mysqldump -u user -ppassword wordpress --no-data Dumpdata.txt * It will export all tables from db wordpress. On Mon, Apr 19, 2010 at 8:57 AM, Prabhat Kumar aim.prab...@gmail.comwrote: use can use mysqldump with option *-no-data* eg. *mysqldump -u user -ppassword wordpress user --no-data Dumpdata.txt * where wordpress is my database and user is my table. Thanks On Sun, Apr 18, 2010 at 9:48 PM, Shawn Green shawn.l.gr...@oracle.comwrote: Vikram A wrote: Hi, I would like to export my table structure from MYSQL from a particular db. Is there any tool for doing this? There are several ways to get structure information from within MySQL: the SHOW COLUMNS... command the SHOW INDEXES... command the SHOW CREATE TABLE... command you can also write queries against the tables in the INFORMATION_SCHEMA You can also use an external utility such as mysqldump. Use the --no-data option to get just a dump of your table definitions. If you also want to see triggers and events and stored procedures, you also need to use the --triggers, --events, and --procedure options, too. Details are in the fine manual: http://dev.mysql.com/doc/refman/5.1/en/show.html http://dev.mysql.com/doc/refman/5.1/en/information-schema.html http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html I guess the final answer depends on which information are you looking for and in what format you want to see it. Got any details you want to share? -- Shawn Green MySQL Principle Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: Mysql - Tables Export to Excel!
The MySQL ODBC driver? / Carsten On Thu, 8 Apr 2010 10:42:28 +0530 (IST), Vikram A vikkiatb...@yahoo.in wrote: Hi, I would like to export my table structure from MYSQL from a particular db. Is there any tool for doing this? Please guide me. Thank you VIKRAM A !DSPAM:451,4bbd65f933049495715525! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Mysql - Tables Export to Excel!
Hi Vikram, You can use toad for mysql ( It's a free tool) to export table structure or data. Even you can use mysqldump also. Krishna On Thu, Apr 8, 2010 at 10:42 AM, Vikram A vikkiatb...@yahoo.in wrote: Hi, I would like to export my table structure from MYSQL from a particular db. Is there any tool for doing this? Please guide me. Thank you VIKRAM A
RE: Mysql - Tables Export to Excel!
You can also use HeidiSQL, another free tool to visually manage and export structure/data etc (and a whole host of other stuff) I use it to manage windows and linux hosted databases. George -Original Message- From: Krishna Chandra Prajapati [mailto:prajapat...@gmail.com] Sent: 08 April 2010 08:18 To: Vikram A Cc: MY SQL Mailing list Subject: Re: Mysql - Tables Export to Excel! Hi Vikram, You can use toad for mysql ( It's a free tool) to export table structure or data. Even you can use mysqldump also. Krishna On Thu, Apr 8, 2010 at 10:42 AM, Vikram A vikkiatb...@yahoo.in wrote: Hi, I would like to export my table structure from MYSQL from a particular db. Is there any tool for doing this? Please guide me. Thank you VIKRAM A -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Mysql - Tables Export to Excel!
Or MySQL Connector :) On Apr 8, 2010 10:11 AM, Carsten Pedersen cars...@bitbybit.dk wrote: The MySQL ODBC driver? / Carsten On Thu, 8 Apr 2010 10:42:28 +0530 (IST), Vikram A vikkiatb...@yahoo.in wrote: Hi, I would like to export my table structure from MYSQL from a particular db. Is there any... !DSPAM:451,4bbd65f933049495715525! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com
Mysql - Tables Export to Excel!
Hi, I would like to export my table structure from MYSQL from a particular db. Is there any tool for doing this? Please guide me. Thank you VIKRAM A
Exporting the result of a Query into excel
Hi all, I would like to know if there is a tool or command in mySQL that allows one to export the result of query into excel formart
Re: Exporting the result of a Query into excel
ishaq gbola wrote: Hi all, I would like to know if there is a tool or command in mySQL that allows one to export the result of query into excel formart select * from table into outfile thefile.txt; That can be imported into excel using CSV and using TAB as separator. http://code.anjanesh.net/2007/12/export-mysql-data-to-excel-compatible.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Exporting the result of a Query into excel
You can use the --xml option to the mysql command to get xnl output. You can also get a tab-delimited output file that can be read into Excel (this is what I usually do). To get a tab-delimited file, you can use the --tab option of mysqldump or start up the mysql program with the -sss option and do a select on the data you want, redirecting it into an output file. On Tue, Jan 5, 2010 at 7:17 AM, ishaq gbola ishaq...@yahoo.co.uk wrote: Hi all, I would like to know if there is a tool or command in mySQL that allows one to export the result of query into excel formart -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: Exporting the result of a Query into excel
Thanks a lot for that, but where does this file get saved in and how can i copy it to my local host if the database is on a remote server --- On Tue, 5/1/10, Jay Ess li...@netrogenic.com wrote: From: Jay Ess li...@netrogenic.com Subject: Re: Exporting the result of a Query into excel To: mysql@lists.mysql.com Mysql mysql@lists.mysql.com Date: Tuesday, 5 January, 2010, 13:22 ishaq gbola wrote: Hi all, I would like to know if there is a tool or command in mySQL that allows one to export the result of query into excel formart select * from table into outfile thefile.txt; That can be imported into excel using CSV and using TAB as separator. http://code.anjanesh.net/2007/12/export-mysql-data-to-excel-compatible.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=ishaq...@yahoo.co.uk
Re: Exporting the result of a Query into excel
ishaq gbola wrote: Thanks a lot for that, but where does this file get saved in and how can i copy it to my local host if the database is on a remote server If you don't specify the absolute location it can be find in DATADIR/DatabaseName/. And after you located the file you have a multitude of choice how to transfer the file. scp,ftp,http,mail all depending on what's installed on the server and what access you got to it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Exporting the result of a Query into excel
Hi What is the OS you are using on the remote and local server. Is there a mysql client on local server? Thanks Suresh Kuna Sent from BlackBerry® on Airtel -Original Message- From: ishaq gbola ishaq...@yahoo.co.uk Date: Tue, 5 Jan 2010 13:31:59 To: mysql@lists.mysql.com Subject: Re: Exporting the result of a Query into excel Thanks a lot for that, but where does this file get saved in and how can i copy it to my local host if the database is on a remote server --- On Tue, 5/1/10, Jay Ess li...@netrogenic.com wrote: From: Jay Ess li...@netrogenic.com Subject: Re: Exporting the result of a Query into excel To: mysql@lists.mysql.com Mysql mysql@lists.mysql.com Date: Tuesday, 5 January, 2010, 13:22 ishaq gbola wrote: Hi all, I would like to know if there is a tool or command in mySQL that allows one to export the result of query into excel formart select * from table into outfile thefile.txt; That can be imported into excel using CSV and using TAB as separator. http://code.anjanesh.net/2007/12/export-mysql-data-to-excel-compatible.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=ishaq...@yahoo.co.uk
Re: Exporting the result of a Query into excel
Am running windows on my local host and Redhat linux on the remote server, I have no mysql client on local host. TAC for the NMS using the database adviced me not to use a msqlclient for it, that it could break the database??? --- On Tue, 5/1/10, sureshkumar...@gmail.com sureshkumar...@gmail.com wrote: From: sureshkumar...@gmail.com sureshkumar...@gmail.com Subject: Re: Exporting the result of a Query into excel To: ishaq gbola ishaq...@yahoo.co.uk, mysql@lists.mysql.com Date: Tuesday, 5 January, 2010, 13:37 Hi What is the OS you are using on the remote and local server. Is there a mysql client on local server? Thanks Suresh Kuna Sent from BlackBerry® on Airtel -Original Message- From: ishaq gbola ishaq...@yahoo.co.uk Date: Tue, 5 Jan 2010 13:31:59 To: mysql@lists.mysql.com Subject: Re: Exporting the result of a Query into excel Thanks a lot for that, but where does this file get saved in and how can i copy it to my local host if the database is on a remote server --- On Tue, 5/1/10, Jay Ess li...@netrogenic.com wrote: From: Jay Ess li...@netrogenic.com Subject: Re: Exporting the result of a Query into excel To: mysql@lists.mysql.com Mysql mysql@lists.mysql.com Date: Tuesday, 5 January, 2010, 13:22 ishaq gbola wrote: Hi all, I would like to know if there is a tool or command in mySQL that allows one to export the result of query into excel formart select * from table into outfile thefile.txt; That can be imported into excel using CSV and using TAB as separator. http://code.anjanesh.net/2007/12/export-mysql-data-to-excel-compatible.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=ishaq...@yahoo.co.uk
Re: Exporting the result of a Query into excel
As test i tried this and I got the result below mysql show tables into outfile trial.txt - ; ERROR 1064: You have an error in your SQL syntax. Check the manual that corresp onds to your MySQL server version for the right syntax to use near 'into outfile trial.txt' at line 1 mysql --- On Tue, 5/1/10, Jay Ess li...@netrogenic.com wrote: From: Jay Ess li...@netrogenic.com Subject: Re: Exporting the result of a Query into excel To: Cc: mysql@lists.mysql.com Date: Tuesday, 5 January, 2010, 13:35 ishaq gbola wrote: Thanks a lot for that, but where does this file get saved in and how can i copy it to my local host if the database is on a remote server If you don't specify the absolute location it can be find in DATADIR/DatabaseName/. And after you located the file you have a multitude of choice how to transfer the file. scp,ftp,http,mail all depending on what's installed on the server and what access you got to it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=ishaq...@yahoo.co.uk
Re: Exporting the result of a Query into excel
Ok. In windows, Use winscp tool, connect to the server and copy the file to local machine and import it in excel. Thanks Suresh Kuna Sent from BlackBerry® on Airtel -Original Message- From: ishaq gbola ishaq...@yahoo.co.uk Date: Tue, 5 Jan 2010 13:42:20 To: mysql@lists.mysql.com Subject: Re: Exporting the result of a Query into excel Am running windows on my local host and Redhat linux on the remote server, I have no mysql client on local host. TAC for the NMS using the database adviced me not to use a msqlclient for it, that it could break the database??? --- On Tue, 5/1/10, sureshkumar...@gmail.com sureshkumar...@gmail.com wrote: From: sureshkumar...@gmail.com sureshkumar...@gmail.com Subject: Re: Exporting the result of a Query into excel To: ishaq gbola ishaq...@yahoo.co.uk, mysql@lists.mysql.com Date: Tuesday, 5 January, 2010, 13:37 Hi What is the OS you are using on the remote and local server. Is there a mysql client on local server? Thanks Suresh Kuna Sent from BlackBerry® on Airtel -Original Message- From: ishaq gbola ishaq...@yahoo.co.uk Date: Tue, 5 Jan 2010 13:31:59 To: mysql@lists.mysql.com Subject: Re: Exporting the result of a Query into excel Thanks a lot for that, but where does this file get saved in and how can i copy it to my local host if the database is on a remote server --- On Tue, 5/1/10, Jay Ess li...@netrogenic.com wrote: From: Jay Ess li...@netrogenic.com Subject: Re: Exporting the result of a Query into excel To: mysql@lists.mysql.com Mysql mysql@lists.mysql.com Date: Tuesday, 5 January, 2010, 13:22 ishaq gbola wrote: Hi all, I would like to know if there is a tool or command in mySQL that allows one to export the result of query into excel formart select * from table into outfile thefile.txt; That can be imported into excel using CSV and using TAB as separator. http://code.anjanesh.net/2007/12/export-mysql-data-to-excel-compatible.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=ishaq...@yahoo.co.uk
Re: Exporting the result of a Query into excel
We have to use outfile with select stmt, not with show. Suresh Kuna Sent from BlackBerry® on Airtel -Original Message- From: ishaq gbola ishaq...@yahoo.co.uk Date: Tue, 5 Jan 2010 13:45:44 To: mysql@lists.mysql.com Subject: Re: Exporting the result of a Query into excel As test i tried this and I got the result below mysql show tables into outfile trial.txt - ; ERROR 1064: You have an error in your SQL syntax. Check the manual that corresp onds to your MySQL server version for the right syntax to use near 'into outfile trial.txt' at line 1 mysql --- On Tue, 5/1/10, Jay Ess li...@netrogenic.com wrote: From: Jay Ess li...@netrogenic.com Subject: Re: Exporting the result of a Query into excel To: Cc: mysql@lists.mysql.com Date: Tuesday, 5 January, 2010, 13:35 ishaq gbola wrote: Thanks a lot for that, but where does this file get saved in and how can i copy it to my local host if the database is on a remote server If you don't specify the absolute location it can be find in DATADIR/DatabaseName/. And after you located the file you have a multitude of choice how to transfer the file. scp,ftp,http,mail all depending on what's installed on the server and what access you got to it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=ishaq...@yahoo.co.uk
Re: Exporting the result of a Query into excel
A command to convert the table mytab in database mydb into a tab-delimited file mytab.txt might be: mysql -e'select * from mydb.mytab' -sss mytab.txt The -sss is necessary to remove all the formatting stuff that you normally have in the output of a select statement. An alternative, if you have a directory *** that mysql can write to ***: mysqldump --tab=/home/mysql/temp mydb mytab This will create 2 files in /home/mysql/temp: mytab.txt and mytab.sql. The one you'll want is in mytab.txt. Then use what transfer tool you have, ftp, scp, winscp, rsync to transfer the file to another server. On Tue, Jan 5, 2010 at 7:31 AM, ishaq gbola ishaq...@yahoo.co.uk wrote: Thanks a lot for that, but where does this file get saved in and how can i copy it to my local host if the database is on a remote server --- On Tue, 5/1/10, Jay Ess li...@netrogenic.com wrote: From: Jay Ess li...@netrogenic.com Subject: Re: Exporting the result of a Query into excel To: mysql@lists.mysql.com Mysql mysql@lists.mysql.com Date: Tuesday, 5 January, 2010, 13:22 ishaq gbola wrote: Hi all, I would like to know if there is a tool or command in mySQL that allows one to export the result of query into excel formart select * from table into outfile thefile.txt; That can be imported into excel using CSV and using TAB as separator. http://code.anjanesh.net/2007/12/export-mysql-data-to-excel-compatible.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=ishaq...@yahoo.co.uk -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: Exporting the result of a Query into excel
TAC for the NMS using the database adviced me not to use a msqlclient for it, that it could break the database??? You are being given a false argument. If the application which connects to MySQL is running on that windows box then there already is a MySQL client on that machine, albeit a specialized one. Putting a plain old client console on that same box does not hurt anything, not even security unless you leave a text file full of passwords laying around. Having that console on the same host as the application is invaluable not only for data-dumping jobs like this one, but for all kinds of trouble shooting in general. - michael dykman On Tue, Jan 5, 2010 at 8:42 AM, ishaq gbola ishaq...@yahoo.co.uk wrote: Am running windows on my local host and Redhat linux on the remote server, I have no mysql client on local host. TAC for the NMS using the database adviced me not to use a msqlclient for it, that it could break the database??? --- On Tue, 5/1/10, sureshkumar...@gmail.com sureshkumar...@gmail.com wrote: From: sureshkumar...@gmail.com sureshkumar...@gmail.com Subject: Re: Exporting the result of a Query into excel To: ishaq gbola ishaq...@yahoo.co.uk, mysql@lists.mysql.com Date: Tuesday, 5 January, 2010, 13:37 Hi What is the OS you are using on the remote and local server. Is there a mysql client on local server? Thanks Suresh Kuna Sent from BlackBerry® on Airtel -Original Message- From: ishaq gbola ishaq...@yahoo.co.uk Date: Tue, 5 Jan 2010 13:31:59 To: mysql@lists.mysql.com Subject: Re: Exporting the result of a Query into excel Thanks a lot for that, but where does this file get saved in and how can i copy it to my local host if the database is on a remote server --- On Tue, 5/1/10, Jay Ess li...@netrogenic.com wrote: From: Jay Ess li...@netrogenic.com Subject: Re: Exporting the result of a Query into excel To: mysql@lists.mysql.com Mysql mysql@lists.mysql.com Date: Tuesday, 5 January, 2010, 13:22 ishaq gbola wrote: Hi all, I would like to know if there is a tool or command in mySQL that allows one to export the result of query into excel formart select * from table into outfile thefile.txt; That can be imported into excel using CSV and using TAB as separator. http://code.anjanesh.net/2007/12/export-mysql-data-to-excel-compatible.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=ishaq...@yahoo.co.uk -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Exporting the result of a Query into excel
Is there any particular reason not to use the MySQL ODBC driver to import the data directly into Excel? / Carsten Jim Lyons skrev: A command to convert the table mytab in database mydb into a tab-delimited file mytab.txt might be: mysql -e'select * from mydb.mytab' -sss mytab.txt The -sss is necessary to remove all the formatting stuff that you normally have in the output of a select statement. An alternative, if you have a directory *** that mysql can write to ***: mysqldump --tab=/home/mysql/temp mydb mytab This will create 2 files in /home/mysql/temp: mytab.txt and mytab.sql. The one you'll want is in mytab.txt. Then use what transfer tool you have, ftp, scp, winscp, rsync to transfer the file to another server. On Tue, Jan 5, 2010 at 7:31 AM, ishaq gbola ishaq...@yahoo.co.uk wrote: Thanks a lot for that, but where does this file get saved in and how can i copy it to my local host if the database is on a remote server --- On Tue, 5/1/10, Jay Ess li...@netrogenic.com wrote: From: Jay Ess li...@netrogenic.com Subject: Re: Exporting the result of a Query into excel To: mysql@lists.mysql.com Mysql mysql@lists.mysql.com Date: Tuesday, 5 January, 2010, 13:22 ishaq gbola wrote: Hi all, I would like to know if there is a tool or command in mySQL that allows one to export the result of query into excel formart select * from table into outfile thefile.txt; That can be imported into excel using CSV and using TAB as separator. http://code.anjanesh.net/2007/12/export-mysql-data-to-excel-compatible.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=ishaq...@yahoo.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Exporting the result of a Query into excel
Carsten: Can you demonstrate this? ~Lawrence Carsten Pedersen wrote: Is there any particular reason not to use the MySQL ODBC driver to import the data directly into Excel? / Carsten Jim Lyons skrev: A command to convert the table mytab in database mydb into a tab-delimited file mytab.txt might be: mysql -e'select * from mydb.mytab' -sss mytab.txt The -sss is necessary to remove all the formatting stuff that you normally have in the output of a select statement. An alternative, if you have a directory *** that mysql can write to ***: mysqldump --tab=/home/mysql/temp mydb mytab This will create 2 files in /home/mysql/temp: mytab.txt and mytab.sql. The one you'll want is in mytab.txt. Then use what transfer tool you have, ftp, scp, winscp, rsync to transfer the file to another server. On Tue, Jan 5, 2010 at 7:31 AM, ishaq gbola ishaq...@yahoo.co.uk wrote: Thanks a lot for that, but where does this file get saved in and how can i copy it to my local host if the database is on a remote server --- On Tue, 5/1/10, Jay Ess li...@netrogenic.com wrote: From: Jay Ess li...@netrogenic.com Subject: Re: Exporting the result of a Query into excel To: mysql@lists.mysql.com Mysql mysql@lists.mysql.com Date: Tuesday, 5 January, 2010, 13:22 ishaq gbola wrote: Hi all, I would like to know if there is a tool or command in mySQL that allows one to export the result of query into excel formart select * from table into outfile thefile.txt; That can be imported into excel using CSV and using TAB as separator. http://code.anjanesh.net/2007/12/export-mysql-data-to-excel-compatible.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=ishaq...@yahoo.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Exporting the result of a Query into excel
Doesn't that depend on how often you want to do it? If it's a one-time thing or an occasional thing, it's easier to just dump the table into a tab-delimited file. If it's something you want to do on a regular basis, ODBC is a way you might prefer to go. But it also would require you to have the authority on a given box to set up an ODBC connection (which might involve downloading software). You would also have to do it on every machine you want to load the data on. A tab-delimited file can be transferred to any machine, even stored on a flash-drive and walked somewhere. On Tue, Jan 5, 2010 at 1:50 PM, Carsten Pedersen cars...@bitbybit.dkwrote: Is there any particular reason not to use the MySQL ODBC driver to import the data directly into Excel? / Carsten Jim Lyons skrev: A command to convert the table mytab in database mydb into a tab-delimited file mytab.txt might be: mysql -e'select * from mydb.mytab' -sss mytab.txt The -sss is necessary to remove all the formatting stuff that you normally have in the output of a select statement. An alternative, if you have a directory *** that mysql can write to ***: mysqldump --tab=/home/mysql/temp mydb mytab This will create 2 files in /home/mysql/temp: mytab.txt and mytab.sql. The one you'll want is in mytab.txt. Then use what transfer tool you have, ftp, scp, winscp, rsync to transfer the file to another server. On Tue, Jan 5, 2010 at 7:31 AM, ishaq gbola ishaq...@yahoo.co.uk wrote: Thanks a lot for that, but where does this file get saved in and how can i copy it to my local host if the database is on a remote server --- On Tue, 5/1/10, Jay Ess li...@netrogenic.com wrote: From: Jay Ess li...@netrogenic.com Subject: Re: Exporting the result of a Query into excel To: mysql@lists.mysql.com Mysql mysql@lists.mysql.com Date: Tuesday, 5 January, 2010, 13:22 ishaq gbola wrote: Hi all, I would like to know if there is a tool or command in mySQL that allows one to export the result of query into excel formart select * from table into outfile thefile.txt; That can be imported into excel using CSV and using TAB as separator. http://code.anjanesh.net/2007/12/export-mysql-data-to-excel-compatible.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=ishaq...@yahoo.co.uk -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
RE: Exporting the result of a Query into excel
-Original Message- From: ishaq gbola [mailto:ishaq...@yahoo.co.uk] Sent: Tuesday, January 05, 2010 7:18 AM To: mysql@lists.mysql.com Subject: Exporting the result of a Query into excel Hi all, I would like to know if there is a tool or command in mySQL that allows one to export the result of query into excel formart If'n you can download and install Toad for MySQL, then the steps might go like this: 1. Write the query 2. Run the query 3. Click the Export data to an Excel file button/icon (if you have Excel 2007 installed, it might throw a warning at you) 4. Note where the file was created to. 5. ? 6. Profit! The only caveat to the above is to be aware of how many rows you want to export and the corresponding column/row limitations of whatever version of Excel you have installed. As an additional caveat, you could export to html or csv formats as well which would allow you to work around any size limitations imposed by Excel. Jason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
[ excel to mysql ]
Hi! How i do for transfer data from excel file to mysql? Some example? Thanks
Re: [ excel to mysql ]
Save the excel file as a CSV text file, then use LOAD DATA INFILE command to import. See the mysql manual for usage and example. On May 8, 2008, at 4:49 AM, Lord Gustavo Miguel Angel wrote: Hi! How i do for transfer data from excel file to mysql? Some example? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Importing data from excel sheet
I have created table in mysql with 12 fields Field1Field2 Field3 Field4 Field12 I have an excel sheet with 12 columns and 150 rows. My question is how can I import all of the columns from the excel sheet to my table without losing any information. - Don't be flakey. Get Yahoo! Mail for Mobile and always stay connected to friends.
Re: Importing data from excel sheet
Hi, sam You can try to export table to file from Excel in CSV format. And then import data from this file to mysql. Something like this should help you: LOAD DATA INFILE 'yourtabledata.txt' INTO TABLE yourtable FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\r\n'; sam rumaizan wrote: I have created table in mysql with 12 fields Field1Field2 Field3 Field4 ……… Field12 I have an excel sheet with 12 columns and 150 rows. My question is how can I import all of the columns from the excel sheet to my table without losing any information. - Don't be flakey. Get Yahoo! Mail for Mobile and always stay connected to friends. -- Andrew Dashin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
excel and Mysql?!
hello . anybody knows how to export from excel to mysql in order to create or fill a database table?? tanks for your help and patience cheers
Re: excel and Mysql?!
Sure, it's usually fairly straightforward to transfer data. You can export to a CSV or other delimited file, then use MySQL's LOAD DATA INFILE command to read it in. See http://dev.mysql.com/doc/refman/5.0/en/load-data.html I haven't ever tried creating tables from something in Excel automagically; I have always created the tables by hand. Dan On 10/18/06, Roberto William Aranda-W Roman [EMAIL PROTECTED] wrote: hello . anybody knows how to export from excel to mysql in order to create or fill a database table?? tanks for your help and patience cheers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: excel and Mysql?!
If you have the Max Binaries of MySQL, do the following: 1) Export Excel Data to a CSV file (mydata.csv) 2) Goto MySQL and create table, without indexes to accept the data (CREATE TABLE myImportCSV) 3) Change the table layout to accept CSV i.e., ALTER TABLE myImport ENGINE = CSV; Please note that CSV tables in MySQL do not support indexes. At this point myImport.csv in the datadir of MySQL is a zero-length file. 4) Goto datadir of MySQL installation and put the contents of mydata.csv into myImport.csv. 5) Run this SQL statement: ALTER TABLE myImport ENGINE = MyISAM; 6) Create any necessary indexes for the MyISAM table myImport. If you do not have Max Binaries, I hope you have Microsoft Access. Then, you can try the following: 1) Download MyODBC onto your PC and install it. 2) Goto Administrative Tasks and goto 32-bit ODBC in Control Panel 3) Set up ODBC entry to point to your MySQL database of choice 4) Goto Microsoft Access and create a new table. 5) Create a link table entry to the Excel spreadsheet 6) Create a link table entry to MySQL table Make sure the MySQL userid and password has full rights to insert data Make sure the MySQL table has a primary key. If it does not have one, make one. Microsoft Access requires all outside databases (i.e., MySQL, Oracle, SQL Server, etc) to have a primary key. 7) Create an Append Query to the MySQL table from the Excel Spreadsheet. If you do not have Microsoft Access, here is a last resort: 1) Export Spreadsheet Pipe Delimited to myImport.txt 2) Create a table to load on MySQL (with indexes if necessary) 3) Use LOAD DATA INFILE if myImport.txt is to be on the server or LOAD DATA LOCAL INFILE if myImport.txt is loaded from your PC Note: If using LOAD DATA LOCAL INFILE, make sure you use forward slashes i.e., C:/data/myImport.txt I hope this helps !!! - Original Message - From: Roberto William Aranda-W Roman [EMAIL PROTECTED] To: MySQL List mysql@lists.mysql.com Sent: Wednesday, October 18, 2006 8:36:41 AM GMT-0500 US/Eastern Subject: excel and Mysql?! hello . anybody knows how to export from excel to mysql in order to create or fill a database table?? tanks for your help and patience cheers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: excel and Mysql?!
I do it all of the time, and once you get the hang of it this is quite easy. The simple-minded way is to generate one SQL statement per row of the original spreadsheet (assuming that each row of the spreadsheet corresponds to a row in the data base table). 1. Create a new worksheet, if needed. Position yourself at cell A1. 2. Using Excel functions, build an entry like =CONCATENATE(INSERT INTO mytable SET col1 = ,'Sheet1'!A1,, col2 = ,'Sheet1'!A2, ...) You can use Excel's click to build the cell links to the other spreadsheet, you don't have to type them by hand. 3. Copy the formula down the range you need. 4. Save the worksheet containing the MySQL commands as a text file. 5. From the mysql command line interface, simply USE the right data base and then source the text file you created. Alternatively, you can insert the USE statement into your spreadsheet (adjusting the row numbers accordingly) or directly into your text file, and redirect the input file for mysql on the command line. Once you get the hang of it, this is all simpler than it sounds. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Roberto William Aranda-W Roman [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 18, 2006 8:37 AM To: MySQL List Subject: excel and Mysql?! hello . anybody knows how to export from excel to mysql in order to create or fill a database table?? tanks for your help and patience cheers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: excel and Mysql?!
As usual, Dan's suggestion is better than mine, especially for large amounts of data (I usually work with perhaps 100 rows, at most). When you save a spreadsheet as a text file, the columns will be delimited by tabs by default. Also, if you go with my suggestion you should probably surround any text values with an extra pair of single quotes: ... SET col1 = ', 'Sheet1'!A1, ', ...) Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Roberto William Aranda-W Roman [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 18, 2006 8:37 AM To: MySQL List Subject: excel and Mysql?! hello . anybody knows how to export from excel to mysql in order to create or fill a database table?? tanks for your help and patience cheers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: excel and Mysql?!
Always being the last to input, there are lots of other database tools out there that let you do this. One in particular is DBTools Professional (which is what I use). You can ADO IMPORT Excel, MS-Access, and others like FoxPro and PostgreSQL. Another is Database Workbench, which one of the guys on this list works on. However, if free is the way to go, then MyODBC and the format below (which I will start using now =) ) are the ways to do it. My 0.02... J.R. cheap is good, free is better From: Jerry Schwartz [EMAIL PROTECTED] Sent: Wednesday, October 18, 2006 10:17 AM To: 'Roberto William Aranda-W Roman' [EMAIL PROTECTED], 'MySQL List' mysql@lists.mysql.com Subject: RE: excel and Mysql?! As usual, Dan's suggestion is better than mine, especially for large amounts of data (I usually work with perhaps 100 rows, at most). When you save a spreadsheet as a text file, the columns will be delimited by tabs by default. Also, if you go with my suggestion you should probably surround any text values with an extra pair of single quotes: ... SET col1 = ', 'Sheet1'!A1, ', ...) Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Roberto William Aranda-W Roman [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 18, 2006 8:37 AM To: MySQL List Subject: excel and Mysql?! hello . anybody knows how to export from excel to mysql in order to create or fill a database table?? tanks for your help and patience cheers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: from MySQL to MS Excel ...
Thanks JR, Shawn, Scott, ... for your replies. I choose to make use of the SELECT ... INTO OUTFILE. This works fine, but I also want a header-line in the CSV file. So I made the following statement: SELECT `ID`, `Code`, `Name` UNION SELECT `ID`, `Code`, `Name` INTO OUTFILE 'D:/MySQL Datafiles/Units.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' FROM Units ORDER BY `ID`; But this makes the header-line a trailer-line in the csv file, like this: 11,kg,KiloGrams 12,g,Grams 13,Ton,Tonne ... ID,Code,Name Any idea how to make a sorted csv file with a real header-line ? TIA, Cor - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Saturday, December 10, 2005 5:17 PM Subject: RE: from MySQL to MS Excel ... There is at least one other option that JR didn't mention... at least some versions of Excel have the menu option Data-Get External Data which allows you to link through ODBC to run queries directly from within Excel. I have barely used it and I have never tried it with MySQL so I can't really explain how to use it or what it's limitations will be but I know that it works through at least two other ODBC drivers. Shawn Green Database Administrator Unimin Corporation - Spruce Pine J.R. Bullington [EMAIL PROTECTED] wrote on 12/10/2005 10:33:18 AM: Here's the skinny -- YES and NO. NO in that it won't export directly, YES in that you have to do a little leg work in order for it to be done. You have 3 options -- ODBC, Code and CSV. ODBC -- Excel has the ability to use ODBC connections to the MySQL database. Run your MySQL query with the HTML flags turned on and then export to a file so that Excel can read it. (Thanks to SGreen for this info from an earlier post). CODE -- If you code it in ASP or PHP, you can get your code to push directly into Excel with field headers and data, and have formatting options because Excel can interpret HTML code. CSV -- Do your MySQL query from the CLI and then use MySQL to export your results to a CSV file. Then open the CSV file in Excel (using the Excel File Open). See ODBC connection above for another option using the HTML flag. HTH, J.R. -Original Message- From: C.R.Vegelin [mailto:[EMAIL PROTECTED] Sent: Saturday, December 10, 2005 9:35 AM To: mysql@lists.mysql.com Subject: from MySQL to MS Excel ... Hi Friends, I am looking for an easy and seamless way to export MySQL query output to MS Excel. At this moment I am using MS Access 2003 as front-end for a MySQL database. With MS Access I can easily send the output of queries on my database to MS Excel. All I need to do is select Tools Office Links Analyze it with Microsoft Office Excel. That's all. This applies to all kinds of MySQL queries, including WITH ROLLUP options. In the manual I found: http://dev.mysql.com/doc/refman/5.0/en/doctoexcel.html But this is too much trouble, and does not allow full functionality of MySQL queries. Question: is it possible to create MS Excel files directly from MySQL ? TIA, Cor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: from MySQL to MS Excel ...
C.R.Vegelin wrote: Thanks JR, Shawn, Scott, ... for your replies. I choose to make use of the SELECT ... INTO OUTFILE. This works fine, but I also want a header-line in the CSV file. So I made the following statement: SELECT `ID`, `Code`, `Name` UNION SELECT `ID`, `Code`, `Name` INTO OUTFILE 'D:/MySQL Datafiles/Units.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' FROM Units ORDER BY `ID`; But this makes the header-line a trailer-line in the csv file, like this: 11,kg,KiloGrams 12,g,Grams 13,Ton,Tonne ... ID,Code,Name Any idea how to make a sorted csv file with a real header-line ? TIA, Cor I suspect your query is treated as (SELECT `ID`, `Code`, `Name`) UNION (SELECT `ID`, `Code`, `Name` INTO OUTFILE 'D:/MySQL Datafiles/Units.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' FROM Units ) ORDER BY `ID`; when you want (SELECT `ID`, `Code`, `Name`) UNION (SELECT `ID`, `Code`, `Name` INTO OUTFILE 'D:/MySQL Datafiles/Units.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' FROM Units ORDER BY `ID`); You see the difference? The former sorts all the rows by id, while the latter only sorts the second query's output. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: from MySQL to MS Excel ...
Here's the skinny -- YES and NO. NO in that it won't export directly, YES in that you have to do a little leg work in order for it to be done. You have 3 options -- ODBC, Code and CSV. ODBC -- Excel has the ability to use ODBC connections to the MySQL database. Run your MySQL query with the HTML flags turned on and then export to a file so that Excel can read it. (Thanks to SGreen for this info from an earlier post). CODE -- If you code it in ASP or PHP, you can get your code to push directly into Excel with field headers and data, and have formatting options because Excel can interpret HTML code. CSV -- Do your MySQL query from the CLI and then use MySQL to export your results to a CSV file. Then open the CSV file in Excel (using the Excel File Open). See ODBC connection above for another option using the HTML flag. HTH, J.R. -Original Message- From: C.R.Vegelin [mailto:[EMAIL PROTECTED] Sent: Saturday, December 10, 2005 9:35 AM To: mysql@lists.mysql.com Subject: from MySQL to MS Excel ... Hi Friends, I am looking for an easy and seamless way to export MySQL query output to MS Excel. At this moment I am using MS Access 2003 as front-end for a MySQL database. With MS Access I can easily send the output of queries on my database to MS Excel. All I need to do is select Tools Office Links Analyze it with Microsoft Office Excel. That's all. This applies to all kinds of MySQL queries, including WITH ROLLUP options. In the manual I found: http://dev.mysql.com/doc/refman/5.0/en/doctoexcel.html But this is too much trouble, and does not allow full functionality of MySQL queries. Question: is it possible to create MS Excel files directly from MySQL ? TIA, Cor smime.p7s Description: S/MIME cryptographic signature
RE: from MySQL to MS Excel ...
There is at least one other option that JR didn't mention... at least some versions of Excel have the menu option Data-Get External Data which allows you to link through ODBC to run queries directly from within Excel. I have barely used it and I have never tried it with MySQL so I can't really explain how to use it or what it's limitations will be but I know that it works through at least two other ODBC drivers. Shawn Green Database Administrator Unimin Corporation - Spruce Pine J.R. Bullington [EMAIL PROTECTED] wrote on 12/10/2005 10:33:18 AM: Here's the skinny -- YES and NO. NO in that it won't export directly, YES in that you have to do a little leg work in order for it to be done. You have 3 options -- ODBC, Code and CSV. ODBC -- Excel has the ability to use ODBC connections to the MySQL database. Run your MySQL query with the HTML flags turned on and then export to a file so that Excel can read it. (Thanks to SGreen for this info from an earlier post). CODE -- If you code it in ASP or PHP, you can get your code to push directly into Excel with field headers and data, and have formatting options because Excel can interpret HTML code. CSV -- Do your MySQL query from the CLI and then use MySQL to export your results to a CSV file. Then open the CSV file in Excel (using the Excel File Open). See ODBC connection above for another option using the HTML flag. HTH, J.R. -Original Message- From: C.R.Vegelin [mailto:[EMAIL PROTECTED] Sent: Saturday, December 10, 2005 9:35 AM To: mysql@lists.mysql.com Subject: from MySQL to MS Excel ... Hi Friends, I am looking for an easy and seamless way to export MySQL query output to MS Excel. At this moment I am using MS Access 2003 as front-end for a MySQL database. With MS Access I can easily send the output of queries on my database to MS Excel. All I need to do is select Tools Office Links Analyze it with Microsoft Office Excel. That's all. This applies to all kinds of MySQL queries, including WITH ROLLUP options. In the manual I found: http://dev.mysql.com/doc/refman/5.0/en/doctoexcel.html But this is too much trouble, and does not allow full functionality of MySQL queries. Question: is it possible to create MS Excel files directly from MySQL ? TIA, Cor
EXCEL Querry
Hello ALL, I am basically from the call center industry, we use customer databses in bulk which consists of telephone numbers, names, addresses, city, state and zip codes., we have to run them thru against national do not call registry in the usa, for example i have a column of 4000 phone numbers with me, after scrubbing against the dnc registry, i get back results for 3000 numbers which means 1000 numbers have to be deleted from the list of 4000, how do i do this, can i get this done in excel., pls help. regards Parker -- Sent from the MySQL - General forum at Nabble.com: http://www.nabble.com/EXCEL-Querry-t486406.html#a1322601
dynamic value like excel
i want to put a value in a column that is the date of today . IS there a solution to do that in sql way ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: dynamic value like excel
this may help you test create table d ( - date date ); Query OK, 0 rows affected (0.45 sec) test insert into d values(now()); Query OK, 1 row affected (0.12 sec) test select * from d; ++ | date | ++ | 2005-09-10 | ++ 1 row in set (0.00 sec) On Sep 10, 2005, at 8:52 AM, liofr wrote: i want to put a value in a column that is the date of today . IS there a solution to do that in sql way ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] Rich Allen Dare Do -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Populate values in an Excel sheet from MySQL
Hello Nick,
This you can do with the MySQL ODBC Driver installed
(http://dev.mysql.com/downloads/connector/odbc/3.51.html). Further more
you need to activate Microsoft ActiveX Data Objects in the references.
You can use the following code:
--Begin Code--
Dim cn As ADODB.Connection
Dim rs As ADODB.RecordSet
Set cn = New ADODB.Connection
Set rs = New ADODB.RecordSet
cn.ConnectionString = DRIVER={MySQL ODBC 3.51
Driver};SERVER=data.domain.com;PORT=3306;DATABASE=myDatabase;USER=myUser
name;PASSWORD=myPassword;OPTION=3;
cn.Open
sSQL = SELECT * FROM database
rs.Open sSQL, cn
If Not rs.BOF Then rs.MoveFirst
Do While Not rs.EOF
Cells(1, 1) = rs.Fields(index)' This line you can
adjust with your own code
rs.MoveNext
Loop
On Error Resume Next' This is my solution to make sure that the
recordset is always closed, _
without the errorhandling there
occurs an error when you use a query _
that doesn't return results
('INSERT' e.g.). If there is a better way _
to close the connection, then
let me know.
If rs.State = adStateOpen Then rs.Close
On Error Goto 0
cn.Close
Set rs = Nothing
Set cn = nothing
--End Code--
HTH,
Arjan.
-Original Message-
From: Nick Jones [mailto:[EMAIL PROTECTED]
Sent: Thursday, September 01, 2005 08:23 PM
To: mysql@lists.mysql.com
Subject: Populate values in an Excel sheet from MySQL
Does anyone know if it is possible to populate values into an Excel
spreadsheet from a MySQL database? Can I do this directly in Excel or do
I need to create an external program to do the work (i.e. in VB).
Thanks
-Nick
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
The information contained in this communication and any attachments is
confidential and may be privileged, and is for the sole use of the intended
recipient(s). Any unauthorized review, use, disclosure or distribution is
prohibited. If you are not the intended recipient, please notify the sender
immediately by replying to this message and destroy all copies of this message
and any attachments. ASML is neither liable for the proper and complete
transmission of the information contained in this communication, nor for any
delay in its receipt.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Populate values in an Excel sheet from MySQL
Hi,
I had to do the same thing on a project and the problem was that if
you use CSV you will not be able to make a formated excel document.
I am using now *Spreadsheet_Excel_Writer
/package/Spreadsheet_Excel_Writer (
*http://pear.php.net/package/Spreadsheet_Excel_Writer ) and it does
everything I need, including formating the page for printing, color,
bold and boarder on cells and the best part is that it's no really hard
to use.
If you change your mind and want to generate that from perl you also
have some PEAR packages for that, but I've sticked to PHP and with this
the problem was solved and I generate my data on access, custom build
depending on the select.
Best regards,
Cristi Stoica
Arjan Hulshoff wrote:
Hello Nick,
This you can do with the MySQL ODBC Driver installed
(http://dev.mysql.com/downloads/connector/odbc/3.51.html). Further more
you need to activate Microsoft ActiveX Data Objects in the references.
You can use the following code:
--Begin Code--
Dim cn As ADODB.Connection
Dim rs As ADODB.RecordSet
Set cn = New ADODB.Connection
Set rs = New ADODB.RecordSet
cn.ConnectionString = DRIVER={MySQL ODBC 3.51
Driver};SERVER=data.domain.com;PORT=3306;DATABASE=myDatabase;USER=myUser
name;PASSWORD=myPassword;OPTION=3;
cn.Open
sSQL = SELECT * FROM database
rs.Open sSQL, cn
If Not rs.BOF Then rs.MoveFirst
Do While Not rs.EOF
Cells(1, 1) = rs.Fields(index) ' This line you can
adjust with your own code
rs.MoveNext
Loop
On Error Resume Next' This is my solution to make sure that the
recordset is always closed, _
without the errorhandling there
occurs an error when you use a query _
that doesn't return results
('INSERT' e.g.). If there is a better way _
to close the connection, then
let me know.
If rs.State = adStateOpen Then rs.Close
On Error Goto 0
cn.Close
Set rs = Nothing
Set cn = nothing
--End Code--
HTH,
Arjan.
-Original Message-
From: Nick Jones [mailto:[EMAIL PROTECTED]
Sent: Thursday, September 01, 2005 08:23 PM
To: mysql@lists.mysql.com
Subject: Populate values in an Excel sheet from MySQL
Does anyone know if it is possible to populate values into an Excel
spreadsheet from a MySQL database? Can I do this directly in Excel or do
I need to create an external program to do the work (i.e. in VB).
Thanks
-Nick
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
Re: Populate values in an Excel sheet from MySQL [SOLVED]
Thanks to all for your help. I solved my problem the
quick and dirty way. Here's what I did:
1. Created my Excel spreadsheet to look the way I
wanted it, saved it as an XML spreadsheet.
2. Used PHP to pull the desired data from the MySQL
server on our iSeries machine.
3. PHP writes the XML data to a new text file using
the values from the MySQL DB and gives it the .xls
extension.
4. Browser is redirected to the new spreadsheet.
5. Spreadsheet opens like normal in IE for
saving/downloading. Or in Firefox it lets you download
it and it opens like a normal .xls spreadsheet.
Thanks to everyone here for their insight. I coudln't
have achieved it without all of you!
-Nick
--- inferno [EMAIL PROTECTED] wrote:
Hi,
I had to do the same thing on a project and the
problem was that if
you use CSV you will not be able to make a formated
excel document.
I am using now *Spreadsheet_Excel_Writer
/package/Spreadsheet_Excel_Writer (
*http://pear.php.net/package/Spreadsheet_Excel_Writer
) and it does
everything I need, including formating the page for
printing, color,
bold and boarder on cells and the best part is that
it's no really hard
to use.
If you change your mind and want to generate
that from perl you also
have some PEAR packages for that, but I've sticked
to PHP and with this
the problem was solved and I generate my data on
access, custom build
depending on the select.
Best regards,
Cristi Stoica
Arjan Hulshoff wrote:
Hello Nick,
This you can do with the MySQL ODBC Driver
installed
(http://dev.mysql.com/downloads/connector/odbc/3.51.html).
Further more
you need to activate Microsoft ActiveX Data Objects
in the references.
You can use the following code:
--Begin Code--
Dim cn As ADODB.Connection
Dim rs As ADODB.RecordSet
Set cn = New ADODB.Connection
Set rs = New ADODB.RecordSet
cn.ConnectionString = DRIVER={MySQL ODBC 3.51
Driver};SERVER=data.domain.com;PORT=3306;DATABASE=myDatabase;USER=myUser
name;PASSWORD=myPassword;OPTION=3;
cn.Open
sSQL = SELECT * FROM database
rs.Open sSQL, cn
If Not rs.BOF Then rs.MoveFirst
Do While Not rs.EOF
Cells(1, 1) = rs.Fields(index)' This line you
can
adjust with your own code
rs.MoveNext
Loop
On Error Resume Next ' This is my solution to make
sure that the
recordset is always closed, _
without the errorhandling there
occurs an error when you use a query _
that doesn't return results
('INSERT' e.g.). If there is a better way _
to close the connection, then
let me know.
If rs.State = adStateOpen Then rs.Close
On Error Goto 0
cn.Close
Set rs = Nothing
Set cn = nothing
--End Code--
HTH,
Arjan.
-Original Message-
From: Nick Jones [mailto:[EMAIL PROTECTED]
Sent: Thursday, September 01, 2005 08:23 PM
To: mysql@lists.mysql.com
Subject: Populate values in an Excel sheet from
MySQL
Does anyone know if it is possible to populate
values into an Excel
spreadsheet from a MySQL database? Can I do this
directly in Excel or do
I need to create an external program to do the work
(i.e. in VB).
Thanks
-Nick
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam
protection around
http://mail.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Populate values in an Excel sheet from MySQL
Does anyone know if it is possible to populate values into an Excel spreadsheet from a MySQL database? Can I do this directly in Excel or do I need to create an external program to do the work (i.e. in VB). Thanks -Nick __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Populate values in an Excel sheet from MySQL
Automatically populate?? Not that _I_ know of, but of course there are those on this list that know much more than I. I do know that you can export the values into tab delimited format and then import it into Excel. I think that you may want to do this via ASP or PHP. It would make life a lot easier. J.R. -Original Message- From: Nick Jones [mailto:[EMAIL PROTECTED] Sent: Thursday, September 01, 2005 2:23 PM To: mysql@lists.mysql.com Subject: Populate values in an Excel sheet from MySQL Does anyone know if it is possible to populate values into an Excel spreadsheet from a MySQL database? Can I do this directly in Excel or do I need to create an external program to do the work (i.e. in VB). Thanks -Nick __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Populate values in an Excel sheet from MySQL
Nick Jones wrote: Does anyone know if it is possible to populate values into an Excel spreadsheet from a MySQL database? Can I do this directly in Excel or do I need to create an external program to do the work (i.e. in VB). Thanks -Nick __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com Yes, you can do it with odbc in excel. Create and ODBC connection using the MySQL driver. (Office XP/Excel XP): Data --Import External Data -- New Database Query -- (select your odbc connection) -- Setup the query (add cols) -Next- select a col. to select the data based upon (if any) -Next- Select a sort col and by (if any) -Next- Select Return Data to Microsoft Excel -Finish- Remember, Excel has a hard limit on the number of rows, (it is not a database.) -- Thanks, James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Populate values in an Excel sheet from MySQL
--- J.R. Bullington [EMAIL PROTECTED] wrote: Automatically populate?? Not that _I_ know of, but of course there are those on this list that know much more than I. I do know that you can export the values into tab delimited format and then import it into Excel. I think that you may want to do this via ASP or PHP. It would make life a lot easier. J.R. PHP would definitely be the way to go on this. I'm working on a web application running on our AS/400 here running Apache, PHP, and MySQL to automate our schedule forms that we fill out daily around here. Everything I've got so far is running through PHP/MySQL so if I can find a way to do this in PHP I'm definitely going to try. I'll google around for awhile and see what I come up with. MS Knowledge Base has proven to be less than useful so far in my endeavor. Also, thanks to James for his suggestion on using the ODBC query directly from Excel. This will get us started for the time being, and give me some leeway so I can work on doing this in PHP. Thanks to you both! -Nick Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Populate values in an Excel sheet from MySQL
Nick Jones [EMAIL PROTECTED] wrote on 09/01/2005 03:18:39 PM: --- J.R. Bullington [EMAIL PROTECTED] wrote: Automatically populate?? Not that _I_ know of, but of course there are those on this list that know much more than I. I do know that you can export the values into tab delimited format and then import it into Excel. I think that you may want to do this via ASP or PHP. It would make life a lot easier. J.R. PHP would definitely be the way to go on this. I'm working on a web application running on our AS/400 here running Apache, PHP, and MySQL to automate our schedule forms that we fill out daily around here. Everything I've got so far is running through PHP/MySQL so if I can find a way to do this in PHP I'm definitely going to try. I'll google around for awhile and see what I come up with. MS Knowledge Base has proven to be less than useful so far in my endeavor. Also, thanks to James for his suggestion on using the ODBC query directly from Excel. This will get us started for the time being, and give me some leeway so I can work on doing this in PHP. Thanks to you both! -Nick The ODBC query is SO simple and flexible once you get through the darn wizard just once. I think it will save you a lot of headaches in the long run. Another alternative is to run your query through the MySQL client with the HTML output flag turned on and Tee your output to some temp file. Excel (at least the modern versions) are HTML aware and will convert the TABLE, TR, TD, etc. tags into cells automagically. Another alternative: I have also changed the Screen Buffer settings of my CMD window so that it no longer wraps at 80 columns. I tend to use something like 1024 x 2048 but it can get much bigger if you need it to. It allows me to catch a whole lot of CLI output before I hit the limits of the buffer. Highlight and copy your query results into Word and replace all | characters with tabs. I had to use Word as you can't enter the tab character into the replace with field in Notepad. -OR- paste into Notepad and save it off as a temp file. Then import that temp file into Excel as | delimited data. Of course if your actual data contains the | character, some of your rows will be wider by a column or two However, the easiest is still the Import External Data wizard via ODBC. If you stick with the PHP solution, remember that Excel will process any tab-delimited file into columns and rows as well as it can do anything else. If you are really gonzo, you can actually produce a fully formatted sheet so long as you conform to the HTML+XML format that Excel uses when you click Save as HTML. That save format IS thoroughly documented in the KB (I know I found it recently). Options. Way too many options ;-D Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Populate values in an Excel sheet from MySQL
Nick Jones wrote: --- J.R. Bullington [EMAIL PROTECTED] wrote: Automatically populate?? Not that _I_ know of, but of course there are those on this list that know much more than I. I do know that you can export the values into tab delimited format and then import it into Excel. I think that you may want to do this via ASP or PHP. It would make life a lot easier. J.R. PHP would definitely be the way to go on this. I'm working on a web application running on our AS/400 here running Apache, PHP, and MySQL to automate our schedule forms that we fill out daily around here. Everything I've got so far is running through PHP/MySQL so if I can find a way to do this in PHP I'm definitely going to try. I'll google around for awhile and see what I come up with. MS Knowledge Base has proven to be less than useful so far in my endeavor. Also, thanks to James for his suggestion on using the ODBC query directly from Excel. This will get us started for the time being, and give me some leeway so I can work on doing this in PHP. Thanks to you both! -Nick Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs How about this for a novel aproach... Use the CSV storage engine that MySQL provides in the source distribution? It is very easy to use, and there is no headaches. -- Partha Dutta -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Populate values in an Excel sheet from MySQL
Here's a little heads-up, as you will need some XML coding to make it look right. PLEASE NOTE: I'm an ASP programmer, so PHP is a little foreign to me. You will need to do some conversion. Here is the way to do it: 1) Define your recordset and connection strings. 2) Write PHP strings to add ContentType (or similar call) = application/vnd.ms-excel 3) (optional) Write PHP echo strings for adding XML tags. (You can find these at http://msdn.microsoft.com, although I'll be damned if I can find them now. Ask for them and I will get them from my other computer and send them on.) 4) In the body of your webpage, put the following snippet: body table width=100% thead tr tdCol 1/td tdCol 2/td ... tdCol end/td /tr /thead tbody ?PHP Open the records, pull the set 'do the loop movefirst while not eof ? tr td? PHP DATA 1 ?/td td? PHP DATA 2 ?/td ... td? PHP DATA end ?/td /tr ?PHP Next in loop Wend Close ? /tbody /table /body /html I hope this helps a little! J.R. PS - Shawn FYI, if you copy and paste a tab into the replace with in notepad, you can do it that way... -Original Message- From: Nick Jones [mailto:[EMAIL PROTECTED] Sent: Thursday, September 01, 2005 3:19 PM To: mysql@lists.mysql.com Subject: RE: Populate values in an Excel sheet from MySQL --- J.R. Bullington [EMAIL PROTECTED] wrote: Automatically populate?? Not that _I_ know of, but of course there are those on this list that know much more than I. I do know that you can export the values into tab delimited format and then import it into Excel. I think that you may want to do this via ASP or PHP. It would make life a lot easier. J.R. PHP would definitely be the way to go on this. I'm working on a web application running on our AS/400 here running Apache, PHP, and MySQL to automate our schedule forms that we fill out daily around here. Everything I've got so far is running through PHP/MySQL so if I can find a way to do this in PHP I'm definitely going to try. I'll google around for awhile and see what I come up with. MS Knowledge Base has proven to be less than useful so far in my endeavor. Also, thanks to James for his suggestion on using the ODBC query directly from Excel. This will get us started for the time being, and give me some leeway so I can work on doing this in PHP. Thanks to you both! -Nick Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Populate values in an Excel sheet from MySQL
--- Partha Dutta [EMAIL PROTECTED] wrote: Nick Jones wrote: --- J.R. Bullington [EMAIL PROTECTED] wrote: Automatically populate?? Not that _I_ know of, but of course there are those on this list that know much more than I. I do know that you can export the values into tab delimited format and then import it into Excel. I think that you may want to do this via ASP or PHP. It would make life a lot easier. J.R. PHP would definitely be the way to go on this. I'm working on a web application running on our AS/400 here running Apache, PHP, and MySQL to automate our schedule forms that we fill out daily around here. Everything I've got so far is running through PHP/MySQL so if I can find a way to do this in PHP I'm definitely going to try. I'll google around for awhile and see what I come up with. MS Knowledge Base has proven to be less than useful so far in my endeavor. Also, thanks to James for his suggestion on using the ODBC query directly from Excel. This will get us started for the time being, and give me some leeway so I can work on doing this in PHP. Thanks to you both! -Nick Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs How about this for a novel aproach... Use the CSV storage engine that MySQL provides in the source distribution? It is very easy to use, and there is no headaches. -- Partha Dutta Ok, here's what we're doing in a nutshell: Every day we fill out forms with backup times and tapes for various computer systems. Three forms a day with 70-100 fields each with all the same data. One Excel sheet and two word documents that end up as PDFs later, all of this is very time consuming. I'm creating a web app in PHP that lets you fill out one form, then click a button and it creates the PDFs for you. I've gotten that far, and now I'm trying to get it to create the Excel sheet too with just one click (fill in your desired date, click submit, and up pops your PDFs and spreadsheet. Thanks -Nick __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help converting tables to excel format
if you want to use excel i suppose you have also windows with more than 3 GO free disk. install win32 mysql put the files in the data directory of one existing database connect and use that database export data to an outfile by : select * from yourtable into outfile csv fields terminated by ';' open the csv with excel. Mathias Selon Tom Beidler [EMAIL PROTECTED]: I recently received some old database files from my ISP. I'm trying to convert the documents to excel format. When I try to load the .frm, .MYI and .MYD files on my OS X MySQL databases I get the following error. #5 - Out of memory (Needed 3024898224 bytes) Unfortunately I can't work with the tables. I'm wondering if it's a platform issue. The information was from a pilot log I setup for some paraglider friends. I am trying to get the information in excel format so they can keep the info. Can someone help convert these tables to Excel format? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help converting tables to excel format
No, I don't have Windows. I need some one to load my tables, export to excel and email me the excel docs. On Jun 7, 2005, at 11:54 PM, [EMAIL PROTECTED] wrote: if you want to use excel i suppose you have also windows with more than 3 GO free disk. install win32 mysql put the files in the data directory of one existing database connect and use that database export data to an outfile by : select * from yourtable into outfile csv fields terminated by ';' open the csv with excel. Mathias Selon Tom Beidler [EMAIL PROTECTED]: I recently received some old database files from my ISP. I'm trying to convert the documents to excel format. When I try to load the .frm, .MYI and .MYD files on my OS X MySQL databases I get the following error. #5 - Out of memory (Needed 3024898224 bytes) Unfortunately I can't work with the tables. I'm wondering if it's a platform issue. The information was from a pilot log I setup for some paraglider friends. I am trying to get the information in excel format so they can keep the info. Can someone help convert these tables to Excel format? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
help converting tables to excel format
I recently received some old database files from my ISP. I'm trying to convert the documents to excel format. When I try to load the .frm, .MYI and .MYD files on my OS X MySQL databases I get the following error. #5 - Out of memory (Needed 3024898224 bytes) Unfortunately I can't work with the tables. I'm wondering if it's a platform issue. The information was from a pilot log I setup for some paraglider friends. I am trying to get the information in excel format so they can keep the info. Can someone help convert these tables to Excel format?
RE: Write to a mysql table from Excel
[snip] Don't be so hasty to criticise. I took the original question to mean While I have an existing spreadsheet open in EXCEL, can I cause a selected row in a MySQL table to be updated?. This is quite a different question than How can I convert one sheet from an EXCEL file into a new MySQL table?. The later can easily be done several ways as you suggest, the former cannot. Can the former be done with ODBC? I didn't find any answers on google. [/snip] A. I was not being critical, the question was very open ended and subject to multiple interpretations, including yours. I was hoping to guide the OP to be able to ask a question with more specific information about what he wanted to do. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Write to a mysql table from Excel
Is it possible to write to a MySQL table from Excel? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Write to a mysql table from Excel
One problem I've encountered in the past creating CSV files from Excel is with fields that exceed 256 or 258 characters. The fields end up truncated in the CSV file. A script to directly access the data in Excel and move it to MySQL is appropriate, but can be a lot of work if you only need to do the import one or twice. Although this is somewhat convoluted it works for me. Start MS Access and create linked tables to you MySQL DB. Then import the data from Excel into Access. They're integrated fairly well so the import is basically a couple of button clicks. Then you're done. Bob -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: Friday, April 22, 2005 3:12 PM To: Huang, Ou; mysql@lists.mysql.com Subject: RE: Write to a mysql table from Excel [snip] Thank you for your reply. Can you provide more details on how to write to a MySQL table from Excel? I am a newbie to MySQL. Thanks. [/snip] You must be new to mailing lists too... http://catb.org/~esr/faqs/smart-questions.html You can save the excel as a CSV file and import that into MySQL, you can write a program that will do it for you using any one of several programming or scripting languages, you can use an ODBC connection. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Write to a mysql table from Excel
[snip] Is it possible to write to a MySQL table from Excel? [/snip] Sure. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Write to a mysql table from Excel
At 03:12 PM 4/22/05, Jay Blanchard wrote: [snip] Thank you for your reply. Can you provide more details on how to write to a MySQL table from Excel? I am a newbie to MySQL. Thanks. [/snip] You must be new to mailing lists too... http://catb.org/~esr/faqs/smart-questions.html You can save the excel as a CSV file and import that into MySQL, you can write a program that will do it for you using any one of several programming or scripting languages, you can use an ODBC connection. Don't be so hasty to criticise. I took the original question to mean While I have an existing spreadsheet open in EXCEL, can I cause a selected row in a MySQL table to be updated?. This is quite a different question than How can I convert one sheet from an EXCEL file into a new MySQL table?. The later can easily be done several ways as you suggest, the former cannot. Can the former be done with ODBC? I didn't find any answers on google. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Write to a mysql table from Excel
[snip] Thank you for your reply. Can you provide more details on how to write to a MySQL table from Excel? I am a newbie to MySQL. Thanks. [/snip] You must be new to mailing lists too... http://catb.org/~esr/faqs/smart-questions.html You can save the excel as a CSV file and import that into MySQL, you can write a program that will do it for you using any one of several programming or scripting languages, you can use an ODBC connection. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Write to a mysql table from Excel
Get a beginners book then rather than posting messages. -Original Message- From: Huang, Ou [mailto:[EMAIL PROTECTED] Sent: Friday, April 22, 2005 5:17 PM To: Jay Blanchard; mysql@lists.mysql.com Subject: RE: Write to a mysql table from Excel Oh well, I am just not smart as you are. Sorry, I am a new comer in the Geek's world. -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: Friday, April 22, 2005 3:12 PM To: Huang, Ou; mysql@lists.mysql.com Subject: RE: Write to a mysql table from Excel [snip] Thank you for your reply. Can you provide more details on how to write to a MySQL table from Excel? I am a newbie to MySQL. Thanks. [/snip] You must be new to mailing lists too... http://catb.org/~esr/faqs/smart-questions.html You can save the excel as a CSV file and import that into MySQL, you can write a program that will do it for you using any one of several programming or scripting languages, you can use an ODBC connection. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Write to a mysql table from Excel
Oh well, I am just not smart as you are. Sorry, I am a new comer in the Geek's world. -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: Friday, April 22, 2005 3:12 PM To: Huang, Ou; mysql@lists.mysql.com Subject: RE: Write to a mysql table from Excel [snip] Thank you for your reply. Can you provide more details on how to write to a MySQL table from Excel? I am a newbie to MySQL. Thanks. [/snip] You must be new to mailing lists too... http://catb.org/~esr/faqs/smart-questions.html You can save the excel as a CSV file and import that into MySQL, you can write a program that will do it for you using any one of several programming or scripting languages, you can use an ODBC connection. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Write to a mysql table from Excel
Thank you for your reply. Can you provide more details on how to write to a MySQL table from Excel? I am a newbie to MySQL. Thanks. -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: Friday, April 22, 2005 3:00 PM To: Huang, Ou; mysql@lists.mysql.com Subject: RE: Write to a mysql table from Excel [snip] Is it possible to write to a MySQL table from Excel? [/snip] Sure. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error when importing mysql table into excel via ODBC
Hello, I have a mysql ODBC driver 3.51.11.00 installed on a xp machine in order to connect to mysql server (on unix) remotely via the ODBC driver. The ODBC driver was working well. What I am trying to do is to import one of the mysql table into excel through New Database Query option in Excel. However, I was not able to import the table , an error was generated at the end of Query Wizard set up: Unknown table histsum_0 in field list The table is actually called histsum but I don't know why it is saying histsum_0. Has anyone ran into this error before? I have four tables in the same database, only this one couldn't be imported, the other three are working fine. I have searched over the internet but didn't find much info on this error. Any help would be appreciated! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Error when importing mysql table into excel via ODBC
I have had this same exact problem. It has forced me to use cancel when it prompts for the table and input the SQL manually. Basically I connect to the datasource and hit cancel to the next screen to allow me to enter the SQL. I would love to know why this happens as well. David Norman Wells Fargo Services -Original Message- From: Huang, Ou [mailto:[EMAIL PROTECTED] Sent: Thursday, April 14, 2005 1:33 PM To: mysql@lists.mysql.com Subject: Error when importing mysql table into excel via ODBC Hello, I have a mysql ODBC driver 3.51.11.00 installed on a xp machine in order to connect to mysql server (on unix) remotely via the ODBC driver. The ODBC driver was working well. What I am trying to do is to import one of the mysql table into excel through New Database Query option in Excel. However, I was not able to import the table , an error was generated at the end of Query Wizard set up: Unknown table histsum_0 in field list The table is actually called histsum but I don't know why it is saying histsum_0. Has anyone ran into this error before? I have four tables in the same database, only this one couldn't be imported, the other three are working fine. I have searched over the internet but didn't find much info on this error. Any help would be appreciated! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Time in VBA for Excel
Hi Dan, Sorry for causing additional work to getting the test setup, I will take your advice should I need to post these type of details again With regards to your solution, it works perfectly!!! Great thanks for putting the time and effort in to help sort this problem. Much appreciated. I didn't even know you could connect without a DSN in VBA. I'm basically teaching myself VBA from doing tutorials and experimenting. I'll take a look on the net and see how the DSN-less connection is done. Thanks for the heads up. Kind Regards [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Time in VBA for Excel
Hey Dan,
Thanks for the post and the code ideas. Unfortuantely I still can't get the
thing to work even when trying the CONCAT and CAST functions.
As per your request, here is the details of what I have got so far:
I'm running MySQL 4.1.9 with MyODBC 3.51. The table in question is the Users
table which has been setup as follows
USERS
=
UserID INT(11) NOT NULL AUTO_INCREMENT,
CompanyCode TEXT NOT NULL,
AssessmentCode TEXT NOT NULL,
UserCode TEXT NOT NULL,
Profession INT(5) NOT NULL DEFAULT '0',
Category INT(5) NOT NULL DEFAULT '0',
Username VARCHAR(10),
Password VARCHAR(10),
AssessmentDate DATE,
StartTime TIME,
EndTime TIME,
Completion ENUM('Y','N') NOT NULL DEFAULT 'N',
The sample data I'm working with is:
UserID :: 1
CompanyCode :: BEC-24ECMQP-TFXCFDOY-290105
AssessmentCode :: BEA-24ECMQP-O701-290105
UserCode :: BEU-24ECMQP-XR01-290105
Profession :: 12
Category :: 2
Username :: BEU1
Password :: password
AssessmentDate :: 2005-03-05
StartTime :: 12:33:59
EndTime :: 14:33:24
Completion :: Y
Here is my VBA Code that aims to get the data from the database and insert
it into a worksheet. I'm pretty new to VBA coding so please excuse any bad
form
==
Sub UserData()
Dim calcMode, updateMode
Dim ws As Worksheet
Dim conn As ADODB.Connection
Dim rec As New ADODB.Recordset
Dim qtTarget$, sqlQuery$, strAssCode$, strCoCode$
Dim i, intUsrCount%
calcMode = Application.Calculation
updateMode = Application.ScreenUpdating
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
strCoCode = ThisWorkbook.Worksheets(AssmntInfo).Range(C8).Value
strAssCode = ThisWorkbook.Worksheets(AssmntInfo).Range(M8).Value
Set ws = ThisWorkbook.Worksheets(UserInfo)
qtTarget = ws.Names(UsrList).RefersToLocal
Set conn = OpenDatabase
If conn Is Nothing Then Exit Sub
sqlQuery = SELECT ud.UserCode, ct.CategoryDesc, ud.StartTime,
ud.EndTime, TIMEDIFF(ud.EndTime,ud.StartTime) AS TotalTime _
FROM Users AS ud _
RIGHT JOIN Categories AS ct ON ud.Category = ct.CategoryID _
WHERE ud.AssessmentCode = ' strAssCode ' AND ud.CompanyCode = '
strCoCode ' _
ORDER BY ud.UserID ASC
rec.Open sqlQuery, conn
With ws
.Range(qtTarget).ClearContents
i = 7
intUsrCount = 1
While Not rec.EOF
.Cells(i, 2) = intUsrCount
.Cells(i, 3) = rec!UserCode
.Cells(i, 4) = rec!CategoryDesc
.Cells(i, 5) = rec!StartTime
.Cells(i, 6) = rec!EndTime
.Cells(i, 7) = rec!TotalTime
i = i + 1
intUsrCount = intUsrCount + 1
rec.MoveNext
Wend
intUsrCount = intUsrCount - 1
.Range(J6) = intUsrCount
.Activate
.Cells(7, 2).CurrentRegion.Select
.Names.Add Name:=UsrList, RefersTo:== + Selection.Address
End With
ThisWorkbook.Worksheets(AssmntInfo).Cells(18, 13) = intUsrCount
rec.Close
conn.Close
Application.Calculation = calcMode
Application.ScreenUpdating = updateMode
Application.Calculate
End Sub
Function OpenDatabase() As Connection
Const DBS$ = DSN=Assessment; _
Uid=username; _
Pwd=password;
Dim conn As ADODB.Connection
On Error Resume Next
Set conn = New ADODB.Connection
With conn
.CursorLocation = adUseClient
.Open DBS
End With
If Err 0 Then
MsgBox REPORT GRAPH GENERATOR ERROR: _
vbCrLf Could not connect to database. The report graph processing
will be stopped.
Exit Function
End If
Set OpenDatabase = conn
End Function
==
Any further ideas or help will be greatly appreciated.
Kind Regards
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Time in VBA for Excel
Dan Wareham wrote: Hey Dan, Thanks for the post and the code ideas. Unfortuantely I still can't get the thing to work even when trying the CONCAT and CAST functions. As per your request, here is the details of what I have got so far: I'm running MySQL 4.1.9 with MyODBC 3.51. The table in question is the Users table which has been setup as follows Before I even get to a solution, here are some tips for next time. When you post details of your setup, try to make it easy to reproduce your setup. It took me 15 minutes to get your test case running on my setup. - use mysql's show create table to create a valid 'create table' statement, or ( even better ): - use mysql's mysqldump to dump the contents of the table you want - don't use DSNs - convert it to a DNS-less connection - I don't have a DSN set up for your test case - if your test case involves data in a spreadsheet, either include the spreadsheet, or alter the code to not rely on this data - include ALL table definitions in the query - you didn't mention the Categories table in your post ( apart from in the SQL ) - don't switch between upper and lower case. You mention a 'USERS' table, and then your code refers to 'Users' --- Now as for the solution, I'm getting time-looking values by using the following: select ud.UserCode, cast(ud.StartTime as char) as StartTime, cast(ud.EndTime as char) as EndTime etc In my previous post I think I said to use 'cast(ud.StartTime as varchar(50))', which doesn't work - I wasn't near a MySQL install, but the basic idea is right, you just have to search for 'cast' on the mysql web site to get the correct usage ( varchar isn't one of the options you can cast to ). -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Time in VBA for Excel
Hello, I have a table in my database with two fields set as Time types. The issue I seem to have is when I use an ADO connection in VBA for Excel to select the two fields. Instead of the expected format HH:MM:SS held within my recordset, I get the current date in the format DD/MM/. Then should it reach a record where the time has been set to the default 00:00:00 I get an error Invalid Use of Null I tried outputting the data from the database to a webpage using scripting and I'm returned with the correct data as entered into the database. This is also confirmed when I view the data in the Query Browser. I've gone through every VBA site possible and found no clues so thought there might be a MySQL wizz that knows what is going on Any help or pointers would be greatly appreciated!! Thanks in advance [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Time in VBA for Excel
Dan Wareham wrote:
Hello,
I have a table in my database with two fields set as Time types.
The issue I seem to have is when I use an ADO connection in VBA for
Excel to select the two fields.
Instead of the expected format HH:MM:SS held within my recordset, I
get the current date in the format DD/MM/. Then should it reach a
record where the time has been set to the default 00:00:00 I get an
error Invalid Use of Null
I tried outputting the data from the database to a webpage using
scripting and I'm returned with the correct data as entered into the
database. This is also confirmed when I view the data in the Query
Browser.
I've gone through every VBA site possible and found no clues so
thought there might be a MySQL wizz that knows what is going on
Any help or pointers would be greatly appreciated!!
Thanks in advance
[EMAIL PROTECTED]
I'm not sure about Excel, but Access doesn't understand Time. You have
to give it DateTime, in the format: '30-12-1899 HH:MM:SS'. All DateTime
values starting with '30-12-1899' are interpreted as being a Time field.
Pretty strange stuff. Having said that, we have a number of scripts that
pull Time values into Excel without the issue you're experiencing. In
some places, we just chuck a single quote at the start of the Time value:
select concat('\'', SomeTimeColumn) as MyTime
You could also try casting your values as text:
select cast(SomeTimeColumn as varchar(20) as MyTime
... or something like that. Can't remember if that's exactly how cast()
works, but this is the general idea.
Otherwise post your table def / code and I'll test it out.
Dan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Import Excel data into table
Can anyone tell me a good way to import individual column data into a table? Is there a tool to assist with this? Thanks, Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Import Excel data into table
NaviCat Thanks Donny Lairson President 29 GunMuse Lane P.O. box 166 Lakewood NM 88254 http://www.gunmuse.com 469 228 2183 -Original Message- From: Steve Grosz [mailto:[EMAIL PROTECTED] Sent: Thursday, January 13, 2005 2:56 PM To: mysql@lists.mysql.com Subject: Import Excel data into table Can anyone tell me a good way to import individual column data into a table? Is there a tool to assist with this? Thanks, Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Import Excel data into table
I think you mean that you want to import .txt or .csv data into an mysql table... http://phpmyadmin.net can do that via a web form to upload plus has many other good admin features. -Original Message- From: Steve Grosz [mailto:[EMAIL PROTECTED] Sent: Thursday, January 13, 2005 1:56 PM To: mysql@lists.mysql.com Subject: Import Excel data into table Can anyone tell me a good way to import individual column data into a table? Is there a tool to assist with this? Thanks, Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Import Excel data into table
Steve Grosz [EMAIL PROTECTED] writes: Can anyone tell me a good way to import individual column data into a table? Is there a tool to assist with this? If your data is a .csv file [a.csv] similar to: 1,Steve,Grosz 2,Brad,Eacker Consistent with the output from Excel. You could use LOAD DATA LOCAL INFILE 'a.csv' INTO TABLE Users FIELDS TERMINATED BY ',' (id,firstName,lastName); Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Import Excel data into table [modified]
I forgot one element LOAD DATA LOCAL INFILE 'a.csv' INTO TABLE Users FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '' (id,firstName,lastName); Brad ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Excel 2 mysql
On Wednesday 08 December 2004 05:07, Patrick Sherrill wrote: David, Please provide the complete LOAD DATA INFILE command you used. mysql -e LOAD DATA INFILE '/home/dlubowa/clients.csv' INTO TABLE bush -p test thats what i did ... Pat... [EMAIL PROTECTED] CocoNet Corporation SW Florida's First ISP - Original Message - From: David Ziggy Lubowa [EMAIL PROTECTED] To: Eric Bergen [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, December 08, 2004 6:54 PM Subject: Re: Excel 2 mysql On Tuesday 07 December 2004 20:02, Eric Bergen wrote: The easiest way to turn excel into MySQL is to have excel save the file as a .csv (comma separated values) file and use the mysqlimport utility or a load data infile query to insert it into a table in cheers guys, i have managed to change my excel file into a .csv and done a LOAD DATA INFILE and dumped the data in the mysql db the only problem though that all the data is going into one field in the table yet it is comma separated with the exact columns. How can i solve this . All help is highly appreciated. -Z MySQL. -Eric On Tue, 7 Dec 2004 16:02:23 -0800, David Ziggy Lubowa [EMAIL PROTECTED] wrote: Hey guys , i know this has been discussed but looking through the archives i have more less hit a stand still , i have one big excel file which has data i would like to extract., Now i am no expert in perl neither am i in php , i have looked at some tools like navicat but i dont see where you actually start the app , if that is what happens. Any help is highly appreciated. cheers -Z -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Excel 2 mysql
On Thursday 09 December 2004 14:33, David Ziggy Lubowa wrote: On Wednesday 08 December 2004 05:07, Patrick Sherrill wrote: David, Please provide the complete LOAD DATA INFILE command you used. mysql -e LOAD DATA INFILE '/home/dlubowa/clients.csv' INTO TABLE bush -p test this is test output that is in my test table, this is how my info is mysql select * from bush; ++--+-+-+-++ | clients| location | service | PhoneNo | contact | emails | ++--+-+-+-++ || NULL | NULL| NULL| NULL| NULL | | CRS, Nsambya, HSDN 350 , 041-267733 / 077-7 | NULL | NULL| NULL| NULL| NULL | ++--+-+-+-++ sorry for the distorted info, but basically the CRS, Nsambya, HSDN 350 , 041-267733 / 077-7 are all under clients field, Yet the info is supposed to be distributed to all the columns of the table. What could i be doing wrong here. cheers thats what i did ... Pat... [EMAIL PROTECTED] CocoNet Corporation SW Florida's First ISP - Original Message - From: David Ziggy Lubowa [EMAIL PROTECTED] To: Eric Bergen [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, December 08, 2004 6:54 PM Subject: Re: Excel 2 mysql On Tuesday 07 December 2004 20:02, Eric Bergen wrote: The easiest way to turn excel into MySQL is to have excel save the file as a .csv (comma separated values) file and use the mysqlimport utility or a load data infile query to insert it into a table in cheers guys, i have managed to change my excel file into a .csv and done a LOAD DATA INFILE and dumped the data in the mysql db the only problem though that all the data is going into one field in the table yet it is comma separated with the exact columns. How can i solve this . All help is highly appreciated. -Z MySQL. -Eric On Tue, 7 Dec 2004 16:02:23 -0800, David Ziggy Lubowa [EMAIL PROTECTED] wrote: Hey guys , i know this has been discussed but looking through the archives i have more less hit a stand still , i have one big excel file which has data i would like to extract., Now i am no expert in perl neither am i in php , i have looked at some tools like navicat but i dont see where you actually start the app , if that is what happens. Any help is highly appreciated. cheers -Z -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Excel 2 mysql
Looks like i have solved the issue, thanks guys for the help this did the trick mysql load data infile '/home/dlubowa/test' into table bush fields terminated by ',' (clients,location,service,PhoneNo,contact,emails); -Z On Thursday 09 December 2004 15:54, David Ziggy Lubowa wrote: On Thursday 09 December 2004 14:33, David Ziggy Lubowa wrote: On Wednesday 08 December 2004 05:07, Patrick Sherrill wrote: David, Please provide the complete LOAD DATA INFILE command you used. mysql -e LOAD DATA INFILE '/home/dlubowa/clients.csv' INTO TABLE bush -p test this is test output that is in my test table, this is how my info is mysql select * from bush; ++--+-+ -+-++ | clients| location | service | PhoneNo | contact | emails | ++--+-+ -+-++ || NULL | NULL| NULL| NULL| NULL | | CRS, Nsambya, HSDN 350 , 041-267733 / 077-7 | NULL | NULL| NULL| NULL| NULL | ++--+-+ -+-++ sorry for the distorted info, but basically the CRS, Nsambya, HSDN 350 , 041-267733 / 077-7 are all under clients field, Yet the info is supposed to be distributed to all the columns of the table. What could i be doing wrong here. cheers thats what i did ... Pat... [EMAIL PROTECTED] CocoNet Corporation SW Florida's First ISP - Original Message - From: David Ziggy Lubowa [EMAIL PROTECTED] To: Eric Bergen [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, December 08, 2004 6:54 PM Subject: Re: Excel 2 mysql On Tuesday 07 December 2004 20:02, Eric Bergen wrote: The easiest way to turn excel into MySQL is to have excel save the file as a .csv (comma separated values) file and use the mysqlimport utility or a load data infile query to insert it into a table in cheers guys, i have managed to change my excel file into a .csv and done a LOAD DATA INFILE and dumped the data in the mysql db the only problem though that all the data is going into one field in the table yet it is comma separated with the exact columns. How can i solve this . All help is highly appreciated. -Z MySQL. -Eric On Tue, 7 Dec 2004 16:02:23 -0800, David Ziggy Lubowa [EMAIL PROTECTED] wrote: Hey guys , i know this has been discussed but looking through the archives i have more less hit a stand still , i have one big excel file which has data i would like to extract., Now i am no expert in perl neither am i in php , i have looked at some tools like navicat but i dont see where you actually start the app , if that is what happens. Any help is highly appreciated. cheers -Z -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Excel 2 mysql
David Ziggy Lubowa wrote: On Thursday 09 December 2004 14:33, David Ziggy Lubowa wrote: On Wednesday 08 December 2004 05:07, Patrick Sherrill wrote: David, Please provide the complete LOAD DATA INFILE command you used. mysql -e LOAD DATA INFILE '/home/dlubowa/clients.csv' INTO TABLE bush -p test this is test output that is in my test table, this is how my info is mysql select * from bush; ++--+-+-+-++ | clients| location | service | PhoneNo | contact | emails | ++--+-+-+-++ || NULL | NULL| NULL| NULL| NULL | | CRS, Nsambya, HSDN 350 , 041-267733 / 077-7 | NULL | NULL| NULL| NULL| NULL | ++--+-+-+-++ sorry for the distorted info, but basically the CRS, Nsambya, HSDN 350 , 041-267733 / 077-7 are all under clients field, Yet the info is supposed to be distributed to all the columns of the table. What could i be doing wrong here. - add FIELDS TERMINATED BY ',' ENCLOSED BY '' to your LOAD DATA INFILE - ian -- +---+ | Ian Sales Database Administrator | | | | eBuyer http://www.ebuyer.com | +---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
