none
Excel sheet with spaces RRS feed

  • Question

  • Anyone have suggestions on how to open an Excel sheet in C# using OleDBConnection when the sheet's name has spaces? Currently it gives me a "is not a valid name" error, if I remove the spaces, it's happy to open but unfortunately, it's not one we actually have a choice with spaces.

    I have tried with single quotes and escaped double quotes without luck

    "SELECT * FROM [" + tab + "$]"
    "SELECT * FROM ['" + tab + "$']"
    "SELECT * FROM [\"" + tab + "$\"]"

    Thanks

    Thursday, January 18, 2018 1:17 AM

All replies

  • Hello,

    You need to use the following, wrap the sheet name with apostrophes.

    SELECT F1,F2,F3,F4,F5,F6,F7 FROM ['Karen Payne$']

    Here is the actual sheet

    Here I've read the sheet in via OleDb

    Note in the ComboBox the apostrophes surrounding Karen Payne, that was done OleDb connection GetSchema method, getting each row's field TABLE_NAME into a List<string>.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, January 18, 2018 2:38 AM
    Moderator
  • Thanks for the response but I had tried that (my second example).

    Or does it have to be done via the GetSchema?

    Thursday, January 18, 2018 5:56 PM
  • If you look at my first reply, the ComboBox shows 'Karen Payne' which was returned from GetSchema, I did not add the apostrophes. So it does not have to come from GetSchema.

    What I had to do was place the dollar sign inside of 'Karen Payne' so it work as 'Karen Payne$'

    The window form in my first reply is from a MSDN code sample I wrote which does not handle spaces in sheet names. Normally it would show e.g. SELECT F1,F2 FROM [Sheet1$A1:G10] which selects a sheet then a range which does not work with spaces in the name.

    Now if you were usng say SpreadSheetLight or Gembox spreadsheet libraries (one or the other) this would be a non-issue, OleDb has restrictions and other than what I showed you are stuck with this space issue.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, January 18, 2018 6:22 PM
    Moderator
  • I would run the below code against your Excel Workbook (change the path in the connection string) to retrieve the actual names.

           private void ListExcelTables()
           {
    
               System.Data.OleDb.OleDbConnection ExcelConnection = null;
               try
               {
                   DataTable ExcelTables;
                   ExcelConnection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Users\\...\\Documents\\My Database\\Excel\\Book2007.xlsx;" +
                       "Extended Properties=\"Excel 12.0;HDR=No;\"");
                   ExcelConnection.Open();
                   ExcelTables = ExcelConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] {
                     null,
                     null,
                     null,
                     null});
                   int i = 0;
                   foreach (DataRow row in ExcelTables.Rows)
                   {
                       Console.WriteLine(row["TABLE_NAME"].ToString());
                       i++;
                   }
               }
               catch (Exception ex)
               {
                   MessageBox.Show(ex.Message);
               }
               finally
               {
                   ExcelConnection.Close();
               }
    
           }

    Here is an example of what the SELECT statement would look like when using a sheet name containing spaces (note there are no single quotes around the name):

    SELECT * from [Sheet With Spaces$]


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, January 18, 2018 7:29 PM
  • If you look at my first reply, the ComboBox shows 'Karen Payne' which was returned from GetSchema, I did not add the apostrophes. So it does not have to come from GetSchema.

    What I had to do was place the dollar sign inside of 'Karen Payne' so it work as 'Karen Payne$'

    Yes, that was all understood and hence my second statement added apostrophes to match but it didn't work.

    What I did do was a pre-step that cycled through sheets, removed the apostrophes and compared that to the name of the sheet I'm looking for. When they matched, I took the sheet name and plugged that in to my SELECT - which worked! So unless those apostrophes are actually an extended character rather than the standard quote, I have no idea how the two SELECTs were any different.

    Thursday, January 18, 2018 11:41 PM
  • Hi RyanAB,

    >>"is not a valid name"

    Please make sure the quantity of the space is the same between the Excel file and your code, for example, if your Excel file has two spaces, but in your code, you just put one space, then the exception named "is not a valid name" will be thrown.

    My test demo(can work with space):

            private void button1_Click(object sender, EventArgs e)
            {
                this.dataGridView1.DataSource = GetDataTable("Sheet 1", @"D:\Book1.xlsx");
            }
    
            protected DataTable GetDataTable(string strSheetName, string strExcelFileName)
            {
                string strConn = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source={0};" + "Extended Properties='Excel 8.0;HDR=NO;IMEX=1';", strExcelFileName);            
                string strExcel = string.Format("select * from [{0}$]", strSheetName);            
                DataSet ds = new DataSet();            
                OleDbConnection conn = new OleDbConnection(strConn);
                try
                {
                    conn.Open();
                    OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, strConn);
                    adapter.Fill(ds, strSheetName);
                }
                catch (Exception e)
                {
                    throw e;
                }
                finally
                {
                    conn.Close();
                }
                return ds.Tables[strSheetName];
            }

    Regards,

    Stanly


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Edited by Stanly Fan Friday, January 19, 2018 8:19 AM
    Friday, January 19, 2018 8:17 AM
  • I'm reasonably confident of that as I copied the sheet name
    Sunday, January 21, 2018 6:13 PM
  • Hi RyanAB,

    Have you tried the code above in your project? It works well for me.

    Or maybe you can provide the related code in your project so that I can test it.

    Regards,

    Stanly


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 22, 2018 6:49 AM
  • Hi RyanAB,

    Have you tried the code above in your project? It works well for me.

    Or maybe you can provide the related code in your project so that I can test it.

    Regards,

    Stanly


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Note my answer on Jan 18. I have got this working by comparing my expected name with the name of the sheet
    Monday, January 22, 2018 6:01 PM
  • Hi,

    Sorry I can not understand what you mean, do you mean you have solved the problem?

    If so, please mark the helpful reply as answer as this will help others looking for the same or similar issue down the road.

    If you still have not resolved it, please describe your current mistake for more details.

    Regards,

    Stanly


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, January 23, 2018 1:02 AM
  • I have created a workaround but not one that's really a long term answer to this problem so don't think any is actually an answer... not that Microsoft would fix it if it was a bug...
    Tuesday, January 23, 2018 7:32 PM