Using Open...As # to Create Text File

Posted on 1998-09-22
Medium Priority
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:

Data Record#1
Data Record#2
Data Record#n

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:

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

Data Record#n^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
Question by:Noggy
  • 4
  • 3
LVL 12

Expert Comment

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>.


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.



Author Comment

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.

Expert Comment

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Author Comment

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.
LVL 12

Expert Comment

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.


Author Comment

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! :-)
LVL 12

Accepted Solution

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


Author Comment

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.

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…
Suggested Courses
Course of the Month9 days, 19 hours left to enroll

569 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