How to add a "Comma" to all data rows

[theghost]

Hello all. Hope you can help me.
Merry Christmas everyone by the way:-)

I would very much like to be able to add either a "," (Comma) or a ";" (Semi Colon) to the end of each piece of data in my text file.
I have some keyword software that exports the data into text format (Which is the data I have now) but without a "," (Comma) on the end. I need this for another piece of software to be able to recognise the data and import it:-(

My data looks like this;
--------------------------------------------------------------------------
dns hosting Stopped Stopped 0 Stopped Stopped Stopped Stopped 0 0 0 Stopped Stopped Stopped 0.00
dns hosting asia Stopped Stopped 0 Stopped Stopped Stopped Stopped 0 0 0 Stopped Stopped Stopped 0.00
dns hosting thailand Stopped Stopped 0 Stopped Stopped Stopped Stopped 0 0 0 Stopped Stopped Stopped 0.00
dns registration Stopped Stopped 0 Stopped Stopped Stopped Stopped 0 0 0 Stopped Stopped Stopped 0.00
dns registration asia Stopped Stopped 0 Stopped Stopped Stopped Stopped 0 0 0 Stopped Stopped Stopped 0.00
dns registration thailand Stopped Stopped 0 Stopped Stopped Stopped Stopped 0 0 0 Stopped Stopped Stopped 0.00
dns server Stopped Stopped 0 Stopped Stopped Stopped Stopped 0 0 0 Stopped Stopped Stopped 0.00
dns server asia Stopped Stopped 0 Stopped Stopped Stopped Stopped 0 0 0 Stopped Stopped Stopped 0.00
dns server name Stopped Stopped 0 Stopped Stopped Stopped Stopped 0 0 0 Stopped Stopped Stopped 0.00
dns server name asia Stopped Stopped 0 Stopped Stopped Stopped Stopped 0 0 0 Stopped Stopped Stopped 0.00
dns server name thailand Stopped Stopped 0 Stopped Stopped Stopped Stopped 0 0 0 Stopped Stopped Stopped 0.00
dns server thailand Stopped Stopped 0 Stopped Stopped Stopped Stopped 0 0 0 Stopped Stopped Stopped 0.00
dns web hosting Stopped Stopped 0 Stopped Stopped Stopped Stopped 0 0 0 Stopped Stopped Stopped 0.00
dns web hosting asia Stopped Stopped 0 Stopped Stopped Stopped Stopped 0 0 0 Stopped Stopped Stopped 0.00
dns web hosting thailand Stopped Stopped 0 Stopped Stopped Stopped Stopped 0 0 0 Stopped Stopped Stopped 0.00
doctor ecommerce web site development Stopped Stopped 0 Stopped Stopped Stopped Stopped 0 0 0 Stopped Stopped Stopped 0.00
doctor ecommerce web site development asia Stopped Stopped 0 Stopped Stopped Stopped Stopped 0 0 0 Stopped Stopped Stopped 0.00
doctor ecommerce web site development thailand Stopped Stopped 0 Stopped Stopped Stopped Stopped 0 0 0 Stopped Stopped Stopped 0.00
domain Stopped Stopped 0 Stopped Stopped Stopped Stopped 0 0 0 Stopped Stopped Stopped 0.00
domain & hosting Stopped Stopped 0 Stopped Stopped Stopped Stopped 0 0 0 Stopped Stopped Stopped 0.00
domain & hosting asia Stopped Stopped 0 Stopped Stopped Stopped Stopped 0 0 0 Stopped Stopped Stopped 0.00
----------------------------------------------------------------------------
In 1 text file there are 18 columns and about 10500 rows ./or lines of data. I'm not sure how the above has come out on this html page. But basically there are 18 columns like I said and every (Cell) if you like on each row needs a "," on the end

Can this be done easily???
Hope someone can help me

Many Thanks
John

[MudGuard]

So what marks the end of each "piece of data"?
Whatever it is, replace it by a comma or a semicolon.

[Bob Hansen]

But if each row is "data", then replace each end of line (\n) with comma or semicolon.
Search and Replace with checkmark in Regular Expressions.
Search for: \n
Replace with: ,\n

===========================
For future postings.
This should be posted in the General Area vs. the How To.

From the sticky at http://forum.textpad.com/viewtopic.php? ... ote]Please do not post questions here. Use either the General or Java forum

This forum is for you to post helpful instructions on how you have done things with TextPad.[/quote]

[theghost]

Firstly,
A big sorry,
It seems I've posted it in the wrong part of the forum.
A genuine mistake. I just thought when it was called "How To-s", this was the part of the forum to ask questions on "how to" do things:-(
Again, sorry.

As to my problem,
I can send this text file out no problem if someone PM'd me as it seems I can't seem to get across how it looks like properly,
I'll try and show you below, I'll just type a line of what I see;
a domain registrar 0 70,500 0 12 40 3 3 0 0 0 0.67 0.2 0.12 0.55

---------------------------------------------------
This is all on 1 line. and there are over 10,000 lines
All the information has been imported into a text file with no commas at the end of each row of data. (15 Columns of data)
I want to be able to add either , or; to the end of each piece of data so I can import it into another piece of software as it can't recognise the different columns without a , or; not being at the end of the data.

I hope this makes sense.
Again,
I can email this to anyone if they really need to see it. its 1.95mb big.
But if I use winRar on best compression it's only 63kb!

Many thanks for all the replies though
John

[theghost]

Sorry again to the moderator of this forum for posting the question in the wrong part of the forum.
I should have asked is it best to continue it here or stop and restart this post in the other part of the forum??

Also, I'm not here at all to promote other software, but if the following formula helps anyone here; well, I thought I'd just post it for you all.

I asked the question on Mr.Excel also.
And someone came back to me with an answer that worked a dream. It's a macro function.
I'm no where in the league of writing formulas, this is all new and beyond me at present.
But my question to add commas to the end of cells,
I really love textpad and would have liked to be able to do it from within it.
But if I have to now, I can do it in excel, then copy it all into a new sheet of textpad
here's what someone sent me.
Hope it might halp anyone here that's all
Go to menu item tools -> macro -> visual basic editor
In the resulting Microsoft Visual Basic menu choose insert - > module
Copy the following lines from "sub addcomma()" to "End Sub"

Sub addcomma()
Dim a As Range, e
Set a = ActiveSheet.UsedRange
For Each e In a
If Not IsEmpty(e) Then
e.Value = e.Value & ","
End If
Next
End Sub

Make sure the cursor remains in the Microsoft Visual Basic window and press key F5.
Close Microsoft Visual Basic window when finished.
Back to top



--------------------------------
Merry christmas by the way evryone
John (newbie)

[Bob Hansen]

Did my earlier suggestion not work for you?

[theghost]

Sorry,
It seems I didn't make myself clear.
Sorry Bob. I wasn't that clear. You answered correct to what I asked, but I asked wrong:-(
My mistake. Sorry.
I said I want to add a comma to the end of each row of data and ther are over 10,000 rows.

I've stated this wrong

What I meant was on each row there are 15 columns of data and I need to add a comma after each column of data.

A big difference to what I stated earlier, sorry:-(

so if the row looks like this;
----------------------------------------------------------------------------
a domain registrar 0 70500 0 12 40 3 3 0 0 0 0.67 0.2 0.12 0.55
-----------------------------------------------------------------------------
so after the words "a domain registrar" i need a comma, then I need commas after each of the blocks of numbers (as they are all data cells)
so comma after 0 then after 70500 then after 0 again etc etc

Hope this is clearer.
I can attach a text file of it if anyone pm's me so they can see exactly what it looks like.
Sorry for the confusion

Many thanks
John

[Bob Hansen]

I am using "_" to represent the invisible space character.

Search for: ([0-9])_
Replace with: \1,
Click on Replace All

Using Regular Expressions.
Configure|Preferences|Editor| Put checkmark to use POSIX syntax.

[rsperberg]

I am using "_" to represent the invisible space character.

Search for: ([0-9])_
Replace with: \1,
Click on Replace All

Using Regular Expressions.
Configure|Preferences|Editor| Put checkmark to use POSIX syntax.

Hm-m. I don't see how this would put a comma after that first entry. And won't this treat an entry like 0.67 as two columns?

Should it perhaps be:

Search for: _([0-9\.])
Replace with: ,\1
Click on Replace All

Roger Sperberg
A longtime regex newbie

[Bob Hansen]

I don't see how this would put a comma after that first entry. And won't this treat an entry like 0.67 as two columns?

Oops, sorry. Thanks for the reminder.
I forgot to add the second Search and Replace for the comma after the first group of text entry.

Second Search/Replace after my earlier one:
Search for: r_
Replace with: r,
Replace All, using _ for the invisible space
==============================================

And NO, the earlier first Search/Replace puts one comma after 0.67 to end up as 0.67, it handles integers and decimals because it is ignoring everything prior to the last numbers before a space.

The alternate that you suggested using the sample of:
a domain registrar 0 70500 0 12 40 3 3 0 0 0 0.67 0.2 0.12 0.55
ends up with this:
a domain registrar,070500,012 40,33,00,00.67 0.2 0.12 0.55 vs. this:
a domain registrar 0,70500,0,12,40,3,3,0,0,0,0.67,0.2,0.12,0.55
You got the comma after the first phrases, but the data changed dramatically.

[rsperberg]

Bob,

I see now that your regex is locating a digit followed by a space, while I was thinking the desired regex should be locating a number (such as 0.67). Absolutely no need for that, of course, and adding the "\." as I did doesn't locate numbers either. I guess that would require a "+":

Search for: _([0-9\.]+)

Re your note:

The alternate that you suggested ... ends up with this:
a domain registrar,070500,012 40,33,00,00.67 0.2 0.12 0.55
vs. this:
a domain registrar 0,70500,0,12,40,3,3,0,0,0,0.67,0.2,0.12,0.55
You got the comma after the first phrases, but the data changed dramatically.

I dunno, it works perfectly for me. I don't get anything close to what you suggest. Is this one of those "Is Posix checked?" situations?

I don't have any hesitation in saying you're my superior at regexes, but if John was using a generic phrase "a domain registrar" then it seems to me that the additional search-and-replace isn't going to work without making it a regex that searches for a space followed by a number anyway.

But saying that makes me see that if a registrar's name includes spaces and numbers, things like "The Number 1 Webhost" or "Paris 3000" will get a comma inserted into the registrar's name where it doesn't belong.

Is there a way to say "Change the spaces that precede numbers to commas, but only the last 14"?

Hm-m. Maybe the two-regex approach is better after all. If there isn't some way to express the above, I seem to recall there is some way to say "only change the last one of these you find." And the last remaining space in the approach you suggested would be between the domain registrar's name and the first number.

Roger

Added later: I have Posix checked, to remove any ambiguity my question may have introduced.

[Bob Hansen]

I don't have any hesitation in saying you're my superior at regexes,

Hi Roger ..... please, hesitate!

I am still forcing myself to get better every day with regex. ben_josephs is our local resident expert, I am surprised he has not provided the single search expression we are looking for.

For the record, re my responses, I usually take the literal samples provided to work with. That forces the submitter to do a better job of defining that vs. trying to anticipate every combination of options that may occur. And I always use POSIX, so I am confused about us having different results unless you were not using POSIX ?

And although I would like a single Search/Replace, I have no problem with doing multiple passes and/or creating macros. TextPad is usually fast enough, but we all have our own acceptance levels of performance.

[ben_josephs]

You could try

Find what: -([^-,]+)((-[^-,]+){0,13})$ [Replace the hyphens with spaces]
Replace with: ,\1\2

[X] Regular expression

and Replace All repeatedly until they're all done.

[Fredkc]

Sure, I'll take a stab at it!

Ok, I took your sample data, and made a 10,100 line file by simply replicating it.

If what you see below is acceptable, then what I did to accomplish it shouldn't take
more than 5-7 minutes, tops. No, this is not a one-button-click answer. But it does
work, and it went fast.

'dns server name','Stopped','Stopped','0','Stopped','Stopped','Stopped','Stopped','0','0','0','Stopped','Stopped','Stopped','0.00'
'dns server name asia','Stopped','Stopped','0','Stopped','Stopped','Stopped','Stopped','0','0','0','Stopped','Stopped','Stopped','0.00'
'dns server name thailand','Stopped','Stopped','0','Stopped','Stopped','Stopped','Stopped','0','0','0','Stopped','Stopped','Stopped','0.00'
'dns server thailand','Stopped','Stopped','0','Stopped','Stopped','Stopped','Stopped','0','0','0','Stopped','Stopped','Stopped','0.00'
'dns web hosting','Stopped','Stopped','0','Stopped','Stopped','Stopped','Stopped','0','0','0','Stopped','Stopped','Stopped','0.00'
'dns web hosting asia','Stopped','Stopped','0','Stopped','Stopped','Stopped','Stopped','0','0','0','Stopped','Stopped','Stopped','0.00'
'dns web hosting thailand','Stopped','Stopped','0','Stopped','Stopped','Stopped','Stopped','0','0','0','Stopped','Stopped','Stopped','0.00'
'doctor ecommerce web site development','Stopped','Stopped','0','Stopped','Stopped','Stopped','Stopped','0','0','0','Stopped','Stopped','Stopped','0.00'
'doctor ecommerce web site development asia','Stopped','Stopped','0','Stopped','Stopped','Stopped','Stopped','0','0','0','Stopped','Stopped','Stopped','0.00'
'doctor ecommerce web site development thailand','Stopped','Stopped','0','Stopped','Stopped','Stopped','Stopped','0','0','0','Stopped','Stopped','Stopped','0.00'
'domain','Stopped','Stopped','0','Stopped','Stopped','Stopped','Stopped','0','0','0','Stopped','Stopped','Stopped','0.00'
'domain & hosting','Stopped','Stopped','0','Stopped','Stopped','Stopped','Stopped','0','0','0','Stopped','Stopped','Stopped','0.00'
'domain & hosting asia','Stopped','Stopped','0','Stopped','Stopped','Stopped','Stopped','0','0','0','Stopped','Stopped','Stopped','0.00'
'a domain registrar','0','70,500','0','12','40','3','3','0','0','0','0.67','0.2','0.12','0.55'
'a domain registrar','0','70,500','0','12','40','3','3','0','0','0','0.67','0.2','0.12','0.55'
'a domain registrar','0','70,500','0','12','40','3','3','0','0','0','0.67','0.2','0.12','0.55'
'a domain registrar','0','70,500','0','12','40','3','3','0','0','0','0.67','0.2','0.12','0.55'
'a domain registrar','0','70,500','0','12','40','3','3','0','0','0','0.67','0.2','0.12','0.55'
'a domain registrar','0','70,500','0','12','40','3','3','0','0','0','0.67','0.2','0.12','0.55'
'a domain registrar','0','70,500','0','12','40','3','3','0','0','0','0.67','0.2','0.12','0.55'
'a domain registrar','0','70,500','0','12','40','3','3','0','0','0','0.67','0.2','0.12','0.55'
_______________________________________________

If you wanted " instead of ' like this:

"doctor ecommerce web site development thailand","Stopped","Stopped","0","Stopped","Stopped","Stopped","Stopped","0","0","0","Stopped","Stopped","Stopped","0.00"
"domain","Stopped","Stopped","0","Stopped","Stopped","Stopped","Stopped","0","0","0","Stopped","Stopped","Stopped","0.00"
"domain & hosting","Stopped","Stopped","0","Stopped","Stopped","Stopped","Stopped","0","0","0","Stopped","Stopped","Stopped","0.00"
"domain & hosting asia","Stopped","Stopped","0","Stopped","Stopped","Stopped","Stopped","0","0","0","Stopped","Stopped","Stopped","0.00"
"a domain registrar","0","70,500","0","12","40","3","3","0","0","0","0.67","0.2","0.12","0.55"
"a domain registrar","0","70,500","0","12","40","3","3","0","0","0","0.67","0.2","0.12","0.55"
"a domain registrar","0","70,500","0","12","40","3","3","0","0","0","0.67","0.2","0.12","0.55"

then substitute it in the instructions below.
______________________________________

Now, rather than confuse hell outa things by trying to put things in quotes, I placed
them around nothing. So, type what you see with the only exception being I placed
[space character] where a space belongs in a search or replace.

1. Turn on the regular expression checkbox in the search/search-replace box.
2. Search and replace , with ~
3. Replace Stopped with , Stopped (43,000+ occurrences)
4. Search for [a-z] [0-9]
5. Then write the following macro:
Ctrl-F Left-Arrow Right-Arow ,
6. Execute that macro to end of file.
7. Search for [0-9] [0-9]
8. Execute same macro to end of file.
9. Search and replace ,[space character] with ',' (141,000 occurrences)
10. Search and replace [space character]\n with '\n' (10,100 occurrences)
Finally
11. Search and replace ~ with ,

No it ain't elegant, tricky or anything close, but it works.

HUGE Hint for macros: When you are executing a macro over a large file, you can speed up the process by a factor of 10, at least.

Heres how:
1. Open any other window, a browser berhaps, and size it's window so it completely covers both the text window, and the Document Selector window, if open.
2. Then go to TextPad, and start your macro.
3. Click on the "other window" with the mouse, and keep the mouse off the TextPad window while the macro runs. If you want to see the difference in speed, place the other window so that only the scroll indicator of the text window can be seen. Updating the screen at each line shift in text takes a huge amount of your computer's CPU time. Cover the window and this is no longer necessary freeing it up for the real work of making the changes.

Enjoy.