Click here to Skip to main content
14,837,944 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello,

I have a Excel file with sheetname, "Test- Sheet!" from where I am importing data to a sql table.

I am using Excel Oledb for this.

string firstSheetName = "Test- Sheet!"

OleDbAdapter oleAdapter = new OleDbAdapter ("SELECT * FROM [" + firstSheetName + "$]", dbConnection);

I am getting error saying, "[Test- Sheet!$] is NOT a VALID Name. Make sure that it does not include Invalid characters or punctuation and that it is not too long"

Can anyone suggest a solution to this issue?

Thanks
Posted

Try using IMEX in Connection String.

See below link for details:
http://www.connectionstrings.com/excel[^]
   
v2
Comments
vasini 13-Apr-11 10:34am
   
Thanks for your response.

I used IMEX. Its considering the Column names with special characters. But I need the sheet names with special characters. I am using OLEDB and importing the data from that sheet which has special characters in it.

Can you pls help me in getting sheet names?

Thanks
In ur case ! represents by _ in .net like

VB
string firstSheetName = "Test- Sheet_"


Why u r harcoding excelname
U can retreive it using like this

C#
dtExcelsheetname = excelcon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
        string[] excelSheets = new String[dtExcelsheetname.Rows.Count];
        int j = 0;
        foreach (DataRow row in dtExcelsheetname.Rows)
        {
            excelSheets[j] = row["TABLE_NAME"].ToString();
            j++;
        }
   
Comments
vasini 13-Apr-11 10:35am
   
I am using the same to get the sheet names. But one of my sheet name has special characters in it. Its faling to read that sheet name by using OLEDB.

string firstSheetName = "Test- Sheet!"

OleDbAdapter oleAdapter = new OleDbAdapter ("SELECT * FROM [" + firstSheetName + "$]", dbConnection);

Do you have any idea for this?
Thanks
Ashishmau 15-Apr-11 0:16am
   
u replace "Test- Sheet!" with "Test- Sheet_"
vasini 19-Apr-11 11:58am
   
Thank you so Much. It worked for me.
Ashishmau 20-Apr-11 0:17am
   
then accept the solution if it works
Vinod_D 5-Apr-13 9:18am
   
Thank for the solution. it worked for me.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900