dcsimg
Results 1 to 6 of 6
  1. #1

    Thread Starter
    Lively Member Christhemist's Avatar
    Join Date
    Sep 2016
    Location
    Nevada
    Posts
    93

    How do I import a text file into a DataTable?

    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)

    Code:
     
            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)
    Last edited by Christhemist; May 18th, 2018 at 11:21 AM.

  2. #2

    Thread Starter
    Lively Member Christhemist's Avatar
    Join Date
    Sep 2016
    Location
    Nevada
    Posts
    93

    Re: How do I import a text file into a DataTable?

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

    Code:
     MyCommand = New System.Data.OleDb.OleDbDataAdapter _
                    ("select * from [CommaDelimitedFile.txt]", MyConnection)
    Last edited by Christhemist; May 18th, 2018 at 11:21 AM.

  3. #3
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    98,682

    Re: How do I import a text file into a DataTable?

    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.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  4. #4

    Thread Starter
    Lively Member Christhemist's Avatar
    Join Date
    Sep 2016
    Location
    Nevada
    Posts
    93

    Re: How do I import a text file into a DataTable?

    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...

    Code:
            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)
    Last edited by Christhemist; May 18th, 2018 at 11:24 AM.

  5. #5
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,608

    Re: How do I import a text file into a DataTable?

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  6. #6

    Thread Starter
    Lively Member Christhemist's Avatar
    Join Date
    Sep 2016
    Location
    Nevada
    Posts
    93

    Re: How do I import a text file into a DataTable?

    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width


×
We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.