Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

merging CSV data using a one liner from shell?

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting merging CSV data using a one liner from shell?
# 1  
Old 08-10-2007
merging CSV data using a one liner from shell?
I'm trying to merge multiple CSV (comma separated value) files into one large master file. All files have a field that is unique to act as the key for entry/merging into the master file & and all files have the same number of fields that are in the master file.

I'll give an example here:

MASTERFILE (BEFORE MERGE):

"userid","logon","name","fname","lname","app1","app2","app3","app4","app5"
"111","","Joe One","","","yes","no","","no",""
"333","","","","","","","","",""
"555","","","Five","","","","","",""
"222","mrtwo","Lou Two","Lou","","no","no","no","no","yes"

MERGEFILE1:

"111","mrone","","Joe","One","","","yes","","no"
"222","","Lou Two","Lou","","no","no","no","no","yes"
"555","mrfive","","","","","","","",""


I imagine it would be something like:
Run the script...
# {somescript} masterfile mergefile1 > masterfile2

So now MASTERFILE2 would become:

"111","mrone","Joe One","Joe","One","yes","no","yes","no","no"
"222","mrtwo","Lou Two","Lou","","no","no","no","no","yes"
"333","","","","","","","","",""
"555","mrfive","","Five","","","","","",""


So the first field would be be scanned from the first line of the mergefile to see if it is in the the first filed of any lines in the masterfile, if it exists in the masterfile, then it would update the fields for that entry accordingly (overwriting any existing fields in masterfile) and continue on to process the next line of the mergefile against the masterfile.


How could I accomplish this? Smilie


Thanks!
# 2  
Old 08-10-2007
The task breaks down to this.

drop the header lines as needed.

insert a second column (sed will work) to both files to indicate the file number (1 or 2 - in your example)

sort the two files together

an awk program to merge the two files together and output the results.



does that make sense? which step to you need help on?
# 3  
Old 08-13-2007
Quote:
Originally Posted by awk
The task breaks down to this.

drop the header lines as needed.

insert a second column (sed will work) to both files to indicate the file number (1 or 2 - in your example)

sort the two files together

an awk program to merge the two files together and output the results.



does that make sense? which step to you need help on?


Hmm... aside from, drop the header lines as needed... all of them?
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Shell script for .Txt to .csv conversion with data processing

Hi experts, I want to convert a txt file having rows and columns (CNAI_DUMP_raw.txt) by comparing it with another text file (paramaters.txt) and generate a output in CSV which contains only 3rd column from CNAI_DUMP_raw.txt, and the columns mentioned in parameters.txt. FYI: There are two... (16 Replies)
Discussion started by: Gautam Banerjee
16 Replies

2. Shell Programming and Scripting

Shell script to extract data from csv file

Hi everyone, I have a csv file which has data with different heading and column names as below. Static Data Ingested ,,,,,,,,,,,,Known Explained Rejections Column_1,column_2,Column_3,Column_4,,Column_6,Column_7,,% Column_8,,Column_9 ,Column_10 ,... (14 Replies)
Discussion started by: Vivekit82
14 Replies

3. UNIX for Dummies Questions & Answers

Shell script to extract data from csv file

Hi Guys, I am new to shell script.I need your help to write a shell script. I need to write a shell script to extract data from a .csv file where columns are ',' separated. The file has 7 columns having values say column 1,column 2.....column 7 as below along with their values. Name, Address,... (7 Replies)
Discussion started by: Vivekit82
7 Replies

4. Shell Programming and Scripting

Matching and Merging csv data fields based on a common field

Dear List, I have a file of csv data which has a different line per compliance check per host. I do not want any omissions from this csv data file which looks like this: date,hostname,status,color,check 02-03-2012,COMP1,FAIL,Yellow,auth_pass_change... (3 Replies)
Discussion started by: landossa
3 Replies

5. Shell Programming and Scripting

creating a csv file from this 1 liner?

I'm trying to create a csv file by running awk and sed on a number of xml files in a directory; I'm using this below: hostname; grep "BuildDate" /dir/ABCD/configuration/*/*.xml | awk -F"/" '{ print $5 }' > /tmp/tempfile.txt; grep "BuildDate" /dir/ABCD/configuration/*/*.xml | awk -F\" '{ print $2... (2 Replies)
Discussion started by: rich@ardz
2 Replies

6. Shell Programming and Scripting

Shell snip to import CSV data into BASH array

I have been trying to write a simple snip of bash shell code to import from 1 to 100 records into a BASH array. I have a CSV file that is structured like: record1,item1,item2,item3,item4,etc.,etc. .... (<= 100 items) record2,item1,item2,item3,item4,etc.,etc. .... (<= 100 items)... (5 Replies)
Discussion started by: dstrout
5 Replies

7. UNIX for Advanced & Expert Users

shell script to format .CSV data

Hi all, I have written a shell script to search a specified directory (e.g. /home/user) for a list of specific words (shown as ${TMPDIR}/wordlist below). The script works well enough, but I was wondering if there was a way to display the line number that the word is found on? Thanks! cat... (1 Reply)
Discussion started by: tmcmurtr
1 Replies

8. Shell Programming and Scripting

csv to table one-liner

I've googled a lot on this, but could not fine a simple one-liner to do this. I have a .csv file that looks like this: Header one Header two Header three col1,col2,col3 short data, very long data, dataIf I use sed and change the comma to tab, being the colums of variable length I don't get a... (6 Replies)
Discussion started by: ahsog
6 Replies

9. Shell Programming and Scripting

Shell script to format a .CSV data

Hi There I needed to write a Unix shell script which will pick up the data from a .CSV file and reformat it as per the requirement and write it to another .CSV file. Currently I am in the proess of Data Import to "Remedy System" (A one kind of incident mangement Application) and this... (8 Replies)
Discussion started by: Uday1982
8 Replies
Login or Register to Ask a Question