使用VBA将linux文本文件加载到excel中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10945779/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Loading linux text file into excel using VBA
提问by Chris
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 可以使用？
回答by Alex K.
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)，因此您不能使用Line input，而是：
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
回答by Cylian
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)
并且可以被 (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.
希望这可以帮助。
回答by jlnerd
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)。无需编码。
