Solved

Using Open...As # to Create Text File

Posted on 1998-09-22
8
190 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

746 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

16 Experts available now in Live!

Get 1:1 Help Now