开发者

Create Table with a Hyphen in the Name Using Excel ACE OLEDB Connection

开发者 https://www.devze.com 2023-03-07 14:04 出处:网络
Has anyone successfully used a CREATE TABLE statement on an ACE OLEDB connection to an Excel 2007 workbook where the table has a hyphen in the name? What I have at the moment is something like

Has anyone successfully used a CREATE TABLE statement on an ACE OLEDB connection to an Excel 2007 workbook where the table has a hyphen in the name? What I have at the moment is something like

using(OleDbConnection c = new OleDbConnection())
{
    c.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\"book.xlsx\";Extended Properties=\"Excel 12.0 Xml;HDR=YES\";";
    c.Open();
    using(OleDbCommand cmd = c.CreateCommand())
    {
        cmd.CommandText = "CREATE TABLE [EN-GB] ([Brand] TEXT,[Text] TEXT, [SortOrder] TEXT, [Image] TEXT);";
        cmd.ExecuteNonQuery();
    }               
}

The table name is a locale and so requires a hyphen (the reason I need this is that 开发者_JAVA百科the output workbook is imported into a SQL Server instance and the table must fit the schema of that database). Using the above code results in a sheet name of "EN_GB." Other attempts at using various quotes or back-ticks results in either the same or a workbook that Excel recovers when opened.

I realise that I can open the workbook later and rename the sheet name but I was hoping there was something I could change in the SQL statement.


I have a static method that I created and call with all of my table names and column names. I pass the method the string, and it replaces any of the characters I have specified in my array with an underscore '_':

internal static string SafeName(string text) {
  if (!String.IsNullOrEmpty(text)) {
    const string array = "~`!@#$%^&*()-[]{}\\|<>,.;'\":/?";
    for (int i = 0; i < array.Length; i++) {
      text = text.Replace(array[i], '_');
    }
    return text;
  }
  throw new NullReferenceException();
}

I rarely use any hard coded SQL like you have shown above in your "CREATE" statement.

I hope this helps.

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号