I have a text file created on linux, if I open it in Word pad the file appears normally. However when I open it in notepad, and when I try to load it into excel using the code below it appears as a single line.
我有一个在linux上创建的文本文件,如果我在Word pad中打开它,文件就会正常显示。但是,当我在记事本中打开它时,当我尝试使用下面的代码将它加载到excel中时,它会以一行的形式出现。
' Open the file
Open Filename For Input As #1
' Look for the Table Title
Do While Not (EOF(1) Or InStr(TextLine, TableTitle) > 0)
Line Input #1, TextLine
Loop
How can I split it into the original lines? Is there an end of line seperator, that vba can use?
我怎样才能把它分成原来的几行呢?是否有一个线分隔符的末端,vba可以使用?
3 个解决方案
#1
11
Linux uses a line-feed (\n
) to denote a new line rather than the carriage return+line-feed (\r\n
) as used by Windows so you can't use Line input
, instead:
Linux使用线提要(\n)来表示新的行,而不是Windows所使用的带回车+线提要(\r\n),因此您不能使用线输入,而是:
Open Filename For Input As #1
'//load all
buff = Input$(LOF(1), #1)
Close #1
'//*either* replace all lf -> crlf
buff = replace$(buff, vbLf, vbCrLf)
msgbox buff
'//*or* line by line
dim lines() As String: lines = split(buff, vbLf)
for i = 0 To UBound(lines)
msgbox lines(i)
next
#2
3
The Function
这个函数
Public Function GetLines(fpath$) As Variant
'REFERENCES:
'Microsoft Scripting Runtime // Scripting.FileSystemObject
'Microsoft VBScript Regular Expressions 5.5 // VBScript_RegExp_55.RegExp
Dim fso As New Scripting.FileSystemObject, RE As New VBScript_RegExp_55.RegExp
If fso.FileExists(fpath) = True Then
Dim mts As MatchCollection, mt As Match
Dim lines() As String
Dim content$: content = fso.OpenTextFile(fpath).ReadAll()
With RE
.Global = True
.Pattern = "[^\r\n]+" 'catch all characters except NewLines/Carraige Returns
If .test(content) = True Then
Set mts = .Execute(content)
ReDim lines(mts.Count - 1)
Dim pos&
For Each mt In mts
lines(pos) = mt.Value
pos = pos + 1
Next mt
Else
MsgBox "'" & Dir(fpath) & "' contains zero bytes!", vbExclamation
End If
End With
GetLines = lines
Else
MsgBox "File not found at:" & vbCrLf & Dir(fpath), vbCritical
End If
End Function
and could be invoked by (from immediate window
)
可以通过(从即时窗口)调用
?GetLines("C:\BOOT.INI")(2)
and the output
和输出
default=multi(0)disk(0)rdisk(0)partition(1)\WINDOWS
The above example could be used to get all lines from any text file originated from any OS.
Hope this helps.
希望这个有帮助。
#3
1
Open the linux text file using Windows "Word Pad". Save the file. Word Pad will convert the linux line-feed (\n) to carriage return+line-feed (\r\n) as it saves the file. No coding is necessary.
使用Windows“Word Pad”打开linux文本文件。保存文件。当它保存文件时,Word Pad将把linux行提要(\n)转换为车厢返回+行提要(\r\n)。不需要编码。
#1
11
Linux uses a line-feed (\n
) to denote a new line rather than the carriage return+line-feed (\r\n
) as used by Windows so you can't use Line input
, instead:
Linux使用线提要(\n)来表示新的行,而不是Windows所使用的带回车+线提要(\r\n),因此您不能使用线输入,而是:
Open Filename For Input As #1
'//load all
buff = Input$(LOF(1), #1)
Close #1
'//*either* replace all lf -> crlf
buff = replace$(buff, vbLf, vbCrLf)
msgbox buff
'//*or* line by line
dim lines() As String: lines = split(buff, vbLf)
for i = 0 To UBound(lines)
msgbox lines(i)
next
#2
3
The Function
这个函数
Public Function GetLines(fpath$) As Variant
'REFERENCES:
'Microsoft Scripting Runtime // Scripting.FileSystemObject
'Microsoft VBScript Regular Expressions 5.5 // VBScript_RegExp_55.RegExp
Dim fso As New Scripting.FileSystemObject, RE As New VBScript_RegExp_55.RegExp
If fso.FileExists(fpath) = True Then
Dim mts As MatchCollection, mt As Match
Dim lines() As String
Dim content$: content = fso.OpenTextFile(fpath).ReadAll()
With RE
.Global = True
.Pattern = "[^\r\n]+" 'catch all characters except NewLines/Carraige Returns
If .test(content) = True Then
Set mts = .Execute(content)
ReDim lines(mts.Count - 1)
Dim pos&
For Each mt In mts
lines(pos) = mt.Value
pos = pos + 1
Next mt
Else
MsgBox "'" & Dir(fpath) & "' contains zero bytes!", vbExclamation
End If
End With
GetLines = lines
Else
MsgBox "File not found at:" & vbCrLf & Dir(fpath), vbCritical
End If
End Function
and could be invoked by (from immediate window
)
可以通过(从即时窗口)调用
?GetLines("C:\BOOT.INI")(2)
and the output
和输出
default=multi(0)disk(0)rdisk(0)partition(1)\WINDOWS
The above example could be used to get all lines from any text file originated from any OS.
Hope this helps.
希望这个有帮助。
#3
1
Open the linux text file using Windows "Word Pad". Save the file. Word Pad will convert the linux line-feed (\n) to carriage return+line-feed (\r\n) as it saves the file. No coding is necessary.
使用Windows“Word Pad”打开linux文本文件。保存文件。当它保存文件时,Word Pad将把linux行提要(\n)转换为车厢返回+行提要(\r\n)。不需要编码。