Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2014-12-12
7
Medium Priority
?
59 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 52

Assisted Solution

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

You could find and replace

Find 2 commas
Replace with 1 comma

Run until nothing found

Regards
0
 
LVL 71

Assisted Solution

by:Qlemo
Qlemo earned 500 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 52

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 1000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

718 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