Solved

PARSE

Posted on 2002-05-21
13
155 Views
Last Modified: 2010-05-02
Hello:

The code below parses the first row in the text file and then stops. Please help.
Thanks.

===================================
Private Sub Command1_Click()
Dim File1 As Integer, File2 As Integer
Dim InputFile As String, OutputFile As String
Dim strLine As String
InputFile = "C:\Copy1.txt" 'Change to your desired name
OutputFile = "C:\Copy2.txt" 'Change to your desired name
File1 = FreeFile
Open InputFile For Input As #File1
File2 = FreeFile
Open OutputFile For Output As #File2
Do Until EOF(File1)
   Line Input #File1, strLine 'Read Line by Line
   If Len(Trim(strLine)) > 87 Then
       Print #File2, Left(strLine, 87)
   Else
       Print #File2, strLine
   End If
Loop
Close #File1 'Close both files
Close #File2
End Sub
0
Comment
Question by:muhammasif
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
  • +2
13 Comments
 
LVL 3

Expert Comment

by:Elmo_
ID: 7024231
muhammasif,

I had a look at your code and it seems fine.  I have tested it as well and it basically made a copy of the first file.

The only thing that it does do is - If the Line is greater than 87 characters it only outputs the first 87 to the new file.

Are you looking for something else here or are you having a different problem?

Cheers,

Ed.
0
 

Author Comment

by:muhammasif
ID: 7024266
When I manually open the first file in Notepad, it shows me a box shaped character at the end of each record. It seems like this character is the culprit.

Although the code looks OK but it outputs only the first record and then stops.

Thanks.
0
 
LVL 4

Expert Comment

by:trkcorp
ID: 7024285
I too ran your code over a "standard" text file (with CR/LF) as record delimiter and it worked perfectly.  I think your record delimiter ("box shaped character") is the problem.  I suspect also that since it is NOT a valid delimiter the entire file is read into strline on the first read...
Find out what the hex value of that character is and then do a replace to substitute a CR or LF for it and I suspect your problem will disappear...  If this file is coming from a third party, ask them about changing this for you.
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 3

Expert Comment

by:Elmo_
ID: 7024375
muhammasif,

I agree with yourself and trkcorp.  This character is the problem and as trkcorp said "since it is NOT a valid delimiter the entire file is read into strline on the first read..."

I have found a way around it that may help, but if this is a third party file, try get them to fix it.

What you can do is open the file As Binary Access.  It should allow you to read past this character.

File1 = FreeFile
Open InputFile For Binary Access Read Lock Read As #File1
File2 = FreeFile
Open OutputFile For Output As #File2
0
 

Author Comment

by:muhammasif
ID: 7024477
Elmo, tried your code. No change. Still reporting first record only.
0
 
LVL 22

Accepted Solution

by:
rspahitz earned 50 total points
ID: 7024488
Did this file come from a Unix system?  They typically put only linefeed characters in rather than CR/LF combos.

One solution is this:

File1 = FreeFile
Open InputFile For Input As #File1
strLine = input$(lof(File1),#File1)
close #File1

dim strFileArray() as string
strFileArray = split(strLine, vbLF)

File2 = FreeFile
Open OutputFile For Input As #File2

for iLine = 0 to ubound(strFileArray)
  If Len(Trim(strLine)) > 87 Then
      Print #File2, Left(strFileArray(iLine), 87)
  Else
      Print #File2, strFileArray(iLine)
  End If
next iLine
close #File2
0
 
LVL 5

Expert Comment

by:KMAN
ID: 7024641
If it is a UNIX file, then an FTP of the file, rather than Samba or NFS file copy, to the windows file system will "fix" the file; that is make the LF into CR/LF.

K
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 7024659
Also, if this is a one-time thing, you can drop the file into Wordpad, save it (answer Yes...lose formatting) and the file will be Windows-ready.
0
 

Author Comment

by:muhammasif
ID: 7025512
The file comes from Mainframe (MVS/TSO) to a Linux system on a daily basis.
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 7025578
Then you should either export it from MVS with CRLF line breaks, or try the code I offered.  Note that some O/Ss use LF as line breaks, and others use CR, so the "split" line in my code should reflect the proper code:

' For Unix:
...
strFileArray = split(strLine, vbLF)
...

' For some other O/Ss:
...
strFileArray = split(strLine, vbCR)
...
0
 
LVL 4

Expert Comment

by:trkcorp
ID: 7025579
Have you been able to determine the value of the delimiter? <<("box shaped character")>>
0
 

Author Comment

by:muhammasif
ID: 7030534
Excellent.

The character was a Line Feed - 0A.
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 7030709
Ah, yes, Line Feed: the dreaded mainframe line delimiter!

Glad you got it working.
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses
Course of the Month5 days, 22 hours left to enroll

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question