Developing access macro to parse the data available in txt file 1

[peace77]

Hello Friends,

I am totally new to the VBA world and I am not very thorough at Access as well. I am required to create a macro in Access that can enable user to parse the data that has more than 65000 records....The data is in in raw form in txt format. The macro has to start right from exporting data from txt to access and end with the parsed data.

This is the available format of data in txt file:

01/01/2008 04:45:00 Company's name Page: 1

01/12/2007 To 31/12/2007
Fund Group: ABC Fuds
[highlight]Fund Code: 123[/highlight] [highlight]Class:ABC[/highlight]
Unit Price: 2.56 AS AT 01/03/07
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Transaction Type Coloumn1 Coloumn2 Coloumn3 Coloumn4 Coloumn5
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

CATEGORY1

Sub Category1
Detailed Description1 0.00 0.00 0.00 0.00 0.0000
Detailed Description2 0.00 0.00 0.00 0.00 0.0000
Detailed Description3 0.00 0.00 0.00 0.00 0.0000
Detailed Description4 0.00 0.00 0.00 0.00 0.0000
Detailed Description5 0.00 0.00 0.00 0.00 0.0000
Detailed Description6 0.00 0.00 0.00 0.00 0.0000
Detailed Description7 0.00 0.00 0.00 0.00 0.0000
Detailed Description8 0.00 0.00 0.00 0.00 0.0000
Detailed Description9 0.00 0.00 0.00 0.00 0.0000
Detailed Description10 0.00 0.00 0.00 0.00 0.0000

------ ------------------ -------------- -------------- ------------------ ----------------------------------------------------------------------------
Total 0.00 0.00 0.00 0.00

CATEGORY2

Sub Category2
Detailed Description1 0.00 0.00 0.00 0.00 0.0000
Detailed Description2 0.00 0.00 0.00 0.00 0.0000
Detailed Description3 0.00 0.00 0.00 0.00 0.0000
Detailed Description4 0.00 0.00 0.00 0.00 0.0000
Detailed Description5 0.00 0.00 0.00 0.00 0.0000
Detailed Description6 0.00 0.00 0.00 0.00 0.0000
Detailed Description7 0.00 0.00 0.00 0.00 0.0000
------ ------------------ -------------- -------------- ------------------ ------------------------------------------------------------------------------
Total 0.00 0.00 0.00 0.00



1/01/2008 04:45:00 Company's name Page: 2

01/12/2007 To 31/12/2007
Fund Group: ABC Funds
[highlight]Fund Code: 456[/highlight] [highlight]Class:XYZ[/highlight]
Unit Price: 4.56 AS AT 01/03/07
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Transaction Type Coloumn1 Coloumn2 Coloumn3 Coloumn4 Coloumn5
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

CATEGORY1

Sub Category1
Detailed Description1 0.00 0.00 0.00 0.00 0.0000
Detailed Description2 0.00 0.00 0.00 0.00 0.0000
Detailed Description3 0.00 0.00 0.00 0.00 0.0000
Detailed Description4 0.00 0.00 0.00 0.00 0.0000
Detailed Description5 0.00 0.00 0.00 0.00 0.0000
Detailed Description6 0.00 0.00 0.00 0.00 0.0000
Detailed Description7 0.00 0.00 0.00 0.00 0.0000
Detailed Description8 0.00 0.00 0.00 0.00 0.0000
Detailed Description9 0.00 0.00 0.00 0.00 0.0000
Detailed Description10 0.00 0.00 0.00 0.00 0.0000

------ ------------------ -------------- -------------- ------------------ -------------------------------------------------------------------
Total 0.00 0.00 0.00 0.00

CATEGORY2

Sub Category2
Detailed Description1 0.00 0.00 0.00 0.00 0.0000
Detailed Description2 0.00 0.00 0.00 0.00 0.0000
Detailed Description3 0.00 0.00 0.00 0.00 0.0000
Detailed Description4 0.00 0.00 0.00 0.00 0.0000
Detailed Description5 0.00 0.00 0.00 0.00 0.0000
Detailed Description6 0.00 0.00 0.00 0.00 0.0000
Detailed Description7 0.00 0.00 0.00 0.00 0.0000
------ ------------------ -------------- -------------- ------------------ ----------------------------------------------------------------------------
Total 0.00 0.00 0.00 0.00

And it goes on and on for different kind of fund codes. The number of records are exceeding 65000.

I am asked to parse the data and present the output in the following 11 coloumns:

As At Date Fund Code Class As At Price Sub-Category Detail Desc. Coloumn1 Coloumn2 Coloumn3 Coloumn4 Coloumn5


I am not sure how to even start with it. I was hoping if anyone be able to help me with either of the following:

1) Help me understand the steps to be followed to bring such an output in Access and/or
2) Help me understand how to define these steps using VBA to develop the macro.


Any kind of help is highly appreciated.

Thanks a ton!

 

[Remou]

You could:

1. Use the FileSystemObject and a Textstream to read in the file line by line and write it out to a new file or table in the appropriate format.

2. Import into Excel and format manually.

3. Import into Access and format manually.

4. Pay someone.

The requested output is not normalized, as far as I can see.

If you decide on option 1, use the Google seach (above) or the advanced search, with the keywords provided, to get some outlines. If you get stuck, post back with where you are up to in code. You are jumping in at the deep end

 

[simian101]

The hardest thing will be determining what information is in each line. As you read down the report you will need to store

Fund Group
Fund Code
Class
Unit Price
CATEGORY1
Sub Category1

in variables. Then once you get to Detailed Description1 information you will need to write each one of those variables to field along with the detail information.

It looks like your page header information will be the same every time so it should be easy to throw it out. Sometimes it is easier to count the number of lines on the page. If they are the same everytime then you can just throw out the first 3 or 4 lines.

Simi

 

[NWildblood]

Guys
haven't tested this yet,but found this

"Importing a text file to an Access database".
"This article will explain how to import the contents of a text file (.txt) when using MS text or OLE DB Drivers wont do! We will be using the FileSystem and TextStream objects for this lesson"

http://www.aspjunction.com/articles/importtext.asp

I have to crack a (non urgent) similar problem for my boss, so will be coming back frequently !
Good luck
.S.

 

[peace77]

I used this function to browse the file and than called this function from another sub linked to form

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

am still working on storing those values , as simian101 had pointed out, its difficult to do that

 

[Remou]

Here is an outline.

Sub GetReport()
'Reference Windows Script Host Object Model
Dim fs As FileSystemObject
Dim f As TextStream
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("hdr")

'Field list for hdr
'ID,AsAtDate,FundCode,Class,AsAtPrice,Category,
'SubCategory,DetailDesc,Coloumn1,Coloumn2,Coloumn3,
'Coloumn4,Coloumn5

Set fs = CreateObject("Scripting.FileSystemObject")

strFile = DT & "Report.txt"
Set f = fs.OpenTextfile(strFile)

Do While AtEndOfStream <> True
    a = f.ReadLine
    
    If f.AtEndOfStream Then Exit Sub
    
    If Left(a, 12) = "Fund Group: " Then
        FG = Trim(Mid(a, 13))
        a = f.ReadLine
        FC = Trim(Mid(a, 12, InStr(a, "Class") - 12))
        Class = Trim(Mid(a, InStr(a, "Class") + 6))
        a = f.ReadLine
        UP = Trim(Mid(a, 13, InStr(a, "AS AT") - 13))
        AAD = Trim(Mid(a, InStr(a, "AS AT") + 6))
    End If
    
    If Left(a, 8) = "Category" Then
        Cat = Trim(a)
    End If
    
    If Left(a, 12) = "Sub Category" Then
        SubCat = Trim(a)
        Do While True
            a = f.ReadLine
            If f.AtEndOfStream Then Exit Sub
            
            If Len(Trim(a)) = 0 Or Mid(Trim(a), 1, 6) = "------" Then
                '
            Else
                DD = Trim(Mid(a, 1, InStr(a, "     ")))
                CTemp = Replace(Trim(Mid(a, InStr(a, "   "))), " ", "|")
                Do While InStr(CTemp, "||") > 0
                    CTemp = Replace(CTemp, "||", "|")
                Loop
                CC = Split(CTemp, "|")
                
                rs.AddNew
                rs!AsAtDate = AAD
                rs!FundCode = FC
                rs!Class = Class
                rs!AsAtPrice = UP
                rs!Category = Cat
                rs!SubCategory = SubCat
                rs!DetailDesc = DD
                rs!Coloumn1 = CC(0)
                rs!Coloumn2 = CC(1)
                rs!Coloumn3 = CC(2)
                rs!Coloumn4 = CC(3)
                rs!Coloumn5 = CC(4)
                rs.Update
                
                If Left(Trim(a), 5) = "Total" Then
                    Exit Do
                End If
            End If
        Loop
    End If
Loop
End Sub

 

[NWildblood]

Remou, I've followed this one too, looks useful, however I'm getting a

"User-defined type not defined"

error against

"fs As FileSystemObject"

in the declaration

Dim fs As FileSystemObject....

Thanks !

 

[HarleyQuinn]

Add a reference to Microsoft Scripting Runtime.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[JerryKlmns]

Remou, I think closing the Recordset and the TextStream would be nice at the end of the loop . And if fs & f were declared as Object you wouldn't have to reference the library that HarleyQuinn mentions

 

[Remou]

JerryKlmns
I opened with "Here is an outline". I thought the purpose of this forum was to provide guidance, and in posting as much as I did, I rather exceeded this.

I am well aware that fs could have been declared as an object, however, declaring it as I did allows easy access the properties and methods, which seemed sensible as I did not believe that the OP was familar with the object. I would have though my comment "'Reference Windows Script Host Object Model" would have been a sufficient guide to referencing: that is the reference I used.

 

[HarleyQuinn]

Jerry - Got to agree with Remou, had he given the OP code using objects then, as the OP didn't seem to fully understand the object model they would have been stuck with a block of code that would have been very hard to play around with (due to the obvious lack of intellisense with objects bound in this way) and therefore useful as the block of code it is, but ultimately as a learning tool, not anywhere near as useful

Remou - I'm assuming that code is used with late binding, but you added in the reference and changed the declarations to fit for the OP's situation? If so, that's an added bit of helpfulness that is above and beyond

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[JerryKlmns]

Remou

I would say that you did over-exceed, providing a turn-key solution (many of us do it often just out of fun or whatever). I just wanted to point the need to close them, just two lines very obvious to you that might got forgotten. In my second sentence "you", was ment for OP and deffinetely not you. Sorry about that. I transelate as I type sometimes I 'm getting confused.

About setting the OP on search for "What is this ?" might do him some good. At least he's getting the habbit of "Search first, then ask" He might also learn on his own. But this is me, my way of learning.

Pls, don't misunterstood me, you have both earned my respect by your numerous helpfull postings.

Kind regards
Gerasimos

 

[Remou]

JerryKlmns
np.