VBA To Import Text File

[jo15765]

I have a text file that has no delimitation, but I have queried MSysIMEXSpecs and gained the start position as well as the width of each column in my text file that I need to import. Question being, is it possible to take this start position and width position from MSysIMEXSpecs and create VBA that will import the text file?

[ssanfu]

Yes.
Open the text file for input,
read a line, parse the line, loop until EOF

You could use the Mid() function since you know the width of each column.
Could set up a loop calculating the start position if the width is fixed.

Would need to see a couple of lines from the text file........

[jo15765]

Yes.
Open the text file for input,
read a line, parse the line, loop until EOF

You could use the Mid() function since you know the width of each column.
Could set up a loop calculating the start position if the width is fixed.

Would need to see a couple of lines from the text file........

Attached is a workbook filled with garbage data to show the structure of the txt fileAttachment 24403

[ssanfu]

Didn't get a workbook, just a text file.
Can't tell from the garbage file.... How many columns? Width of each column?

Column start	Column Width

Have you started any VBA code????

[jo15765]

I do not have any VBA syntax yet, was not sure how to code this process...Below is what the table shows, more than just column start and width but should help

And this is for Access 2013 not 2000 like my header info shows.

Attributes    DataType    FieldName    IndexType    SkipColumn    SpecID    Start    Width 
0    10    1    0    FALSE    2    225    15 
0    10    10    0    FALSE    2    360    15 
0    10    11    0    FALSE    2    375    15 
0    10    12    0    FALSE    2    390    15 
0    10    13    0    FALSE    2    405    15 
0    10    14    0    FALSE    2    420    15 
0    10    15    0    FALSE    2    435    15 
0    10    16    0    FALSE    2    450    15 
0    10    17    0    FALSE    2    465    15 
0    10    18    0    FALSE    2    480    15 
0    10    19    0    FALSE    2    495    15 
0    10    2    0    FALSE    2    240    15 
0    10    20    0    FALSE    2    510    15 
0    10    21    0    FALSE    2    525    15 
0    10    22    0    FALSE    2    540    15 
0    10    23    0    FALSE    2    555    15 
0    10    24    0    FALSE    2    570    15 
0    10    25    0    FALSE    2    585    15 
0    10    26    0    FALSE    2    600    15 
0    10    3    0    FALSE    2    255    15 
0    10    4    0    FALSE    2    270    15 
0    10    5    0    FALSE    2    285    15 
0    10    6    0    FALSE    2    300    15 
0    10    7    0    FALSE    2    315    15 
0    10    8    0    FALSE    2    330    15 
0    10    9    0    FALSE    2    345    15 
0    10    100    0    FALSE    2    68    60 
0    10    101    0    FALSE    2    128    30 
0    10    102    0    FALSE    2    158    13 
0    10    103    0    FALSE    2    203    22 
0    10    104    0    FALSE    2    188    15 
0    10    105    0    FALSE    2    171    6 
0    10    106    0    FALSE    2    17    13 
0    10    107    0    FALSE    2    1    16 
0    10    108    0    FALSE    2    30    38 
0    10    109    0    FALSE    2    177    11

[ssanfu]

I created a form and added a button.
The code if for the click event of the button. (I threw this together - seems to work)

Private Sub cmdParseFile_Click()
    '*******************************
    ' requires a reference set to Microsoft Office xx.0 Object Library
    '*******************************

    Const ForReading = 1, ForWriting = 2, ForAppending = 8

    Dim fso, MyFile
    Dim fDialog As Object
    Dim TextLine As String
    Dim FileName As String

    Set fDialog = Application.FileDialog(msoFileDialogFilePicker)

    With fDialog
        .AllowMultiSelect = False
        .Title = "Select a File To Copy"
        .Filters.Clear
        .Filters.Add "Text", "*.txt"
        .Filters.Add "Text", "*.csv"

        .InitialFileName = Application.CurrentProject.Path
        .Show
        If .SelectedItems.Count = 0 Then
            MsgBox "No file selected."
        Else
            FileName = fDialog.SelectedItems(1)
        End If
    End With


    Set fso = CreateObject("Scripting.FileSystemObject")

    ' Open the file for input.
    Set MyFile = fso.OpenTextFile(FileName, ForReading)

    ' Read from the file
    Do While MyFile.AtEndOfStream <> True
        TextLine = MyFile.ReadLine

        '------------------------------------
        'your code to parse the data goes here
        
        Debug.Print TextLine
        '------------------------------------

    Loop
    MyFile.Close
    Set MyFile = Nothing
    Set fDialog = Nothing
    MsgBox "Done"
End Sub

Don't know what you want to do with the data, soooo.........

Have fun

[jo15765]

I am wanting to take the file and import it into an access table.

Thank you for the syntax, I will test this evening when I can get back to my computer.

[ssanfu]

You still have to write code to parse the line and append the data to the correct table..