By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,790 Members | 1,409 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,790 IT Pros & Developers. It's quick & easy.

How to parse text file and make a report out of it?

P: 1
I would like to know how to parse a text file, then make a report out of it using Microsoft Access Macro. The text looks like this:

------------------------------------------------------------------------

*HEADER INFO*
*View Object:* 6599729 *BPO:* 0020072122 *Ack Date:* 2012-05-31
*Operation(s):* SYCTO_ASY_Queue, SYCTO_ASY_Build, PRETEST, RUNIN,
SYCTO_ASY_FASSY, SYCTO_ASY_Kitt, SYCTO_100pcAudit, SYCTO_SPU_BoxAll,
SYCTO_SPU_Print, SGP_End

------------------------------------------------------------------------

*EXTERNAL ORDER NUMBER REFERENCE*
*SAP Sales Order Number* *Customer P.O. Number* *Legacy Order Number*
0310481165 D42A81165304

------------------------------------------------------------------------

*PRODUCTS FOR THIS WORK OBJECT/OPERATION(S)*
*PL* *Product #* *Qty* *Options* *Serial #*
SY 483874-B21 1 #UUF SGH2216E2J
SI 601323-L21 1
SI 601323-B21 1 #0D1 SGH2216E2K
SI 500658-B21 2 #0D1 RAKWF8DXV2Q100
RAKWF8DXV2Q101
SI 534108-B21 1
SI 512327-B21 2 #0D1
SI 508107-B21 1 #0D1
SI 462969-B21 1 #0D1

------------------------------------------------------------------------

*Station Info*
*Start Station:* SGP_End *Location:* Done *Station:*
*Birth Date/Time:* 2012-05-23 14:07:33 SGT *Power Cord:* *Voltage:*

------------------------------------------------------------------------

*MATERIAL LIST FOR THIS WORK OBJECT/OPERATION(S)*
*Part Number* *Qty* *Description* *BB Type* *Material
Location* *Serial Number*
013224-001 1 PCA,DDR2-800,MINIDIMM MOD256MBx 40 BOM ASSY
ID121608ZS
122657-00A 1 SOFTWARE TEST (US M3 CTO) BOM ASSY
376383-002 8 ASSY, BLANK,SFF BOM ASSY
458943-003 1 CA ASSY, SFP BATTERY, 15 POS, 28AWG, 24 BOM ASSY
460499-001 1 ASSY, 4/V650HT BATTERY CHARGER MODULE BOM ASSY
499256-001 2 ASSY, BLANK,MEDIA BAY,ML350G6 BOM ASSY
500203-061 2 DIMM,4GB PC3-10600R,256Mx4,RoHS BOM ASSY
RAKWF8DXV2Q100
RAKWF8DXV2Q101
506821-001 2 PS,GNRC,1U,750W,12V,HTPLG,RED BOM ASSY 5AQXF0CLL2N0KX
5AQXF0CLL2N0K1
507930-001 1 HEATSINK ASSY, PROC,ML370G6 BOM ASSY
508107-B21@@ 1 HP ML/DL370G6 Redundant Fan Kit BOM ASSY
513875-001 1 ASSY, 8 SFF,370G6,PRI BOM ASSY
579616-001 1 LBL, ESS COMPOSITE 2009 BOM ASSY
582626-001 1 LBL, INTEL XEON RACK 320824-1 BOM ASSY
586631-001 2 IC, uP, WSM X5650 6c, 2.66 Ghz, 12M, 95W BOM ASSY
TBADV8DVX2Q04G
TBADV8DVX2Q04H
597695-001 1 ASSY DL370R06 Enhanced MOD0 SVR BOM ASSY LCN721209L7
142263-001 4 ASSY, CORD,AC,IEC TO IEC BOM KITT
410886-001 1 ASSY, RETAINER, PWR CRD-WRC BOM KITT
508037-371 1 CKIT, MMD-APD BOM KITT
533942-009 1 KIT, FIRMWARE DVD V9.30-CTO ONLY BOM KITT
374503-004 1 KIT, MNTG,HDWR,COMMON,3-7UG6 BOM PACK
410100-001 1 LBL, INTEL, BOX 253988-002 BOM PACK
247348-003 4 SCR, M3,TT,HI/TOP,SH,T15 BOM TKTKA1
293765-001 1 CARD, PASSWORD TAG BOM TKTKA1
305557-001 4 LBL, PN, SECURITY, BC, HP BOM TKTKA1
518171-001 2 SCR, 6-32,T10,FLUCH MOUNT BOM TKTKA1
534350-021 1 CARD, RTF SS CD AVAILABLE BY ORDER-SS BOM TKTKKI
0960-2697 1 LABEL 915 MHZ 96 BIT GEN II 4X2 ROHS BOM TKTKPA
106128-001 1 LBL, BLNK,THERMAL PRINT BOM TKTKPA
106128-011 1 LBL, BLNK,THERMAL 4x6 BOM TKTKPA
137301-002 1 BAG PLASTIC GUS-10X27X40 BOM TKTKPA
304049-001 80 TAPE, HP LOG0 72mm,MACHINE BOM TKTKP0
444770-002 1 TAPE, NON-RESIDUE, UNIT AND BAG-FT BOM TKTKP0
493892-001 1 INSERT, CORR. 370g6 TWR BOM TKTKP0
493896-001 1 CUSHION ASSY FT, 370g6 RCK BOM TKTKP0
493897-001 1 CUSHION ASSY FB, 370g6 RCK BOM TKTKP0
493898-001 1 CUSHION ASSY RT, 370g6 RCK BOM TKTKP0
493899-001 1 CUSHION ASSY RB, 370g6 RCK BOM TKTKP0
534138-001 1 TRAY INSERT BOM TKTKP0
534139-001 1 CUSHION, PS, RACK BOM TKTKP0
535358-001 1 TRAY, CORR, ML370 RACK M INSERT BOM TKTKP0
601979-001 1 BOX, RSC, 370G6 RACK, NEW BOM TKTKP0
602169-001 1 INSERT, CORR, DL370 FRONT BOM TKTKP0

I would like to parse the sales order, view object and part numbers, serial numbers and quantity so i can convert them into barcodes later. Problem is not all text is like this, it just follows this pattern. How do I do this?
Jun 6 '12 #1
Share this Question
Share on Google+
1 Reply


MMcCarthy
Expert Mod 10K+
P: 14,534
Firstly, have a look at the below code for reading text from a file. It also writes text out to a new file which you probably won't need. So you can parse the text until you reach a particular value like "View Object" then move to the appropriate location to find the value and store it wherever you wish.

This is a basic function for Reading from and Writing to a text file using VBA.

Expand|Select|Wrap|Line Numbers
  1. Public Function readTextFile(ByVal fpath As String, ByVal filename As String) As String
  2. On Error GoTo Err_readTextFile
  3. Dim fhandle1 As Integer
  4. Dim fhandle2 As Integer
  5. Dim fline As String
  6. Dim dateValue As String
  7. Dim counter As Integer
  8.  
  9.     counter = 1
  10.     ' open the text file to be read
  11.     fhandle1 = FreeFile()
  12.     Open fpath & filename For Input Access Read Lock Write As #fhandle1
  13.  
  14.     newFile "C:\Documents and Settings\Mary McCarthy\My Documents\UB\170106\testOut.txt"
  15.  
  16.     'open the text file to be written to
  17.     fhandle2 = FreeFile
  18.     Open fpath & "testOut.txt" For Output Access Write As #fhandle2
  19.  
  20.     Do While Not EOF(fhandle1) ' loop until you reach the end of the file.
  21.  
  22.         Line Input #fhandle1, fline
  23.         fline = Trim(fline)  ' cut off spaces
  24.  
  25.         If counter = 1 Then
  26.             dateValue = Mid$(fline, 14, 8)
  27.         Else
  28.             Print #fhandle2, fline
  29.         End If
  30.         counter = counter + 1
  31.     Loop
  32.  
  33.     Close #fhandle1
  34.     Close #fhandle2
  35.  
  36.     readTextFile = dateValue
  37.  
  38. Exit_readTextFile:
  39. Exit Function
  40. Err_readTextFile:
  41.     Select Case Err
  42.     Case 0      '.insert Errors you wish to ignore here
  43.         Resume Next
  44.     Case Else   '.All other errors will trap
  45.         Beep
  46.         MsgBox Err.Description, , "Error in Function Text File Handler.readTextFile"
  47.         Resume Exit_readTextFile
  48.     End Select
  49.     Resume 0    '.FOR TROUBLESHOOTING
  50. End Function
  51.  
Jun 12 '12 #2

Post your reply

Sign in to post your reply or Sign up for a free account.