How to convert Unix Text File to PC Text File

[ilak1008]

Any one who knows how to convert a Unix Text file to PC Text File using VBA in Excel?

 

[ilak1008]

Anyone else has an idea?

 

[xenou]

[Edit]: Okay - I think if I understand you aren't actually trying to convert a text file from unix to windows but rather just read a text file into Excel ... right?

Chances are this is a unicode problem, but its hard to be sure. If notepad can read it okay Excel should too. It's hard to give advice without knowing more though. If you want to PM me your address I can look at the text file more closely. You might try recording a macro while you open the text file with the text import wizard.

Note: Can you say more about what this file is? Is it a log file? OOorg file? Shell script? You might be able to change the file save format on the Linux side - specify the use of Ascii, UTF-8, or UTF-16 encoding.

 

[Norie]

Can somebody explain what a UNIX text file is?

Surely a text file is, well, a text file.

The data in it might be organised/delimited in a particular way depending on how it was generated but you should still be able to treat it as a text file.

In Excel VBA you can read any file character by character, or line by line etc for that matter, using the standard file I/O functions/methods.

You might need further code to interpret/extract/parse the file to get the actual data.

A quick search finds this, which appears to be the main difference between the format of Unix generated and Windows generated text files.

The format of Windows and Unix text files differs slightly. In Windows, lines end with both the line feed and carriage return ASCII characters, but Unix uses only a line feed. As a consequence, some Windows applications will not show the line breaks in Unix-format files.

That doesn't seem like much of a radical difference to me.

 

[Easy-XL Support]

Line Input is expecting to find a carriage return for the end of line marker. Since it doesn't find one it ends up reading in the entire file.

One way to deal with this situation is to replace the line feed characters with carriage returns before processing the file.

The Sub below will read a text file delimited with line feeds, change them all to carriage returns and then write the file back to disk.

You call it as follows:

ConvertLFtoCR "C:\Temp\Test.txt"

' convert the line feeds (Chr(10) - vbLf) in a text file to carriage returns (Chr(13) - vbCr)
 
Sub ConvertLFtoCR(fileToConvert As String)
 
On Error GoTo ConvertFileLFtoCR_Error
 
' get next available VB file handle
Dim fileNum As Integer
fileNum = FreeFile
 
' open the file (read/write access required)
Open fileToConvert For Binary As #fileNum
 
' read the file into a string variable
Dim fileBuffer As String
fileBuffer = String(LOF(fileNum), 0)
Get #fileNum, , fileBuffer
 
' change all line feeds to carriage returns
fileBuffer = Replace(fileBuffer, vbLf, vbCr)
 
' write the file back to disk
Put #fileNum, 1, fileBuffer
 
' close the file
Close #fileNum
 
' Thank you. Please come again.
Exit Sub
 
ConvertFileLFtoCR_Error:
 
    MsgBox Err.Description
 
End Sub

 

[xenou]

For what its worth, I created a text file on Linux -- using Gedit -- just now, and Excel opened it without any problems. Actually, I created three different text files (one with LF, the other with CR, and the last with CRLF). No problems with any of these. As you can see, Linux is also generally very generous about letting you save a file however you'd like - so it may be worth checking those save options on the *nix end.

ξ

 

[xenou]

I've looked at the text file briefly and it does work flawlessly on my pc. I'm still puzzled but suspecting a problem with encoding (end of line markers might give you one long string of data but not garbage).

Does anyone have any other ideas?

Another shot at this:
Open the text file with the text import wizard (this should kick in if you just open the text file normally with Excel). Check the first step of the text import wizard to see what the default encoding is. Mine is 65001: Unicode UTF-8 Maybe try some others that make sense? Although as I understand it, Unicode and Ascii should be compatible for the first 255 characters - so this may all be irrelevant. If in your code you don't set this with the OpenText method arguments, you'll get whatever is there by default.

And, as a variation on a theme, here's another program for reading the file (looks like we're collecting these) - this time with the FileSystemObject. To test this, open a workbook called Book1.xls and save it. Then run the code, having changed the file location in the code first.

[COLOR="Navy"]Sub[/COLOR] Foo()
[COLOR="Navy"]Dim[/COLOR] FSO [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Object[/COLOR] [COLOR="SeaGreen"]'//FileSystemObject[/COLOR]
[COLOR="Navy"]Dim[/COLOR] ws [COLOR="Navy"]As[/COLOR] Worksheet
[COLOR="Navy"]Dim[/COLOR] ts [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Object[/COLOR] [COLOR="SeaGreen"]'//TextStream[/COLOR]
[COLOR="Navy"]Dim[/COLOR] lngCount [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] sCellToStartAt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] s [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] a [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Variant[/COLOR]
[COLOR="Navy"]Dim[/COLOR] sFilePath [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]


    [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]GoTo[/COLOR] Handler:
    
    [COLOR="SeaGreen"]'------------------------------------------------------[/COLOR]
    [COLOR="SeaGreen"]'//Source text file[/COLOR]
    sFilePath = "C:\myTemp\100731_SENSOR_Test_PM.txt"
    [COLOR="SeaGreen"]'//Worksheet to write to[/COLOR]
    [COLOR="Navy"]Set[/COLOR] ws = Workbooks("Book2.xls").Worksheets(1)
    [COLOR="SeaGreen"]'//Range to start at[/COLOR]
    sCellToStartAt = "$A$1"
    [COLOR="SeaGreen"]'------------------------------------------------------[/COLOR]
        
    [COLOR="SeaGreen"]'//Read file[/COLOR]
    [COLOR="Navy"]Set[/COLOR] FSO = CreateObject("Scripting.FileSystemObject")
    [COLOR="Navy"]Set[/COLOR] ts = FSO.OpenTextFile(sFilePath, 1, False, -2) [COLOR="SeaGreen"]'//ForReading, Create=False, TristateUseDefault[/COLOR]

    [COLOR="SeaGreen"]'//Write rows[/COLOR]
    lngCount = 0
    [COLOR="Navy"]Do[/COLOR] [COLOR="Navy"]While[/COLOR] [COLOR="Navy"]Not[/COLOR] ts.AtEndOfStream
        s = ts.ReadLine
        [COLOR="SeaGreen"]'//Only read lines on or after column headers[/COLOR]
        [COLOR="Navy"]If[/COLOR] Left(s, 3) = "Box" [COLOR="Navy"]Or[/COLOR] Left(s, 3) = "FDU" [COLOR="Navy"]Then[/COLOR]
            a = Split(s, Chr(9)) [COLOR="SeaGreen"]'//break into "cells" on tab delimiters[/COLOR]
            ws.Range(sCellToStartAt).Offset(lngCount, 0).Resize(1, UBound(a) + 1) = a
            lngCount = lngCount + 1
        [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
    [COLOR="Navy"]Loop[/COLOR]
            
My_Exit:
[COLOR="Navy"]If[/COLOR] [COLOR="Navy"]Not[/COLOR] ts [COLOR="Navy"]Is[/COLOR] [COLOR="Navy"]Nothing[/COLOR] [COLOR="Navy"]Then[/COLOR]
    ts.Close
    [COLOR="Navy"]Set[/COLOR] ts = [COLOR="Navy"]Nothing[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]If[/COLOR] [COLOR="Navy"]Not[/COLOR] FSO [COLOR="Navy"]Is[/COLOR] [COLOR="Navy"]Nothing[/COLOR] [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]Set[/COLOR] FSO = [COLOR="Navy"]Nothing[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]

[COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Handler:
MsgBox "Error: " & Err.Description
[COLOR="Navy"]Resume[/COLOR] My_Exit
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]