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

 

[Jerry Sullivan]

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.

Hi Paul, The OP commented earlier that the .txt files already exist.

Perhaps a macro that uses a hybrid of those suggestions that would avoid the need to change the Windows separator character could be:
1. Read the csv file into Excel
2. Replace the literal commas with an unused character (e.g. with §).
3. Export as csv
4. Use relatively simple VBA IO to replace commas with pipes then § with commas.

 

[Macropod]

Going back a step, though, the OP said:

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.

It's before the re-saving that I'm suggesting the commas should be replaced and then, before uploading to the other system, the pipe-delimited text file gets edited to restore them.

 

[Jerry Sullivan]

Going back a step, though, the OP said:

It's before the re-saving that I'm suggesting the commas should be replaced and then, before uploading to the other system, the pipe-delimited text file gets edited to restore them.

Hmmm....That is what it says, but I believe there's more to it. If the users have already opened the comma delimited file and their list separators have been changed to a pipe, they could just save the file as .txt and the file should be ready to be uploaded to the other system. (The fields with the literal commas won't be wrapped in quotes because commas are not the user's list separator).

I think there's some constraint against doing that that we are missing.

 

[thisoldman]

I use Perl for complex text manipulations and avoid VB. But I did come up with a regex I think works.

You want to replace all commas that have an even number of quotation marks after them. You want to save the commas that have an odd number of quotation marks after them.

This is the comma you want to replace with the pipe character '|':

,(?=(?:[^"]|"[^"]*")*$)

Here's the explanation:

, — The comma you want to replace, if...

(?= — it is followed by (start of positive look-ahead)
​

(?: — start of non-capturing group
[^"] — a single character that is not a quotation mark​

| — Or
​

" — a quotation mark followed by
[^"]* — zero or more characters that are not quotation marks
" — followed by one quotation mark
) — end of non-capturing group
* — We want the non-captured group any number of times, including zero times
​

$ — to the end of the line​

) — end of look-ahead​

Tested in perl with these strings:

foo,"bar,baz",foo
"bar,baz",foo,foo
foo,foo,"bar,baz"
foo,foo,foo
"bar,baz","bar,baz","bar,baz"

 

[thisoldman]

Looking over my previous post, I noticed I didn't test for an empty line and I didn't test for a line of commas only. My regex worked on both of these in Perl.

I also did not perhaps make clear, in my explanation of the regex, that the alternation, the pipe character in `,(?=(?:[^"]|"[^"]*")*$)`, only extends from the single character immediately preceding the pipe to the single character immediately following the pipe.

 

[fddekker]

Macropod, that is my current 'manual' way of getting around it. The problem is that using any special character leaves me exposed when somebody for one or other reason enters that in text line.

 

[fddekker]

Thanks everyone (and old man with not so old brain)
I solved another complicated search with regex, so I had high hopes for this one.

Below is my original attempt (after going through this page: https://msdn.microsoft.com/en-us/library/ms974570.aspx#scripting05_topic5
and the code you suggested. Somewhere I am making a syntax error and I just can't figure it out. Is it something stupid that I am overlooking?

If I can figure out the syntax error, I can test the scenarios.

Thanks in advance!!

 

[fddekker]

Looks like the double quote needs an escape (also double quote), but still gets error message. Replacing the quotes with Chr(34) seems to work.

 

[fddekker]

thisoldman .... and others..... is there a difference with regexp and PERL in handling the script? This is the results I have so far:

It looks like the code that works in PERL does not work in regexp, or am I doing something wrong?

Thanks

 

[Macropod]

An alternative approach for you to consider:

Sub Demo()
Dim r As Long, c As Long, StrData As String, DataFile As String
With ActiveSheet.UsedRange
  For r = 1 To .Cells.SpecialCells(xlCellTypeLastCell).Row
    For c = 1 To .Cells.SpecialCells(xlCellTypeLastCell).Column
      With .Cells(r, c)
        If InStr(.Value, ",") > 0 Then
          If (Left(.Value, 1) <> Chr(34)) Or (Right(.Value, 1) <> Chr(34)) Then
            StrData = StrData & Chr(34) & .Value & Chr(34) & "|"
          Else
            StrData = StrData & .Value & "|"
          End If
        Else
          StrData = StrData & .Value & "|"
        End If
      End With
    Next
    StrData = StrData & vbCrLf
  Next
  StrData = Replace(StrData, "|" & vbCrLf, vbCrLf)
  StrData = Left(StrData, Len(StrData) - 1)
End With
DataFile = ThisWorkbook.Path & "\Data.txt"
Open DataFile For Output As #1
Print #1, StrData
Close #1
End Sub