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?
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. 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
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. 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
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. 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
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. 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
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. 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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,522
Messages
5,837,836
Members
430,517
Latest member
chessypack86

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top