Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


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

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.

Question has a verified solution.

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

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

722 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