Developing access macro to parse the data available in txt file

[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
Fund Code: 123 Class:ABC
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
Fund Code: 456 Class:XYZ
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!

 

[DJkarl]

Yikes, I've had to deal with txt files like this before and it's a pain in the @$$. The output is formatted for viewing as a report and not intended for data manipulation, the reports I get like this are coming out of an old AS/400 system. IF possible try to get them to export the raw data for you to a csv or tab delimited file, if that's not possible (and if your experience is like mine they'll to you it's not possible) then you are left to import this file and analyze it 1 line at a time. You could try importing it into a table but if it is from a mainframe you may have issues because some mainframes use the same line terminating characters that Access uses so Access tries to read the whole file into a single record and fails proclaiming it's too big.

Basic VBA to read in your file:

Open Filename For Input As FileNumber
Do Until EOF(FileNumber)
Line Input #FileNumber, LineData
<Some kind of analysis here>
Loop
Close FileNumber

If the report doesn't use character 13 (vbCR) to terminate the line then you may need to use a different input method.

 

[KeithG]

If you pulling data from AS400 check to see if you have an ODBC driver available then you can write a ps query to get your data.