VB 2010 Express - Import a .txt file content into and Access .mbd table.


  • Hello,

    I am looking for a solution in VB code to import values from a .txt file into a specific table in an .mdb file.

    The text file is strucured like this :


    Column 1 is the first 2 characters, Column 2 is the next 5 characters, Column 3 is the next 10, and Column 4 the next 4. Each line is a row of fixed length : 21 chars. The number of rows varies from a .txt file to another.

    I do already have the .mdb file integrated. It is just the structure with blank tables, and the actual data comes in in txt files as described here above.

    My end goal is, of course, to be able to click a button (on per table in my .mdb file), select a file, and have it's contents written to the corresponding table in the Access database.

    So far, i am struggling around with

    Public Structure Struct
        <VBFixedString(2)> Dim Col1 As String
        <VBFixedString(5)> Dim Col2 As String
        <VBFixedString(10)> Dim Col3 As String
        <VBFixedString(4)> Dim Col4 As String
    End Structure

    I figured out how to pull out one value, but i have absolutely no idea on how to write all this file's content into my .mdb table.

    Could anyone help ? Thanks in any case !

    Sunday, April 22, 2012 9:00 PM


  • Jubar,

    Take it a bit at a time - for now, just reading the lines of ASCII text in.

    Before I continue, Paul Clement - an MVP here in this forum - has shown before how you can directly import text files into a database. He's quite good with databases and for that matter, pretty much everything else I've seen! He may look in tomorrow and offer other suggestions. But that aside for now, let's just talk about getting your text file like you want it.

    I sort of think I kind of undestand what you're doing ;-), and assuming I'm correct in that assessment, consider doing something like this: In your class-level declaration of a structure (or class, my preference), just set it up something like the following:

    Public Class TextFieldsFromFile
        Public Col1 As String = "--"
        Public Col2 As String = "--"
    End Class

    Private listOfTextFields As New ListOf(TextFieldsFromFile)

    That's just pseudo-code there obviously, so hopefully you can make sense of it if I misspelled something, but essentially you're setting up your "object structure" and then declaring a list OF that object structure (either a list of instances of the structure or a list of instances of the class).

    Next, use a StreamReader to read the text file in, line-by-line so you want use the ".ReadLine" method. What I usually do after that is, per line read in, test to see if the trim value is an empty string. If not, now you can process it.

    If it's not a blank string, now create a new instance of that structure (or class) and begin pulling things out using the String.Substring method. Look into that - you can specify the start position (zero-based) and the length. Add those to your newly create instance and when done with all of them, add that instance to your list OF that structure (or class).

    I always put a StreamReader into a "Using" block and I would encourage you to do the same. Once out of the Using block, you have a list of the instances of those structures (or classes) which you created but now the file is closed and disposed, the reader is closed and disposed, and you can then use that list to add into your datatable.

    Make sense? :)

    Please call me Frank :)

    Sunday, April 22, 2012 10:54 PM