Importing a txt file

[pmking]

Hello All,

I have this txt file that I am trying to import into an excel file. I only need bits and pieces of info within this txt file. Here is a sample of what the txt file that I need to import looks like: How to I pars this file on a windows platform. Or is there some VB code I can use to help me here. I have tried every function in excel and in access. I have used fixed width, and comma delimited options. But the way it looks here, is a cut and paste, that is how it shows up in excel. Is there a cleaner method. I need help please...

What I need from the below sample is LOCATION - SUBSYSTEM - DATE AND TIME - APPLICATION ELAPSE TIME for APPL (CL1)

Thank you and any help is greatly appreicated.

LOCATION: TEST DB2 PERFORMANCE EXPERT (V2) PAGE: 1-1
GROUP: TESTP0B ACCOUNTING REPORT - LONG REQUESTED FROM: NOT SPECIFIED
MEMBER: TEST TO: NOT SPECIFIED
SUBSYSTEM: TEST ORDER: PRIMAUTH INTERVAL FROM: 02/20/06 20:55:00.01
DB2 VERSION: V SCOPE: MEMBER TO: 02/20/06 21:55:00.00

PRIMAUTH: TEST

ELAPSED TIME DISTRIBUTION CLASS 2 TIME DISTRIBUTION
---------------------------------------------------------------- ----------------------------------------------------------------
APPL |==========================================> 84% CPU |========================================> 81%
DB2 |=======> 15% NOTACC |=====> 10%
SUSP |> 1% SUSP |====> 8%

AVERAGE APPL(CL.1) DB2 (CL.2) IFI (CL.5) CLASS 3 SUSPENSIONS AVERAGE TIME AV.EVENT HIGHLIGHTS
------------ ---------- ---------- ---------- -------------------- ------------ -------- --------------------------
ELAPSED TIME 0.056435 0.009664 N/P LOCK/LATCH(DB2+IRLM) 0.000416 1.02 #OCCURRENCES : 123592
NONNESTED 0.056068 0.009297 N/A SYNCHRON. I/O 0.000072 0.04 #ALLIEDS : 0
STORED PROC 0.000000 0.000000 N/A DATABASE I/O 0.000006 0.01 #ALLIEDS DISTRIB: 0
UDF 0.000000 0.000000 N/A LOG WRITE I/O 0.000067 0.03 #DBATS : 4369
TRIGGER 0.000368 0.000368 N/A OTHER READ I/O 0.000000 0.00 #DBATS DISTRIB. : 119223
OTHER WRTE I/O 0.000000 0.00 #NO PROGRAM DATA: 2
CPU TIME 0.009324 0.007871 N/P SER.TASK SWTCH 0.000005 0.01 #NORMAL TERMINAT: 123592
AGENT 0.009324 0.007871 N/A UPDATE COMMIT 0.000003 0.01 #ABNORMAL TERMIN: 0
NONNESTED 0.009255 0.007802 N/P OPEN/CLOSE 0.000000 0.00 #CP/X PARALLEL. : 0
STORED PRC 0.000000 0.000000 N/A SYSLGRNG REC 0.000000 0.00 #IO PARALLELISM : 0
UDF 0.000000 0.000000 N/A EXT/DEL/DEF 0.000002 0.00 #INCREMENT. BIND: 0
TRIGGER 0.000069 0.000069 N/A OTHER SERVICE 0.000000 0.00 #COMMITS : 127717
PAR.TASKS 0.000000 0.000000 N/A ARC.LOG(QUIES) 0.000000 0.00 #ROLLBACKS : 770
ARC.LOG READ 0.000000 0.00 #SVPT REQUESTS : 0
SUSPEND TIME 0.000000 0.000804 N/A DRAIN LOCK 0.000000 0.00 #SVPT RELEASE : 0
AGENT N/A 0.000804 N/A CLAIM RELEASE 0.000000 0.00 #SVPT ROLLBACK : 0
PAR.TASKS N/A 0.000000 N/A PAGE LATCH 0.000000 0.00 MAX SQL CASC LVL: 2
STORED PROC 0.000000 N/A N/A NOTIFY MSGS 0.000000 0.00 UPDATE/COMMIT : 0.28
UDF 0.000000 N/A N/A GLOBAL CONTENTION 0.000311 0.02 SYNCH I/O AVG. : 0.002000
COMMIT PH1 WRITE I/O 0.000000 0.00
NOT ACCOUNT. N/A 0.000990 N/A ASYNCH CF REQUESTS 0.000000 0.00
DB2 ENT/EXIT N/A 23.94 N/A TOTAL CLASS 3 0.000804 1.08
EN/EX-STPROC N/A 0.00 N/A
EN/EX-UDF N/A 0.00 N/A
DCAPT.DESCR. N/A N/A N/P
LOG EXTRACT. N/A N/A N/P

GLOBAL CONTENTION L-LOCKS AVERAGE TIME AV.EVENT GLOBAL CONTENTION P-LOCKS AVERAGE TIME AV.EVENT
------------------------------------- ------------ -------- ------------------------------------- ------------ --------
L-LOCKS 0.000311 0.02 P-LOCKS 0.000000 0.00

 

[PHV]

In the VBA help have a look to OPEN, EOF, Line Input, InStr, Mid and Close.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

Hi,

Scraping a report to create a table is a one-time time consuming task.

You must define the logic that will tell the program what each line of data is (ie detail single or multiple, heading single or multiple, footing single or multiple) and how to map the data to a tabular format.

It takes ALOT of time and detailed examination of the data.

Once you have done that exersize, then you can proceed with PHV's suggestions for coding what logic you have discovered and codified.

Skip,

_{[red]Be Advised![/red] A man's home is his castle...
in a Manor of speaking.}

 

[pmking]

Hello Skip,

Could help me get started please. What tool would I use, how do I define the mapping of my txt file?

Thanks.

 

[StuckInTheMiddle]

Search for help on Excels Text File Import wizard, it can help you.

A,

 

[SkipVought]

Well you could do a straight import into a SINGLE COLUMN in a sheet (Data/Get External Data/Import Text File...

Then I'd format the font in that column as Courier. Courier is a "fixed pitch" font, while most other are variable. This will make it easier to observe how data lines up with what headings.

Then YOU have to figger out what needs to be done with the data.

Skip,

_{[red]Be Advised![/red] A man's home is his castle...
in a Manor of speaking.}

 

[pmking]

Hello

SkipVought you were right on with the positioning of my imported file into excel. Now, you did state that 'I' have to figure what to do with the data. I know what pieces of data I need, my question to all is, now that it formats better than before with Skip's help. How do I extract the pieces of data I need. Do I need to use VB? If so, can you please just give me the VB code/syntax that will find the pieces of infor I need and place them into another worksheet. I am not asking for somebody to do the work for me, but please point me into the right direction, or get me started off. I am not sure how to via VB to:

1. Have the excel macro prompt me for what directory is my file in so I can manually choose it.

2. How do I choose via VB certain words that look like this and have the AVG, MPL, and ENDED under the TRANSACTION column be placed into another work sheet in a certain cell:
TRANSACTIONS
AVG 0.78
MPL 0.78
ENDED 13588


Can somebody just help me get started, please?

Thanks.