Ascii data file manipulation question

Factual Questions
1

My primary software app exports polygons to ascii files in the proprietor’s own format that their rep says they don’t have a filter for, and acknowledges that it’s a problem. The data are pairs of X-Y coordinates. The (abbreviated) format is displayed in a sample file below. As opposed to the common columnar format for many ascii data files, this software outputs paired values linearly for so many samples, then does a hard return and a line feed and repeats. In the dummy sample it goes linearly for three samples; IRL it’s five. The value on the first line indicates how many samples there are.

None of my other software can deal with this format.

I’m not a programmer, and I’m sure there’s an answer to this in that arena; the only tools I bring to the fight are Word (or Wordpad) and Excel.

The sample:


11
3678900 360000  3678910 360100  3678920 360200
3678930 360300  3678940 360400  3678950 360500
3678960 360600  3678950 360650  3678940 360500
3678930 360450  3678900 360000

In Word, I can search for the two spaces between sample pairs, and manually replace them with a carriage return. Fine for something like the sample, but impractical for a data file with thousands of lines of samples. What I thought might work there would be if I could figure out how to enter a hard return (perhaps through an escape sequence?) as the Replace with value when it finds a double space. So far, I haven’t made that work.

With Excel, I’ve been able to get it to generate a column of values (A2, C2, E2, A3, C3, E3, etc.) with a drag, but it needs, (in this example it’s done in column D) the =INDIRECT($D$7) function to do it, and that will not, at my level of understanding, pick up the series repitition.

So, with Word or Excel, how would I best convert the above file format to a sequential two column ascii file?

2

It sounds like a Word Macro would do what you want. Here’s a tutorial. Basically, you can just do the find-replace thing, set it to a macro, and then automate it.

3

If this is a one shot deal, post it or email, and I’ll convert it for you.

4

Do have Access? You didn’t say you did but maybe you do.

You can do it in Access:

  1. Load the whole file into a table using fixed width delimiters.
  2. Build an make table query that puts the first two columns in a table.
  3. Build and append query that appends the 3rd and 4th row to the table.
  4. Build and append query that appends the 5rd and 6th row to the table.

That gives you a table with two columns and all of the data.

Done.

5

I have done a similar search and replace in word, around 10,000 lines, and it worked fine. I don’t think a macro is required, cos it’s so easy to do with search and replace…

a

6

Search and Replace in Word should do it. You can replace the double space with a RETURN pretty easily.

Failing that, round up a computer geek and pay them for two hours work to write a conversion program. Just make sure you find one who really understands what you want (as opposed to trying to do what they find interesting).

7

As others have said S&R in word does this for you. On my copy of word it’s Edit/Replace then under the More button is bunch of stuff including a Special button which allows you to find or insert various things, including paragraph marks and manual line feeds one of which will end up with the format you need.

A macro would work just as well. If you’re going to run into this sort of issue a lot you might want to look at getting some sort of text-manipulation software and learning the basics.

SD

8

Unbelievable. Your primary software vendor doesn’t have the software development to convert this? Hmmm…

Do you have a Java Runtime on your machine? To find out, just go out to a command prompt and type java. If you have it (and your path is pointing to it), you should get back a bunch of options you can use. If you do, and you are willing, this would take me just a few minutes to spit something out. Trivial, really.

If you’re willing, type “java -version” so I can code to the right version of java you have, and let me know.

9

Sheesh! I guess I should’ve poked around a little more before asking, but I rarely use Word.

Anyway, SpaceDog’s suggestion got me to where I needed to be, right quickly I might add.

Thanks, y’all.