Solved

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

Posted on 2014-12-12
7
42 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
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 49

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 69

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 49

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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
LVL 32

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 32

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

776 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