MySQL->ODBC->Excel: DataSource.Error: You have an error in your SQL syntax;

Question

I've built a pretty basic database using MySQL Workbench - and I've uploaded a bunch of data. Now I am trying to pull data from my database into MS Excel using an ODBC connection via Excel's Get Data. However, after setting up the ODBC connection, I'm getting the following error for ALL my data fields/columns:

DataSource.Error: ODBC: ERROR [42000] [MySQL][ODBC 8.0(w) Driver][mysqld-8.0.26]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.columns_priv' at line 8 Details: DataSourceKind=Odbc DataSourcePath=dsn=MySQL Local OdbcErrors=[Table]

I've searched all over for ideas - I've even tried adding text: SET sql_mode='ANSI_QUOTES' into the Initial Statement field in the Details option in the ODBC connector, as I thought it could be quotes related?

My versions: MySQL Workbench 8.0.26, MySQL Connector/ODBC 8.0.26, MS Excel 2019

Can anyone see where I'm going wrong, or how I can fix this?

Any help would be most appreciated. Thanks,

Stevie

nbk nbk · Accepted Answer · 2021-07-24T23:53:40

i tested it with my mysql 8.0.26

i used

MySQL ODBC UNICODE driver

My Server is configured to run in legacy mode on local host, but when youz can conect via Workbench, you can use tcpip and localhost

The steps are

Connecting Excel to MySQL with Data Connection Wizard (Legacy Wizard)

Start a new worksheet
Go to the Data tab.
Click From Other Sources, and then click From Data Connection Wizard.
In the opened dialog, you select ODBC DSN and click Next to continue.
Now select a data source you want to connect to, and click Next.
To connect to the table containing the wanted data, select its name and click Next to enter and save information about your new file or click Finish.
In the Import data dialog, you can select the way your data will be viewed in Excel and the place where to put it in the worksheet, and click OK.
The selected data are displayed in the prior empty Excel worksheet.

MySQL->ODBC->Excel: DataSource.Error: You have an error in your SQL syntax;

2 Answers