# Ascii data file manipulation question
**URL:** https://boards.straightdope.com/t/ascii-data-file-manipulation-question/290139
**Category:** Factual Questions
**Created:** [February 15, 2005, 3:20am UTC](https://boards.straightdope.com/t/ascii-data-file-manipulation-question/290139 "2005-02-15T03:20:29Z")
**Posts on this page:** 9
**Page:** 1
### Author:  [@Ringo](https://boards.straightdope.com/u/Ringo)
#### Post date: [February 15, 2005, 3:20am UTC](https://boards.straightdope.com/t/ascii-data-file-manipulation-question/290139/1 "2005-02-15T03:20:29Z")
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:
```auto
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?
---
### Author:  [@iamthewalrus\_3](https://boards.straightdope.com/u/iamthewalrus_3)
#### Post date: [February 15, 2005, 3:38am UTC](https://boards.straightdope.com/t/ascii-data-file-manipulation-question/290139/2 "2005-02-15T03:38:43Z")
It sounds like a Word Macro would do what you want. Here’s a [tutorial](http://wordprocessing.about.com/od/workingwithmacro1/l/blmacrointro.htm). Basically, you can just do the find-replace thing, set it to a macro, and then automate it.
---
### Author:  [@Bill\_H](https://boards.straightdope.com/u/Bill_H)
#### Post date: [February 15, 2005, 4:29am UTC](https://boards.straightdope.com/t/ascii-data-file-manipulation-question/290139/3 "2005-02-15T04:29:04Z")
If this is a one shot deal, post it or email, and I’ll convert it for you.
---
### Author:  [@Shagnasty](https://boards.straightdope.com/u/Shagnasty)
#### Post date: [February 15, 2005, 4:56am UTC](https://boards.straightdope.com/t/ascii-data-file-manipulation-question/290139/4 "2005-02-15T04:56:07Z")
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.
---
### Author:  [@abby](https://boards.straightdope.com/u/abby)
#### Post date: [February 15, 2005, 5:31am UTC](https://boards.straightdope.com/t/ascii-data-file-manipulation-question/290139/5 "2005-02-15T05:31:28Z")
> [@Ringo](#):
>
> 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.
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
---
### Author:  [@Quercus](https://boards.straightdope.com/u/Quercus)
#### Post date: [February 15, 2005, 2:42pm UTC](https://boards.straightdope.com/t/ascii-data-file-manipulation-question/290139/6 "2005-02-15T14:42:28Z")
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).
---
### Author:  [@SpaceDog](https://boards.straightdope.com/u/SpaceDog)
#### Post date: [February 15, 2005, 2:52pm UTC](https://boards.straightdope.com/t/ascii-data-file-manipulation-question/290139/7 "2005-02-15T14:52:23Z")
> [@Ringo](#):
>
> 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.
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
---
### Author:  [@Level3Navigator](https://boards.straightdope.com/u/Level3Navigator)
#### Post date: [February 15, 2005, 10:04pm UTC](https://boards.straightdope.com/t/ascii-data-file-manipulation-question/290139/8 "2005-02-15T22:04:13Z")
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.
---
### Author:  [@Ringo](https://boards.straightdope.com/u/Ringo)
#### Post date: [February 16, 2005, 3:49am UTC](https://boards.straightdope.com/t/ascii-data-file-manipulation-question/290139/9 "2005-02-16T03:49:10Z")
_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.