Search code examples
sqlexcelescapingodbcdouble-quotes

How to escape double quotes in a column name when querying Excel file using ODBC

I need to query an Excel file using ODBC.
A sheet, Sheet1 has a column that contains double quotes. For example, he"llo.

Querying the sheet without that column yields correct results, for instance:

SELECT "test" FROM "Sheet1$"

But when a column contains double quotes, I'm unable to query it. I've read somewhere you can escape those quotes by adding double quotes, but the following query SELECT "he""llo" FROM "Sheet1$" yields the following error:

Syntax error (missing operator) in query expression '[he][llo]'.

I have tried all sorts of ways to escape that character but to no avail.
Please note that changing the data source is not an option, or at least a last resort option.

Thanks in advance!

Solution

  • Found the solution.
    When a column contains double quotes, you can change the quoted identifier to back ticks. You can even mix them with regular quoting for other columns such as:

    SELECT "test", `he"llo` FROM "Sheet1$"

    Hope this helps someone else as well!