OleDbCommand use brackets '[' ']' in a query as table name

Question

I have a Excel file which contains the following column names: name,Display name [de-DE],Comment [de-DE].

If I use this query, Insert into [foo$] ([Name],[Display name [de-DE]],[Comment [de-DE]], I get an exception:.

Syntax error in INSERT INTO statement

How do I have to escape the brackets to use them in the query?

sample code

var connectionString = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\"{Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "foo.xlsx")}\";Extended Properties=\"Excel 12.0;HDR=YES;\"";

using (OleDbConnection conn = new OleDbConnection(connectionString))
{
    conn.Open();
    using (OleDbCommand cmd = new OleDbCommand())
    {
        try
        {
            cmd.Connection = conn;
            cmd.CommandText = @"Insert into [Hmi Tags$] ([Name],[Display name [de-DE]],[Comment [de-DE]]) 
                                VALUES ('foo1','foo2','foo3');";
            cmd.ExecuteNonQuery();
            Console.WriteLine("success");

        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
    }
}


Console.ReadKey();

workaround

The only working solution so far is based on this answer: https://stackoverflow.com/a/12499797/6229375

One option is to remove the column declaration completely and just do insert into..

Try this: `[Display name [de-DE]]]` or that: `[Display name [[de-DE]]]` — Longoon12000, Nov 15 '19 at 09:47
Maybe this post helps you. https://stackoverflow.com/questions/439495/how-can-i-escape-square-brackets-in-a-like-clause — dns_nx, Nov 15 '19 at 09:49

score 0 · Answer 1 · answered Nov 15 '19 at 09:54

0

Try something like this

Insert into [foo$] ('[Name]','[Display name [de-DE]]','[Comment [de-DE]]',

answered Nov 15 '19 at 09:54

Mukul Keshari

495
2
7

@DominicJonas Can you please add your full insert query? exactly what are you using? – Mukul Keshari Nov 15 '19 at 10:15
I have added additional infos to my question including sample – Dominic Jonas Nov 15 '19 at 10:25

OleDbCommand use brackets '[' ']' in a query as table name

sample code

workaround

1 Answers1