Need to merge multiple text files vertically and place comma between fields


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Need to merge multiple text files vertically and place comma between fields
# 1  
Old 06-13-2014
Need to merge multiple text files vertically and place comma between fields

Hello expert friends,

I'm writing a script to capture stats using sar and stuck up at report generation.

I have around 10 files in a directory and need to merge them all vertically based on the time value of first column (output file should have only one time value) and insert comma after every field. For ex:

Code:
# cat File01

17:40:01       569056   2538672     81.69    436568   1771600   6213560        64      0.00         0
17:45:01       570420   2537308     81.65    436568   1771600   6213560        64      0.00         0
17:50:01       569676   2538052     81.67    436568   1771624   6213560        64      0.00         0

Code:
#cat File02

17:40:01            0       130      0.00      0.00      0.00
17:45:01            0       126      0.00      0.00      0.00
17:50:01            0       129      0.06      0.03      0.01

Code:
#cat File03
17:40:01            180
17:45:01            190
17:50:01            200

Then Output file should be:
Code:
17:40:01,       569056,   2538672,     81.69,    436568,   1771600,   6213560,        64,      0.00,         0,   0,       130,      0.00,      0.00,      0.00,   180
17:45:01,       570420,   2537308,     81.65,    436568,   1771600,   6213560,        64,      0.00,         0,   0,       126,      0.00,      0.00,      0.00,   190
17:50:01,       569676,   2538052,     81.67,    436568,   1771624,   6213560,        64,      0.00,         0,   0,       129,      0.06,      0.03,      0.01,   200

Many thanks,

Last edited by prvnrk; 06-13-2014 at 05:51 PM..
# 2  
Old 06-13-2014
Try:
Code:
awk 'NR==FNR{a[$1]=$0}NR!=FNR{for (i=2;i<=NF;i++) a[$1]=a[$1]" "$i}END{for (i in a) print a[i]}' File01 File02 File03 | sed 's/\([^ ]\) /\1, /g' | sort

This User Gave Thanks to bartus11 For This Post:
# 3  
Old 06-13-2014
If you don't mind dropping the spaces between fields in your output (which aren't usually desired in a comma separated values file anyway), this seems simpler:
Code:
awk '
{	for(i = 2; i <= NF; i++) d[$1] = d[$1]","$i }
END {	for (i in d) print i d[i] }' File?? | sort

With your sample input, it produces the output:
Code:
17:40:01,569056,2538672,81.69,436568,1771600,6213560,64,0.00,0,0,130,0.00,0.00,0.00,180
17:45:01,570420,2537308,81.65,436568,1771600,6213560,64,0.00,0,0,126,0.00,0.00,0.00,190
17:50:01,569676,2538052,81.67,436568,1771624,6213560,64,0.00,0,0,129,0.06,0.03,0.01,200

This User Gave Thanks to Don Cragun For This Post:
# 4  
Old 06-13-2014
Wonderful dear mods Smilie - Both solutions worked great, I have 2 solutions to choose from Smilie

I just didn't want to complicate my first post so I didn't include another requirement to it initially. Only if you are not angry and don't mind, could you please

1. Insert date in the beginning
2. add the value of "100 minus 5th field" (the one with 81.69 in first row)

then the output should be:

Code:
06/13/2014,17:40:01,569056,2538672,81.69,436568,1771600,6213560,64,0.00,0,0,130,0.00,0.00,0.00,180,18.31
06/13/2014,17:45:01,570420,2537308,81.65,436568,1771600,6213560,64,0.00,0,0,126,0.00,0.00,0.00,190,18.35
06/13/2014,17:50:01,569676,2538052,81.67,436568,1771624,6213560,64,0.00,0,0,129,0.06,0.03,0.01,200,18.33

Many thanks!!
# 5  
Old 06-13-2014
So you asked for code and we gave it to you.

Then you changed the requirements.

You have asked for our help 78 times. You have posted 180 times.

What have you tried to solve your new problem? Have you learned anything about awk from us in the 7 years you've posting on The UNIX and Linux forums?
# 6  
Old 06-13-2014
Quote:
Originally Posted by Don Cragun
Have you learned anything about awk from us in the 7 years
Good question. awk is always a mystery for me though I'm not bad in scripting. I even requested few times for a good reference (easier way) to learn awk. But no luck.

Quote:
What have you tried to solve your new problem?
I already managed to get it done. I looped in each line and appended (used echo )the date using date +"%D" in the front and difference value in the end. I know this is not efficient but does the job for me. Thought to use your more efficient solution but no problem. Cheers!
# 7  
Old 06-13-2014
If you don't understand the scripts you're given here; ask questions. I haven't seen any of the volunteers here who were unwilling to explain how their code worked if anyone bothered to ask.

Your description of your input files is a little bit sparse. Making the assumptions:
  1. Some timestamps might not appear in all input files.
  2. The first file processed containing a given timestamp might not contain four fields.
  3. The output should have a minimum of 6 fields.
  4. If the output for a given timestamp doesn't include 5 fields (after adding the date to the start of the line), an empty field should be added and the final field should be 100.
With these assumptions, you could try the following code:
Code:
awk -v dt=$(date +%m/%d/%Y) '
{	# For each line in each input file...
	# For each field after the 1st field on each line...
	for(i = 2; i <= NF; i++) {
		# Add a comma and the contents of that field to the data
		# accumulated for the timestamp in the 1st field of this line.
		d[$1] = d[$1]","$i
		# Increment the number of fields we have found for this
		# timestamp.  If it is the 3rd field for this timestamp, also
		# save it in f3[].
		if(++c[$1] == 3)
			f3[$1] = $i
	}
}
END {	# We have hit EOF on the last input file...
	# For each different timestamp that was in the 1st field of any of the
	# input files...
	for(i in d) {
		# If the number of fields associated with this timestamp was
		# less than three, add empty fields...
		while(c[i]++ < 3)
			d[i] = d[i]","
		# Print the date, the timestamp, the input data fields
		# associated with this timestamp, and 100 - the 3rd data field
		# found for this timestamp with commas as field separators.
		print dt","i d[i]","(100 - f3[i])
	}
}' File?? | sort

If we add an additional input file (named File04) to those you had before:
Code:
17:40:00	1
17:45:00	2	3
17:50:00	4	5	6
17:50:01	300
17:55:00	7	8	9.1112	10

then running the above script today produces the output:
Code:
06/13/2014,17:40:00,1,,,100
06/13/2014,17:40:01,569056,2538672,81.69,436568,1771600,6213560,64,0.00,0,0,130,0.00,0.00,0.00,180,18.31
06/13/2014,17:45:00,2,3,,100
06/13/2014,17:45:01,570420,2537308,81.65,436568,1771600,6213560,64,0.00,0,0,126,0.00,0.00,0.00,190,18.35
06/13/2014,17:50:00,4,5,6,94
06/13/2014,17:50:01,569676,2538052,81.67,436568,1771624,6213560,64,0.00,0,0,129,0.06,0.03,0.01,200,300,18.33
06/13/2014,17:55:00,7,8,9.1112,10,90.8888

Does this do what you want?

Is there anything in this awk script you don't understand?
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

How to extract fields from a CSV i.e comma separated where some of the fields having comma as value?

can anyone help me!!!! How to I parse the CSV file file name : abc.csv (csv file) The above file containing data like abv,sfs,,hju,',',jkk wff,fst,,rgr,',',rgr ere,edf,erg,',',rgr,rgr I have a requirement like i have to extract different field and assign them into different... (4 Replies)
Discussion started by: J.Jena
4 Replies

2. UNIX for Dummies Questions & Answers

Merge two text files by two fields and mixed output

Hello, I'm back again looking for your precious help- This time I need to merge two text files with matching two fields, output only common records with mixed output. Let's look at the example: FILE1 56153;AAA0708;3;TEST1TEST1; 89014;BBB0708;3;TEST2TEST2; 89014;BBB0708;4;TEST3TEST3; ... (7 Replies)
Discussion started by: emare
7 Replies

3. Shell Programming and Scripting

How to merge two or more fields from two different files where there is non matching column?

Hi, Please excuse for often requesting queries and making R&D, I am trying to work out a possibility where i have two files field separated by pipe and another file containing only one field where there is no matching columns, Could you please advise how to merge two files. $more... (3 Replies)
Discussion started by: karthikram
3 Replies

4. Shell Programming and Scripting

Merge the multiple text files into one file

Hi All, I am trying to merge all the text files into one file using below snippet cat /home/Temp/Test/Log/*.txt >> all.txt But it seems it is not working. I have multiple files like Output_ServerName1.txt, Output_ServreName2.txt I want to merge each file into one single file and... (6 Replies)
Discussion started by: sharsour
6 Replies

5. Shell Programming and Scripting

Insert comma in place of column

Hi all, I have a file in which I have to insert commna between entries of 2 column and createa new file separated by commas not a columns if input is FHIT Adenosine Monotungstate Not Available CS Trifluoroacetonyl Coenzyme A Not Available Theo expected output is ... (5 Replies)
Discussion started by: manigrover
5 Replies

6. Shell Programming and Scripting

AWK: merge two files and replace some fields

Need some code tweak: awk 'END { for (i=1; i<=n; i++) if (f2]) print f2] } NR == FNR { f2 = $1] = $0 next } $1 in f2 { delete f2 }1' FS=, OFS=, 2.csv 1.csv > 3.csvfile 1.csv have: $1,$2,$3,$4,$5,$6,$7,$8,$9...... file 2.csv have: $1,$2,$3,$4,$5,$6 (2 Replies)
Discussion started by: u10
2 Replies

7. Shell Programming and Scripting

Place a comma on lines

Is it possible to place a comma in the desired places, like 10spaces after or 15 spaces after, irrespective of the contents??? Ex:File: TEST TEST: vimalthomaswants to place a comma can he do it in the desired places? as per the above file, i need to place a comma after 10th space... (4 Replies)
Discussion started by: vj8436
4 Replies

8. Shell Programming and Scripting

Merge text files while combining the multiple header/trailer records into one each.

Situation: Our system currently executes a job (COBOL Program) that generates an interface file to be sent to one of our vendors. Because this system processes information for over 100,000 employees/retirees (and growing), we'd like to multi-thread the job into processing-groups in order to... (4 Replies)
Discussion started by: oordonez
4 Replies

9. Shell Programming and Scripting

How do you delete multiple text from a comma delimited file

I would like to know code that will delete multiple text from a comma delimited file. For example, how would the comma delimited file below delete the word 'PEST' in Perl language (previously an excel file that was converted to a csv and the last column was PEST): 1, 2,43,34, bosx,PEST 1,... (1 Reply)
Discussion started by: dolo21taf
1 Replies

10. UNIX for Dummies Questions & Answers

Update text files in place (string substitution) ??

The auditors have nailed us for world writeable files.... Apparently in years gone by, quite a number of our kornshell scripts have had: umask 000 put in the script. We have been able to turn off world writeable for existing dirs & files, but as these scripts run, new files keep getting... (1 Reply)
Discussion started by: kornshellmaven
1 Replies
Login or Register to Ask a Question