Hello everyone-

I am working at a company that uses a very old host system to run their
construction business. Until yesterday, nobody has ventured in capturing
reports to file, instead of printing them out. After 8 hours of actually
accessing the FTP remote thru DOS(!!!!!) I was able to get .txt files out of
system.
However, importing them into excel has been a big pain.
The reports do have a delimiter between the main columns (+), so the main
part of the report is seperated into the proper columns. However the header
information is a major problem. The delimiters do not match up with the
columns of the main part of the report, so I have to manually delete their
seperators, which leaves me with useless header information. Unfortunately
the header information repeats after each job, and the report has about 80
jobs on it. So there is a lot of useless information trapped in there that i
dont know what to do with.

Does anyone have any ideas for a solution??

Mariano

Just summarising the issue : you get a text version of a report which is
a good old fashined report.
The report has page breaks and page headers and footers and section
headers in it.

In this case I usually find it easiest to parse the file before
importing it. I do this using the "Open file for input" and the input
line statement. A few well planned tests (looking for blank line, the
text of the headers etc) can soon determine whether you want to keep
the line or not.

Dim TextLine As String
Open "inFILE.TXT" For Input As #1 ' Open file.
Open "outFILE.TXT" For Output As #2
Do While Not EOF(1) ' Loop until end of file.
Line Input #1, TextLine ' Read line into variable.
If Mid(TextLine, 5, 6) < "Page :" Then
Print #2, TextLine
End If
Loop
Close #1, #2

The resultant file should import with no difficulty.

If the structure is complex it may be worth parsing each line for data
and placing it directly into the spreadsheet. without seeing your text
file it is difficult to say what is best.

Good luck


--
tony h
------------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
View this thread: http://www.excelforum.com/showthread...hreadid=505339

I've done a lot of this type of work in the past, and have used Visual
Basic to great success. I'm guessing since you even know of the
existence of the DOS command prompt FTP program you can write code, or
at least figure it out- you can also do this with the VBA compiler that
is native to Excel.

It sounds like the report contains information you want and header /
footer / whitespace information that you do not want. You are manually
deleting that stuff already, so the trick is to figure out what makes
the useful information different from the useless information and
translate that difference into a decision the computer can make. For
instance- the useful information contains a + sign delimiter. Does the
header contain that? If it does not, you could write a quick program
to open the source file, read each line, and write only the lines that
contain a + to a separate file. Then import the "plusses only" file to
Excel. Or, the header line may contain the word "Date: ", so you could
write the program to exclude lines that contain that text string.

Assume for the moment that the headers do NOT contain a + sign. To
pursue this solution, you could run code that looks like this to
"condition" the data into a useable format. This code is written for
Excel VBA, in case you do not have a Visual Basic compiler on your
computer. The apostrophe within the code indicates a comment.

Sub Kill_Headers()
'declare variables
Dim File_Line As String

Open "c:\FTP_File.txt" For Input As #1 'Open the source file
Open "c:\No_Headers.txt" For Output As #2 'Create a file for data only

Do While Not EOF(1) 'Process the source file through to the end
Line Input #1, File_Line 'read a line from the source file into
memory
If InStr(1, File_Line, "+") < 0 Then Print #2, File_Line 'write the
line to the output file if it contains a +
Loop

Close #1
Close #2
End Sub

The output file would then be free of headers, and ready for import to
Excel. You could also expand this code to write the information
directly into Excel instead of an output file, but this may be an
easier first step.

Let me know what you think- I'll be glad to help revise this sample
code to match your situation.

Are the headers always the same?

Can you pick out something on each line of the headers? Then you could use that
to delete those rows. Then insert new headers yourself.

If you import your data via a macro, you could adjust the macro to add the
headers--and other stuff you need, too.



Mariano wrote:

Hello everyone-

I am working at a company that uses a very old host system to run their
construction business. Until yesterday, nobody has ventured in capturing
reports to file, instead of printing them out. After 8 hours of actually
accessing the FTP remote thru DOS(!!!!!) I was able to get .txt files out of
system.
However, importing them into excel has been a big pain.
The reports do have a delimiter between the main columns (+), so the main
part of the report is seperated into the proper columns. However the header
information is a major problem. The delimiters do not match up with the
columns of the main part of the report, so I have to manually delete their
seperators, which leaves me with useless header information. Unfortunately
the header information repeats after each job, and the report has about 80
jobs on it. So there is a lot of useless information trapped in there that i
dont know what to do with.

Does anyone have any ideas for a solution??

Mariano

--

Dave Peterson

Thank you for the suggestions...
I am still learning visual basic, I can follow code, but not neccessarily
write it. I basically learn as I go.
I am going to play with all these suggestions and hopefully drag a buddy of
mine into this to help me with VB.

I kept playing with the text file, and I was incorrect about the delimiter.
The "+" delimiter is actually only included in the column header. The body
of the report uses a "|" as a delimiter. I would need to do something that
would find all the lines that begin with a "+" and only use the data in
between that and the next line that begins with a "+", but also delimit all
the rows at the same place the "+" is in the column header. I hope that makes
some sense.
Tony, you were correct the reports does have page breaks with the headers
and footers, which i would also need to get rid of.

man, i got to learn VB asap!



"tony h" wrote:


Just summarising the issue : you get a text version of a report which is
a good old fashined report.
The report has page breaks and page headers and footers and section
headers in it.

In this case I usually find it easiest to parse the file before
importing it. I do this using the "Open file for input" and the input
line statement. A few well planned tests (looking for blank line, the
text of the headers etc) can soon determine whether you want to keep
the line or not.

Dim TextLine As String
Open "inFILE.TXT" For Input As #1 ' Open file.
Open "outFILE.TXT" For Output As #2
Do While Not EOF(1) ' Loop until end of file.
Line Input #1, TextLine ' Read line into variable.
If Mid(TextLine, 5, 6) < "Page :" Then
Print #2, TextLine
End If
Loop
Close #1, #2

The resultant file should import with no difficulty.

If the structure is complex it may be worth parsing each line for data
and placing it directly into the spreadsheet. without seeing your text
file it is difficult to say what is best.

Good luck


--
tony h
------------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
View this thread: http://www.excelforum.com/showthread...hreadid=505339

If you record a macro when you open your .txt file, you can step through the
wizard. But it sounds like you could use | as the delimiter--since you're going
to be tossing the records that begin with +.

I'd bring my data in the first field (column A) as text so that the the records
that start with + will be treated like text--not formulas.

Then after I recorded my import the data macro, I'd add a little bit of code
that cleans up the headers.



Option Explicit
Sub CleanPlusses()
Dim FoundCell As Range
Dim wks As Worksheet

Set wks = ActiveSheet
With wks
With .Range("a:a")
Do
Set FoundCell = .Cells.Find(what:="+*", _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
If FoundCell Is Nothing Then
Exit Do
Else
FoundCell.EntireRow.Delete
End If
Loop
End With

'add your own headers--I used 6 columns.
.Rows(1).Insert
With .Range("a1").Resize(1, 6)
.Value _
= Array("header1", "header" & "2", "header" & vbLf & "3", _
"h4", "h5", "H6")
.WrapText = True
End With
End With

End Sub

Mariano wrote:

Thank you for the suggestions...
I am still learning visual basic, I can follow code, but not neccessarily
write it. I basically learn as I go.
I am going to play with all these suggestions and hopefully drag a buddy of
mine into this to help me with VB.

I kept playing with the text file, and I was incorrect about the delimiter.
The "+" delimiter is actually only included in the column header. The body
of the report uses a "|" as a delimiter. I would need to do something that
would find all the lines that begin with a "+" and only use the data in
between that and the next line that begins with a "+", but also delimit all
the rows at the same place the "+" is in the column header. I hope that makes
some sense.
Tony, you were correct the reports does have page breaks with the headers
and footers, which i would also need to get rid of.

man, i got to learn VB asap!

"tony h" wrote:


Just summarising the issue : you get a text version of a report which is
a good old fashined report.
The report has page breaks and page headers and footers and section
headers in it.

In this case I usually find it easiest to parse the file before
importing it. I do this using the "Open file for input" and the input
line statement. A few well planned tests (looking for blank line, the
text of the headers etc) can soon determine whether you want to keep
the line or not.

Dim TextLine As String
Open "inFILE.TXT" For Input As #1 ' Open file.
Open "outFILE.TXT" For Output As #2
Do While Not EOF(1) ' Loop until end of file.
Line Input #1, TextLine ' Read line into variable.
If Mid(TextLine, 5, 6) < "Page :" Then
Print #2, TextLine
End If
Loop
Close #1, #2

The resultant file should import with no difficulty.

If the structure is complex it may be worth parsing each line for data
and placing it directly into the spreadsheet. without seeing your text
file it is difficult to say what is best.

Good luck


--
tony h
------------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
View this thread: http://www.excelforum.com/showthread...hreadid=505339



--

Dave Peterson