Open csv file limited by "|" or not a common delimiter

Question

I am trying to customize excel vba code that opens some csv files and breaks into columns the information contained and limited by the "|" character. I manage to open the file, but the code I use opens my files without breaking the text according to the delimiter. I have been stuck with this since 2 days. 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 

Does anyone have any suggestions?

thanks

Alberto

Answer 1

I tried to do it. This does not work. But if you try to do the same in a text file (by copying the paste of csv content into a text file), it works.

If you look at MSDN Link , it says that if it is a text file , the description of the "Separator" parameter of the "workbooks.open" method. Perhaps for this reason it does not work.

I'm not sure. This is new to me too. Hope this helps.

Answer 2

I remember that it drove me a little crazy. Excel seems to have uncontrollable greed for .csv files. If you just change the ending ( .txt , .dat or something else), it will work!

Answer 3

Try using

 Delimiter:= Chr(124) 

Symbol 124 is the trumpet "|"

Answer 4

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 5

The solution on Rowan really works. The key replaces the file name "Test.csv" in its solution "Test.txt" in your "\ CSV_Files \" location. "Test.txt" should not be a separate type of comma value. It must be a true TXT file type.

Check the file type in Windows Explorer. Make sure it is not a CSV. If you use the CSV type, you will actually tell Excel, the data is parsed with a comma, not a delimiter.

If your book is located in the root: c: \ Create a directory: C: \ CSV_Files Place the text file: Test.txt in the \ CSV_Files directory

In your book, open VBA and copy the full VBA code below.

The full VBA code should look like this:

 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 6

 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 7

 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