2

My VB.NET app currently allows me to convert CSV files to a datatable thanks to the code provided by David in this question I posted: Previous Question

Now I am trying to allow .XLSX files to be imported to a datatable as well. Currently the code looks like this:

Private Function ConvertCSVToDataTable(ByVal path As String) As DataTable
    Dim dt As DataTable = New DataTable()
    Using con As OleDb.OleDbConnection = New OleDb.OleDbConnection()
        Try
            If System.IO.Path.GetExtension(path) = ".csv" Then
                con.ConnectionString = String.Format("Provider={0};Data Source={1};Extended Properties=""Text;HDR=YES;FMT=Delimited""", "Microsoft.Jet.OLEDB.4.0", IO.Path.GetDirectoryName(path))
            ElseIf System.IO.Path.GetExtension(path) = ".xlsx" Then
                con.ConnectionString = String.Format("Provider={0};Data Source={1};Extended Properties=""Excel 12.0 XML;HDR=Yes;""", "Microsoft.ACE.OLEDB.12.0", IO.Path.GetDirectoryName(path))
            End If
            Using cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand("SELECT * FROM " & IO.Path.GetFileName(path), con)
                Using da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(cmd)
                    con.Open()
                    da.Fill(dt)
                    con.Close()
                End Using
            End Using
        Catch ex As Exception
            Console.WriteLine(ex.ToString())
        Finally
            If con IsNot Nothing AndAlso con.State = ConnectionState.Open Then
                con.Close()
            End If
        End Try
    End Using
    Return dt
End Function

However, when I run the code using the .XLSX file, I get the following error:

{"The Microsoft Office Access database engine cannot open or write to the file 'C:\Users\XSLXFilePath'. It is already opened exclusively by another user, or you need permission to view and write its data."}

The file is not open anywhere else to my knowledge. And the app also runs fine when .CSV file is put through it instead. How do I get the app to properly work for .XLSX, or any Excel file format?

AlanPear
  • 737
  • 1
  • 11
  • 32

2 Answers2

0

I think that the error is that from the connection string and the OLEDB Command:

ConnectionString

You don't have to use IO.Path.GetDirectoryName(path) it returns the directory name, you have to provide the file full path:

con.ConnectionString = String.Format("Provider={0};Data Source={1};Extended Properties=""Excel 12.0 XML;HDR=Yes;""", "Microsoft.ACE.OLEDB.12.0", path)

Refer to this link for excel connectionstring generation function: import data from excel 2003 to dataTable

OLEDB Command

You must provide the Worksheet name in the Command instead of the Filename:

 Using cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand("SELECT * FROM [Sheet1$]" , con)

If the Sheet names is dynamic and you have to get the first sheet in the excel file:

Dim dbSchema as DataTable = con.GetOleDbSchemaTable (OleDbSchemaGuid.Tables, null)

Dim firstSheetname as String = dbSchema.Rows(0)("TABLE_NAME").ToString
Using cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand("SELECT * FROM [" & firstSheetname & "]" , con)

References

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • I tried eliminating the getdirectoryname but it won't read the data. Note, I use a similar string with CSV and it reads fine that way. Changing the oledb command also does not work. – AlanPear Jan 07 '18 at 22:43
  • 1
    actually, I'm getting a new error which is: System.Data.OleDb.OleDbException (0x80040E14): Syntax error in FROM clause. I'm assuming it doesn't like the name of the worksheet. – AlanPear Jan 07 '18 at 22:51
  • @AlanPear you have to put the worksheetname inside of 2 brackets `"SELECT * FROM [Sheet1$]"`. Check my answer update – Hadi Jan 08 '18 at 04:48
  • 1
    this worked. However, I'm having a weird issue where my form shrinks after converting the excel file. Also, I may post this as a separate question, if my CSV or Excel file has a period in the filename, or is over 60 chars it will not read the file. any idea why these things are happening? – AlanPear Jan 08 '18 at 14:21
  • You have to provide the application code to identify the problem. So it is better to ask a new question. Happy to get your things solved. Good luck – Hadi Jan 08 '18 at 14:54
  • @AlanPear If you [use the OleDbConnectionStringBuilder class](https://stackoverflow.com/a/11349921/1115360) to create your connection string then that will take care of any quotes needed to make the filename work. – Andrew Morton Oct 25 '19 at 09:09
0

Use can use the following connection string for .xlsx file. I have used it and working fine.

P_FIle = ( File Name with path )

P_Con_Str = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & P_File & ";Extended Properties=""Excel 12.0 XML;HDR=Yes;"""
Ihor Patsian
  • 1,288
  • 2
  • 15
  • 25
Milind Paranjape
  • 1