Determine delimiter in .csv file

Ozhatch

Board Regular
Joined
Oct 31, 2011
Messages
65
Office Version
  1. 2007
Platform
  1. Windows
How would I find out if the delimiter in a .csv file is either a comma or a semi-colon? And then, having determined that it's a semi-colon, how would I change that to a colon?
(Answers, please, in a format that can be easily understood by a "Bear with very little brain".)
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
7,977
Office Version
  1. 2016
Platform
  1. Windows
Something like this :
VBA Code:
Option Explicit


Sub Test()

    Const sFilePathName = "C:\Users\[B]YourCVSFilePathName.csv[/B]"
  
    If CVS_CHANGE_DELIMITER(sFilePathName, ";", ",") Then
        MsgBox "Success."
    Else
        MsgBox "Failure."
    End If

End Sub


Function CVS_CHANGE_DELIMITER(ByVal File As String, ByVal OldDelimiter As String, ByVal NewDelimiter As String) As Boolean

    Dim oFso As Object, oStream As Object, sData As String
  
    On Error GoTo errHandler

    If Len(Dir(File)) Then
        Set oFso = CreateObject("Scripting.FileSystemObject")
        Set oStream = oFso.OpenTextFile(File)
        sData = oStream.ReadAll
        If UBound(Split(sData, OldDelimiter)) >= 2 Then
            oFso.OpenTextFile(File, 2).Write Replace(Replace(sData, OldDelimiter, NewDelimiter), Chr(34), "")
            CVS_CHANGE_DELIMITER = True
        End If
errHandler:
        oStream.Close
    End If

End Function
 

Ozhatch

Board Regular
Joined
Oct 31, 2011
Messages
65
Office Version
  1. 2007
Platform
  1. Windows
Thank you, Jaafar. Please would you explain, in very simple terms, how I should apply this fix? I'm not familiar with running VBA code.
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
7,977
Office Version
  1. 2016
Platform
  1. Windows
Hi Ozhatch,

Add the entire code to a normal module and run the Test macro.

But before that, edit the constant sFilePathname with your CVS file actual path and name
Const sFilePathName = "YourCVSFilePathName.csv" ... Something like : Const sFilePathName = "C:\Test\MyCvs.csv"

EDIT:
Make your first test on a copy of your original cvs file just in case something goes wrong !!
 

Ozhatch

Board Regular
Joined
Oct 31, 2011
Messages
65
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

Hi again Jaafar.

I'm not having much success! Here's a snip of the error message I get when running the code.

What have I missed/messed up?

VBA code.JPG


Really appreciate your help!
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
7,977
Office Version
  1. 2016
Platform
  1. Windows
VBA Code:
Sub Test()

    Const sFilePathName = "C:\Users\David\Desktop\HNY 20191221_1_test.csv"
    
    If CVS_CHANGE_DELIMITER(sFilePathName, vbTab, ",") Then
        MsgBox "Success."
    Else
        MsgBox "Failure."
    End If

End Sub


Function CVS_CHANGE_DELIMITER(ByVal File As String, ByVal OldDelimiter, ByVal NewDelimiter) As Boolean

    Dim oFSO As Object, oStream As Object, sData As String
    
    On Error GoTo errHandler

    If Len(Dir(File)) Then
        Set oFSO = CreateObject("Scripting.FileSystemObject")
        Set oStream = oFSO.OpenTextFile(File)
        sData = oStream.ReadAll
        If UBound(Split(sData, OldDelimiter)) >= 2 Then
            oFSO.OpenTextFile(File, 2).Write Replace(Replace(sData, OldDelimiter, NewDelimiter), Chr(34), "")
            CVS_CHANGE_DELIMITER = True
        End If
errHandler:
        oStream.Close
    End If

End Function
 

Ozhatch

Board Regular
Joined
Oct 31, 2011
Messages
65
Office Version
  1. 2007
Platform
  1. Windows
Hi Jaafar.

I ended up re-creating the spreadsheet from scratch - entering one cell at a time. Took me quite some time, but it all worked out in the end.

Thanks again for your efforts.

🤜🤛
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
7,977
Office Version
  1. 2016
Platform
  1. Windows
Hi Jaafar.

I ended up re-creating the spreadsheet from scratch - entering one cell at a time. Took me quite some time, but it all worked out in the end.

Thanks again for your efforts.

🤜🤛
Glad you got this sorted and thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,112,788
Messages
5,542,520
Members
410,559
Latest member
jordansmith6532
Top