Hi,
I am newbie in unix.
Could someone tell me how do I redirect my grep output to a csv/excel ?
I have used below command but the outputs are appearing in one column Not in different column.
grep -e cmd -e cmd1 test.txt | cut -f 5 | sort | uniq -c> op.csv
I do not understand how do I specify comma(,) as a delimiter in output csv.
Please help.
grep -e cmd -e cmd1 test.txt | cut -f 5 | sort | uniq -c | awk '{print}' OFS=,> op.csv
Pls be more specific, telling us what system you're on, what the input is and how the desired output should look like. btw, of course there's only one column, as you only cut one col in your long pipe.
Hi RudiC,
I am sorry for the confusion. I am using Linux Ubuntu. And the output is not one column, because count will also appear.
So my desired output is like below:
count, field
10, field5
222,field5
5, field5
grep -e cmd -e cmd1 test.txt | cut -f 5 | sort | uniq -c | awk '{print}' OFS=,
The above code does not show output in console as I have shown above.
Thanks for quick answers.
Looking at your command line, I assume that you need the count of 5th tab-separated columns from each line of test.txt matching the criteria specified by grep.
Just change your command line to:
grep -e cmd -e cmd1 test.txt | cut -f 5 | sort | uniq -c|sed 's/\([0-9]\) /\1,/' > op.csv
Or use:
awk -F'\t' '/cmd1?/{a[$5]++}END{for(i in a) print a,i|"sort -t, -k2"}' OFS=, test.txt > op.csv
slight changes to my last post
please ignore the last one
grep -e cmd -e cmd1 test.txt | cut -f 5 | sort | uniq -c | awk '{$1=$1;print}' OFS=,> op.csv
OK, got you. Then consider using raj_saini20's suggestion or use tr ' ' ',' at the end of the pipe. (You also might want to sort -n by numerical value?)
Not a good piece of advice. The 5th column might also have spaces. Also, the counts will mostly be preceded by spaces.
Actually you know what once I create this file from Linux then using Java I am reading that created excel/csv file and it is failing there.
Exception is "Unable to read entire header". And what I have seen the csv file created through linux is proper. But if I try to create a excel file the values are not appearing in different column , in this case I do not even need ',' as a delimiter.
So can someone tell me how do I create excel file from that grep command so that output goes into different column per row instead of one column per row?
Just a suspicion: when you transfer the file from Unix/Linux to Windows to pick it up with Excel, do you adjust the line endings? If not, then Excel probably might be confused ...
Check it with notepad: after transferring the file open it with notepad and if it looks like a regular text file, everything is OK. If everything appears to be on one very long line with funny characters instead of the line feeds then retransmit the file using "ascii" as the FTP mode instead of "binary".
I hope this helps.
bakunin
Hi bakunin,
Thank you for the reply. But I do not think there is any encoding issue. Everything looks great.
The issue is the values are appearing in one column(with , delimited) instead of different column (with no comma).
Desired Excel Output:-
Column1 Column2
Count Field
222 Field5
10 Field5
Thanks,
Kousik
When importing to EXCEL, you can select which chars be the separators. Are you sure you are using "," there?
Yes. I am.
And also other excel files are looking fine except those I create from linux by those grep commands.
Could you please provide meaningful samples of the output of the various stages of your pipeline? Seems like there is something rotten in the state of Denmark.
This was your initial pipe:
grep -e cmd -e cmd1 test.txt | cut -f 5 | sort | uniq -c> op.csv
Now do the following:
grep -e cmd -e cmd1 test.txt > intermediate1.out
grep -e cmd -e cmd1 test.txt | cut -f 5 > intermediate2.out
grep -e cmd -e cmd1 test.txt | cut -f 5 | sort > intermediate3.out
grep -e cmd -e cmd1 test.txt | cut -f 5 | sort | uniq -c > op.csv
and post a meaningful part of each of these files, ideally from each the part which deals with the same data.
It might help in finding a solution to see what happens to the data in the various steps. Somehow i believe it might not be what you expect it to be.
I hope this helps.
bakunin
Hi bakunin,
I have bunch of log files. I have to look for all the logs from 20th August onwards by seeing their timestamps.
Now what I have to look for is:
i) 15 different keywords in each of those log files and get the 5th column as well as the count of those keywords. In each log files for each row there will only one keyword, so multiple keyword will not appear in same row.
Then store the output of this grep command in excel.
In java read that excel file and call database to compare some values and produce the final output in final excel.
Example log:-
abc.com 20120816141935 sasas1 keyword1 noneAAAAAAAAAAAA noneAAAAAAAAAAAA 0 0 N sasasasa
abc.com 20120816141935 sasasa keyword2 noneAAAAAAAAAAAB noneAAAAAAAAAAAA 0 0 N sasasasa
abc.com 20120816141935 sasasa keyword2 noneAAAAAAAAAAAC noneAAAAAAAAAAAA 0 0 N sasasasa
keyword:- keyword1, keyword2
Output required in excel:-
keyword1 noneAAAAAAAAAAAA
keyword2 noneAAAAAAAAAAAB
keyword2 noneAAAAAAAAAAAC
Note:- I am reading the created excel using POI api in Java which is causing error.
Java Code:-
public Vector ReadCSV(String fileName) {
Vector cellVectorHolder = new Vector();
try {
FileInputStream myInput = new FileInputStream(fileName);
POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput);
HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem);
HSSFSheet mySheet = myWorkBook.getSheetAt(0);
Iterator rowIter = mySheet.rowIterator();
while (rowIter.hasNext()) {
HSSFRow myRow = (HSSFRow) rowIter.next();
Iterator cellIter = myRow.cellIterator();
Vector cellStoreVector = new Vector();
while (cellIter.hasNext()) {
HSSFCell myCell = (HSSFCell) cellIter.next();
cellStoreVector.addElement(myCell);
}
cellVectorHolder.addElement(cellStoreVector);
}
} catch (Exception e) {
e.printStackTrace();
}
return cellVectorHolder;
}