Solved

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

Posted on 2010-09-01
9
874 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 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Over the years I've spent many an hour playing on hardened, DMZ'd servers, with only a sub-set of the usual GNU toy's to keep me company; frequently I've needed to save and send log or data extracts from these server back to my PC, or to others, and…
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 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…
In a previous video, we went over how to export a DynamoDB table into Amazon S3.  In this video, we show how to load the export from S3 into a DynamoDB table.

770 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