FileSystemObject to import DAT and TXT files

[peace77]

Hello,

I am required to convert a dat file into access and structure the information as per the desired output. Initially I had thought that the file to be converted into access is in txt format and hence I wrote the complete code accordingly. I just learnt that the file will be available in .DAT format and hence I have to rewrite the code.

I had used the FileSystemObject and OpenTextFile method to import the data. Is it possible that I tweak this part so that the user has the option to open both in txt and DAT format. I am a novice in programming and even obvious things appear blurred to me ?

This is the part of the code that enables user to open the txt files. Please suggest the ways to enable user to open both txt and dat files….I am trying not to change anything else in the code as it is all interconnected and will lead to cascade effects.

Dim fs As FileSystemObject
Dim dfile As Object
Dim dline As String


Set RS = CurrentDb.OpenRecordset("Select * from Data")

'Code to import raw data
If IsNull(filepath) = False Then

    Set fs = CreateObject("Scripting.FileSystemObject")

    If fs.FileExists(filepath) Then
    
        Set dfile = fs.OpenTextFile(filepath)
        
            Do While dfile.AtEndofStream <> True
            dline = dfile.ReadLine


If InStr(1, dline, "Opening Balance") <> 0 Then
                RS.AddNew
                RS("AllData") = "BALANCE"
                RS.Update
End If
            
            RS.AddNew
            RS("AllData") = dline
            RS.Update
  Loop
    
    End If
               
End If

Thanks for the help.

 

[JoeAtWork]

I see nothing in your code that restricts them from opening the file based on the extension. If there is such a restriction then it would be in the code where the "filepath" variable is set, which you have not shown us.

 

[peace77]

Sorry about that..Here is the piece where i set the filepath variable.

Function filebrowser() As String
    'Declare a variable as a FileDialog object.
    Dim fd As FileDialog
    Dim filepath As String
    Dim vrtSelectedItem As Variant
    Set fd = Application.FileDialog(msoFileDialogFilePicker)

    With fd
        .Filters.Add "Text Files", "*.txt"
        .AllowMultiSelect = False

        If .Show = -1 Then
            For Each vrtSelectedItem In .SelectedItems
                filepath = vrtSelectedItem
            Next vrtSelectedItem
        Else
        End If
    End With
    
    Set fd = Nothing
    filebrowser = filepath
End Function

[\code]

 

[JoeAtWork]

Take out this line:

.Filters.Add "Text Files", "*.txt"

 

[peace77]

Thanks JoeAtWork

i edited as :
.Filters.Add "Data Files", "*.txt;*.dat