Error copying table data
Posted by: Paul Frank
Date: August 10, 2018 01:28PM
Here's an example of the larger problem I am encountering.
I have a two column (int, varchar) MySQL table with 3 rows of data in it. When I attempt to import or edit the data in Excel using the MySQL for Excel plugin, I get error message: "Error. Error copying table data. Input string was not in a correct format." I hit OK, and when the Preview MySQL Data window pops up, it shows the columns ordered incorrectly -- in alphabetical order of column name. They should be ordered by column ordinal position of the columns, which seems to be (my guess) how the table data is coming in.
I just confirmed my conjecture: I created a two varchar column table with
"create table t5 (c1 varchar(5), b1 varchar(5));"
then insert one row:
"insert into t5 (c1,b1) values ("hello", "world");"
The I go to Excel and edit the table, and the preview comes up:
It's loading metadata and data in different orders, as I conjectured, above.
I have a database of about a dozen tables, and I have multiple users who need to hit this database via Excel with the plugin.
The thing that makes this so weird is that when I started out, I built my database from a build script I had prepared. Then we populated the tables through Excel with the plugin. The column mapping worked perfectly for all tables. My problems began when I altered the tables in MySQL Workbench using the table modification frame that comes up when you click the teeny wrench icon to the right of the table name (in the schemas navigator). I added a column, hit the Apply button: in the Apply SQL Script to Database dialogue that followed, I hit Apply and Finish. That action seemed to have made some kind of change to the server, because now all tables get mapped incorrectly -- even new ones I create from SQL DDL statements.
I am using:
• MySQL Server 8.0.11
• MySQL Workbench 8.0.11 RC
• MySQL for Excel 1.3.7
• MySQL Shell 8.0.11
• MySQL Router 8.0.11
• Connector/ODBC 8.0.11
• Connector/NET 8.0.11
One google hit said that the only solution is to make the ordinal position match the alphabetical order of the column names. But that is just too hokey, and my program manager doesn't like it.
Thank you for any help you can offer.
Error copying table data
August 10, 2018 01:28PM
September 11, 2018 02:15PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.