text import for non-delimited file

[TiderInsider]

I need to import many non-delimited files from text into an access database. The text files come from the mainframe and are all of the same format. I have written the code to import the files into excel, but now I am thinking access would be a better tool for this project.

My question is...how much do I need to change the code to get access to import these text files? Currently, with excel I have all the file names in a directory sheet and I have written a program that imports these files into excel. I think you should be able to get the gist of what this program does.

I am not looking for someone to write the code for me...rather I would like to hear of some good resources for doing something like this...and have a reasonble expectation of how difficult this is to do.

Thanks in advance for any help.

Below is some of my code:

Sub importprn()

    Dim strFileName As String
    Dim Row As Integer, Column As Integer
    Dim Data As String, i As Integer, Age As String, Year As String
    Dim Exposure As Double, Lapse As Double, Ratio As Double
    Dim strTime As String, Duration As String, StartRow As Integer
    Dim finalif As Integer, j As Integer

    Application.ScreenUpdating = False
    
    finalif = Application.WorksheetFunction.CountIf _
        (Sheets("Directory").Range("D2:D200"), "*")
    Row = 5

    For i = 1 To finalif
        Sheets("Test").[fname] = Sheets("Directory").Cells(1 + i, 5)
        Sheets("Test").[Name] = Sheets("Directory").Cells(1 + i, 1)
        Sheets("Test").[Mode] = Sheets("Directory").Cells(1 + i, 2)
    
        strFileName = [directory] & "\" & [fname]
        Open strFileName For Input As #1
    
        While Not EOF(1)
            Line Input #1, Data
            Year = Mid(Data, yStart, 2)
            
            Line Input #1, Data
            Age = Mid(Data, aStart, 2)
            
            SkipLines 2               'A FUNCTION TO AID IN MOVING THROUGH
            For j = 1 To 12          'THE TEXT FILE
                Line Input #1, Data
                Duration = Mid(Data, dStart, 8)
                Exposure = CDbl(Mid(Data, eStart, 12))
                Lapse = CDbl(Mid(Data, lStart, 12))
                Ratio = CDbl(Mid(Data, rStart, 6))
                Sheets("Test").Cells(Row, 1) = [Name]
                Sheets("Test").Cells(Row, 2) = [Mode]
                Sheets("Test").Cells(Row, 3) = Year
                If Age = "  " Then
                    Sheets("Test").Cells(Row, 4) = "Aggregate"
                Else
                    If CDbl(Age) = 0 Then
                        Sheets("Test").Cells(Row, 4) = CDbl(Age)
                    Else
                        Sheets("Test").Cells(Row, 4) = CDbl(Age) + 2
                    End If
                End If
                Sheets("Test").Cells(Row, 5) = Duration
                Sheets("Test").Cells(Row, 6) = Exposure
                Sheets("Test").Cells(Row, 7) = Lapse
                If Ratio > 1 Then
                    Sheets("Test").Cells(Row, 8) = 1
                Else
                    Sheets("Test").Cells(Row, 8) = Ratio
                End If
                Row = Row + 1
            Next j
            
.........SOME CODE NOT INCLUDED TO SAVE SPACE
            
            For j = 1 To 13
                Line Input #1, Data
                Duration = Mid(Data, dStart, 8)
                Exposure = CDbl(Mid(Data, eStart, 12))
                Lapse = CDbl(Mid(Data, lStart, 12))
                Ratio = CDbl(Mid(Data, rStart, 6))
                Sheets("Test").Cells(Row, 1) = [Name]
                Sheets("Test").Cells(Row, 2) = [Mode]
                Sheets("Test").Cells(Row, 3) = Year
                If Age = "  " Then
                    Sheets("Test").Cells(Row, 4) = "Aggregate"
                Else
                    If CDbl(Age) = 0 Then
                        Sheets("Test").Cells(Row, 4) = CDbl(Age)
                    Else
                        Sheets("Test").Cells(Row, 4) = CDbl(Age) + 2
                    End If
                End If
                Sheets("Test").Cells(Row, 5) = Duration
                Sheets("Test").Cells(Row, 6) = Exposure
                Sheets("Test").Cells(Row, 7) = Lapse
                If Ratio > 1 Then
                    Sheets("Test").Cells(Row, 8) = 1
                Else
                    Sheets("Test").Cells(Row, 8) = Ratio
                End If
                Row = Row + 1
            Next j
            
            SkipLines 3
        Wend
    
        Close #1
    Next i
    Application.ScreenUpdating = True

End Sub

 

[Norie]

You can import text files into Access using the TransferText method.

General syntax:

DoCmd.TransferText [transfertype][, specificationname], tablename, filename[, hasfieldnames][, HTMLtablename]

What you need to do is create an import specification.

To do this just import one of your files into Access via File>Get External Data. It will take you through the steps of importing a file.

Then just set up a similar routine to your Excel one to loop through the files.

If as you say the files are all in the same format it should be a breeze.

 

[mdmilner]

Throwing in more description.
Access allows you to manually import text files File-Get External Data-Import and then just before finishing to save it.

This by default drops it into a table saved in the database.

I'm guessing by the name of some of your subroutines that it's not just row after row of data - you have some blank lines in the system. This suggests your next step will be to clean up the imported data a little (removing the blank records that will appear in the table)

After that your approach depends on your needs. You could work with them using recordset objects or SQL. If I get a chance, I'll throw up some code I use to do a very similar task although this particular one was a little more complex -- I had multiple segments within the text file each with unique headers. I ended up linking instead of importing, and then identifying each row before I 'processed' it into it's final form. (I was transforming a text based datafile into a Table.

Mike

 

[TiderInsider]

Thanks Mike and Norie for the suggestions...I'll let you all know how it turns out.