Importing text file to excel, when there are multiple rows?

[amtrak23]

I have a very large report that is generated in a txt file that I need to import to excel or access so the data can be manipulated, etc.
I have done this in the past very easily with excel and other tools like Crimson Editor etc., but never when there are two rows involved that are all the same data.

Generally it's a single row that can easily be converted over to excel, but now that there are two or three rows of data that all belong to the same block of info I don't know what to do.

Here is an example of the text:

http://mistertees.net/test.txt

Some sections of data go on for pages while other just have one or two entry's like in this example. Is there any way to get this straightened out? We need to be able to sort by date, case number (the CVF#), or the file number (123456). This particular file is 196 pages long so doing anything manually is kind out of the picture.

I was thinking as a last resort, print out the txt file and scan/ocr so it could at least be searched via PDF? It would be great if we could get it into excel and have full ability to sort, etc.

Thanks,

Amtrak23

 

[warpdrve]

Amtrak:

If this is something that you are running into quite frequently, you might want to look into a program called Monarch, from Datawatch. Here is the website:

http://monarch.datawatch.com/monarch-standard.asp

I have used this program ever since version 3 (the DOS version) and have continually found that it makes my life a lot easier when it comes to grabbing and sorting through data. It is primarily designed for data mining of mainframe and host report data, but works well on standard text files as well.

How it works is that you "build" a model based on the layout of the data you want to mine and from there, you simply export the data to any number of file formats (including Excel, and .dbf). The only caveat is that it works best when the data being "grabbed" is uniform in its structure ...

Hope this helps.

Regards - M

----------------------------------------------------------------
Always proofread carefully to see if you any words out.]
----------------------------------------------------------------

 

[KenWright]

One manual way:-

Assuming your data is absolutely uniform in that there are for example 5 rows for EVERY 'record', then assuming your data starts at cell A1, with next at A6, A11, etc, then insert 2 new columns at the start such that you now have a blank Col A & B.

In cell A1 put =MOD(ROW()-1,5)+1 and copy down to the end of your data. Select Col A and copy then paste special values

In cells B1:Bxx fill down with 1,2,3,4,5,6,7,8 etc.

Your data should now look like

1 01 Record1 Record1
2 02 Record1 Record1
3 03 Record1 Record1
4 04 Record1 Record1
5 05 Record1 Record1
1 06 Record2 Record2
2 07 Record2 Record2
3 08 Record2 Record2
4 09 Record2 Record2
5 10 Record2 Record2
1 11 Record3 Record3
2 12 Record3 Record3
3 13 Record3 Record3
4 14 Record3 Record3
5 15 Record3 Record3

Now just select all and sort on Col A Ascending & then B Ascending (one sort with both parameters in it). This will give you 5 blocks of data denoted by the data in Col A. Just drag Data 2 up next 1, Data 3 up next to 2, Data 4 up next to 3, Data 5 up next 4 and you are done.

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[amtrak23]

That Monarch software looks like a very nice tool. Unfortunately this is probably only a one time deal so dropping $500 on it is probably out of the question. I was hoping they had a demo version but they don't.

Ken, our data is not exactly uniform, but for each different section it is. That probably doesn't make to much sense, unless you look at the sample I posted. I was playing with the excel function and I was getting some pretty interesting results, just not exactly what I needed.

 

[CautionMP]

Who needs Monarch, we're programmers!

Here is a code snippet that should get you moving in the right direction. The output is to the immediate window in the Project Explorer and the format is a complete assumption on my part.

Public Sub ProcessTextFile()
'Point to the text file you want to open
Const SourceFile = "C:\thread68-1032059.txt"
Const Comma = ","
Dim intInputFile As Integer
Dim intLineIndex As Integer
Dim strLineText As String, strRecordText As String
Dim H1 As String, H2 As String

'Open the source text file defined by Const SourceFile
intInputFile = FreeFile
Open SourceFile For Input As #intInputFile

'Move through the file one line at a time until the end of the & _
file has been reached.
Do
  'Grab the next line in the file
  Line Input #intInputFile, strLineText
  
  'Look at the line that was just grabbed, if it isn't blank & _
  do something with it
  If Trim(strLineText) <> "" Then
    
    'Define the begining and end of each 'record' using keys in & _
    in the text file
    If InStr(strLineText, "Count Case No") > 0 Then
      intLineIndex = -1
    ElseIf InStr(strLineText, "Run date") > 0 Then
      intLineIndex = 1
    Else
      intLineIndex = intLineIndex + 1
    End If
    'Decide what to do with the text in the currnet line based on & _
    where in the 'record' the current line falls
    Select Case intLineIndex
      Case -1, 1
        H1 = ""
        H2 = ""
        strRecordText = ""
      Case 2
        H1 = Trim(strLineText)
      Case 3
        H2 = Trim(strLineText)
      Case Else
        strRecordText = strRecordText & strLineText
        'It appears that every odd line after 3 signals the end of a & _
        record so write the complete record to the Immediate window
        If intLineIndex Mod 2 = 1 Then
          Debug.Print H1 & Comma & H2 & Comma & strRecordText
        End If
    End Select
    
  End If
Loop Until EOF(intInputFile)

'Clean up by closing the text file
Close #intInputFile
End Sub

I have used similar code to read text files, HTML files, host application screens and can be adapted to output to another text file, a worksheet, an Access Recordset... depending on what your needs are.

Hope this helps.

 

[amtrak23]

The bad news is I'm not a programmer and I only understand about 10% of that snippet. The good news is that I did have one VB class college so maybe I can make something out of this. I also did stay at a Holiday Inn lately!

 

[CautionMP]

If the Holiday Inn doesn't help, this might.

After the Open SourceFile For Input As #intInputFile try adding this code:

Dim intOutputFile as Integer
intOutputFile = FreeFile
Open "C:\Output.txt" For Output As #intOutputFile

Then replace the Debug.Print with:

Write #intOutputFile,

Then at the very bottom:

Close #intFileOutput

This will write the output as a text file that you can then open with Excel or link to with Access to manipulate further.

Motel Six will leave the light on for you if Holiday Inn doesn't work.

 

[CautionMP]

I had an afterthought.

If you are going to open the file in Excel or link to it with Access and are not planning to expand on the code I would take out the Trim statments for H1 & H2 and remove the comma from the debug.print/write # statement (but leave 1 ampersand to glue the strings together). This should give you a file that can be interperated as 'fixed width' by Excel or Access.

CMP
Just because something can be done doesn't make it a good idea...

...but how will you know until you try?