How do I import a text file into a DataTable?

[Christhemist]

I keep getting this error: "The Microsoft Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data."

Obviously I have checked if the file is open, rebooted, etc. It is not open and it has full permissions!


Here is my code: (Highlighted is where the error is occurring)

 
        Dim PathToUse As String = "C:\WORK\A_TEST\"

        Dim MyConnection As System.Data.OleDb.OleDbConnection

        Dim MyCommand As System.Data.OleDb.OleDbDataAdapter

        MyConnection = New System.Data.OleDb.OleDbConnection _
            ("provider=Microsoft.ACE.OleDB.12.0;Data Source='" & PathToUse & "';Extended Properties=Text;")

        MyCommand = New System.Data.OleDb.OleDbDataAdapter _
                ("select * from [CommaDelimitedFile]", MyConnection)

        MyCommand.TableMappings.Add("Table", "TestTable")

        MyCommand.Fill(AddressListDatatable)

[Christhemist]

I figured out why it wasn't working. I forgot the file extension here:

 MyCommand = New System.Data.OleDb.OleDbDataAdapter _
                ("select * from [CommaDelimitedFile.txt]", MyConnection)

[jmcilhinney]

Unrelated to your question but I would make two recommendations:

1. Learn to import namespaces. Prefixing System.Data.OleDb to type names over and over again actually makes your code harder to read by increasing clutter. I notice that you just use String rather than System.String, so why qualify all the OleDb types? The System.Data namespace is already imported by default so, even without doing anything else, you can at least reduce the qualifying namespace to just OleDb instead of System.Data.OleDb. If you import the System.Data.OleDb namespace though, either at the file level or the project level, then you can refer to types in that namespace unqualified.

2. If you're going to connect to files created using the newer Office formats (DOCX, XLSX, ACCDB, etc) then you need to use the ACE engine. If you're connecting to older Office formats (DOC, XLS, MDB, etc) or more standard formats (CSV, TXT, etc) then you can use the Jet engine. Because Jet is effectively part of Windows these days, every user will have it on their system even if they haven't installed Office, so it is the safer choice in situations where it can be used.

[Christhemist]

I've made your suggested changes below, good advice!

Quick question for ya, when I import a tab delimited file (as seen below) it combines the entire row into the first column.... am I doing something wrong??

Odd that when I use the above code for a comma delimited file it works as intended...

        Dim PathToUse As String = "C:\WORK\A_TEST\"

        Dim MyConnection As OleDbConnection

        Dim MyCommand As OleDbDataAdapter

        MyConnection = New OleDbConnection _
           ("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & PathToUse & "';Extended Properties='Text;HDR=Yes;FMT=Delimited(" & vbTab & ")'")

        MyCommand = New OleDbDataAdapter _
                ("select * from [TabDelimitedFile.txt]", MyConnection)

        MyCommand.TableMappings.Add("Table", "TestTable")

        MyCommand.Fill(AddressListDatatable)

[techgnome]

Yes...your FMT is incorrect in the connectionstring... I believe it should be FMT=Delimited(Tab) ... the literal string "tab" not the tab character.

-tg

[Christhemist]

That had the same affect...

This is an separate issue than the original thread. I have created a new thread to address this issue, located here: http://www.vbforums.com/showthread.p...63#post5290363