Open csv file delimited by pipe character "|" or not common delimiter
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.
Ali Gröch
Updated on May 10, 2021Comments
-
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 over 10 yearsThanks for your reply, I set up the extension change in VBA and now it works fine!
-
J. Ghyllebert almost 9 yearsCould you add some explanation?
-
Mauro over 5 yearsDid 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 over 5 yearsThat would not make any difference (you might only screw up the encoding when you copy the content into a new file).
-
Sebastien about 3 yearsExaclty. 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.