Sponsored Content
Top Forums UNIX for Beginners Questions & Answers Pivoting values from column to rows Post 302995935 by Don Cragun on Saturday 15th of April 2017 05:27:15 AM
Old 04-15-2017
Quote:
Originally Posted by Booo
Can we replace the empty spaces in the column to zero. If its empty while sending as an excel format the empty column is copied by adjacent column data.
Yes. You can do that by changing 1 character in my suggestion or by adding 2 characters (4 if you add spaces around arithmetic operators) to either of our suggestions.

Can we know which of our suggestions you're using?

Can we know what fails in the suggestions that have been provided for you? (In other words, please explain in what way the suggestions you were given did not do what you requested in the requirements you stated before adding your new requirement in post #11?)

Can you try to modify the code we have supplied instead of expecting us to change our code for you every time you change your requirements? If you can't get it to work, show us what you have a tried an we'll be glad to help you finish it.
This User Gave Thanks to Don Cragun For This Post:
 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Pivoting Dynamic rows into columns

Original file we are getting ....... Item Period Amt P1 106 1000 P1 206 1500 P1 106 2000 P2 256 5800 P2 650 7500 My output should be like this Item 106 206 256 650 ............ P1 1000 1500 0 ... (1 Reply)
Discussion started by: dprakash
1 Replies

2. Shell Programming and Scripting

How to consolidate values in one column from different rows into one?

Hi Guys, Thank you all for helping me with my different queries and I continue to get better at scripting because of help from all of you! I have a file that would look something like - ID SUB ID VALUE 1 10 5 2 18 7 1 ... (1 Reply)
Discussion started by: sncoupons
1 Replies

3. UNIX for Dummies Questions & Answers

How to assign scores to rows based on column values

Hi, I'm trying to assign a score to each row which will allow me to identify which rows differ. In the example file below, I've used "," to indicate column separators (my actual file has tab separators). In this example, I'd like to identify that row 1 and row 5 are the same, and row 2 and row... (4 Replies)
Discussion started by: auburn
4 Replies

4. UNIX for Dummies Questions & Answers

count number of rows based on other column values

Could anybody help with this? I have input below ..... david,39 david,39 emelie,40 clarissa,22 bob,42 bob,42 tim,32 bob,39 david,38 emelie,47 what i want to do is count how many names there are with different ages, so output would be like this .... david,2 emelie,2 clarissa,1... (3 Replies)
Discussion started by: itsme999
3 Replies

5. Shell Programming and Scripting

join rows based on the column values

Hi, Please help me to convert the input file to a new one. input file: -------- 1231231231 3 A 4561223343 0 D 1231231231 1 A 1231231231 2 A 1231231231 4 D 7654343444 2 A 4561223343 1 D 4561223343 2 D the output should be: -------------------- 1231231231 3#1#2 A 4561223343 0 D... (3 Replies)
Discussion started by: vsachan
3 Replies

6. Shell Programming and Scripting

Transposing rows and columns (pivoting) using shell scripting

Here is the contents of an input file. A,1,2,3,4 10,aaa,bbb,ccc,ddd 11,eee,fff,ggg,hhh 12,iii,jjj,lll,mmm 13,nnn,ooo,ppp I wanted the output to be A 10 1 aaa 10 2 bbb 10 3 ccc 10 4 ddd 11 1 eee 11 2 fff 11 3 ggg 11 4 hhh ..... and so on How to do it in ksh... (9 Replies)
Discussion started by: ksatish89
9 Replies

7. Shell Programming and Scripting

Remove rows with e column values

Hi All, I have a big file with 232 columns and 9 million rows, I want to delete all rows with same column values in col3 through col232. Also the output should be sorted based on first 2 columns. Here is a reduced example with 6 columns. I want to remove rows with duplicate values in col3... (9 Replies)
Discussion started by: alpesh
9 Replies

8. Shell Programming and Scripting

Choosing rows based on column values

I have a .csv file: A,B,0.6 C,D,-0.7 D,E,0.1 A,E,0.45 D,G, -0.4 I want to select rows based on the values of the 3rd columns such that it is >=0.5 or <= -0.5 Thanks. A,B,0.6 D,G, -0.7 (1 Reply)
Discussion started by: Sanchari
1 Replies

9. Shell Programming and Scripting

Convert Column data values to rows

Hi all , I have a file with the below content Header Section employee|employee name||Job description|Job code|Unitcode|Account|geography|C1|C2|C3|C4|C5|C6|C7|C8|C9|Csource|Oct|Nov|Dec|Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep Data section ... (1 Reply)
Discussion started by: Hypesslearner
1 Replies

10. UNIX for Beginners Questions & Answers

Compare values in multiple rows in one column using awk

I would like to compare values in column 8, and grep the ones where the different is > 1, columns 1 and 2 are the key for array. Every 4 rows the records values in columns 1 and 2 changed. Then, the comparison in the column 8 need to be done for the 4 rows everytime columns 1 and 2 changed ... (4 Replies)
Discussion started by: jiam912
4 Replies
Text::Format(3pm)					User Contributed Perl Documentation					 Text::Format(3pm)

NAME
Text::Format - various subroutines to format text. SYNOPSIS
use Text::Format; my $text = Text::Format->new ( { text => [], # all columns => 72, # format, paragraphs, center leftMargin => 0, # format, paragraphs, center rightMargin => 0, # format, paragraphs, center firstIndent => 4, # format, paragraphs bodyIndent => 0, # format, paragraphs rightFill => 0, # format, paragraphs rightAlign => 0, # format, paragraphs justify => 0, # format, paragraphs extraSpace => 0, # format, paragraphs abbrevs => {}, # format, paragraphs hangingIndent => 0, # format, paragraphs hangingText => [], # format, paragraphs noBreak => 0, # format, paragraphs noBreakRegex => {}, # format, paragraphs tabstop => 8, # expand, unexpand, center } ); # these are the default values my %abbr = (foo => 1, bar => 1); $text->abbrevs(\%abbr); $text->abbrevs(); $text->abbrevs({foo => 1,bar => 1}); $text->abbrevs(qw/foo bar/); $text->text(@text); $text->columns(132); $text->tabstop(4); $text->extraSpace(1); $text->firstIndent(8); $text->bodyIndent(4); $text->config({tabstop => 4,firstIndent => 0}); $text->rightFill(0); $text->rightAlign(0); DESCRIPTION
The format routine will format under all circumstances even if the width isn't enough to contain the longest words. Text::Wrap will die under these circumstances, although I am told this is fixed. If columns is set to a small number and words are longer than that and the leading 'whitespace' than there will be a single word on each line. This will let you make a simple word list which could be indented or right aligned. There is a chance for croaking if you try to subvert the module. If you don't pass in text then the internal text is worked on, though not modfied. Text::Format is meant for more powerful text formatting than what Text::Wrap allows. I also have a module called Text::NWrap that is meant as a direct replacement for Text::Wrap. Text::NWrap requires Text::Format since it uses Text::Format->format to do the actual wrapping but gives you the interface of Text::Wrap. General setup should be explained with the below graph. columns <------------------------------------------------------------> <----------><------><---------------------------><-----------> leftMargin indent text is formatted into here rightMargin indent is firstIndent or bodyIndent depending on where we are in the paragraph. format @ARRAY || @ARRAY || [<FILEHANDLE>] || NOTHING Allows one to do some advanced formatting of text into a paragraph, with indent for first line and body set separately. Can specify total width of text, right fill with spaces or right align or justify (align to both margins), right margin and left margin, non- breaking space, two spaces at end of sentence, hanging indents (tagged paragraphs). Strips all leading and trailing whitespace before proceeding. Text is first split into words and then reassembled. If no text is passed in then the internal text in the object is formatted. paragraphs @ARRAY || @ARRAY || [<FILEHANDLE>] || NOTHING Considers each element of text as a paragraph and if the indents are the same for first line and the body then the paragraphs are separated by a single empty line otherwise they follow one under the other. If hanging indent is set then a single empty line will separate each paragraph as well. Calls format to do the actual formatting. If no text is passed in then the internal text in the object is formatted, though not changed. center @ARRAY || NOTHING Centers a list of strings in @ARRAY or internal text. Empty lines appear as, you guessed it, empty lines. Center strips all leading and trailing whitespace before proceeding. Left margin and right margin can be set. If no text is passed in then the internal text in the object is formatted. expand @ARRAY || NOTHING Expand tabs in the list of text to tabstop number of spaces in @ARRAY or internal text. Doesn't modify the internal text just passes back the modified text. If no text is passed in then the internal text in the object is formatted. unexpand @ARRAY || NOTHING Tabstop number of spaces are turned into tabs in @ARRAY or internal text. Doesn't modify the internal text just passes back the modified text. If no text is passed in then the internal text in the object is formatted. new \%HASH || NOTHING Instantiates the object. If you pass a reference to a hash, or an anonymous hash then it is used in setting attributes. config \%HASH Allows the configuration of all object attributes at once. Returns the object prior to configuration. You can use it to make a clone of your object before you change attributes. columns NUMBER || NOTHING Set width of text or retrieve width. This is total width and includes indentation and the right and left margins. tabstop NUMBER || NOTHING Set tabstop size or retrieve tabstop size, only used by expand, unexpand and center. firstIndent NUMBER || NOTHING Set or get indent for the first line of paragraph. This is the number of spaces to indent. bodyIndent NUMBER || NOTHING Set or get indent for the body of paragraph. This is the number of spaces to indent. leftMargin NUMBER || NOTHING Set or get width of left margin. This is the number of spaces used for the margin. rightMargin NUMBER || NOTHING Set or get width of right margin. This is the number of spaces used for the margin. rightFill 0 || 1 || NOTHING Set right fill or retrieve its value. The filling is done with spaces. Keep in mind that if rightAlign is also set then both rightFill and rightAlign are ignored. rightAlign 0 || 1 || NOTHING Set right align or retrieve its value. Text is aligned with the right side of the margin. Keep in mind that if rightFill is also set then both rightFill and rightAlign are ignored. justify 0 || 1 || NOTHING Set justify or retrieve its value. Text is aligned with both margins, adding extra spaces as necessary to align text with left and right margins. Keep in mind that if either of rightAlign or rightFill are set then justify is ignored, even if both are set in which case they are all ignored. text @ARRAY || NOTHING Pass in a reference to your text, or an anonymous array of text that you want the routines to manipulate. Returns the text held in the object. hangingIndent 0 || 1 || NOTHING Use hanging indents in front of a paragraph, returns current value of attribute. This is also called a tagged paragraph. hangingText @ARRAY || NOTHING The text that will be displayed in front of each paragraph, if you call format then only the first element is used, if you call paragraphs then paragraphs cycles through all of them. If you have more paragraphs than elements in your array than the remainder of the paragraphs will not have a hanging indented text. Pass a reference to your array. This is also called a tagged paragraph. noBreak 0 || 1 || NOTHING Set whether you want to use the non-breaking space feature. noBreakRegex \%HASH || NOTHING Pass in a reference to your hash that would hold the regexes on which not to break. Without any arguments, it returns the hash. eg. {'^Mrs?.$' => '^S+$','^S+$' => '^(?:S|J)r.$'} don't break names such as Mr. Jones, Mrs. Jones, Jones Jr. The breaking algorithm is simple. If there should not be a break at the current end of sentence, then a backtrack is done till there are two words on which breaking is allowed. If no two such words are found then the end of sentence is broken anyhow. If there is a single word on current line then no backtrack is done and the word is stuck on the end. This is so you can make a list of names for example. extraSpace 0 || 1 || NOTHING Add extra space after end of sentence, normally format would add 1 space after end of sentence, if this is set to 1 then 2 spaces are used. Abbreviations are not followed by two spaces. There are a few internal abbreviations and you can add your own to the object with abbrevs abbrevs \%HASH || @ARRAY || NOTHING Add to the current abbreviations, takes a reference to your hash or an array of abbreviations, if called a second time the original reference is removed and replaced by the new one. Returns the current INTERNAL abbreviations. EXAMPLE
use Text::Format; my $text = Text::Format->new; $text->rightFill(1); $text->columns(65); $text->tabstop(4); print $text->format("a line to format to an indented regular paragraph using 65 character wide display"); print $text->paragraphs("paragraph one","paragraph two"); print $text->center("hello world","nifty line 2"); print $text->expand(" hello world ","hmm, well "); print $text->unexpand(" hello world "," hmm"); $text->config({columns => 132, tabstop => 4}); $text = Text::Format->new(); print $text->format(@text); print $text->paragraphs(@text); print $text->center(@text); print $text->format([<FILEHANDLE>]); print $text->format([$fh->getlines()]); print $text->paragraphs([<FILEHANDLE>]); print $text->expand(@text); print $text->unexpand(@text); $text = Text::Format->new ({tabstop => 4,bodyIndent => 4,text => @text}); print $text->format(); print $text->paragraphs(); print $text->center(); print $text->expand(); print $text->unexpand(); print Text::Format->new({columns => 95})->format(@text); BUGS
Line length can exceed the number of specified columns if columns is set to a small number and long words plus leading whitespace exceed the specified column length. Actually I see this as a feature since it can be used to make up a nice word list. AUTHOR
Gabor Egressy gabor@vmunix.com Copyright (c) 1998 Gabor Egressy. All rights reserved. All wrongs reversed. This program is free software; you can redistribute and/or modify it under the same terms as Perl itself. Adopted and modified by Shlomi Fish, <http://www.shlomifish.org/> - all rights disclaimed. ACKNOWLEDGMENTS
Tom Phoenix Found a bug with code for two spaces at the end of the sentence and provided a code fragment for a better solution. Also some preliminary suggestions on the design. Brad Appleton Suggestion and explanation of hanging indents, suggestion for non-breaking whitespace, general suggestions with regard to interface design. Byron Brummer Suggestion for better interface design and object design, code for better implementation of getting abbreviations. H. Merijn Brand Suggestion for a justify feature and original code for doing the justification. I changed the code to take into account the extra space at end of sentence feature. TODO
perl v5.14.2 2012-05-31 Text::Format(3pm)
All times are GMT -4. The time now is 07:42 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy