Login or Register to Ask a Question and Join Our Community


UNIX for Beginners Questions & Answers

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

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums UNIX for Beginners Questions & Answers Replace a column in tab delimited file with column in other tab delimited file,based on match
# 1  
Old 02-06-2019
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

Code:
rectyp	stckno	strpno	prcname	prccode	pfxcode	startdt	enddt	fcpgno	cstq1	cstp1	cstp3	cstp4	vndrop	leadtime	autoord	prjctnum	future1	pcstq1	pcstc1	pcstq2	pcstc2	pcstq3	pcstc3	future2	saleq1	salep1	saleq2	salep2	saleq3	salep3	shiplt	catlist2
P	AAG122-200	AAG122200	GL Catalog Consumer Prices-SC	LB		4012016	12312016	438	1	1372	1372	1372		1	1035	1	1035	1	1035		1	1769	1	1769	1	1769		1769	EA	DNC	N	Y
P	AAG122-905	AAG122905	GL Catalog Consumer Prices-SC	LB		4012016	12312016	438	1	1542	1542	1542		1	1163	1	1163	1	1163		1	1979	1	1979	1	1979		1979	EA	DNC	N	Y

FileI.txt

Code:
rectyp	stckno	strpno	pdesc	pstat	nitem	suom	ccpgno	flpgno	fcpgno	unused1	unused2	pkgqty1	pkguom1	pkduom1	pkgqty2	pkguom2	pkduom2	pkgqty3	pkguom3	pkduom3	weight	height	length	width	prclcd	country	rta	recycle	upsok	brkitm	retail	ruom	rpack	hazmat	subitems	oitem	catlist	catuom	minvnd	custom	dtdgoods	qsuom	nrtnable	alwaysnet	special
I	AAG122-200	AAG122200	PLANNER,WK/MTH,VIENNA,8.5X5	C		EA	438					3	EA	PK	4	PK	CT	0			50	850	60	540	ECAB	CN	N	Y	Y	N	1859	EA	1	N			1859	EA	N	N	Y	1	Y	N	N
I	AAG122-905	AAG122905	PLANNER,WK/MTH,VIENNA,11X9	C		EA	438					3	EA	PK	4	PK	CT	0			114	1110	60	960	ECAB	CN	N	Y	Y	N	2389	EA	1	N			2389	EA	N	N	Y	1	Y	N	N



Expected Output:

Code:
rectyp	stckno	strpno	prcname	prccode	pfxcode	startdt	enddt	fcpgno	cstq1	retail	cstp3	cstp4	vndrop	leadtime	autoord	prjctnum	future1	pcstq1	pcstc1	pcstq2	pcstc2	pcstq3	pcstc3	future2	saleq1	salep1	saleq2	salep2	saleq3	salep3	shiplt	catlist2	catuom2	prcid	firm	net
P	AAG122-200	AAG122200	GL Catalog Consumer Prices-SC	LB		4012016	12312016	438	1	1859	1372	1372		1	1035	1	1035	1	1035		1	1769	1	1769	1	1769		1769	EA	DNC	N	Y				
P	AAG122-905	AAG122905	GL Catalog Consumer Prices-SC	LB		4012016	12312016	438	1	2389	1542	1542		1	1163	1	1163	1	1163		1	1979	1	1979	1	1979		1979	EA	DNC	N	Y	

1372--replaced with 1859
1542--replaced with 2389

Code I have been trying:

Code:
 awk  'NR==FNR{A[$3]=$32; next} $3 in A{$11=A[$3]}1' FS='\t' FileI.txt FileP.txt > output2.txt

Output for above code:

Code:
rectyp stckno strpno prcname prccode pfxcode startdt enddt fcpgno cstq1 retail cstp3 cstp4 vndrop leadtime autoord prjctnum future1 pcstq1 pcstc1 pcstq2 pcstc2 pcstq3 pcstc3 future2 saleq1 salep1 saleq2 salep2 saleq3 salep3 shiplt catlist2 catuom2 prcid firm net
P AAG122-200 AAG122200 GL Catalog Consumer Prices-SC LB  4012016 12312016 438 1 1859 1372 1372  1 1035 1 1035 1 1035  1 1769 1 1769 1 1769  1769 EA DNC N Y    
P AAG122-905 AAG122905 GL Catalog Consumer Prices-SC LB  4012016 12312016 438 1 2389  15421542  1 1163 1 1163 1 1163  1 1979 1 1979 1 1979  1979 EA DNC N Y

Above output looks correct, but the tabs are not retained anymore,when I open output2.txt in excel.Output file is not tab delimited file.

I'm a newbie in Linux. Any help will be appreciated.!
Last edited by YogeshG; 02-06-2019 at 08:37 AM..
# 2  
Old 02-06-2019
Add variable OFS='\t'

--- Post updated at 11:12 ---

Conveniently view output
awk ... | cat -vet
This User Gave Thanks to nezabudka For This Post:
YogeshG
# 3  
Old 02-06-2019
Quote:
Originally Posted by nezabudka
Add variable OFS='\t'

--- Post updated at 11:12 ---

Conveniently view output
awk ... | cat -vet
Thanks nezabudka. It worked.!! Smilie
This User Gave Thanks to YogeshG For This Post:
Neo
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Delete an entire column from a tab delimited file

Hi, Can anyone please tell me about how we can delete an entire column from a tab delimited file? Mu input_file.txt looks like this: And I want the output as: I used the below code nawk -v d="1" 'BEGIN{FS=OFS="\t"}{$d=""}{print}' input_file.txtBut in the output, the first column is... (5 Replies)
Discussion started by: sampoorna
5 Replies

2. Shell Programming and Scripting

Convert a 3 column tab delimited file to a matrix

Hi all, I have a 3 columns input file like this: CPLX9PC-4943 CPLX9PC-4943 1 CPLX9PC-4943 CpxID123 0 CPLX9PC-4943 CpxID126 0 CPLX9PC-4943 CPLX9PC-5763 0.5 CPLX9PC-4943 CpxID13 0 CPLX9PC-4943 CPLX9PC-6163 0 CPLX9PC-4943 CPLX9PC-6164 0.04... (7 Replies)
Discussion started by: AshwaniSharma09
7 Replies

3. UNIX for Dummies Questions & Answers

add (append) a column in a tab delimited file

I have a file having the following entries: test1 test2 test3 11 22 33 22 44 66 99 99 44 --- I want to add a column so that the above file becomes: test1 test2 test3 notest 11 22 33 * 22 44 66 * 99 99 44 * --- Thanks (6 Replies)
Discussion started by: mary271
6 Replies

4. Shell Programming and Scripting

Extract second column tab delimited file

I have a file which looks like this: 73450 articles and news developmental psychology 2006-03-30 16:22:40 1 http://www.usnews.com 73450 articles and news developmental psychology 2006-03-30 16:22:40 2 http://www.apa.org 73450 articles and news developmental psychology 2006-03-30... (1 Reply)
Discussion started by: shoaibjameel123
1 Replies

5. UNIX for Dummies Questions & Answers

Using awk to log transform a column in a tab-delimited text file?

How do I use awk to log transform the fifth column of a tab-delimited text file? Thanks! (1 Reply)
Discussion started by: evelibertine
1 Replies

6. Shell Programming and Scripting

Using sed on 1st column of tab delimited file

Hi all, I'm new to Unix and work primarily in bioinformatics. I am in need of a script which will allow me to replace "1" with "chr1" in only the first column of a file which looks like such: 1 10327 rs112750067 T C . PASS ASP;RSPOS=10327;... (4 Replies)
Discussion started by: Hkins552
4 Replies

7. UNIX for Dummies Questions & Answers

Add a new column to a tab delimited text file

I want to add a new column to a tab delimited text file. It will be the first column and it will just be 1's. How do I go about doing that? Thanks! (1 Reply)
Discussion started by: evelibertine
1 Replies

8. Shell Programming and Scripting

Delete first column in tab-delimited text-file

I have a large text-file with tab-delimited genetic data that looks like: KSC112 KSC234 0 0 1 1 A G C T I simply wan to delete the first column, but since the file has 600 000 columns, it is not possible with awk (seems to be limited at 32k columns). Does anyone have an idea how to do this? (2 Replies)
Discussion started by: andmal
2 Replies

9. Shell Programming and Scripting

Delete parts of a string of character in one given column of a tab delimited file

I would like to remove characters from column 7 so that from an input file looking like this: >HWI-EAS422_12:4:1:69:89 GGTTTAAATATTGCACAAAAGGTATAGAGCGT U0 1 0 0 ref_chr8.fa 6527777 F DD I get something like that in an output file: ... (13 Replies)
Discussion started by: matlavmac
13 Replies

10. UNIX for Dummies Questions & Answers

Trim String in 3rd Column in Tab Delimited File...SED/PERL/AWK?

Hey Everybody, I am having much trouble figuring this out, as I am not really a programmer..:mad: Datafile.txt Column0 Column1 Column2 ABC DEF xxxGHI I am running using WGET on a cronjob to grab a datafile, but I need to cut the first three characters from... (6 Replies)
Discussion started by: rickdini
6 Replies
Login or Register to Ask a Question