Solved

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

Posted on 2014-12-12
7
56 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 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 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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

615 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