VBA 将带分隔符的文本文件导入 Excel
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42805779/
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
VBA import delimited text file to Excel
提问by rei123
I'm trying to import text file to excel using vba. The text file I have has all the data in one line and contains two delimiters "|" and ",". Where, "," will separate the data into columns and "|" separate the data to rows.
我正在尝试使用 vba 将文本文件导入到 excel。我拥有的文本文件在一行中包含所有数据，并包含两个分隔符“|” 和 ”，”。其中，“,”将数据分成列和“|” 将数据分隔为行。
I have got a code, but it seems like doing the opposite and because I'm very new to vba, I couldn't really figure out where it goes wrong.
我有一个代码，但似乎正好相反，因为我对 vba 很陌生，我无法真正弄清楚哪里出了问题。
I was thinking if there is other ways of doing to vba, something will performs like, if it recognize the delimiter it will move the data to specified cell?
我在想是否还有其他方法可以对 vba 做一些事情，如果它识别出分隔符，它会将数据移动到指定的单元格？
Here is what my text file looks like.
这是我的文本文件的样子。
27/2/2017 17:14:32 | 54,11,6,32,58,83,0 | 0,0,0,0,0,0,0 | 0,0,0,0,0,0,0 | 0,69,8,86,0,241,255 | 0,71,69,404,0,553,0 | 15,0,0,0,53,0,0 | 0,0,0,0,0,0,0 | 0,867,2,18,0,939,0 | 0,0,0,0,0,0,0 | 0,0,0,0,0,0,0 | 0,0,0,0,0,0,0 | 0,0,0,0,0,0,0 | 0,0,0,0,0,0,0 | 16,0,0,0,0,0,85 | 647,509,18,82,18,670,85 | 1433,0,0,0,0,0,0 | 1432,882,0,0,0,939,0 | 32,861,1,20,0,938,0 | 0,887,0,0,0,939,0 | 0,886,0,0,0,939,0 | 12,801,4,42,0,912,0 | 0,867,0,0,0,939,0 | 0,0,0,0,0,0,0 | 0,890,0,0,0,939,0 | 0,871,0,0,0,930,85 | 0,891,0,0,0,939,0 | 0,892,0,0,0,939,0 | 0,894,0,0,0,939,0 | 0,895,0,0,0,954,0 | 0,0,0,0,0,0,0 | 0,905,0,0,0,954,0 | 0,792,6,35,0,897,85 | 4,697,40,202,0,952,0 | 0,640,13,108,0,807,0 | 0,0,0,0,507,0,0 | 60,24,23,211,1128,296,0 | 4,81,16,148,569,348,0 | 0,0,0,0,0,0,0 | 0,0,0,0,0,0,0 | 77,224,22,367,159,791,0 | 118,42,1,7,1051,104,0 | 58,0,0,0,654,0,0 | 260,0,0,0,642,0,0 | 172,0,0,0,1241,0,0 | 0,0,0,0,1433,0,0 | 0,0,0,0,1433,0,0 | 0,0,0,0,1433,0,0 | 0,0,0,0,1433,0,0 | 0,0,0,0,1434,0,0 | 0,0,0,0,1433,0,0 | 0,0,0,0,1433,0,0 | 0,0,0,0,1433,0,0 | 0,0,0,0,1433,0,0 | 0,0,0,0,1434,0,0 | 0,0,0,0,1433,0,0 | 114,0,0,0,1284,0,0 | 0,0,0,0,1429,0,0 | 0,0,0,0,1353,0,0 | 0,0,0,0,1433,0,0 | 0,0,0,0,1434,0,0 | 0,0,0,0,1433,0,0 | 0,0,0,0,1433,0,0 | 0,0,0,0,1432,0,0 | 0,0,0,0,1434,0,0 | 0,0,0,0,1433,0,0 | 0,0,0,0,1433,0,0 | 86,89,1,51,1279,141,0 | 0,0,0,0,1433,0,0 | 0,0,0,0,1433,0,0 | 0,0,0,0,1433,0,0 | 0,0,0,0,1433,0,0 | 0,0,0,0,1434,0,0 | 0,0,0,0,1433,0,0 | 0,0,0,0,1433,0,0 | 0,0,0,0,1433,0,0 | 0,0,0,0,1433,0,0 | 0,0,0,0,1434,0,0 | 0,0,0,612,751,613,0 | 0,0,2,662,0,710,0 | 0,0,0,0,0,0,0 | 0,0,0,0,0,0,0 | 0,0,0,0,0,0,0 | 0,0,0,0,0,0,0 | 0,0,0,0,0,0,0 | 0,0,0,0,0,0,0 | 0,0,0,0,0,0,0 | 0,0,0,0,0,0,0 | 0,0,0,0,0,0,0 | 0,0,0,0,0,0,0 | 0,0,0,0,0,0,0 | 0,0,0,0,0,3,0 |
Here is my code
这是我的代码
Private Sub CommandButton1_Click()
Dim sPath As String, sLine As String
Dim oFile As String
Dim i As Long
Dim workRange As Range
Dim destCell As Range
Set destCell = Range("A1")
Set workRange = Range("A1" & ":" & Range("A1").End(xlDown).Address)
Unload Me
oFile = Application.GetOpenFilename()
i = 1
Open oFile For Input As #1 ' Open file for input.
Do While Not EOF(1) ' Loop until end of file.
Input #1, sLine ' Read data
i = i + 1
Range("A" & i).Formula = sLine ' Write data line
Loop
Close #1 ' Close file.
'Text to Columns
With workRange
.TextToColumns Destination:=destCell, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="|", _
FieldInfo:=Array(1, 1), TrailingMinusNumbers:=False
End With
Application.ScreenUpdating = True
End Sub
I want to the result to look like this
我想让结果看起来像这样
采纳答案by Winterknell
Edited to remove a date objection and declare my variables
编辑以删除日期反对并声明我的变量
Dim aCol, aRow, aNames, colNow As Long, rowNow As Long, sLine As String
'Text to Columns
aRow = Split(sLine, "|")
With ActiveSheet
.Cells(2, 1) = Trim(Left(aRow(0), InStr(aRow(0), " ")))
'.Cells(2, 1) = Format(DateValue(aRow(0)), "d/m/yy")
For rowNow = 1 To UBound(aRow)
aCol = Split(aRow(rowNow), ",")
For colNow = 0 To UBound(aCol)
Sheet1.Cells(rowNow + 1, colNow + 2) = aCol(colNow)
Next
Next
End With
Oh, edited to add the column headers:
哦，编辑添加列标题：
aNames = Array("Date and Time", "John", "Kate", "Sean", "Stephen", "Brian", "Philip", "Peter")
For colNow = 0 To UBound(aNames)
ActiveSheet.Cells(1, colNow + 1) = aNames(colNow)
Next
回答by rei123
Modified code to make it more clear, its working now. See code below.
修改代码以使其更清晰，现在可以使用了。请参阅下面的代码。
Private Sub CommandButton1_Click()
Dim text As String, textline As String
Dim Cell As Range
Dim strLine() As String
Dim aCol, aRow, aNames, colNow As Long, rowNow As Long
Dim oldDate As Date, newDate As Date
Unload Me
myFile = Application.GetOpenFilename("Text Files (*.txt), *.txt")
If myFile = False Then
Exit Sub
'MsgBox ("No File Select. Exit")
Else
'Open and read file
Open myFile For Input As #1
Do Until EOF(1)
Line Input #1, textline
text = text & textline
Loop
Close #1
aRow = Split(textline, "|")
With ActiveSheet
.Cells(2, 1) = Trim(Left(aRow(0), InStr(aRow(0), " ")))
For rowNow = 1 To UBound(aRow)
aCol = Split(aRow(rowNow), ",")
For colNow = 0 To UBound(aCol)
ActiveSheet.Cells(rowNow + 1, colNow + 2) = aCol(colNow)
Next
Next
End With
End If
End Sub
