How to convert Unix Text File to PC Text File

ilak1008

New Member
Joined
Aug 2, 2010
Messages
38
Any one who knows how to convert a Unix Text file to PC Text File using VBA in Excel?
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,687
Office Version
2013
Platform
Windows
Most PC's can probably handle Unix text files. What's going on in your case? The main difference is: Unix uses Carriage Return characters (Ascii 13) as end of line markers, and Windows PC's frequently use Carriage Return Line Feeds (Ascii 13 + Ascii 10) as end of line markers - but many programs read both equally well without problems.
 
Last edited:

ilak1008

New Member
Joined
Aug 2, 2010
Messages
38
I am trying to make a macro on how to convert unix text to pc text file.
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,687
Office Version
2013
Platform
Windows
Okay. I'd try it the way it is first. Should work - then worry. :)
 

ilak1008

New Member
Joined
Aug 2, 2010
Messages
38

ADVERTISEMENT

I tried to open the unix file and saved it in PC text format but it resulted to having rubbish characters. This is not what I wanted. I want my macro to do is to convert the unix file to pc readable file first before doing string manipulation, etc. I hope you could help me. Thanks in advance.
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,687
Office Version
2013
Platform
Windows
How are you opening and reading the file?
Chances are this is a unicode conversion issue rather than a pc/unix issue. Or you just have a binary file, not a text file.
 

ilak1008

New Member
Joined
Aug 2, 2010
Messages
38

ADVERTISEMENT

My code below does not convert unix text file to pc text file. I want to insert a script to do the conversion.

Sub Sensors()

Application.ScreenUpdating = False

Dim myline As String
Dim myrange As Range

Sheets("Sensors").Select
Range("A6:I6").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("A6").Select


myfile = Application.GetOpenFilename("All Files (*.*),*.*")
If myfile = False Then End

Set myrange = Range("A6")

Open myfile For Input As #2

While Not EOF(2)

Line Input #2, myline
If Left(myline, 3) = "FDU" Then
myrange = Mid(myline, 7, 9)
myrange.Offset(0, 1) = Mid(myline, 23, 6)
myrange.Offset(0, 2) = Mid(myline, 31, 7)
myrange.Offset(0, 3) = Mid(myline, 47, 3)
myrange.Offset(0, 4) = Mid(myline, 95, 9)
myrange.Offset(0, 5) = Mid(myline, 103, 7)
myrange.Offset(0, 6) = Mid(myline, 110, 7)
myrange.Offset(0, 7) = Mid(myline, 117, 7)
myrange.Offset(0, 8) = Mid(myline, 128, 19)
Set myrange = myrange.Offset(1, 0)
End If

Wend
Close #2
Range("A5").Select
Application.ScreenUpdating = True
SendKeys "^{HOME}", True
End Sub
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,687
Office Version
2013
Platform
Windows
I'm off for a little while.
You may find it useful to download Notepad++ (freeware). It's a very good cross-platform editor, and, if you can read your file at all, you can read it there (as well as perform conversions). It may help you to understand what you are dealing with better.

As I've said, as far as I know Unix text files are normally not a problem for Excel/Windows - where is this file coming from and what kind of data is in it?
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,687
Office Version
2013
Platform
Windows
Sorry, didn't see your last post. Well, you're reading the text file and writing it to Excel so I would assume it is in fact neither a unix text file or a pc text file, but rather an Excel file. :)
 

ilak1008

New Member
Joined
Aug 2, 2010
Messages
38
The file is coming from a Linux based operating system. I can read the file in textpad but I don't want to do that. What I want to do is insert the conversion script in my code so I don't have to do it manually.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,907
Messages
5,514,080
Members
408,983
Latest member
mlee13

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top