06-30-2009
Join 3 files using key column in a mapping file
I'm new of UNIX shell scripting. I'm recently generating a excel report in UNIX(file with delimiter is fine). How should I make a script to do it?
1 file to join comes from output of one UNIX command, the second from another UNIX command, and third from a database query. The key columes of all these 3 files come from a mapping file. It's basically as following:
MappingFile:
===========
job1 a_name1
job2 c_name2
job3 b_name3
job4 e_name4
file1_toJOIN:
============
b_name3 12:00:03 15:00:09
e_name4 10:30:00 11:00:00
c_name2 09:40:00 10:12:00
a_name1 22:00:00 23:00:00
file2_toJOIN:
=============
job2 tom 22
job1 dan 18
job4 jim 25
job3 kim 20
the spreadsheet format to generate:
===========================
job1 22:00:00 23:00:00 01:00:00 dan 18
......
Appreciate your help!
10 More Discussions You Might Find Interesting
1. Shell Programming and Scripting
Hi All,
I have working (Perl) code to combine 2 input files into a single output file using the join function that works to a point, but has the following limitations:
1. I am restrained to 2 input files only.
2. Only the "matched" fields are written out to the "matched" output file and... (1 Reply)
Discussion started by: Katabatic
1 Replies
2. UNIX for Dummies Questions & Answers
Hi,
I'm trying to compare the first column of two files (tab or whitespace delimited, either way's fine, I`ve got both) and print the lines that are identical for the first column of both files. Something like this:
File1
AAA 26 49 7 27 36 33 46 75 73 69
AAAAA 4 10 4 7 10 18 21... (2 Replies)
Discussion started by: vanesa1230
2 Replies
3. Shell Programming and Scripting
Dear Forum,
Full title of the topic would be: "Join 3 or more files using matching column without full list in any of these columns"
I have several, typically 3 or 4 files which I need to join, something like FULL JOIN in slq scripts, all combinations of matches should be printed into an... (3 Replies)
Discussion started by: cyz700
3 Replies
4. Shell Programming and Scripting
Hi All,
I am trying to join to files based on one common column.
Cat File1
ID HID
Ab_1 23
Cd 45
df 22
Vv 33
Cat File2
ID pval
Ab_1 0.3
Cd 10
Vv 0.0444 (3 Replies)
Discussion started by: newpro
3 Replies
5. UNIX for Dummies Questions & Answers
Hi,
I have 20 tab delimited text files that have a common column (column 1). The files are named GSM1.txt through GSM20.txt. Each file has 3 columns (2 other columns in addition to the first common column).
I want to write a script to join the files by the first common column so that in the... (5 Replies)
Discussion started by: evelibertine
5 Replies
6. UNIX for Dummies Questions & Answers
I have file input
file1
1/1/2013 A 553.0763397 96 16582
1/1/2013 B 459.8333588 195 11992
1/2/2013 A 844.2973022 306 19555
1/2/2013 B 833.9300537 457 20165
1/3/2013 A 563.6917419 396 13879
1/3/2013 B 632.0749969 169 ... (1 Reply)
Discussion started by: radius
1 Replies
7. UNIX for Dummies Questions & Answers
I have file input1.txt
11103|11|OTTAWA|City|AA|CAR|0|0|1|-1|0|8526|2014-09-07 23:00:14
11103|11|OTTAWA|City|BB|TRAIN|0|0|2|-2|6|6359|2014-09-07 23:00:14
11104|11|CANADA|City|CC|CAR|0|0|2|-2|0|5947|2014-09-07 23:00:14
11104|11|CANADA|City|DD|TRAIN|0|0|2|-2|1|4523|2014-09-07 23:00:14... (5 Replies)
Discussion started by: radius
5 Replies
8. Shell Programming and Scripting
Dear All,
I have many files formatted like this:
file1.txt:
1/2-SBSRNA4 18
A1BG 3
A1BG-AS1 6
A1CF 0
A2LD1 1
A2M 1160
file2.txt
1/2-SBSRNA4 53
A1BG 1
A1BG-AS1 7
A1CF 0
A2LD1 3
A2M 2780 (5 Replies)
Discussion started by: paolo.kunder
5 Replies
9. Shell Programming and Scripting
Hi,
I am trying to join 2 csv files, to create a 3rd output file with the joined data.
Below is an example of my Input Data:
Input File 1
NAME, FAV_FOOD, FAV_DRINK, ID, GENDER
Bob, Fish, Coke, 1, M
Lisa, Rice, Water, 2, F
Jenny, Noodle, Tea, 3, F
Ken, Pizza, Coffee, 4, M
Lisa,... (7 Replies)
Discussion started by: RichZR
7 Replies
10. Shell Programming and Scripting
Join and merge multiple files with duplicate key and fill void columns
Hi guys,
I have many files that I want to merge:
file1.csv:
1|abc
1|def
2|ghi
2|jkl
3|mno
3|pqr
file2.csv: (5 Replies)
Discussion started by: yjacknewton
5 Replies
LEARN ABOUT DEBIAN
xmlstarlet
XMLSTARLET(1) xmlstarlet Manual XMLSTARLET(1)
NAME
xmlstarlet - command line XML/XSLT toolkit
SYNOPSIS
xmlstarlet [<options>] [<command>] [<cmd-options>]
INTRODUCTION
XMLStarlet is a set of command line utilities (tools) which can be used to transform, query, validate, and edit XML documents and files us-
ing simple set of shell commands in similar way it is done for plain text files using UNIX grep, sed, awk, diff, patch, join, etc commands.
This set of command line utilities can be used by those who deal with many XML documents on UNIX shell command prompt as well as for auto-
mated XML processing with shell scripts.
OPTIONS
--version
Display the version of xmlstarlet.
--help Display help.
COMMANDS
Type: xmlstarlet <command> --help <ENTER> for command help
Available commands include:
ed (or edit)
Edit/update XML document(s).
sel (or select)
Select data or query XML document(s) (XPATH, etc).
tr (or transform)
Transform XML documents(s) using XSLT.
val (or validate)
Validate XML document(s) (well-formed/DTD/XSD/RelaxNG).
fo (or format)
Format XML document(s).
el (or elements)
Display element structure of XML document.
c14n (or canonic)
XML canonicalization.
ls (or list)
List directory as XML.
esc (or escape)
Escape special XML characters.
unesc (or unescape)
Unescape special XML characters.
pyx (or xmln)
Convert XML into PYX format (based on ESIS - ISO 8879).
p2x (or depyx)
Convert PYX into XML.
REFERENCES
XMLStarlet is a command line toolkit to query/edit/check/transform XML documents (for more information see http://xmlstar.source-
forge.net/).
AUTHOR
Mikhail Grushinskiy.
XMLSTARLET(1)