Read a file and import its data into a table (1 Viewer)

[anishkgt]

Well, now parsing the File is complete. How can these data be added to a database like access ?

 

[namliam]

this link should get you started
http://msdn.microsoft.com/en-us/library/office/ff197799(v=office.15).aspx#sectionSection1658

 

[jdraw]

You may get some ideas re reading/importing a text file from
http://www.access-programmers.co.uk/forums/showthread.php?t=270569

 

[anishkgt]

Jdraw, the link does not show anything related to importing to a table. Namliam, hard time kick starting

 

[namliam]

dim rs as dao.recrodset
set rs = currentdb.openrecordset("YourTable") 'Opens a table


then use either
rs.addnew ' adds a new record
or
rs.edit 'to edit an existing record

rs![somefieldname] = 10 'assign 10 to somefieldname that exists in the table
rs![Another] = "Hello table" ' assign another value to another column

rs.update 'to write the data to the table

rs.close 'close the table
set rs = nothing 'to make sure memory is cleaned

Does that help you more?

 

[anishkgt]

Tried the same got an error on way "Method or data member not found" for RS.AddNew, below is part of the code

Dim FOP As String
        Dim RS As DAO.Database
        
        Set RS = CurrentDb.OpenRecordset("tblMIR")
        
        
    If IsNull(txtMIRPath) = True Then
    MsgBox ("Check MIR file path")
    Exit Sub
    End If
    MIR = [Forms]![form1]![txtMIRPath] & ".MIR"
        DoCmd.Hourglass True
        Open MIR For Input As #1 'Open specified file
        Do While Not EOF(1)
            Line Input #1, TextLine
            
            If Left(TextLine, 2) = "T5" Then
                
                IATACode = Trim(Mid(TextLine, 5, 4)) 'Reads the IATA Code
                RS.AddNew
                RS![IATACode] = IATACode

 

[anishkgt]

tried the code and got an error on the way "Method or Data member not found" for RS.AddNew

 

[anishkgt]

ok got it changed

set rs = currentdb.openrecordset("YourTable") 'Opens a table

to

set rs = RecordSet

 

[anishkgt]

Done ! Managed to add data of the text file to the table. How can i automate this ? like read all files with extention .txt in a folder and move processed file into C:\Processed with current date

 

[jdraw]

Jdraw, the link does not show anything related to importing to a table. Namliam, hard time kick starting

No it doesn't show importing to a table. It shows you how to read a text file. The other poster wanted to put the incoming data into an array.
Do you really think/find that reading a text file into a table is in no way related.

As I suggested, you may get some ideas for reading/importing a text file. Many would, but some wouldn't. It focused on reading a text file, but the poster's related issue was array manipulation.

Good luck with your project.

 

[namliam]

Done ! Managed to add data of the text file to the table.

Curious to see your code

How can i automate this ? like read all files with extention .txt in a folder and move processed file into C:\Processed with current date

https://support.office.com/en-us/article/Dir-Function-1a1a4275-f92f-4ae4-8b87-41e4513bba2e

Should explain it to you

 

[anishkgt]

Will post the code once completed, Sure

Tried the same got an error "File not found"

Dim src As String
Dim dst As String 
    src = Dir("C:\MIR\*.MIR")
        dst = "C:\MIR\Processed"
        
        Set RS = CurrentDb.OpenRecordset("tblMIR")
        Do While src <> ""
    DoCmd.Hourglass True
        Open src For Input As #1 'Open specified file
        'Do While Not EOF(1)
            Line Input #1, TextLine

            If Left(TextLine, 2) = "T5" Then
..................Code follows..................

        DoCmd.Hourglass False
        RS.Update                   ' update table tblMIR
                RS.Close            ' Close the table
                Set RS = Nothing    ' Clear RS
        Close #1                    ' Close file

Don't know how to read one file at a time and move to next,latter move already read file to C:\Processed

Help

 

[anishkgt]

With that link i've managed to do file at a time. Clicking the reads the file, displays in form, imports to the db and moves that file to another folder. How can i automate this ?

 

[namliam]

Your rs.update you want/need per record, not sure if you are doing that?

Notice the small but possibly significant changes made below

    dim srcfolder as string
    Dim srcfile As String
    Dim dstfolder As String

    srcfolder = "C:\MIR\"
    dstfolder = "C:\MIR\Processed\"
    srcfile = Dir(srcFolder & "*.MIR")
        
    Set RS = CurrentDb.OpenRecordset("tblMIR")
    Do While src <> ""
        DoCmd.Hourglass True
        Open srcFolder & src For Input As #1 'Open specified file
        Do While Not EOF(1)
            Line Input #1, TextLine

            If Left(TextLine, 2) = "T5" Then
..................Code follows..................

        
            RS.Update                   ' update table tblMIR
        loop
        Close #1                    ' Close file
        name srcfolder & srcfile as dstfolder & srcfile
        srcfile = dir 'get next file
    loop
    DoCmd.Hourglass False
    RS.Close            ' Close the table
    Set RS = Nothing    ' Clear RS

 

[anishkgt]

Hi namliam,

Correction,

Do While src <> ""

to

Do While srcfile <> ""

and

Open srcFolder & src For Input As #1

to

Open SrcFolder & srcfile For Input As #1

with this all works well as i wanted Thanks a lot namliam

 

[anishkgt]

that worked well, now how can i show the list of files processed and moved to destination directory. Tried

txtMIRActivity.Value = srcfile

even if the above code worked it would just show all the files in soruce folder.

secondly, how can i skip files if any of the fields are missing. Mostly the A02 line should always be present if not it should move that file to a folder C:\Error and continue with the next file.

 

[namliam]

sorry for the mistypes on the src vs srcfile
Perhaps if you populate a list box? depends on how big the list grows per processing, why even display the files processed?
May be a good thing to store the filenames in a table with a now() stamp as to keep a track of when what file was imported.
Should be easy to do with your current (running) knowledge

How to skip files.... well you would first have to check the file, read it completely I guess check for problems and skip it....
Or you can add it into your table as a record, then check the record(s) for any problems before you move the file?
If a problem is found, remove the record and move the file to error folder instead of processed?

Again should be easy to do now

 

[anishkgt]

hmmmm sounds like a better idea. will try your suggestion

Thanks

 

[anishkgt]

i just need to correct this code, which is more than enough for me to eliminate error files, well at the moment

If Left(TextLine, 3) = Trim("A02") Then 'First A02(PAX) line
    pax = Trim(Mid(TextLine, 4, 30))
    TKT = ALC & Trim(Mid(TextLine, 49, 10))
    Passenger = TKT & " " & pax
    Line Input #1, TextLine
    Line Input #1, TextLine
Else
    MsgBox ("Failed to find passengers in file " & "'" & SrcFolder & srcfile & "'" & vbNewLine & _
    "Try moving" & "'" & SrcFolder & srcfile & "'" & " to another folder and re-run the Parser again.")
    Close #1
    Exit Sub
End if

when compiling it just skips the if Conditon and displays the message box every time.

Thanks

 

[anishkgt]

Ok managed to add the file name also to the respective record imported Sill trying to display processed files in the text box with

txtMIRActivity.Value = srcfile

it only display the last file processed file name