Open csv file delimited by pipe character "|" or not common delimiter

33,000

Solution 1

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!

Solution 2

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.

Solution 3

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.

Share:
33,000
Ali Gröch
Author by

Ali Gröch

Updated on May 10, 2021

Comments

  • Ali Gröch
    Ali Gröch over 2 years

    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
    
  • Ali Gröch
    Ali Gröch over 10 years
    Thanks for your reply, I set up the extension change in VBA and now it works fine!
  • J. Ghyllebert
    J. Ghyllebert almost 9 years
    Could you add some explanation?
  • Mauro
    Mauro over 5 years
    Did you just changed the ".csv" part of the string for ".txt" or do you created a new text file copying the content before open?
  • KekuSemau
    KekuSemau over 5 years
    That would not make any difference (you might only screw up the encoding when you copy the content into a new file).
  • Sebastien
    Sebastien about 3 years
    Exaclty. The only way I could get Workbooks.OpenText to work with a SemiColon delimited .csv was to use the Name method to temporarily change the filename to .txt, load the contents, then finally rename the file back to .csv.