Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2010-09-01
9
Medium Priority
?
887 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
  • 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 1000 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 1000 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Java performance on Solaris - Managing CPUs There are various resource controls in operating system which directly/indirectly influence the performance of application. one of the most important resource controls is "CPU".   In a multithreaded…
FreeBSD on EC2 FreeBSD (https://www.freebsd.org) is a robust Unix-like operating system that has been around for many years. FreeBSD is available on Amazon EC2 through Amazon Machine Images (AMIs) provided by FreeBSD developer and security office…
Learn how to get help with Linux/Unix bash shell commands. Use help to read help documents for built in bash shell commands.: Use man to interface with the online reference manuals for shell commands.: Use man to search man pages for unknown command…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

927 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