converting single column of data into rows

[mealypudding]

Hello, i'm new to macros and need some help formatting some data. Im using windows XP and excel 2007. I am using Micros fidello, which creates data logs in .jnl files that can be opened in excel. My problem is the output is an undefined number of records of different lengths in 1 single column of data separated by (================================)
My desired outcome is to have several rows of data

Example input:
<TABLE style="WIDTH: 173pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=230><COLGROUP><COL style="WIDTH: 173pt; mso-width-source: userset; mso-width-alt: 8411" width=230><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 173pt; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20 width=230>Chk 9880 BFAST NON INC Gst 1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20>19 Employee1 WS02</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20>CE: 19 CC: 0 TC: 0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20>Trn 5424 01Apr'11 08:35</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20>--------------------------------</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20> S t a n d a r d </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20>08:40 Total 9 . 9 5 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20>================================</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20>Chk 9881 2008 Gst 1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20>19 Employee2 WS02</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20>CE: 19 CC: 0 TC: 0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20>Trn 5425 01Apr'11 11:20</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20>--------------------------------</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20> S t a n d a r d </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20> 2008 SMITH/MR</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20> Room Charge 9.95</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20> 1.66 VAT TTL 9.95</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20> Net TTL 8.29</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20> Subtotal 9.95</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20> Payment 9.95</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20>================================</TD></TR></TBODY></TABLE>

Example Output:
<TABLE style="WIDTH: 294pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=391><COLGROUP><COL style="WIDTH: 57pt; mso-width-source: userset; mso-width-alt: 2779" width=76><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2450" width=67><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 2121" width=58><COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2669" width=73><COL style="WIDTH: 36pt; mso-width-source: userset; mso-width-alt: 1755" width=48><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2523" width=69><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 57pt; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20 width=76>Chk 9880</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 50pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=67>19 Emp1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 44pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=58>CE: 19</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 55pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=73>Trn 5424</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 36pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=48>---------</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 52pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=69>Standard </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20>Chk 9881</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">19 Emp2</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">CE: 19</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">Trn 5425</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">---------</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">Standard</TD></TR></TBODY></TABLE>

Obviously I have shortened the output to fit it on the screen, but I basically do not want to change any information just turn it into rows of data and exclude the ======= separators.

Any help would be great as currently it is a nightmare to find anything in this data.

Cheers,

Mealypudding

 

[Ruddles]

Chk 9880 BFAST NON INC Gst 1
19 Employee1 WS02
CE: 19 CC: 0 TC: 0
Trn 5424 01Apr'11 08:35
--------------------------------
S t a n d a r d
08:40 Total 9 . 9 5

I suggest this needs a small VBA routine but in order to program it, we need to know exactly what data to expect. Can you confirm that this describes your data precisely:-

• Line 1: always Chk forllowed by a space, a number then another space
• Line 2: Always a number, a space and the word Employee with a number attached to it
• Line 3: CE: a space and a number
• Line 4: Trn a space and a number
• Line 5: A row of hyphens
• Line 6: The letters S t a n d a r d
• Lines 7->: some other lines which we ignore
• A row of equals characters telling us that the next record follows

 

[Ruddles]

Ignore those questions - i just re-read your post more carefully! I'll post some code in a moment...

 

[Ruddles]

The following code works on the basis of the small sample of data you've provided:-

[FONT=Fixedsys]Option Explicit[/FONT]
 
[FONT=Fixedsys]Public Sub ImportFile()[/FONT]
 
[FONT=Fixedsys]Dim sFileName As String[/FONT]
[FONT=Fixedsys]Dim sRec As String[/FONT]
[FONT=Fixedsys]Dim intFH As Integer[/FONT]
[FONT=Fixedsys]Dim iRec As Long[/FONT]
 
[FONT=Fixedsys]Dim ws As Worksheet[/FONT]
[FONT=Fixedsys]Dim iRow As Long[/FONT]
[FONT=Fixedsys]Dim iCol As Long[/FONT]
 
[FONT=Fixedsys]Dim iPtr As Integer[/FONT]
[FONT=Fixedsys]Dim sTime As Date[/FONT]
 
[FONT=Fixedsys]Set ws = ThisWorkbook.Sheets("Sheet1")[/FONT]
 
[FONT=Fixedsys]ws.Columns("A:N").ClearContents[/FONT]
[FONT=Fixedsys]ws.Range("A1:N1").Font.Bold = True[/FONT]
[FONT=Fixedsys]' set up column headings[/FONT]
[FONT=Fixedsys]ws.Range("A1:N1") = Array( _[/FONT]
[FONT=Fixedsys]         "Col.A", "Col.B", "Col.C", "Col.D", "Col.E", "Col.F", "Col.G", _[/FONT]
[FONT=Fixedsys]         "Col.H", "Col.I", "Col.J", "Col.K", "Col.L", "Col.M", "Col.N")[/FONT]
 
[FONT=Fixedsys] sFileName = Application.GetOpenFilename( _[/FONT]
[FONT=Fixedsys]             FileFilter:="Micros Fidelio Files (*.jnl), *.jnl, Text Files (*.txt), *.txt, All Files (*.*), *.*")[/FONT]
 
[FONT=Fixedsys]If sFileName = "False" Then Exit Sub[/FONT]
 
[FONT=Fixedsys]sTime = Now()[/FONT]
[FONT=Fixedsys]iRec = 0[/FONT]
[FONT=Fixedsys]iRow = 1[/FONT]
 
[FONT=Fixedsys]Close[/FONT]
[FONT=Fixedsys]intFH = FreeFile()[/FONT]
[FONT=Fixedsys]Open sFileName For Input As intFH[/FONT]
 
[FONT=Fixedsys]Do Until EOF(intFH)[/FONT]
[FONT=Fixedsys]  Line Input #intFH, sRec[/FONT]
[FONT=Fixedsys]  iRec = iRec + 1[/FONT]
[FONT=Fixedsys]  If Left(sRec, 4) = "Chk " Then[/FONT]
[FONT=Fixedsys]    ' first line of a record - start a new worksheet row[/FONT]
[FONT=Fixedsys]    iRow = iRow + 1[/FONT]
[FONT=Fixedsys]    iCol = 1[/FONT]
[FONT=Fixedsys]    ws.Cells(iRow, iCol) = sRec[/FONT]
[FONT=Fixedsys]    ' get next field for JNL file[/FONT]
[FONT=Fixedsys]    Line Input #intFH, sRec[/FONT]
[FONT=Fixedsys]    iRec = iRec + 1[/FONT]
[FONT=Fixedsys]    Do Until Left(sRec, 4) = "====" Or EOF(intFH)[/FONT]
[FONT=Fixedsys]      ' copy current record fields to worksheet[/FONT]
[FONT=Fixedsys]      iCol = iCol + 1[/FONT]
[FONT=Fixedsys]      ws.Cells(iRow, iCol) = sRec[/FONT]
[FONT=Fixedsys]      Line Input #intFH, sRec[/FONT]
[FONT=Fixedsys]      iRec = iRec + 1[/FONT]
[FONT=Fixedsys]      DoEvents[/FONT]
[FONT=Fixedsys]    Loop ' next field[/FONT]
[FONT=Fixedsys]  End If[/FONT]
[FONT=Fixedsys]  DoEvents[/FONT]
[FONT=Fixedsys]Loop ' next record[/FONT]
 
[FONT=Fixedsys]Close intFH[/FONT]
 
[FONT=Fixedsys]ws.Columns("A:N").EntireColumn.AutoFit[/FONT]
 
[FONT=Fixedsys]MsgBox "Finished:-" & vbCrLf & vbCrLf _[/FONT]
[FONT=Fixedsys]     & CStr(iRec) & " records imported from " & sFileName & Space(10) & vbCrLf & vbCrLf _[/FONT]
[FONT=Fixedsys]     & CStr(iRow) & " records created in worksheet" & Space(10) & vbCrLf & vbCrLf _[/FONT]
[FONT=Fixedsys]     & "Run time: " & Format(Now() - sTime, "hh:nn:ss"), vbOKOnly + vbInformation[/FONT]
 
[FONT=Fixedsys]End Sub[/FONT]

It goes into a new standard code module. If you're not sure how to do this, shout, and I or someone will post an explanation.

 

[mealypudding]

Thanks for speedy reply. I run the macro and get the message

Finished:-
3716 records imported from C:\doc+sett\user\desktop\WS01.jnl
292 records created in worksheet
Run time 00:00:00

However when I open the document there are no changes present.

I had a look and can see it's a simple loop macro but I'm still not sure how to figure out what I'm doing wrong.

Tried to extract the core of the program and run it in a excel sheet I had open but couldnt breakdown the code easily enough to understand what I didnt need.

Thanks for future assistance

 

[Ruddles]

However when I open the document there are no changes present.

Which document are you opening? The data from the JNL file is reformatted and imported into Sheet1 of the current Excel workbook. The fact that you're getting a message box with some record counts is A Good Sign.

Tried to extract the core of the program and run it in a excel sheet I had open but couldnt breakdown the code easily enough to understand what I didnt need.

You need all of it. I don't write code which doesn't do anything! Okay, well, maybe sometimes I do...

Create a new workbook, then press Alt-F11 and go Insert > Module. Paste my code into the new module and run it. Select a nice simple JNL file to start with, and when you get the message box with the record counts, go and check Sheet1.

 

[Ruddles]

Did you get anywhere with this?