Select * from [sheet1$] what if sheet name is unknown

Question

0.00/5 (No votes)

See more:

I have many Excel files which I need to import to access several times. The sheet names are also different, like it's not sheet every time.

Can you please help me with the below code and make so that it is possible to access the files without knowing the sheet name, so that I can transfer data?

What I have tried:

VB

Dim connect As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + _filename + ";Extended Properties=Excel 8.0;"
Using conn As New OleDbConnection(connect)
	Using cmd As New OleDbCommand()
		cmd.Connection = conn
		cmd.CommandText = "INSERT INTO [MS Access;Database=" + DBPath + "].[test] SELECT * FROM [Sheet1$]"
		conn.Open()
		cmd.ExecuteNonQuery()
	End Using

Posted 27-Jan-22 9:57am

Member 15515171

Updated 28-Jan-22 6:05am

Sean Ewington

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

M Imran Ansari · Answer 1 · 2022-01-27T11:02:00

One way is you get all the worksheets of your workbook and after that traverse the workbook and insert your data like:

Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook excelBook = xlApp.Workbooks.Open("D:\\YourWorkbook.xlsx");

foreach (Microsoft.Office.Interop.Excel.Worksheet wSheet in excelBook.Worksheets)
{
         cmd.Connection = conn
         cmd.CommandText = "INSERT INTO [MS Access;Database=" + DBPath + "].[test] SELECT * FROM [" + wSheet.Name + "]";
}

Second way is, If your worksheets name is same as your database tables then you can get schema of you database and traverse on the tables like:

var excelWorksheets = conn.GetSchema("Tables");
foreach (var row in excelWorksheets.Rows)
{
        cmd.Connection = conn
        cmd.CommandText = "INSERT INTO [MS Access;Database=" + DBPath + "].[test] SELECT * FROM [" + row["TABLE_NAME"] + "]";
}

Choice is yours. Happy Coding!!

Richard MacCutchan · Answer 2 · 2022-01-28T06:05:00

You can get the names of all the WorkSheets in OLEDB with the following:

C#

oleDbConnection = new OleDbConnection(ConnectionString);
oleDbConnection.Open();
DataTable dtSchema = oleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
// Use LINQ to get the table (i.e. sheet) names from the spreadsheet schema
List<object> worksheets = (from dr in dtSchema.AsEnumerable()   // returns an IEnumerable<DataRow> collection
                           select dr["TABLE_NAME"]).ToList();   // gets the sheet name from each schema entry

List<string> sheets = new List<string>();
foreach (string name in worksheets)
{
    if (!name.StartsWith("_"))
    {
        sheets.Add(name);
    }
}

I know this is C# code, but VB.NET is much the same.

Select * from [sheet1$] what if sheet name is unknown

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0