ReadLine command for UNIX txt file

[marissaliana]

Hello,

I have found this (https://stackoverflow.com/questions/20301663/importing-separate-rows-and-columns-from-a-txt-file-into-an-excel-worksheetvba) code about reading a two columns text file. We read the file with the ReadLine command and the split it. My problem is that the text file I need to read is in UNIX. Is there anyway to adapt my code, so that I can read the UNIX file? As I have searched, I couldn't find any solution to this.


Sub ImportTextFilePass()
Const ForReading = 1, ForWriting = 2, ForAppending = 3


Dim i, iup, varArray
Dim fso As Object, tso As Object
Dim strFilePath, strLine
Dim ws As Worksheet, ws1

Set ws = ThisWorkbook.Sheets.Add(After:= _
ThisWorkbook.Sheets("Sheet1"))
Set ws1 = ThisWorkbook.Sheets.Add(After:= _
ThisWorkbook.Sheets("Sheet2"))
ws.Name = "Test"
ws1.Name = "Output"

strFilePath = "C:\sample.txt" ' adapt here your text file name:




Set fso = CreateObject("Scripting.FileSystemObject") ' get TextStream:
Set tso = fso.OpenTextFile(strFilePath, ForReading)


i = 2
Do While Not tso.atendofstream '' read a line from the file:
strLine = tso.ReadLine ' split with separator tab:

varArray = Split(strLine, vbTab)
iup = UBound(varArray) ' split with separator space:



If (iup <= 0) Then

varArray = Split(strLine, " ")
iup = UBound(varArray)

End If

On Error Resume Next

If Not IsEmpty(varArray(0)) Then
If Not Err = 9 Then 'Blank line error


strLine = "A" & i 'fill a cell: using strLine as range address:
Sheets("Test").Range(strLine).Value = varArray(0)
Sheets("Test").Range(strLine).NumberFormat = "General"

End If
End If

On Error GoTo 0



If (iup > 0) Then ' if there is more then two words, fill cell 2:


strLine = "B" & i
Sheets("Test").Range(strLine).Value = varArray(UBound(varArray))
Sheets("Test").Range(strLine).NumberFormat = "General"


End If


i = i + 1

Loop




' clean objects:


tso.Close
Set tso = Nothing


Set fso = Nothing

I have also asked here (https://stackoverflow.com/questions/43919822/readline-command-in-unix-file-vba) but I had no luck.

Thank you

[mdmackillop]

Can you post a sample UNIX txt file?

BTW, You should show a link to here in your other posting.

[marissaliana]

I attach one text file in UNIX and the same one in DOS edition.

I also edit my other post with a new link.

[mdmackillop]

This should convert Unix to Dos

Sub test()
ConvertUnix "C:\VBAX\New1.txt", "C:\VBAX\New2.txt"
End Sub


Sub ConvertUnix(fOld, fNew)

'https://www.mrexcel.com/forum/excel-questions/265695-converting-file-sequential-file-visual-basic-applications-excel.html#post1306321
' this file contains what appears to be a unix file
Open fOld For Input As #1
'write out to DOS file with carriage return
Open fNew For Output As #2
Do While Not EOF(1)
Line Input #1, string_all
'header is 440bytes
header_length = 440
Print #2, Mid(string_all, 1, header_length - 1)
'data length is 711bytes
d_length = 711
For i = d_length + 2 To Len(string_all) - d_length Step d_length
Print #2, Mid(string_all, i, d_length - 1)
Next
Loop
Close #2
Close #1
End Sub

[marissaliana]

This should convert Unix to Dos


Great!!!! Thank you!!!!!!!