Solved

How can I concatenate two .csv files in unix with header by first grepping on creation time

Posted on 2010-09-01
9
879 Views
Last Modified: 2013-12-26
I have some .csv files in the directory "/v/region/na/appl/qatools/util/data/reports/automation_framework"

The .csv files have been created from corresponding .xls files through
mv Int_Res1.xls Int_Res1.csv

the .csv files have the structure like below

header1  header2  header3
value1      value2     value3

I need to concatenate the .csv files into a final_res.csv file by first grepping on files beginning with "Int_Res" in the dir "/v/region/na/appl/qatools/util/data/reports/automation_framework, then putting the file which was created first in the final_res.csv along with header, then appending the next created file but stripping off the header row which is row 0 and so on.

How can I do this in Unix?
0
Comment
Question by:sunny82
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
9 Comments
 
LVL 7

Expert Comment

by:vikas_madhusudana
ID: 33577904

count=0
for a in `ls Int_res*`
do
if [ $count -eq 0 ]; then
cat $a > final_res.csv
else
tail -n +2  $a > final_res.csv
fi
done

Open in new window

0
 
LVL 7

Accepted Solution

by:
vikas_madhusudana earned 250 total points
ID: 33577918

count=0
for a in `ls Int_res*`
do
if [ $count -eq 0 ]; then
cat $a > final_res.csv
count=1
else
tail -n +2  $a > final_res.csv
fi
done

Open in new window

0
 
LVL 68

Assisted Solution

by:woolmilkporc
woolmilkporc earned 250 total points
ID: 33577968
Yep,
but line 8 should read
tail -n +2  $a >> final_res.csv
And ... just renaming a .xls file to .csv doesn't convert it from EXCEL to CSV, or am I wrong here?
wmp
 
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

Author Comment

by:sunny82
ID: 33578994
Hi, many thanks for the reply.

On running the code below, I am getting a final_res.csv file which cannot be opened in recognizable format. It is distorted.
=================================
#!/bin/ksh
count=0
for a in `ls Int_Res*`
do
if [ $count -eq 0 ]; then
cat $a > final_res.csv
count=1
else
tail -n +2  $a > final_res.csv
fi
done
============================


These are the files in my folder --

/v/region/na/appl/qatools/util/data/reports/automation_framework/Results 225$ ls Int_Res*
Int_Res18_2_10.csv  Int_Res18_2_13.csv  Int_Res18_2_15.csv  Int_Res18_2_18.csv  Int_Res18_2_20.csv  Int_Res18_2_7.csv  Int_Res18_2_9.csv
Int_Res18_2_12.csv  Int_Res18_2_14.csv  Int_Res18_2_17.csv  Int_Res18_2_19.csv  Int_Res18_2_21.csv  Int_Res18_2_8.csv

The output I am getting is attached..why is that?
final-res.csv
0
 

Author Comment

by:sunny82
ID: 33579016
The .csv Int_Res files are fine and can be opened with Excel.
0
 

Author Comment

by:sunny82
ID: 33579123
Ok by doing the step as "woolmilkpork", I have got the output but now the problem is it contains the result of only Int_Res18_2_9.csv. The others are not there. I am attaching the output plus 4 other .csv sheets. Why did it remove the results of the others?


final-res.csv
Int-Res18-2-7.csv
Int-Res18-2-8.csv
Int-Res18-2-9.csv
Int-Res18-2-10.csv
0
 

Author Comment

by:sunny82
ID: 33579638
Any solutions pls on this...help me...
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 33579849
Your files are not containing "comma separated values" (CSV) but rather tables in Microsoft Excel format.
As I wrote: A simple "mv" doesn't do data conversion!

Those tables cannot be concatenated as if they were text files.
You will have to either find a method to create real CSV files from the XLS tables, or you must find a way to concatenate the Excel sheets themselves.

I'm not a Microsoftie, so I can' tell you for sure, but I guess there are some Windows programs around which could achieve what you desire. Google for it!

By the way, when you look at the file sizes you will notice that with my version actually all files got concatenated (size 82 K) as opposed to the first version (6 K).

The problem is that Excel only reads upt to its first internal ending mark and not further, regardless of what might follow.

wmp


0
 

Author Comment

by:sunny82
ID: 33580079
Thx it worked. I wrote a perl script to copy all excels to comma-delimited flat files and then used the above code, so got all the values concatenated. here are the results
final-res.csv
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Attention: This article will no longer be maintained. If you have any questions, please feel free to mail me. jgh@FreeBSD.org Please see http://www.freebsd.org/doc/en_US.ISO8859-1/articles/freebsd-update-server/ for the updated article. It is avail…
Background Still having to process all these year-end "csv" files received from all these sources (including Government entities), sometimes we have the need to examine the contents due to data error, etc... As a "Unix" shop, our only readily …
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
Learn how to navigate the file tree with the shell. Use pwd to print the current working directory: Use ls to list a directory's contents: Use cd to change to a new directory: Use wildcards instead of typing out long directory names: Use ../ to move…
Suggested Courses

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question