Convert one line file into a file with three columns

upkar · 03-22-2016, 05:55 AM

I have file with input like below in a single line.

Albany, N.Y. Albuquerque, N.M. Anchorage, Alaska Asheville, N.C. Atlanta, Ga. Atlantic City, N.J. Austin, Texas Baltimore, Md. Baton Rouge, La. Billings, Mont. Birmingham, Ala. Bismarck, N.D. Boise, Idaho Boston, Mass. Bridgeport, Conn.

I need an output with only three fields in the line. Please suggest.

Required Output

Albany, N.Y. Albuquerque, N.M. Anchorage, Alaska
Asheville, N.C. Atlanta, Ga. Atlantic City, N.J.
Austin, Texas Baltimore, Md. Baton Rouge, La.
Billings, Mont. Birmingham, Ala. Bismarck, N.D.
Boise, Idaho Boston, Mass. Bridgeport, Conn.

wpeckham · 03-22-2016, 06:04 AM

So, how are you approaching this? What have you tried?
If you are planning to use a shell script, what shell do you use?
What is special about three columns, why not one?
What are your restrictions?
We need to know your boundary conditions and assumptions.

upkar · 03-22-2016, 06:08 AM

Tanks for prompt reply. I tried using awk but the output is not correct. This format is required for one of our report.

hydrurga · 03-22-2016, 06:34 AM

Quote:

Originally Posted by upkar

Tanks for prompt reply. I tried using awk but the output is not correct. This format is required for one of our report.

Can you paste your awk command code here please?

BW-userx · 03-22-2016, 08:38 AM

yeah what @wpeckham said.

with no "I have tired this and this and this". Showing your work too, and the "but it does not work please help" out of you. Just the teachers assignment for others to do for you? hummm perhaps an ethics class too is in order.

A.Thyssen · 03-23-2016, 02:07 AM

Input is all one line... Okay, first make each ',' entry a seperate line, then join every 3 lines to one line

Code:

  sed 's/, */,\n/g' input.txt | sed 'N;N;N; s/\n//g' > output.txt

PS: I originally tried to use "join -t\ - - -" for the second command, which would have worked years ago but now ignores the second and later '-' to read stdin. Arrgghhhh....

wpeckham · 03-23-2016, 05:49 AM

Quote:

Originally Posted by A.Thyssen

Input is all one line... Okay, first make each ',' entry a seperate line, then join every 3 lines to one line

Code:

  sed 's/, */,\n/g' input.txt | sed 'N;N;N; s/\n//g' > output.txt

PS: I originally tried to use "join -t\ - - -" for the second command, which would have worked years ago but now ignores the second and later '-' to read stdin. Arrgghhhh....

No, you are breaking on comma. Look again at the source data. You want to break on space UNLESS there is a comma.
I could solve this with pure bash (no awk) but it would hardly be elegant.
Because the source data seems explicitly formatted to support it, you could work with simple string locations (break into sets of six) and have it work. Not very general, and it would fail like crazy on something like "Sault Saint Marie, Michigan"!

A.Thyssen · 03-23-2016, 07:03 PM

Quote:

Originally Posted by wpeckham

No, you are breaking on comma. Look again at the source data. You want to break on space UNLESS there is a comma.

Which comes back to properly defining the problem before starting.

wpeckham · 03-23-2016, 08:14 PM

Quote:

Originally Posted by A.Thyssen

Which comes back to properly defining the problem before starting.

Exactly. If this IS homework (and that is what it look like) it is poorly designed to reflect real world problems, but well designed to make the student THINK about the data and deal with the existing structure.

The very structure presented gives me several ideas of non-general ways to address the question, but I am still waiting to see what the OP came up with that did not work.

BW-userx · 03-23-2016, 08:39 PM

Quote:

Originally Posted by wpeckham

Exactly. If this IS homework (and that is what it look like) it is poorly designed to reflect real world problems, but well designed to make the student THINK about the data and deal with the existing structure.

The very structure presented gives me several ideas of non-general ways to address the question, but I am still waiting to see what the OP came up with that did not work.

Code:

1                       2                          3
city       state  |     city          state        city           state 
Albany,   | N.Y.  |   Albuquerque, |   N.M.   |    Anchorage,     | Alaska 
Asheville,| N.C.  |   Atlanta,     |   Ga.    |    Atlantic City, |  N.J.
Austin,   | Texas |   Baltimore,   |   Md.    |    Baton Rouge,   | La.
Billings, | Mont. |   Birmingham,  |   Ala.   |    Bismarck,      | N.D.
Boise,    | Idaho |   Boston,      |   Mass.  |    Bridgeport,    | Conn.

that's be the only logical ending format but it actually equals 6 if you split the city state .. strange assignment

hydrurga · 03-26-2016, 05:41 PM

Just in case anyone in the future sees this thread, here's one (probably inefficient) way of achieving the result that the OP wanted:

Code:

sed 's/\([a-zA-Z ]\+, [a-zA-Z.]\+ \)/\1\n/g' aaaa | awk '{getline b;getline c;printf("%s%s%s\n",$0,b,c)}'

allend · 03-26-2016, 10:21 PM

A purely awk solution.

Code:

awk '{x=y=1; while (x<=NF) {printf("%s ",$x); if (y==4) {printf("\n"); y=1}; if (index($x,",")) y+=1; x+=1}}' input.txt