Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Converting Column values to comma delimted single Row

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Converting Column values to comma delimted single Row
# 1  
Old 01-22-2009
Converting Column values to comma delimted single Row
I have a requirement in which i have to read a file which has multiple columns seperated by a pipe "|" from this i have to read each column values seperately and create a comma seperated row for the column and write to another file.

eg:

Input file:
ColA ColB
1 2
2 x
3 y
4 c
5 q

from this i have to create two filles, each having data as

file_colA:
1,2,3,4,5

file_colB:
2,x,y,c,q


i have created shell script using a for loop, which may take long time when i implement it in prod.

can any one suggest an approach using awk or sed?


thanks
# 2  
Old 01-22-2009

Code:
file=/path/to/inputfile
numcols=2 ## adjust to taste
n=1
  IFS='
'
while [ $n -le $numcols ]
do
  {
    printf "%s," $( cut -d '|' -f "$n" "$file" )
    echo
  } > file_col$n
  n=$(( $n + 1 ))
done

# 3  
Old 01-22-2009
it doesn't look like your 'input' file has '|' separated fields.
does the 'ColA' and 'ColB' headers actually exist in the 'input' file?
# 4  
Old 01-22-2009
Quote:
Originally Posted by vgersh99
it doesn't look like your 'input' file has '|' separated fields.
does the 'ColA' and 'ColB' headers actually exist in the 'input' file?


sorry for the wrong file
Input file:
ColA ColB
1|2
2|x
3|y
4|c
5|q

and it doesnt have column header.

thanks johnson for the approach, but it will try to read the file line by line, which i want to avoid. since in production i may have millions of records and this may cause a bottleneck.
# 5  
Old 01-22-2009
Quote:
Originally Posted by nvuradi
thanks johnson for the approach, but it will try to read the file line by line, which i want to avoid. since in production i may have millions of records and this may cause a bottleneck.
how else would you transpose a matrix?
# 6  
Old 01-23-2009
i have single column which is starting with same string(many number of rows)
i have to convert each into a single row.how can i do that?

laknar
std
mes
23
55
laknar
isd
phone no
address
amount
99

I have to convert above like below.

laknar|std|mes|23|55
laknar|isd|phone no|address|amount|99
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Bring values in the second column into single line (comma sep) for uniq value in the first column

I want to bring values in the second column into single line for uniq value in the first column. My input jvm01, Web 2.0 Feature Pack Library jvm01, IBM WebSphere JAX-RS jvm01, Custom01 Shared Library jvm02, Web 2.0 Feature Pack Library jvm02, IBM WebSphere JAX-RS jvm03, Web 2.0 Feature... (10 Replies)
Discussion started by: kchinnam
10 Replies

2. Shell Programming and Scripting

Converting a single row to multiple rows

Hi, I want to convert a single row values to multiple rows, but the no. of rows are not fixed. For example, I have a row as below abc-def-lmn-mno-xyz out put should be get abc get def get lmn get xyz (4 Replies)
Discussion started by: Suneel Mekala
4 Replies

3. Shell Programming and Scripting

Converting Single Column into Multiple rows, but with strings to specific tab column

Dear fellows, I need your help. I'm trying to write a script to convert a single column into multiple rows. But it need to recognize the beginning of the string and set it to its specific Column number. Each Line (loop) begins with digit (RANGE). At this moment it's kind of working, but it... (6 Replies)
Discussion started by: AK47
6 Replies

4. Shell Programming and Scripting

Converting odd values to even values(or vice-versa) located in a column

Hello All, I have a below data in a .csv file where all rows where col1 is A, col2 is odd numbers, similarly even numbers for all rows where col1 is B. Note that my data has some other columns(not shown here) too (around 100) after col2. Tool,Data A,1 A,3 A,5 .... so on B,2 B,4 .... ... (4 Replies)
Discussion started by: ks_reddy
4 Replies

5. Shell Programming and Scripting

Subtracting each row from the first row in a single column file using awk

Hi Friends, I have a single column data like below. 1 2 3 4 5 I need the output like below. 0 1 2 3 4 where each row (including first row) subtracting from first row and the result should print below like the way shown in output file. Thanks Sid (11 Replies)
Discussion started by: ks_reddy
11 Replies

6. Shell Programming and Scripting

converting column to row

Hi everyone.. I have a list of values in a file... 1.2345e-1 2.282828e+ 3.2341e-1 1.1223445e-1 I am interested in converting this column to a row.. 1.2345e-1 2.282828e+ 3.2341e-1 1.1223445e-1 can anyone pls help?? I am a liunx newbie.. Thanks.. (7 Replies)
Discussion started by: kjha
7 Replies

7. Shell Programming and Scripting

Converting Multiple rows to Single Row using unix commands

Can somebody help me in solving this.. Input data is like 0 A 1 B 2 C 3 D 0 A1 1 B1 2 C1 3 D1 0 A2 1 B2 2 C2 3 D2 Output should be like A B C D A1 B1 C1 D1 A2 B2 C2 D2 (7 Replies)
Discussion started by: Mahantesh Patil
7 Replies

8. UNIX for Advanced & Expert Users

Converting rows to a single row

Hi all I have a file as below : Development System User Production i want to convert the file to below format: "Development","System","User","Production" Is it possible with UNIX ? if so can you please give me some direction on it ? Thanks, Satya Use code tags please, ty. (10 Replies)
Discussion started by: satyaranjon
10 Replies

9. Shell Programming and Scripting

Converting values in a ROW to COLUMN

Hi All, I needd to convert values in a row to a column. eg: Input is as: value1,value2,value3,value4,.........,value N Required Output: Value1 Value2 Value3 . . . Value N Please help.... (3 Replies)
Discussion started by: sambaman
3 Replies

10. Shell Programming and Scripting

Concatenating column values with unique id into single row

Hi, I have a table in Db2 with data say id_1 phase1 id_1 phase2 id_1 phase3 id_2 phase1 id_2 phase2 I need to concatenate the values like id_1 phase1,phase2,phase3 id_2 phase1,phase2 I tried recursive query but in vain as the length of string to be concatenated in quite long. ... (17 Replies)
Discussion started by: jsaravana
17 Replies
Login or Register to Ask a Question