Solved

Using Open...As # to Create Text File

Posted on 1998-09-22
8
194 Views
Last Modified: 2010-04-30
I have a text file that I have to create on the PC using Excel v8. This file is then transported to a Unix machine where it is analysed by a current programming system.

The text file consists of 3 different types of data: a header, data records, and, finally, a footer. Each of these different data types has a different total character length. I.e. the text file should have the following structure:

Header
Data Record#1
Data Record#2
….
Data Record#n
Footer

Rather than have the text file consist of one long string, each of these different data types should be delimited by a Carriage Return. However, there is to be no delimiting within each of the different data types.

The problem that I have is that, although I can create the text file OK on the PC, when it is viewed on Unix, the Carriage Return appears as ^M. E.g. in Unix, it appears as:

Header^M
Data Record#1^M
Data Record#2^M

Data Record#n^M
Footer^M

How do I get rid of this? Or can I? Personally I believe that it would not be possible to get a carriage return between the different types without something having to appear in Unix; after all, you can't get something for nothing.

I have tried all different configurations of using the Open method for creating these files but the same thing (or worse in the second case below) happens in either case.



Below is a dummy sample indicating the type of coding that I have used to generate this text file:

Private Type HeaderData
    sH1 As String * 10
    sH2 …
    sCR as String * 1 'Carriage Return
End Type

Private Type RecordData
    sD1 As String * 3
    sD2 …
    sCR as String * 1 'Carriage Return
End Type

Private Type FooterData
    sF1 As String * 5
    sF2 …
    sCR as String * 1 'Carriage Return
End Type


Public Sub WriteFile()
    Dim Header As HeaderData '40 characters long
    Dim Data As RecordData '315 characters long
    Dim Footer As FooterData '55 characters long
    Dim File As String 'The full pathname of the .TXT file to be created
   
    'Populating the .sCR for each data type with Chr(13)
    'This worked out the worst as there were also (315-40) ^@ characters at the end of the Header record before commencing the Data record
    Open File For Random As #1 Len = Len(Data)
    Put #1, 1, Header
    Put #1, 2, Data
    Put #1, 3, Footer
    Close #1
   
    'Not including the .sCR data type elements in the Print # statements
    Open File For Output As #1
    With Header
        Print #1, .sH1, .sH2,....
    End With
    With Data
        Print #1, .sD1, .sD2,...
    End With
    With Footer
        Print #1, .sF1, .sF2,...
    Close #1
End Sub
0
Comment
Question by:Noggy
  • 4
  • 3
8 Comments
 
LVL 12

Expert Comment

by:mark2150
ID: 1436080
<Ctrl M> is a <LF>. Your Unix system only needs the <CR> instead of the <CR>/<LF> pair that PRINT # supplies by default. You can inhibit the automatic <CR>/<LF> by ending your PRINT # statement with a semicolon (;). This will block *both*. What you need is to explicity place a <CR> into each line and *then* block the automatic <CR>/<LF>.

Try:

PRINT #1, .sH1, .sH2, ..., vbCR ;

This will place an explicit <CR> on the line and block the auto <CR>/<LF>.

The file will look a little weird if you do a DOS TYPE command - all of the text will rapidly scroll on one line. This is actually correct as there are no <LF>'s in the data stream.

M

0
 
LVL 4

Author Comment

by:Noggy
ID: 1436081
I tried this out but it actually came out worse: at the end of every entry, there were spurious quantities of spaces before actually putting in the carriage return. In addition, the carraige return also still appeared as ^M when viewed in Unix.

I have since found that, in Unix, there is a Shell command called DOS2UNIX where such anomalous characters are converted into the ISO Unix character set.

However, this can only be done on the Unix station. I would still like to know if there is any way of achieving the correct result on a normal PC.
0
 
LVL 2

Expert Comment

by:AllenC_Jr
ID: 1436082
Put #1, ,Header
For i = 1 to RecordCount
Put #1, ,DataRecord(i)
Next
Put #1, ,Footer

0
 
LVL 4

Author Comment

by:Noggy
ID: 1436083
Sorry, but that's the same as the first method that I tried; the only difference being that you have not stipulated the record numbers that the records are Put at. But, believe me, I've tried this also.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 12

Expert Comment

by:mark2150
ID: 1436084
The spurious spaces have to be comming from the vars you're saving. The PRINT # statement doesn't invent chars. Try using just the LF instead of the CR and see if that helps.

M
0
 
LVL 4

Author Comment

by:Noggy
ID: 1436085
I'm sorry, Mark, but the spurious spaces weren't from the vars that I was using. I discovered this by just remming out the ",vbCR;" and, lo and behold, the spurious spaces had gone.

As to just using ",vbLF;" instead, I will have to give it a go later. However, I will not be able to try this out myself as I do not have access to a Unix logon to view the results. At the moment, my users are using the Unix DOS2UNIX shell command instead. Do you know if there's a PC equivalent of this shell command? Not that I would expect it to come from Microsoft even if one should exist! :-)
0
 
LVL 12

Accepted Solution

by:
mark2150 earned 50 total points
ID: 1436086
Your're using a *COMMA* between the last var and the vbCr! Use a ";" and the spaces will vanish!

0
 
LVL 4

Author Comment

by:Noggy
ID: 1436087
Aha. But I do have to say it: You didn't have "; vbCR" in your first proposed answer, you had ", vbCR;" instead.

I shall accept your answer even though I haven't been able to test it yet. I know the sort of ball park area that needs to be looked at. Thanks.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

920 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now