Read .xlsx file

I'm trying to read .xlsx file and populate a DataTable.
I've added test.xlsx  file to the project.
Error: The Microsoft Office Access database engine could not find the object 'Sheet1'.  Make sure the object exists and that you spell its name and the path name correctly.

  <connectionStrings>
    <add name="xlsx" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=test.xlsx;Extended Properties=Excel 12.0"/>  
  </connectionStrings>

Private xlsx As String = ConfigurationManager.ConnectionStrings("xlsx").ConnectionString

Public Function GetData() as DataTable
 Dim dt As New DataTable
        Dim oleDbCon As New OleDbConnection(xlsx)
        oleDbCon.Open()
        Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM [Sheet1]", oleDbCon)
        Dim oleda As OleDbDataAdapter = New OleDbDataAdapter(cmd)
        oleda.Fill(dt)
oleDbCon.Close()
return dt
End Sub

Any help would be greatly appreciated!!
shahjagatAsked:
Who is Participating?
 
Imran Javed ZiaConsultant Software Engineer - .NET ArchitectCommented:
Please try this

Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM Sheet1$", oleDbCon)

further more use connection string as
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=test.xlsx;Extended Properties=""Excel 12.0;HDR=Yes;IMEX=2"
0
 
lenordisteCommented:
as suggested by IJZ, what's important is the missing "$" at the end of the sheet name, so this would work also:
SELECT * FROM [Sheet1$]

Open in new window

0
 
Éric MoreauSenior .Net ConsultantCommented:
I prefer to use tools such as http://www.aspose.com/categories/.net-components/aspose.cells-for-.net/default.aspx to manipulate Excel files
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.