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".)
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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
 
Upvote 0
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.
 
Upvote 0
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 !!
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
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.

??
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top