vba 打开由管道字符“|”分隔的csv文件 或不常见的分隔符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17832618/
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
Open csv file delimited by pipe character "|" or not common delimiter
提问by Ali Gr?ch
I'm trying to set up an Excel VBA code that opens up some .csv files and split into columns the information contained and delimited by the character |. I manage to open the file but the code I use opens my files without splitting the text according to the delimiter. So far I have tried the following code:
我正在尝试设置一个 Excel VBA 代码，该代码可打开一些 .csv 文件并将包含的信息拆分为由字符 分隔的列|。我设法打开了文件，但我使用的代码打开了我的文件，而没有根据分隔符拆分文本。到目前为止，我已经尝试了以下代码：
Sub OpenCSV()
Dim wkbTemp As Workbook
Dim sPath As String, sName As String
sPath = ThisWorkbook.Path & "\CSV_Files\"
sName = "Test.csv"
Set wkbTemp = Workbooks.Open(Filename:=sPath & sName, Format:=6, Delimiter:="|")
End Sub
回答by Bharath Raja
I tried doing this. It doesn't work. But if you try doing the same on a text file(by copy pasting the csv contents to a text file), it works.
我试过这样做。它不起作用。但是，如果您尝试对文本文件执行相同操作（通过将 csv 内容复制粘贴到文本文件中），它会起作用。
If you look at MSDN Link, it specifically says that 'if it is a text file'in the description of 'Delimiter' parameter of 'workbooks.open' method. Maybe this is the reason that it is not working.
如果您查看MSDN Link，它会在“workbooks.open”方法的“Delimiter”参数的描述中特别指出“如果它是文本文件”。也许这就是它不起作用的原因。
I am not sure. This is a new thing for me too. Hope this helps.
我不确定。这对我来说也是一件新鲜事。希望这可以帮助。
回答by KekuSemau
I remember this has driven me insane some time back.
It seems that Excel has an uncontrolled greed for .csvfiles. If you just change the ending (.txt, .dator whatever), it will work!
我记得这让我发疯了一段时间。
Excel似乎对.csv文件有一种不受控制的贪婪。如果您只是更改结尾（.txt，.dat或其他），它将起作用！
回答by RowanC
I think this should help you.
我想这应该对你有帮助。
Sub OpenCSV()
Dim wkbTemp As Workbook
Dim sPath As String, sName As String
sPath = ThisWorkbook.Path & "\CSV_Files\"
sName = "Test.csv"
Workbooks.OpenText Filename:=sPath & sName, _
Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
, Comma:=False, Space:=False, Other:=True, OtherChar:="|"
Set wkbTemp = ActiveWorkbook
end sub
回答by Anthony
Rowan's solution actually does work. The key is replace the file name "Test.csv" in his solution with "Test.txt" in your "\CSV_Files\" location. The "Test.txt" should not be a comma separate value type. It should be a true TXT file type.
Rowan 的解决方案确实有效。关键是将其解决方案中的文件名“Test.csv”替换为“\CSV_Files\”位置中的“Test.txt”。“Test.txt”不应是逗号分隔值类型。它应该是真正的 TXT 文件类型。
Check the file type in Windows Explorer. Make sure it is not CSV. If you use a CSV type you will be in fact telling Excel the data is parsed by a comma rather than the pipe delimiter.
在 Windows 资源管理器中检查文件类型。确保它不是 CSV。如果您使用 CSV 类型，您实际上会告诉 Excel 数据是由逗号而不是管道分隔符解析的。
If your workbook is in root: c:\ Create the directory: C:\CSV_Files Put the text file: Test.txt in the directory \CSV_Files
如果您的工作簿位于根目录：c:\ 创建目录：C:\CSV_Files 将文本文件：Test.txt 放入目录 \CSV_Files
In your workbook open VBA and copy the full VBA code below.
在您的工作簿中打开 VBA 并复制下面的完整 VBA 代码。
The full VBA code should read:
完整的 VBA 代码应为：
Sub OpenCSV()
Dim wkbTemp As Workbook
Dim sPath As String, sName As String
sPath = ThisWorkbook.Path & "\CSV_Files\"
sName = "Test.txt"
Workbooks.OpenText Filename:=sPath & sName, _
Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
, Comma:=False, Space:=False, Other:=True, OtherChar:="|"
Set wkbTemp = ActiveWorkbook
end sub
Close VBA and run the macro.
关闭 VBA 并运行宏。
回答by Pawan Sharma
Option Explicit
Private Sub Text2Excel()
Dim excel_app As Excel.Application
Dim max_col As Integer
Dim txtFromFile As Variant
Dim Sep As String
'DoEvents
Application.ScreenUpdating = False
txtFromFile = Application.GetOpenFilename(FileFilter:="Text Files (*.txt),*.txt," & _
"CSV Files (*.csv),*.csv")
If txtFromFile = False Then
''''''''''''''''''''''''''
' user cancelled, get out
''''''''''''''''''''''''''
Exit Sub
End If
Sep = Application.InputBox("Enter a separator character." & vbNewLine & "For TAB Delimited keep BLANK.", Type:=2)
If Sep = vbNullString Then
' user cancelled, get out
Sep = vbTab
End If
'Pull the data from test file to activesheet
Workbooks.OpenText FileName:=txtFromFile, DataType:=xlDelimited, Other:=True, otherchar:=Sep, local:=True
MsgBox "Data from selected file " & txtFromFile & " has been pulled to Excel.", vbInformation
Application.ScreenUpdating = False
End Sub
回答by tanoMandanga
Sub CSVtoXLS()
Dim xFd As FileDialog
Dim xSPath As String
Dim xCSVFile As String
Dim xWsheet As String
Dim xOtherChar As String
Application.DisplayAlerts = False
Application.StatusBar = True
xWsheet = ActiveWorkbook.Name
Set xFd = Application.FileDialog(msoFileDialogFolderPicker)
xFd.Title = "Select a folder:"
If xFd.Show = -1 Then
xSPath = xFd.SelectedItems(1)
Else
Exit Sub
End If
If Right(xSPath, 1) <> "\" Then xSPath = xSPath + "\"
xCSVFile = Dir(xSPath & "*.csv")
xOtherChar = InputBox("Please indicate delimiter:", "CSV file/Text to column Converter", ",")
Do While xCSVFile <> ""
Application.StatusBar = "Converting: " & xCSVFile
Workbooks.OpenText Filename:=xSPath & xCSVFile, DataType:=xlDelimited, Tab:=True, Other:=True, OtherChar:=";"
ActiveWorkbook.SaveAs Replace(xSPath & xCSVFile, ".csv", ".xls", vbTextCompare), xlWorkbookDefault
ActiveWorkbook.Close
Windows(xWsheet).Activate
xCSVFile = Dir
Loop
Application.StatusBar = False
Application.DisplayAlerts = True
End Sub
回答by Andy G
Try it with
试试看
Delimiter:= Chr(124)
Character 124 is the pipe "|"
字符 124 是管道“|”
