Thread: Help with macros and Java

Put an autofilter/autosort at the top of each column. Select the dud values and move or delete them.

All done and compiled, runs perfectly. Thanks for all your help WoodPixel, I couldn't have done it without you.
If you're ever down in Tooradin, I'll shout you a few at the sports club.

Its all gratis.

I have started to edit another NAMES database and a new problem.
I need to separate the Lat, Long coordinates with a comma and insert a CR at the end of the coordinates in red removing any spaces.
In the last database it was easy as the coords were e.g. -23.31000 149.14000 . Just search for 000 1 and insert 000,1. Then search for 000(space) and insert 000CR.
I've done a bit of research and there is a plugin called Python that is supposed to be good for this type of thing.

1 MILE WATERHOLE,Waterhole,,-20.06327 137.02683 1 XE MOUNTAIN,Mountain/Peak/Hill,,-17.75106 144.41183 10 MILE CREEK,Watercourse,,-18.91145 128.28398 10 MILE CREEK,Watercourse,,-20.41063 134.45267 10 MILE CREEK,Watercourse,,-23.07116 149.18259 12 MILE,Homestead,,-21.84802 149.14621 12 MILE CREEK,Watercourse,,-21.99546 130.98233 12 MILE CREEK,Watercourse,,-23.94348 149.94395 12 MILE GULLY,Watercourse,,-21.73499 148.07807 12 MILE SIDING,Place Name,,-31.95086 141.13095 12 MILE WATERHOLE,Lake,,-18.73250 134.49800 12 MILE WATERHOLE,Watercourse,,-20.21750 137.13300 13 CREEK,Watercourse,,-23.31167 120.44849 13 MILE SWAMP,Swamp,,-17.96750 141.38500 14 MILE CREEK,Watercourse,,-17.22585 130.86932 14 MILE POOL,Lake,,-22.55550 119.86200 14 MILE WATERHOLE,Waterhole,,-26.58746 145.02077 14 MILE WATERHOLE,Lake,,-24.66310 139.54500 1400 DAM,Reservoir,,-26.69570 147.50100 15 MILE WATERHOLE,Waterhole,,-24.71111 133.43082 17 MILE FALLS,Waterfall,,-14.11716 132.37573 18 MILE CREEK,Watercourse,,-22.23687 149.30774 18 MILE CROSSING,Place Name,,-23.88612 146.55490 18 MILE DAM,Reservoir,,-20.84690 141.74700 18 MILE SOAK,Waterhole,,-27.96998 121.50140 18 MILE WATERHOLE,Lake,,-19.15870 136.44900 18 MILE WATERHOLE,Lake,,-23.89130 143.98500 2 MILE CREEK,Watercourse,,-18.07713 128.92200 2 MILE CREEK,Watercourse,,-22.21063 149.53125 2 MILE HOLE,Waterhole,,-21.06503 141.44978 20 MILE

Seriously? Someone just gave you an entire lump of text without a carriage return or break or identifying character? This is some hideous data.

Python isn't something one picks up in a day

There is no simple regex (regular expression) one can use or any means of doing this programmatically (simply) due to the presence of of a street address. (Im thinking a look-ahead regex might be usable).

I could write a php program to: find two numbers with a dot and five numbers then insert a carriage return.

This is how you want it to present?

1 MILE WATERHOLE,Waterhole,,-20.06327 137.02683
1 XE MOUNTAIN,Mountain/Peak/Hill,,-17.75106 144.41183
10 MILE CREEK,Watercourse,,-18.91145 128.28398
10 MILE CREEK,Watercourse,,-20.41063 134.45267
10 MILE CREEK,Watercourse,,-23.07116 149.18259
12 MILE,Homestead,,-21.84802 149.14621
12 MILE CREEK,Watercourse,,-21.99546 130.98233
12 MILE CREEK,Watercourse,,-23.94348 149.94395
12 MILE GULLY,Watercourse,,-21.73499 148.07807
12 MILE SIDING,Place Name,,-31.95086 141.13095
12 MILE WATERHOLE,Lake,,-18.73250 134.49800
12 MILE WATERHOLE,Watercourse,,-20.21750 137.13300

Contrary to what I said 2 minutes ago about being impossible, I did it.

Find values: (\d{5} \d{3}.\d{5} )
replace values: \1\n

There are two screenshots showing before and after.

In English:

Find: a 5 digit number then a space then a three digit number followed by a period then a five digit number and a space. (the brackets say "and keep the value you find")
Replace: the found value and insert a newline character.

Next, I'll show you how to add the comma in the last space for your LAT/LONGs



1.JPG2.JPG

Originally Posted by woodPixel

Seriously? Someone just gave you an entire lump of text without a carriage return or break or identifying character? This is some hideous data.

Python isn't something one picks up in a day

There is no simple regex (regular expression) one can use or any means of doing this programmatically (simply) due to the presence of of a street address. (Im thinking a look-ahead regex might be usable).

I could write a php program to: find two numbers with a dot and five numbers then insert a carriage return.

This is how you want it to present?

1 MILE WATERHOLE,Waterhole,,-20.06327 137.02683
1 XE MOUNTAIN,Mountain/Peak/Hill,,-17.75106 144.41183
10 MILE CREEK,Watercourse,,-18.91145 128.28398
10 MILE CREEK,Watercourse,,-20.41063 134.45267
10 MILE CREEK,Watercourse,,-23.07116 149.18259
12 MILE,Homestead,,-21.84802 149.14621
12 MILE CREEK,Watercourse,,-21.99546 130.98233
12 MILE CREEK,Watercourse,,-23.94348 149.94395
12 MILE GULLY,Watercourse,,-21.73499 148.07807
12 MILE SIDING,Place Name,,-31.95086 141.13095
12 MILE WATERHOLE,Lake,,-18.73250 134.49800
12 MILE WATERHOLE,Watercourse,,-20.21750 137.13300

This is not a text file per se, the original file is called Hema Australia.names. It is the file generated from a text file, I'm reverse engineering the file to incorporate it into another database.
It would need to be 2 numbers dot 5 numbers comma 3 numbers dot five numbers carriage return.

1 MILE WATERHOLE,Waterhole,,-20.06327,137.02683
1 XE MOUNTAIN,Mountain/Peak/Hill,,-17.75106,144.41183
10 MILE CREEK,Watercourse,,-18.91145,128.28398
10 MILE CREEK,Watercourse,,-20.41063,134.45267
10 MILE CREEK,Watercourse,,-23.07116,149.18259
12 MILE,Homestead,,-21.84802,149.14621
12 MILE CREEK,Watercourse,,-21.99546,130.98233
12 MILE CREEK,Watercourse,,-23.94348,149.94395
12 MILE GULLY,Watercourse,,-21.73499,148.07807
12 MILE SIDING,Place Name,,-31.95086,141.13095
12 MILE WATERHOLE,Lake,,-18.73250,134.49800
12 MILE WATERHOLE,Watercourse,,-20.21750,137.13300

Have a look Here under Utilities, Name Search Creator if you want to get idea of what I'm trying to do.

I was writing the above post while you were writing post #37.

Two steps, pretty easy.

STEP 1:

-- Find a space and number
-- replace with comma number

Find: (\d{3}.\d{5} )\n
Replace: ,\1\n

Note the screenshots. There is a space in that nasty expression.

3-before.JPG3-after.JPG


STEP 2: replace the spare spaces:

find: space, (I can't copy paste the space here, look at the screenshot. You will see a gap in the Find field)
replace: , (i.e. just comma).


4-before.JPG4-after.JPG

I went looking for a list of regular expressions and found this.

Regex can burn out your DNA and imperil your soul.

I've deliberately done this as multiple steps, for while a zen purist could do it all as one, doing it with unknown data and unknown blowouts will cause you to loose your hair (and DNA and soul). By breaking it into simple steps, one can see what to do next, and/or put in small fixes to correct inevitable weird-isms.

I used to run large teams as CTO of some seriously hard core IT guys and always bashed them into thinking simply. Smart-arsery always lead to Chernobyls and Fukushimas.

It is useful to know as an IT guy (sooooo few know Regex) but quite useless as a Regular Citizen

Originally Posted by woodPixel

Two steps, pretty easy.

STEP 1:

-- Find a space and number
-- replace with comma number

Find: (\d{3}.\d{5} )\n
Replace: ,\1\n

Note the screenshots. There is a space in that nasty expression.

3-before.JPG3-after.JPG


STEP 2: replace the spare spaces:

find: space, (I can't copy paste the space here, look at the screenshot. You will see a gap in the Find field)
replace: , (i.e. just comma).


4-before.JPG4-after.JPG

In Step 1, I'm getting the error message - Find: Cant find the text "(\d{3}.\d{5} )\n"
Is it because I'm currently working with one very long line with no line breaks or carriage returns.
Should I be looking for (\d{3}.\d{5} ) ?

I ran the revised argument with one replace and got this result.
I notice that a comma has been inserted between the two coordinates.

1 MILE WATERHOLE,Waterhole,,-20.06327 ,137.02683 space LF
1 XE MOUNTAIN,Mountain/Peak/Hill,,-17.75106 144.41183 10 MILE CREEK,Watercourse,,-18.91145 128.28398 10 MILE CREEK,Watercourse,,-20.41063 134.45267 10 MILE CREEK,Watercourse,,-23.07116 149.18259 12 MILE,Homestead,,-21.84802 149.14621 12 MILE CREEK,Watercourse,,-21.99546 130.98233 12 MILE CREEK,Watercourse,

1 - did you forget to click the Regular Expression button? hmmm?
2 - When I say "space" I mean " " .... i.e. a blank area
3 - where I put "quotes" around things, it is simply to illustrate what Im trying to show you. Ignore and dont use the quotes. Copy the stuff inside the quotes....

Look at the screenshots on each step carefully. I've deliberately highlighted the bits that would be tricky so you can see the spaces Im using....

reg.JPG