import txt file with multiple headers

[vojinb]

Hi,
I have txt file which have header and footer occurring every 70-80 lines. how to import file avoiding this header and footer, just to get data into table?
File looks like this:

Header


Class 1
Value 1 Value 2 Value 3
Value 1 Value 2 Value 3
Value 1 Value 2 Value 3
.
.
Class 2
Value 1 Value 2 Value 3
Value 1 Value 2 Value 3

I need code which reads file line by line and when it comes to header ignore it, when it comes to class1 puts in table in different columns values 1,2,3 until it gets to class 2. I've been looking all over the internet and have three access and VBA books, but nothing.
Could anyone please help me?
Thanks

 

[thechazm]

Instead of examples of the data you are dealing with you need to actually provide some of the actual data otherwise I won't be able to help. Also is this text file created with your db? If so then it would make it so much easier if you dumped the data out with xml then you could read it easier. Either way let me know.

TheChazm

 

[vojinb]

Hi, here is part of original file which is repeating.
This file is from external source, not from my DB, I'm just trying to import it into DB with one table within 5 columns, ignore header, read class, until reach other class insert data in 4 other columns with reference to that class.
Thanks

 

[thechazm]

Ok from the file if you take a look at the data. There are exactly 60 characters before the first period of the first value. Then there are 23 spaces after each "." till the next data rows ".".

Meaning:

AC.1.TR.CHF.10000.1000..N...... 25,892.

That is 60 characters of data.

And from

.96db 21,580

Is 23. I will try to come up with some code to help you along.

 

[thechazm]

What version of access do you use?

 

[vojinb]

Hi,
access 2007.

 

[thechazm]

Ok so here is a module I am creating for this. This is only the code to read the file you select line by line but it does nothing with the data yet:

Option Compare Database
Option Explicit
Function ImportFile(strFileName As String)
Dim oFSO As New FileSystemObject
Dim oFS
Dim stext As Variant
If strFileName = "" Then
    Set oFSO = Nothing
    Exit Function
End If
Set oFS = oFSO.OpenTextFile(strFileName)
Do Until oFS.AtEndOfStream
    stext = oFS.ReadLine
    Debug.Print stext
Loop
Set oFSO = Nothing
Set oFS = Nothing
End Function
Function GetFileName() As String
Dim fd As FileDialog
Dim objfl As Variant
Dim filnam As String
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
    .ButtonName = "Select"
    .AllowMultiSelect = False
    .Filters.Add "Text Files", "*.txt", 1
    .title = "Choose file to import"
    .InitialView = msoFileDialogViewDetails
    .Show
    For Each objfl In .SelectedItems
        GetFileName = objfl
    Next objfl
    On Error GoTo 0
End With
Set fd = Nothing
End Function

I will be constructing the datatables to store the data and the commands to search through each line for the right data. I do however need quite a bit of input from you on this so can you email me at charles.branham@navy.mil and I will stay in touch through email as this forum just runs terribly slow at the location I am at.

We will then post the final solution here in the forum so everyone else can utilize the code if they want.

Thanks,

TheChazm

 

[vojinb]

Hi,
thanks, I have sent whole file to e-mail you mentioned.
Thank you very much