Need help with tab delimited file in unix


 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers Need help with tab delimited file in unix
# 1  
Old 02-13-2014
Need help with tab delimited file in unix

Hi,

I need urgent help with a tab delimited file I am working on.
This is the file :
Code:
TTTT|YYYYYYY|jargon-journal|MP0000000UID||"j1, j2, j3"

I need th following output:
Code:
TTTT|YYYYYYY|jargon-journal|MP0000000UID||ji
TTTT|YYYYYYY|jargon-journal|MP0000000UID||j2
TTTT|YYYYYYY|jargon-journal|MP0000000UID||j3

Please suggest

Thanks,
Ray

Moderator's Comments:
Mod Comment Please use code tags, not font and color styles, to enclose and highlight machine text.

Last edited by Corona688; 02-13-2014 at 03:19 PM.. Reason: Code tags
# 2  
Old 02-13-2014
That is pipe-delimited, a tab delimited file would use tabs...

Code:
awk -F"|" -v OFS="|" '{ M=split($6, A, ",");
        for(M=0; M<N; M++) { gsub(/[ ,"]/, "", A[M]);
                $6=A[M] ; print
        } }' inputfile

# 3  
Old 02-13-2014
Perl approach:
Code:
perl -F"\|" -alne '$F[5]=~s/"//g;$,="|";map {$F[5]=$_;print @F} split /, /, $F[5]' file

This User Gave Thanks to bartus11 For This Post:
# 4  
Old 02-13-2014
Quote:
Originally Posted by Corona688
That is pipe-delimited, a tab delimited file would use tabs...

Code:
awk -F"|" -v OFS="|" '{ M=split($6, A, ",");
        for(M=0; M<N; M++) { gsub(/[ ,"]/, "", A[M]);
                $6=A[M] ; print
        } }' inputfile

throws an error when I run it in my unix terminal..
bash-3.00$ ./shell.sh
awk: syntax error near line 1
awk: bailing out near line 1

---------- Post updated at 02:42 PM ---------- Previous update was at 02:39 PM ----------

Quote:
Originally Posted by bartus11
Perl approach:
Code:
perl -F"\|" -alne '$F[5]=~s/"//g;$,="|";map {$F[5]=$_;print @F} split /, /, $F[5]' file

This works beautifully, but is there any way we can we do it using awk?
# 5  
Old 02-13-2014
OR

Code:
$ cat file
T|YYYYYYY|jargon-journal|MP0000000UID||"j1, j2, j3"

Code:
$ awk '{for(i=2;i<=NF;i++)if($i)print $1 $i}' FS='"|,[[:space:]]+' file
T|YYYYYYY|jargon-journal|MP0000000UID||j1
T|YYYYYYY|jargon-journal|MP0000000UID||j2
T|YYYYYYY|jargon-journal|MP0000000UID||j3

---------- Post updated at 02:16 AM ---------- Previous update was at 02:13 AM ----------

If solaris use nawk

---------- Post updated at 02:21 AM ---------- Previous update was at 02:16 AM ----------

@ Corona I think you missed something it was suppose to beN=split($6, A, ",")
# 6  
Old 02-13-2014
Quote:
Originally Posted by Akshay Hegde
OR

Code:
$ cat file
T|YYYYYYY|jargon-journal|MP0000000UID||"j1, j2, j3"

Code:
$ awk '{for(i=2;i<=NF;i++)if($i)print $1 $i}' FS='"|,[[:space:]]+' file
T|YYYYYYY|jargon-journal|MP0000000UID||j1
T|YYYYYYY|jargon-journal|MP0000000UID||j2
T|YYYYYYY|jargon-journal|MP0000000UID||j3

---------- Post updated at 02:16 AM ---------- Previous update was at 02:13 AM ----------

If solaris use nawk

---------- Post updated at 02:21 AM ---------- Previous update was at 02:16 AM ----------

@ Corona I think you missed something it was suppose to beN=split($6, A, ",")
@Akshay: Your script works in Linux, but it does not work in Solaris even when I replace awk with nawk

Code:
bash-3.00$ awk '{for(i=2;i<=NF;i++)if($i)print $1 $i}' FS='"|,[[:space:]]+' test
TTTT|YYYYYYY|jargon-journal|MP0000000UID||j1, j2, j3
bash-3.00$ nawk '{for(i=2;i<=NF;i++)if($i)print $1 $i}' FS='"|,[[:space:]]+' test
TTTT|YYYYYYY|jargon-journal|MP0000000UID||j1, j2, j3
bash-3.00$





@Corona
Code:
bash-3.00$ cat shell.sh
#!/bin/bash
nawk -F"|" -v OFS="|" '{N=split($6, A, ",");
        for(M=0; M<N; M++) { gsub(/[ ,"]/, "", A[M]);
                $6=A[M] ; print
        } }' test > testing

bash-3.00$ cat testing
TTTT|YYYYYYY|jargon-journal|MP0000000UID||
TTTT|YYYYYYY|jargon-journal|MP0000000UID||j1
TTTT|YYYYYYY|jargon-journal|MP0000000UID||j2

Thanks to Both of you for helping..

Moderator's Comments:
Mod Comment Code tags for code, PLEASE!
# 7  
Old 02-13-2014
Change for(M=0; M<N; M++) into for(M=1; M<=N; M++)
This User Gave Thanks to Corona688 For This Post:
 
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Replace a column in tab delimited file with column in other tab delimited file,based on match

Hello Everyone.. I want to replace the retail col from FileI with cstp1 col from FileP if the strpno matches in both files FileP.txt ... (2 Replies)
Discussion started by: YogeshG
2 Replies

2. UNIX for Beginners Questions & Answers

UNIX - 2 tab delimited files, conditional column extraction

Please know that I am very new to unix and trying to learn 'on the job'. I'm only manipulating large tab-delimited files (millions of rows), but I'm stuck and don't know how to proceed with the following. Hoping for some friendly advice :) I have 2 tab-delimited files - with differing column &... (10 Replies)
Discussion started by: GTed
10 Replies

3. UNIX for Dummies Questions & Answers

Need to convert a pipe delimited text file to tab delimited

Hi, I have a rquirement in unix as below . I have a text file with me seperated by | symbol and i need to generate a excel file through unix commands/script so that each value will go to each column. ex: Input Text file: 1|A|apple 2|B|bottle excel file to be generated as output as... (9 Replies)
Discussion started by: raja kakitapall
9 Replies

4. Shell Programming and Scripting

How to make tab delimited file to space delimited?

Hi How to make tab delimited file to space delimited? in put file: ABC kgy jkh ghj ash kjl o/p file: ABC kgy jkh ghj ash kjl Use code tags, thanks. (1 Reply)
Discussion started by: jagdishrout
1 Replies

5. Shell Programming and Scripting

Help with converting Pipe delimited file to Tab Delimited

I have a file which was pipe delimited, I need to make it tab delimited. I tried with sed but no use cat file | sed 's/|//t/g' The above command substituted "/t" not tab in the place of pipe. Sample file: abc|123|2012-01-30|2012-04-28|xyz have to convert to: abc 123... (6 Replies)
Discussion started by: karumudi7
6 Replies

6. UNIX for Dummies Questions & Answers

tab delimited file that is not tab delimited.

Hi Forum I have a tab delimited file that opens well in Openoffice calc (excel). But when I perform any operation in command line, it reads the file incorrectly. When I 'save As' the same file in office as tab delimited then it works fine. The file that I think is tab delimited is actually... (8 Replies)
Discussion started by: imlearning
8 Replies

7. UNIX for Advanced & Expert Users

merge two tab delimited file with exact same number of rows in unix/linux

Hi I have two tab delimited file with different number of columns but same number of rows. I need to combine these two files in such a way that row 1 in file 2 comes adjacent to row 1 in file 1. For example: The content of file1: field1 field2 field3 a1 a2 a3 b1 b2 b3... (2 Replies)
Discussion started by: mary271
2 Replies

8. Shell Programming and Scripting

Retrieving values from tab-delimited file in unix script

Hi I am trying to retrieve values from a tab-delimited file.I am using while read record value=`echo $record | cut -f12` done Where 12 is the column no i want retieve and record is one line of the file. But it is returning the full record. Plz help (4 Replies)
Discussion started by: akashtcs
4 Replies

9. UNIX for Dummies Questions & Answers

Converting Space delimited file to Tab delimited file

Hi all, I have a file with single white space delimited values, I want to convert them to a tab delimited file. I tried sed, tr ... but nothing is working. Thanks, Rajeevan D (16 Replies)
Discussion started by: jeevs81
16 Replies

10. Shell Programming and Scripting

Converting Tab delimited file to Comma delimited file in Unix

Hi, Can anyone let me know on how to convert a Tab delimited file to Comma delimited file in Unix Thanks!! (22 Replies)
Discussion started by: charan81
22 Replies
Login or Register to Ask a Question