Parsing irregular text strings

[nwallac1]

I am trying to parse stock holdings for certain companies but I am running into trouble because they are not uniform in their form. The data has a general form but each company uses a slightly different style for the forms. These differences are hamstringing my efforts to effectively parse the data.
I have attempted to parse the data using the split function to get it into a more manageable form, and then use various comparisons to tease out the right information. This, however, has not panned out like I hoped.
Below are some sample data forms and some of my (albeit poorly written and structured) VBA to try and solve the problem.

3COM CORP COM 885535104 6,987 1,700,000 SH OTHER 01 1,700,000

11 HILTON HOTELS CORPORATION common 432848109 3,490 100,000 SH CALL Shared-Defined NONE

Prudential Financial, Inc. COM 744320102 $ 1,796,511 20,923,720 SH SOLE 20,923,720

I am trying to extract the same fields from each, such as name or number of shares.

here is some of my code to try and accomplish this. this seems to work the data if it is formatted correctly, but I would like write a general parser.

txt = ActiveCell.Value
x = Split(txt, " ")

name = ""
cusip = ""
shrs = ""

Do While name = "" And cusip = "" And shrs = ""
For i = 0 To i = UBound(x)
If i = 0 Then
name = x(i)
End If

If Len(x(i)) = 9 And InStr(1, x(i), " ") = False And InStr(1, x(i), ",") = False Then
cusip = x(i)
End If

If i > 0 Then
If Len(x(i)) >= 4 And IsNumeric(Right(x(i - 1), 2)) = True And x(i - 1) <> cusip Then
shrs = x(i)
End If
End If
Next i
Loop



Any help or tips would be much appreciated. Thanks.

 

[SkipVought]

Hi,

Try parsing, using " " (two spaces) as the delimiter, and disregard empty array elements.

Skip,

_[red][/red]

 

[Bong]

You don't say what application you're using but if it's Excel, you might want to check this thread: thread707-1318585. This sounds like a very good application for Regular Expression matching.

_________________
Bob Rashkin

 

[CautionMP]

To build on what SkipVought said, you can use something like this to trim all spaces of 3 or more to double spaces then you don't have to 'eliminate disregard empty array elements'.

txt = ActiveCell.Value
Do
  l = Len(txt)
  txt = Replace(txt, Space$(3), Space$(2))
Loop Until l = Len(txt)
x = Split(txt, Space$(2))

Hope this helps,
CMP


[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[nwallac1]

Right, sorry about that. I am using Excel to try to do this. I have tried the " " delimiter, but problems arrise because fields are sometimes separated by a single " " if they are quite long. This leads a " " delimiter to mess up an entire entry because everything will be off by one entry.

 

[WinblowsME]

' TOOLS -> REFERENCES -> MICROSOFT VBSCRIPT REGULAR EXPRESSIONS 5.5

Sub Parse_Stock_Holdings()
   Dim regex As New RegExp, m As Match
   Dim curr_line As String, company As String, cusip As String, shares As String
   
   For i = 1 To 8
      curr_line = Cells(i, 1)
      
      regex.Global = False
      regex.Pattern = " [Cc][Oo][Mm][ Mm].*$"
      company = Trim(regex.Replace(curr_line, ""))
      
      regex.Pattern = " [0-9]{9} "
      For Each m In regex.Execute(curr_line)
         cusip = Trim(m)
      Next
      
      regex.Pattern = " ([0-9]*[,])*([0-9]*)* +SH +"
      For Each m In regex.Execute(curr_line)
         regex.Pattern = " *SH *$"
         shares = Trim(regex.Replace(m, ""))
      Next
      
      Debug.Print company & "~" & cusip & "~" & shares
   Next i
End Sub

 

[stevexff]

A regex-based solution looks the way to go, but I'm concerned that we are proposing a solution based on a sample of three records.

There seem to be nine data fields in the record[ul][li]company name[/li][li]COM or common[/li][li]a number[/li][li]a value, with commas and optional leading separate currency symbol[/li][li]another value with commas[/li][li]SH[/li][li]an alphabetic type[/li][li]things break down after this, so it's more tricky if you want this data parsed too[/li][/ul]Fields are separated by one or more blanks.

Because there are no reliable delimiters a truly generic solution won't be possible, but we can use similarities in the data as an heuristic.

We can probably use fields 2 and 6 as 'hooks' to help position the parser (as WinblowsME has done), but we are going to need to know which fields you want, what format you want them in, and a bigger representative sample of data to give you a really robust answer.


Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[nwallac1]

I am unfamiliar with regex-based code, so this is all new. The fields I am interested in are name, the first number (after COM) and the third number. I have tried using com as a marker for position in the file, but I found that there are other words used besides COM and common.

here are some of the listings from a single company. The problem I run into is the different words instead of COM that are used and the spacing, as sometimes there is only one space between entries if they are quite long. WinblowsME, when I try running your code I get an error saying user-defined type not defined in reference to the regex creation. Are there separate modules that need to be installed to get this to work?

3COM CORP COM 885535104 6,987 1,700,000 SH OTHER 01 1,700,000
ABB LTD SPONSORED ADR 000375204 19,897 1,106,600 SH OTHER 01 1,106,600
ACCREDITED HOME LENDRS HLDG COM 00437P107 54,016 1,975,000 SH OTHER 01 1,975,000
AFFORDABLE RESIDENTIAL CMNTY NOTE 7.500% 8/1 00828UAB9 2,221 2,000,000 PRN OTHER 01 2,000,000
AFFORDABLE RESIDENTIAL CMTYS COM 008273104 53,031 4,552,000 SH OTHER 01 4,552,000
AIRTRAN HLDGS INC COM 00949P108 40,855 3,480,000 SH OTHER 01 3,480,000
AMERICA MOVIL S A DE C V SPON ADR L SHS 02364W105 11,531 255,000 SH OTHER 01 255,000
AMKOR TECHNOLOGY INC COM 031652100 176 18,809 SH OTHER 01 18,809
AMR CORP COM 001765106 17,820 589,490 SH OTHER 01 589,490
AMYLIN PHARMACEUTICALS INC COM 032346108 89,544 2,482,500 SH OTHER 01 2,482,500
APOLLO GROUP INC CL A 037604105 14,419 370,000 SH OTHER 01 370,000
ARBOR RLTY TR INC COM 038923108 19,775 657,200 SH OTHER 01 657,200
ARCH CAP GROUP LTD ORD G0450A105 129,274 1,912,055 SH OTHER 01 1,912,055
ASIAINFO HLDGS INC COM 04518A104 11,520 1,500,000 SH OTHER 01 1,500,000
ATHEROGENICS INC COM 047439104 6,855 691,728 SH OTHER 01 691,728
AVIS BUDGET GROUP COM 053774105 1,670 77,000 SH OTHER 01 77,000
BAIDU COM INC SPON ADR REP A 056752108 15,213 135,000 SH OTHER 01 135,000
BED BATH & BEYOND INC COM 075896100 48,578 1,275,000 SH OTHER 01 1,275,000
BELLSOUTH CORP COM 079860102 141,483 2,986,850 SH OTHER 01 2,986,850
BOSTON SCIENTIFIC CORP COM 101137107 87,704 5,105,000 SH OTHER 01 5,105,000
BROCADE COMMUNICATIONS SYS I COM 111621108 173 21,011 SH OTHER 01 21,011
BUILDERS FIRSTSOURCE INC COM 12008R107 187 10,472 SH OTHER 01 10,472
CALLON PETE CO DEL COM 13123X102 174 11,563 SH OTHER 01 11,563
CAPELLA EDUCATION COMPANY COM 139594105 14,429 595,000 SH OTHER 01 595,000
CAPITALSOURCE INC COM 14055X102 757,389 27,733,036 SH OTHER 01 27,733,036
CARDINAL HEALTH INC COM 14149Y108 47,678 740,000 SH OTHER 01 740,000
CASCADE MICROTECH INC COM 147322101 6,419 490,000 SH OTHER 01 490,000
CASELLA WASTE SYS INC CL A 147448104 14,407 1,178,002 SH OTHER 01 1,178,002
CELANESE CORP DEL COM SER A 150870103 265 10,243 SH OTHER 01 10,243
CENTRAL EUROPEAN DIST CORP COM 153435102 1,782 60,000 SH OTHER 01 60,000
CHARLES RIV LABS INTL INC COM 159864107 91,474 2,115,000 SH OTHER 01 2,115,000
CHART INDS INC COM PAR $0.01 16115Q308 8,157 503,200 SH OTHER 01 503,200
CHINA TECHFAITH WIRLS COMM T SPONSORED ADR 169424108 2,965 275,000 SH OTHER 01 275,000
CHIPMOS TECH BERMUDA LTD SHS G2110R106 199 29,309 SH OTHER 01 29,309
CITADEL BROADCASTING CORP NOTE 1.875% 2/1 17285TAB2 11,802 14,240,000 PRN OTHER 01 14,240,000
CITIZENS COMMUNICATIONS CO COM 17453B101 249 17,305 SH OTHER 01 17,305
CLEAR CHANNEL COMMUNICATIONS COM 184502102 211,314 5,945,800 SH OTHER 01 5,945,800
COMCAST CORP NEW CL A SPL 20030N200 117,683 2,810,000 SH OTHER 01 2,810,000
COMVERSE TECHNOLOGY INC COM PAR $0.10 205862402 34,937 1,655,000 SH OTHER 01 1,655,000
CONTINENTAL AIRLS INC CL B 210795308 72,423 1,755,703 SH OTHER 01 1,755,703
COSTCO WHSL CORP NEW COM 22160K105 9,913 187,500 SH OTHER 01 187,500
CUBIST PHARMACEUTICALS INC COM 229678107 42,156 2,327,800 SH OTHER 01 2,327,800
D & E COMMUNICATIONS INC COM 232860106 4,379 346,157 SH OTHER 01 346,157
E M C CORP MASS COM 268648102 85,272 6,460,000 SH OTHER 01 6,460,000
EBAY INC COM 278642103 27,815 925,000 SH OTHER 01 925,000
EDISON INTL COM 281020107 50,028 1,100,000 SH OTHER 01 1,100,000
EMBARQ CORP COM 29078E105 10,978 208,872 SH OTHER 01 208,872
ESCHELON TELECOM INC COM 296290109 853 43,041 SH OTHER 01 43,041
FIDELITY NATIONAL FINANCIAL CL A 31620R105 2,282 95,551 SH OTHER 01 95,551
FIDELITY NATL INFORMATION SV COM 31620M106 171,144 4,269,000 SH OTHER 01 4,269,000
FIRST DATA CORP COM 319963104 26,355 1,032,700 SH OTHER 01 1,032,700
FLORIDA ROCK INDS INC COM 341140101 28,736 667,500 SH OTHER 01 667,500
FREEPORT-MCMORAN COPPER & CL B 35671D857 13,683 245,516 SH OTHER 01 245,516
GEOEYE INC COM 37250W108 14,211 734,438 SH OTHER 01 734,438
GOLF TR AMER INC COM 38168B103 165 112,200 SH OTHER 01 112,200
GOOGLE INC CL A 38259P508 38,680 84,000 SH OTHER 01 84,000
GREY WOLF INC COM 397888108 174 25,395 SH OTHER 01 25,395
GRUPO TELEVISA SA DE CV SP ADR REP ORD 40049J206 279,797 10,359,000 SH OTHER 01 10,359,000
HARRAHS ENTMT INC COM 413619107 372,054 4,497,750 SH OTHER 01 4,497,750
HAYES LEMMERZ INTL INC COM NEW 420781304 3,319 848,800 SH OTHER 01 848,800
HERCULES TECH GROWTH CAP INC COM 427096508 26,700 1,873,701 SH OTHER 01 1,873,701
HILTON HOTELS CORP COM 432848109 155,480 4,455,000 SH OTHER 01 4,455,000
HURRAY HLDGS CO LTD SPONSORED ADR 447773102 3,720 600,000 SH OTHER 01 600,000
INTERSTATE HOTELS & RESRTS COM 46088S106 190 25,500 SH OTHER 01 25,500
INVITROGEN CORP COM 46185R100 28,912 510,900 SH OTHER 01 510,900
ISHARES TR DJ US REAL EST COM 464287739 53,761 645,000 SH PUT OTHER 01 645,000
ISHARES TR MSCI EMERG MKT COM 464287234 38,247 335,000 SH PUT OTHER 01 335,000
ISHARES TR NASDQ BIO INDX COM 464287556 78,833 1,013,800 SH PUT OTHER 01 1,013,800
ISHARES TR RUSSELL 2000 COM 464287655 1,647,213 21,110,000 SH PUT OTHER 01 21,110,000
J CREW GROUP INC COM 46612H402 18,697 485,000 SH OTHER 01 485,000
JDA SOFTWARE GROUP INC COM 46612K108 17,448 1,267,100 SH OTHER 01 1,267,100
KINDRED HEALTHCARE INC COM 494580103 19,443 770,000 SH OTHER 01 770,000
KKR FINL CORP COM 482476306 85,672 3,197,900 SH OTHER 01 3,197,900
KNOLOGY INC COM 499183804 26,595 2,499,510 SH OTHER 01 2,499,510
LEAP WIRELESS INTL INC COM NEW 521863308 104,721 1,760,900 SH OTHER 01 1,760,900
LIBERTY MEDIA HLDG CORP INT COM SER A 53071M104 117,017 5,425,000 SH OTHER 01 5,425,000
LOEWS CORP CAROLNA GP STK 540424207 254 3,926 SH OTHER 01 3,926
MAGNA ENTMT CORP CL A 559211107 8,524 1,890,000 SH OTHER 01 1,890,000
MARATHON OIL CORP COM 565849106 239 2,587 SH OTHER 01 2,587
MASTERCARD INC CL A 57636Q104 134,803 1,368,700 SH OTHER 01 1,368,700
MDS INC COM 55269P302 9,760 535,000 SH OTHER 01 535,000
MEDECISION INC COM 58406P102 3,750 375,000 SH OTHER 01 375,000
MEDICINES CO COM 584688105 65,819 2,075,000 SH OTHER 01 2,075,000
MI DEVS INC CL A SUB VTG 55304X104 99,214 2,779,100 SH OTHER 01 2,779,100
MICROSOFT CORP COM 594918104 215,365 7,212,500 SH OTHER 01 7,212,500
MILLS CORP COM 601148109 122,900 6,145,000 SH OTHER 01 6,145,000
NETEASE COM INC SPONSORED ADR 64110W102 3,738 200,000 SH CALL OTHER 01 200,000
NEWFIELD EXPL CO COM 651290108 238 5,177 SH OTHER 01 5,177
NEWS CORP CL A 65248E104 63,688 2,965,000 SH OTHER 01 2,965,000
ORACLE CORP COM 68389X105 145,491 8,488,399 SH OTHER 01 8,488,399
PAPA JOHNS INTL INC COM 698813102 4,642 160,000 SH OTHER 01 160,000
PHARMACEUTICAL HLDRS TR DEPOSITRY RCPT 71712A206 26,933 350,000 SH PUT OTHER 01 350,000
PHELPS DODGE CORP COM 717265102 42,046 351,200 SH OTHER 01 351,200
PHH CORP COM NEW 693320202 131,514 4,555,400 SH OTHER 01 4,555,400
PHILIPPINE LONG DISTANCE TEL SPONSORED ADR 718252604 63,120 1,234,500 SH OTHER 01 1,234,500
PRIMEDIA INC COM 74157K101 3,253 1,925,000 SH OTHER 01 1,925,000
PT INDOSAT TBK SPONSORED ADR 744383100 6,812 176,700 SH OTHER 01 176,700
QUALCOMM INC COM 747525103 46,742 1,236,900 SH CALL OTHER 01 1,236,900
QUALCOMM INC COM 747525103 167,599 4,435,000 SH OTHER 01 4,435,000
QUANTUM CORP COM DSSG 747906204 508 218,800 SH OTHER 01 218,800
RAM ENERGY RESOURCES INC COM 75130P109 4,212 764,370 SH OTHER 01 764,370
REALNETWORKS INC COM 75605L104 175 16,014 SH OTHER 01 16,014
REALOGY CORP COM 75605E100 72,086 2,377,500 SH OTHER 01 2,377,500
REDBACK NETWORKS INC COM NEW 757209507 29,928 1,200,000 SH OTHER 01 1,200,000
ROGERS COMMUNICATIONS INC CL B 775109200 20,536 690,000 SH OTHER 01 690,000
ROTECH HEALTHCARE INC COM 778669101 958 427,760 SH OTHER 01 427,760
RYANAIR HLDGS PLC SPONSORED ADR 783513104 810 9,934 SH OTHER 01 9,934
SANDERSON FARMS INC COM 800013104 20,491 676,500 SH OTHER 01 676,500
SEAGATE TECHNOLOGY SHS G7945J104 70,978 2,678,400 SH OTHER 01 2,678,400
SEALED AIR CORP NEW COM 81211K100 66,543 1,025,000 SH OTHER 01 1,025,000
SEALY CORP COM 812139301 65,638 4,450,000 SH OTHER 01 4,450,000
SHANGHAI CENTURY ACQUISIT CO SHS G80637104 3,680 503,400 SH OTHER 01 503,400
SHAW COMMUNICATIONS INC CL B CONV 82028K200 29,170 921,400 SH OTHER 01 921,400
SHERWIN WILLIAMS CO COM 824348106 68,728 1,080,961 SH OTHER 01 1,080,961
SOUTHWEST AIRLS CO COM 844741108 129,760 8,470,000 SH OTHER 01 8,470,000
SPIRIT FIN CORP COM 848568309 63,924 5,126,200 SH OTHER 01 5,126,200
SPRINT NEXTEL CORP COM FON 852061100 65,594 3,472,436 SH OTHER 01 3,472,436
STATION CASINOS INC COM 857689103 50,105 613,500 SH OTHER 01 613,500
STEEL DYNAMICS INC COM 858119100 246 7,588 SH OTHER 01 7,588
SUN MICROSYSTEMS INC COM 866810104 63 11,621 SH OTHER 01 11,621
SYSTEMAX INC COM 871851101 228 13,053 SH OTHER 01 13,053
TARGET CORP COM 87612E106 210,229 3,685,000 SH OTHER 01 3,685,000
TECK COMINCO LTD CL B 878742204 55,790 740,000 SH OTHER 01 740,000
TELIK INC COM 87959M109 3,544 800,000 SH OTHER 01 800,000
TOWN SPORTS INTL HLDGS INC COM 89214A102 87,859 5,331,279 SH OTHER 01 5,331,279
TREEHOUSE FOODS INC COM 89469A104 49,764 1,595,000 SH OTHER 01 1,595,000
TRM CORP COM 872636105 1,571 734,274 SH OTHER 01 734,274
TYCO INTL LTD NEW COM 902124106 55,024 1,810,000 SH OTHER 01 1,810,000
U S AIRWAYS GROUP INC COM 90341W108 85,547 1,588,616 SH OTHER 01 1,588,616
UAL CORP COM NEW 902549807 136,400 3,100,000 SH OTHER 01 3,100,000
UNITED STATES STL CORP NEW COM 912909108 246 3,370 SH OTHER 01 3,370
UNITED THERAPEUTICS CORP DEL COM 91307C102 28,207 518,800 SH OTHER 01 518,800
UNIVISION COMMUNICATIONS INC CL A 914906102 185,046 5,224,350 SH OTHER 01 5,224,350
VAALCO ENERGY INC COM NEW 91851C201 160 23,748 SH OTHER 01 23,748

 

[Bong]

Well that field appears to always be 9 characters, the first of which is a number:

Sub parseLst(strA)
    arrA = Split(strA)
    n = UBound(arrA)
    For i = 0 To n
        If Len(arrA(i)) = 9 And IsNumeric(Left(arrA(i), 1)) Then Debug.Print "number>", arrA(i)
    Next
End Sub

Whereby:
parseLst("AMERICA MOVIL S A DE C V SPON ADR L SHS 02364W105 11,531 255,000 SH OTHER 01 255,000")
gives: number> 02364W105


_________________
Bob Rashkin

 

[stevexff]

I don't know if it is just an artefact of the way you've pasted it, but in the data from the first post, they aren't spaces between the fields, they are CHR(160). This means you can

Sub Parse(s As String)
    
    Dim r As RegExp, mc As MatchCollection
    Set r = New RegExp
    r.Global = True
    r.Pattern = "(\d),(\d)"
    s = r.Replace(s, "$1$2")    'remove commas from numbers
    r.Pattern = "([^\xA0]+)"    'anything not x'A0'
    Set mc = r.Execute(s)
    For Each m In mc
        For Each sm In m.SubMatches
            Debug.Print sm
        Next
    Next
End Sub

which produces

11 HILTON HOTELS CORPORATION
common
432848109
3490
100000
SH
CALL
Shared-Defined
NONE
Prudential Financial, Inc.
COM
744320102
$ 1796511
20923720
SH
SOLE
20923720

from the first three rows in the OP. In other words, it parses it perfectly, leaving you to do whatever you want with the resulting data.

BTW I've taken the liberty of using another regex to strip out the commas from the numbers, if you don't need this you can comment it out...

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[stevexff]

OK, cleaned up version. Replace the Debug.Print with whatever you want to do with the 3 fields it collects

Sub Parse(s As String)
    
    Dim r As RegExp, mc As MatchCollection
    Set r = New RegExp
    r.Global = True
    r.Pattern = "(\d),(\d)"
    s = r.Replace(s, "$1$2")    'remove commas from numbers
    r.Pattern = "([^\xA0]+)"    'anything not x'A0'
    Set mc = r.Execute(s)
    If mc.Count > 3 Then
       Debug.Print mc.Item(0) & "|" & mc.Item(2) & "|" & mc.Item(4)
    End If
    
End Sub

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[nwallac1]

Thanks Stevexff. unfortunatly I have not been able to try it out yet. I am using Excel (VBA) to try and parse this stuff and regexp does not seem to be functional for VBA. I read somewhere that there is a way to get regexp for VBA but, i have not figured out how. Does anyone have any advice?

 

[stevexff]

WinblowsME already posted this in the code - go to TOOLS -> REFERENCES -> MICROSOFT VBSCRIPT REGULAR EXPRESSIONS 5.5 and check the box. Once you've done that, you should be in business.

My example ran in Excel, which is VBA.

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[CautionMP]

Ok let's take a step back. The latest file sample you provided is fixed width so there should be no need build fancy parsing logic. Some quick questions to help clarify what your doing.

[ol][li]You said there are several file formats, of the three provided are the other two a similar fixed width format?[/li][li]Do they come as seperate files from each source so the data is only in one format per file?[/li][li]Are you currently opening the flat file in Excel and then trying to parse the data out of column A?[/li][/ol]

If the answers to the above are all yes, try something like this. It will open a text file in a specific format (the data you posted earlier) and drop the data into the active sheet in Excel.

If you have multiple files of different formats you could easily loop through them and use different data structures to break the files up and drop them into the same Excel worksheet, and determine the data structure by looking at the filename or the first line or two of data. This would allow you to process x number of files in any number of data formats with one routine.

Here is an example that will work for the large dataset you posted earlier:

'You can change the field names to something more relevent to you
Public Type dFormat1
  F1 As String * 30
  F2 As String * 17
  F3 As String * 9
  F4 As String * 12
  F5 As String * 12
  F6 As String * 4
  F7 As String * 5
  F8 As String * 9
  F9 As String * 2
  F10 As String * 17
  LineBreak As String * 2
End Type

Sub Read_dForamt1()
'Make sure the filename matches you real data
Const cFilename As String = [b]"[i]C:\dFormat1.txt[/i]"[/b]
Dim udt1 As dFormat1
Dim lngRow As Long
Dim intFile As Integer
Dim intField As Integer

intFile = FreeFile
Open cFilename For Binary As #intFile
Do
  Get #intFile, , udt1
  lngRow = lngRow + 1
  ActiveSheet.Cells(lngRow, 1).Value = udt1.F1
  ActiveSheet.Cells(lngRow, 2) = udt1.F2
  ActiveSheet.Cells(lngRow, 3) = udt1.F3
  ActiveSheet.Cells(lngRow, 4) = udt1.F4
  ActiveSheet.Cells(lngRow, 5) = udt1.F5
  ActiveSheet.Cells(lngRow, 6) = udt1.F6
  ActiveSheet.Cells(lngRow, 7) = udt1.F7
  ActiveSheet.Cells(lngRow, 8) = udt1.F8
  ActiveSheet.Cells(lngRow, 9) = udt1.F9
  ActiveSheet.Cells(lngRow, 10) = udt1.F10
Loop Until EOF(intFile)
Reset
End Sub

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[stevexff]

there should be no need build fancy parsing logic

Possibly, but where's the fun in that...

Besides, if the other company files are slightly different, the regex solution is more likely to be able to cope with it.

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]