I need to import into MSAccess from an undelimited text file.
The file has 4 fields: AABBCCCD and 450 lines (same format).

where BB is the unique field in the record and the name of the table where I want to put CCC (that would make 450 tables) is this sane?

or

BB is the unique field in the record and the name of the filed in a table that contains 450 fields.

Which way is better?
How can I accomplish this ?
Thanks a lot.

Can you clarify that a little?

In scenario A you would have 450 tables each of which contains only two values (not sane).

Scenario B doesn't seem to make any sense.

Creating a single table with the following structure is certainly possible.

          Col 1 Col 2
Row BB CCC
Row bb ccc
Row BB CCC
Row bb ccc
Row BB CCC
Row bb ccc
Row BB CCC
Row bb ccc
Row BB CCC

where column 1 is your unique(BB) value and column 2 is your data(CCC) value. If you have something like that in mind I can post the code that printed the above table from a text file.

Bob

That's pretty much what I'm looing for...
What command would I have to use ?

I have used the open for output and print# statements for creating the file. Now I dont know how to read and put it on the table.

Thanks a lot.


----
I promise to be a good boy mummy... -Chucky

Hello,

Call ImportFile, passing it the path to your text file. The code parses your file lines into the two substrings you need, fills an array of user-defined types with the substring values, creates a new table, and then loads the array of UDTs into the table. (it prints the array of UDT values to the debug window along the way).

HTH,

Bob

Option Compare Database

' User-Defined Type (UDT) definition
Type Substring
    UniqueValue As String ' BB
    DataValue As String ' CCC
End Type


Sub ImportFile(strSourceFile As String)
    ' Call with ImportFile "C:\ArrayValues.txt"
    Dim intIndex As Integer

    ' Dynamic array of text file lines
    Dim astrLines() As String

    intIndex = 0

    ' Import text file
    intFileDesc = FreeFile
    Open strSourceFile For Input As #intFileDesc
    Do While Not EOF(intFileDesc)
        Line Input #intFileDesc, strTextLine

        ReDim Preserve astrLines(intIndex)
        astrLines(intIndex) = strTextLine

        intIndex = intIndex + 1
    Loop

    Close #intFileDesc

    ' Pass array of file lines to parsing function and
    ' store 2 substrings (BB and CCC) in a UDT
    Call ParseTextFileLine(astrLines())

End Sub

Sub ParseTextFileLine(a() As String)
    Dim subSubstring As Substring
    Dim boolOK As Boolean

    ' Dynamic array of Substring types
    Dim asubSubstrings() As Substring

    For x = LBound(a) To UBound(a)
        ' Get BB value
        subSubstring.UniqueValue = Mid$(a(x), 3, 2)
        ' Get CCC value
        subSubstring.DataValue = Mid$(a(x), 5, 3)

        ' Add substring to substring array
        ReDim Preserve asubSubstrings(x)
        asubSubstrings(x) = subSubstring
    Next x

    ' Print UDT array values to debug window
    Call PrintUDTArray(asubSubstrings())

    ' Create new import table
    Call CreateTable

    ' Load UDT array into table, returns True and displays
    ' message box if import was successful.
    If PopulateTable(asubSubstrings()) Then
        MsgBox Prompt:="Import completed successfully.", _
              Buttons:=vbInformation Or vbOKOnly, _
              Title:="Import Status"
    End If
End Sub

Sub PrintUDTArray(a() As Substring)
    ' Print UDT array values to debug window
    Dim temp As String

    temp = " Col 1 Col 2"
    Debug.Print Space$(9) & temp

    For x = LBound(a) To UBound(a)
        temp = "Row " & Space$(6) & a(x).UniqueValue & _
                        Space$(5) & a(x).DataValue & " "
        Debug.Print temp
    Next

End Sub

Sub CreateTable()
    Dim strSQL As String

    strSQL = "CREATE TABLE tblImport(" & _
                "BB CHAR(2) NOT NULL PRIMARY KEY, " & _
                "CCC CHAR(3) NOT NULL);"

    Debug.Print strSQL

    DoCmd.RunSQL (strSQL)

End Sub

Function PopulateTable(a() As Substring) As Boolean
    On Error GoTo ErrorHandler

    Dim rst As ADODB.Recordset
    Dim cnn As ADODB.Connection

    Set cnn = CurrentProject.Connection
    Set rst = New ADODB.Recordset
    rst.Open "tblImport", cnn, adOpenKeyset, adLockOptimistic, adCmdTable

    ' Add substrin values to new table
    With rst
        If (.EOF And .BOF) Then ' Table is empty
            For x = LBound(a) To UBound(a)
                 ' Add new records
                 rst.AddNew Array("BB", "CCC"), _
                            Array(a(x).UniqueValue, a(x).DataValue)
            Next x
        End If
    End With

      ' Clean up
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing

    PopulateTable = True 'OK Flag
    Exit Function

ErrorHandler:
    ' Clean up
    If Not rst Is Nothing Then
        If rst.State = adStateOpen Then rst.Close
    End If
    Set rst = Nothing

    If Not cnn Is Nothing Then
        If cnn.State = adStateOpen Then cnn.Close
    End If
    Set cnn = Nothing

    If Err <> 0 Then
        MsgBox Err.Source & "-->" & Err.Description, , "Error"
    End If

End Function

Bob, help me out here (once more)

How do I put this code in VB?
1- I made a command button.
2- In the event On click, I pasted the code.
3- I get the following error:

"Cannot define a public user-defined type inside an object module."

The option compare and the UDT part I put it on Declaration.
The other code I put it on the Onclick event.
What am I doing wrong?
One more question, how do I specify the location of the text file?
This is how It ends up.

Sub imporfile_Click(strSourceFile As String)
    ' Call with ImportFile "C:\ArrayValues.txt"
    Dim intIndex As Integer

---------->8---v-e-r-y-b-i-g-s-n-i-p------>8----------

Thanks thanks a lot !

----
Not in the face !!! Giordano

There are two ways you can go.

The easiest, and preferred, is to paste the code into a new standard module, instead of the form class module. Name the standard module anything you like.

Then add two controls to your form:


Control Name Text/Caption
-------------------------------------
Button cmdImport Import
TextBox txtPath

When the form loads, enter the full path of the file you want to import in txtPath and click the button.

The OnClick event for the button looks like:


Private Sub cmdImport_Click()
    Dim strSourceFile As String

    strSourceFile = Me.txtPath

    ' call public procedure in standard module, passing
    ' strSourceFile as the procedure's argument.
    ImportFile (strSourceFile)

End Sub


If you left the code in the form module you would need to change the scope of:
 - the user-defined type
 - any procedure signatures that use the user-defined type as a parameter

from Public to Private.

So the UDT declaration, the PrintUDTArray sub procedure, and the PopulateTable function would all need to be private members of the form class.

Let me know how it goes, or if you have further questions.

Bob