Solved

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

Posted on 2014-12-12
7
51 Views
Last Modified: 2014-12-31
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
Comment
Question by:agwalsh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 51

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 125 total points
ID: 40495722
HI,

You could find and replace

Find 2 commas
Replace with 1 comma

Run until nothing found

Regards
0
 
LVL 70

Assisted Solution

by:Qlemo
Qlemo earned 125 total points
ID: 40495737
Or use PowerShell to generate a clean file:
   (get-content file.txt) -replace ',,+',',' | out-file newfile. txt
That will do in one go.
0
 
LVL 51

Expert Comment

by:Rgonzo1971
ID: 40495745
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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 33

Expert Comment

by:Rob Henson
ID: 40495759
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
 

Author Comment

by:agwalsh
ID: 40495770
@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
 
LVL 33

Accepted Solution

by:
Rob Henson earned 250 total points
ID: 40495871
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
 

Author Closing Comment

by:agwalsh
ID: 40525248
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question