Solved

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

Posted on 2010-09-01
9
876 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Issue with cURL on Windows 7 112
vi editor showing strange character when i hit spacebar everytime 2 80
Linux "time" command output redirection 16 217
Work with App store 7 90
Using libpcap/Jpcap to capture and send packets on Solaris version (10/11) Library used: 1.      Libpcap (http://www.tcpdump.org) Version 1.2 2.      Jpcap(http://netresearch.ics.uci.edu/kfujii/Jpcap/doc/index.html) Version 0.6 Prerequisite: 1.      GCC …
Utilizing an array to gracefully append to a list of EmailAddresses
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.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

740 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