Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 62
  • Last Modified:

how to remove many commas from a text file...

hi Folks
I have a customer who imports a text file from a JD Edwards system and it comes in with many many commas e.g.
item,,,,,,,,,widget,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,thing,,,,,,,,,,,,,,,,,,,,,,,
(of course the number of commas is not consistent....:-)
Any suggestions on how she could clean up the file...thanks. :-)
0
agwalsh
Asked:
agwalsh
  • 2
  • 2
  • 2
  • +1
3 Solutions
 
Rgonzo1971Commented:
HI,

You could find and replace

Find 2 commas
Replace with 1 comma

Run until nothing found

Regards
0
 
QlemoC++ DeveloperCommented:
Or use PowerShell to generate a clean file:
   (get-content file.txt) -replace ',,+',',' | out-file newfile. txt
That will do in one go.
0
 
Rgonzo1971Commented:
with vba it would be

With Selection
    Set c = .Find(",,", LookIn:=xlValues)
    If Not c Is Nothing Then
        Do
            c.Replace What:=",,", Replacement:=",", LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False
        Set c = .FindNext(c)
        Loop While Not c Is Nothing
    End If
End With

Open in new window

Regards
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Rob HensonIT & Database AssistantCommented:
Use the Text to Columns functions with comma as a separator. Ensure the "Treat multiple separators as one" option is ticked.

Thanks
Rob H
0
 
agwalshAuthor Commented:
@Rob, I actually tried that one and it didn't seem to work. But will suggest again...will pass on all suggestions.. thank you :-)
0
 
Rob HensonIT & Database AssistantCommented:
Text to columns seems to work OK for me on the sample you supplied:
Text to Columns
As you can see the Comma option and consecutive delimiters are ticked. The pane below shows how it will come out, the three values in separate columns.

Thanks
Rob H
0
 
agwalshAuthor Commented:
This option worked for me - the Text to columns option when I tried it again and put the excess delimiter in the Other box. Great stuff.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now