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

[anishkgt]

Hi All,

Was wondering how i can import data from a file to a table in different fields. The data inside the file is described with the number of characters and space's between. The attached file has the data.

For example in the file attached, the first four characters represent a data like 'ticket number' and maybe the third line 6 to 10 charcaters represent 'name of a passenger'.

Now how do i import these data to a table into respective fields in a table. Hope to read from anyone.

 

[namliam]

Your only solution for files like this is to handle them line by line in VBA and extract the information from the places you know where the information is.

I assume the first four chars of any line will identify the "line type"
I.e. on line A040 you know you can find the airline name from character 11 to 24
Research Line Input or is it Input line, that should get you started.

Come back if you run into more detailed problems.

 

[anishkgt]

Thanks namliam. Well i was looking something to learn how to do it. Could you recommend me what i have to learn to do this.

 

[namliam]

Research Line Input or is it Input line, that should get you started.

Come back if you run into more detailed problems.

Like I said, research that ... should give you a working sample how to open the file and read the lines.

 

[anishkgt]

no clue where to begin on this. So thought i'd come here to start with.

 

[namliam]

Well to start... Try googleing and/or looking at the help for Line Input.

If you google a bit you should be able to find samples
You might find this for example:
http://msdn.microsoft.com/en-us/library/aa243392(v=vs.60).aspx

 

[anishkgt]

Thanks naliam. I don't think i'd be able get this done, from scratch. I've got no experience with programming. Would you be able to recommend a course for me that would include this area. Tried googling but those are just bits of what i may need. Can find anywhere as to how i can link to the database.

 

[namliam]

Link to the database? you dont even need to google I gave you a link that has a nice sample of how you can do it, atleast the basics of it..

 

[anishkgt]

Thanks namliam. But that has data in each line and number of characters represent a unique data like name of a passenger and an airline, fare of the ticket. Its called a MIR file in the GDS industry.

 

[anishkgt]

I could share a PDF do. That explains the structure of the MIR file in detail

 

[namliam]

Well reading each line is atleast the starting point, walk before you can run.

Once you can read each line, you can then handle the different lines using an

If Left(yourLine,3) = "A12" then
   'handle the A12 line
endif
If Left(yourLine,3) = "A14" then
   'handle the A14 line
endif

etc...

 

[anishkgt]

Not sure what it meant, but thanks a lot man.

 

[namliam]

Not sure what what meant?

 

[anishkgt]

What does yourline,3 mean and what is after then. Should that be declared before ? Can i use the same line of code for rest of the lines to be read like A15, A16 and so on. Sorry if am a little trouble for you

 

[anishkgt]

In access it would macro so it was easy for me understand, i understand macro is not going to help here and after reading a line how can it be added to a table in the database.

 

[BlueIshDan]

GetFileLines Function here

then use:

Option Compare Database
Option Explicit

Private Sub Command0_Click()
    Dim rs As Recordset: Set rs = CurrentDb.OpenRecordset("table_name")
    Dim line As Variant
    
    For Each line In GetFileLines("file_address", True, True, False)
        ' Code depends on format of file.
        ' Example
        rs.AddNew
            rs!Field = Split(line, ",")(0)
            rs!Field = Split(line, ",")(1)
            rs!Field = Split(line, ",")(2)
            rs!Field = Split(line, ",")(3)
        rs.Update
    Next
    rs.Close
End Sub

' Reference Microsoft Scriping Runtime
   
Public Function GetFileLines(ByVal address As String, _
                             ByVal remove_blank_lines As Boolean, _
                             ByVal trim_lines As Boolean, _
                             ByVal keep_newline_char)
    
    ' keep_newline_char represents the Chr(byte) value of 10 and 13. These Bytes represent a NewLine.
    ' passing a true value to this parameter will cause returned lines to contain the new line value at the end of them.
    ' Use: True - Displaying data in a message
    '      False - Parsing the data line by line.
    
                             
    Dim fs As New FileSystemObject
    
    Dim arr_bytes() As Byte
    Dim file_node As Long

    Dim lines() As String
    Dim line_count As Long: line_count = 0
    
    Dim var_byte As Variant
    
    ReDim lines(line_count)
        
    If fs.FileExists(address) Then
    
        ReDim arr_bytes(FileLen(address))
        file_node = FreeFile
        
        Open address For Binary Access Read As file_node
            Get 1, , arr_bytes
        Close file_node
        
        For Each var_byte In arr_bytes
            
            If var_byte = 10 Or var_byte = 13 Then
                If trim_lines Then: lines(line_count) = Trim(lines(line_count))
                
                If remove_blank_lines And Trim(lines(line_count)) = "" Then
                    lines(line_count) = ""
                Else
                    If keep_newline_char Then: lines(line_count) = lines(line_count) & Chr(var_byte)
                    line_count = line_count + 1
                    ReDim Preserve lines(line_count)
                End If
                
            Else
                lines(line_count) = lines(line_count) & Chr(var_byte)
            End If
            
            var_byte = Empty
            
        Next
        
    End If
    
    ' If your last result is wonky, focus on the following line. It was a quick fix for me :)
    lines(line_count) = Left(lines(line_count), Len(lines(line_count)) - 1)

    ReDim arr_bytes(0)
    Set fs = Nothing
    file_node = Empty
    line_count = Empty
    
    GetFileLines = lines
    
    ReDim lines(0)
    
End Function

 

[namliam]

It means...

If Left(yourLine,3) = "A12" then
   'handle the A12 line
endif

When you handle the A12 line, you have to extract the data from the different lines

I.e.
for every A12 you might close the record, thus "rs.update" would be included
For every A02 you may want to start a new record, thus "rs.addnew" would be included
etc... etc...

"yourline" would be a variable that holds the current line that you just read from the file....
That is why I said "walk before you run" do things one step at a time, read the file first, then make more logic.

 

[anishkgt]

So do i just copy paste the code to VB and change the table_name to tblMIR and the file to read file_address to "C:\test.txt"

Is that it ? As a reference i've attached a the test.txt and structure specification file. The structure spec is a PDF file but i've copied the content to .docx and zipped.

 

[BlueIshDan]

I honestly see no format in this file...

 

[anishkgt]

Hi namliam,

I know what i have to do with the file but since i've no experience with VB coding i can't accomplish it. Well, if i try now, i know i can finish it by the time my hair turns grey. I can declaring a variable and little here and there, but its gets harder down the road.