none
Getting error in excel reading in c#. Error: 'Sale report 17th to 22nd Feb14$' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long. RRS feed

  • Question

  • I am reading excel file with name as “Weekly report 17th Feb'14 to 22nd Feb'14” and worksheet name as “Sale report 17th to 22nd Feb'14”.  I am getting Error: 'Sale report 17th to 22nd Feb14$' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.

    How can I read workbook without changing worksheet name?

    My code:

    Config:

    <add name="ExcelConnString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=&quot;Excel 8.0;HDR=NO;IMEX=1;TypeGuessRows=0;&quot;"/>

            public static System.Data.DataSet GetDataSetFromExcel(string sourceFilePath)
            {
                try
                {
                    string connectionString;
                    connectionString = String.Format(ConfigurationManager.ConnectionStrings["ExcelConnString"].ConnectionString,
                        sourceFilePath);
                    
                    using (OleDbConnection cn = new OleDbConnection(connectionString))
                    {
                        cn.Open();
                        System.Data.DataTable dbSchema = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
    
                        //get all the available sheets   
                        string[] workSheetNames = new String[dbSchema.Rows.Count];
                        if (dbSchema == null || dbSchema.Rows.Count < 1)
                        {
                            throw new Exception("Error: Could not determine the name of the first worksheet.");
                        }
    
                        DataSet ds = new DataSet();
                        System.Data.DataTable dt = new System.Data.DataTable();
                        int i = 0;
                        string rowName = "";
                        //iterate through all the sheets in the file
                        foreach (DataRow row in dbSchema.Rows)
                        {
                            //insert the sheet's name in the current element of the array   
                            //and remove the $ sign at the end  
                            rowName = row["TABLE_NAME"].ToString().Replace("'","");
                            if (rowName.EndsWith("$"))
                            {
                                workSheetNames[i] = rowName.Trim(new[] { '$' });
                                dt = GetWorksheet(workSheetNames[i], connectionString).Copy();
                                dt.TableName = workSheetNames[i]; // "dt" + i;
                                ds.Tables.Add(dt);
                                i++;
                            }
                        }
    
                        if (cn != null)
                        {
                            cn.Close();
                            cn.Dispose();
                        }
                        // return only 1st sheet
                        //if (ds != null) dbSchema = ds.Tables[0];
                        return ds;
                        //if (dbSchema != null) dbSchema.Dispose();
                        //return dbSchema;
                    }
                }
                catch (Exception e)
                {
                    try
                    {
                        return ImportExcelXML(sourceFilePath);
                    }
                    catch (Exception ex)
                    {
                        throw e;
                    }
                }
            }
    
            public static System.Data.DataTable GetWorksheet(string worksheetName, string connectionString)
            {
                try
                {
                    OleDbConnection con = new System.Data.OleDb.OleDbConnection(connectionString);
                    OleDbDataAdapter cmd = new System.Data.OleDb.OleDbDataAdapter(
                        //"select * from [" + worksheetName + "]", con);
                        "select * from [" + worksheetName + "$]", con);
    
                    con.Open();
                    System.Data.DataSet excelDataSet = new DataSet();
                    cmd.Fill(excelDataSet);
                    con.Close();
                    con.Dispose();
                    System.Data.DataTable dt = excelDataSet.Tables[0];
                    if (excelDataSet != null)
                        excelDataSet.Dispose();
                    return dt;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }


    Manoj Kumar

    Friday, April 11, 2014 8:44 PM

Answers

  • Hi,

    This time I used all codes from you and when I debugged it, I found the name is:

    'Sale report 17th to 22nd Feb''14$', so it will end with "'" and it will skip the “if “condition:

    if (rowName.EndsWith("$"))
    
                            {
    
                                workSheetNames[i] = rowName.Trim(new[] { '$' });
    
                                dt = GetWorksheet(workSheetNames[i], connectionString).Copy();
    
                                dt.TableName = workSheetNames[i]; // "dt" + i;
    
                                ds.Tables.Add(dt);
    
                                i++;
    
                            }
    

    So you may need to try with below logic:

                           

    rowName = row["TABLE_NAME"].ToString().Replace("''", "'");
    
                            if (rowName.EndsWith("$'"))
    
                            {
    
                                workSheetNames[i] = rowName.Trim(new[] { '$','\'' });
    
                                dt = GetWorksheet(workSheetNames[i], connectionString).Copy();
    
                                dt.TableName = workSheetNames[i]; // "dt" + i;
    
                                ds.Tables.Add(dt);
    
                                i++;
    
                            }
    

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, April 15, 2014 8:02 AM
    Moderator

All replies

  • Hello,

    >> How can I read workbook without changing worksheet name?

    You may need to comment this line:

    rowName = row["TABLE_NAME"].ToString().Replace("'",""); 

    After the replace operation, the table name which is used to query is changed and is different from the worksheets name, so the provider cannot find the correspondent table any more.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, April 14, 2014 3:11 AM
    Moderator
  • After removing replace same error. Doesn't work.

    Manoj Kumar

    Monday, April 14, 2014 8:29 PM
  • Hi,

    This time I used all codes from you and when I debugged it, I found the name is:

    'Sale report 17th to 22nd Feb''14$', so it will end with "'" and it will skip the “if “condition:

    if (rowName.EndsWith("$"))
    
                            {
    
                                workSheetNames[i] = rowName.Trim(new[] { '$' });
    
                                dt = GetWorksheet(workSheetNames[i], connectionString).Copy();
    
                                dt.TableName = workSheetNames[i]; // "dt" + i;
    
                                ds.Tables.Add(dt);
    
                                i++;
    
                            }
    

    So you may need to try with below logic:

                           

    rowName = row["TABLE_NAME"].ToString().Replace("''", "'");
    
                            if (rowName.EndsWith("$'"))
    
                            {
    
                                workSheetNames[i] = rowName.Trim(new[] { '$','\'' });
    
                                dt = GetWorksheet(workSheetNames[i], connectionString).Copy();
    
                                dt.TableName = workSheetNames[i]; // "dt" + i;
    
                                ds.Tables.Add(dt);
    
                                i++;
    
                            }
    

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, April 15, 2014 8:02 AM
    Moderator
  • Ya it worked. 

    Many many thanks to you.


    Manoj Kumar

    Tuesday, April 15, 2014 8:50 PM