Replace comma with pipe in .csv file

[fddekker]

I have searched the internet but have not find the proper solution; however it seems that several users have the same problem. My current client receives a .csv comma delimited files from the source. In these files, some fields contains commas that appear in a text file in double quotes. Editing takes place in Excel, and the files get re-saved and then uploaded to a different software system. In order not to manipulate the files other than making necessary changes, I would like to run a macro on the csv file to replace commas that serves as delimiters with pipes, and then remove the double quotes (leaving the commas that do not serve as delimiters). I know how to delete the double quotes, but am having problems with replacing the correct commas with pipes. The reason I would like to do this as an Excel macro, is because I would like to add it to a ribbon that already contains other macros for them.

I found this on VBA express (please let me know if this was solved here on Mr Excel):

In a similar posting on Stockoverflow, some users suggest Imports System, Text, RegularExpressions. Can this be imported into VBA code in Excel?

Any help approciated

 

[mole999]

have a read here Export or Save Excel Files with Pipe (or Other) Delimiters Instead of Commas

though CSV does imply Comma

 

[fddekker]

Hi, I am aware of this, but these files are NOT Excel files that are saved as csv files. I have already changed the defaults for the users to pipe as per the help page you provided.

 

[mole999]

I was thinking, if the come into excel as CSV, then saved as CSV with pipe, that might do the conversion for you

 

[fddekker]

Unfortunately this method replaces all commas, including those that are not delimiters.

I feel that there might be a simple solution, like when replacing commas with blanks as in:

Dim FileContent As String: FileContent = GetFile(FileName)
FileContent = VBA.Replace(FileContent, """", "")

but by searching for a pattern.
The problem is that I don't know how to construct the pattern to work.

 

[mole999]

one quick way is to record the find replace you want to occur, then post the code here to get it tweaked if not evident

 

[fddekker]

Just to recap. The code should remove all commas with pipes, unless the comma is in a string that are bounded by double quotes.

This is what I feel should work, but there is a syntax error:

Function CommaPipe(s As String)
With CreateObject("vbscript.regexp")
.Pattern = ",([^,"]*(?:"[^"]*")?[^,"]*)"
.ignorecase = True
.Global = True
CommaPipe = .Replace(s, "|$")
End With
End Function

Any indication where I am going wrong?

 

[mole999]

This is the excel macro recorder

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("A1:D10").Select
    Selection.Replace What:=",", Replacement:="|", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub

not sure how to check for "," straight off

 

[Jerry Sullivan]

Just to recap. The code should remove all commas with pipes, unless the comma is in a string that are bounded by double quotes.

Hi fddekker, If your objective is to change the delimiter from comma to pipe in the existing .txt file, I think that task involves more than replacing commas with pipes and ignoring literal commas nested in fields wrapped with quotes.

Additionally, the code needs to:
1. Remove the quotes wrapping the fields have nested commas.

2. Add quotes wrapping any fields that had literal pipes in the existing .txt file (if that is a possibility).

To do that effectively and robustly probably requires a parser similar to the VB.net (not VBA) Text Field Parser class used in the example in your OP.

While RegEx might be able to achieve this, if the results are critical, I'd be concerned that an exceptional unanticipated pattern in your data might cause incorrect results.

You might consider using VBA to Import the comma delimited file into Excel and export the file with as pipe delimited. (Mole suggested that in Post #4 although it wasn't clear whether he was suggesting a manual process or a VBA macro that could be launched from the Ribbon).

Just let me know if you want some help coding that approach.

 

[Macropod]

My approach would be to replace the commas in the Excel file before exporting (e.g. with §), then replacing the § in the output file with a comma. That way, you probably won't even have any double quotes to deal with, either.