Open csv file delimited by pipe character “|” or n

Question

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:

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

Answer 1

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

Answer 2

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

Answer 3

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.

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.

I am not sure. This is a new thing for me too. Hope this helps.

Answer 4

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.

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.

If your workbook is in root: c:\ Create the directory: C:\CSV_Files Put the text file: Test.txt in the directory \CSV_Files

In your workbook open VBA and copy the full VBA code below.

The full VBA code should read:

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.

Answer 5

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

Answer 6

I remember this has driven me insane some time back.
It seems that Excel has an uncontrolled greed for .csv files. If you just change the ending (.txt, .dat or whatever), it will work!