Create Table with a Hyphen in the Name Using Excel ACE OLEDB Connection
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.
精彩评论