Formatting Script needed

[Allanm]

We need to populate an excel sheet with the information we have from logs. I am attaching the document for your reference and described what we have and what we need.

A shell script preferably using sed or awk instead of perl will be highly appreciable.


Thanks,
Allan

[James R. Ferguson]

Hi:

Let me guess, you're going to present this to your boss and pretend that you did it...right?

...JRF...

[Allanm]

Well I intend to present the data but not the script.

Thanks,
Allan

[Allanm]

Ok here is a start I did ...

grep -v "Customer count" customercount_20090201-155501 |egrep '14|15|16|17|18|19|20|21|22'|awk -F- '{print $2}'| tr -s '\n'

2
3
1
2
3
5
6
4
4

My tr doesnt seem to be working as I want it to be ...

I want the following as my result..

2 3 1 2 3 5 6 4 4

How do I remove the newlines here...

[Allanm]

it worked with tr -d '\n'

Now trying to find a way it can be pasted easily into an excel file... I need some tabs in between the output.

[Bob E Campbell]

If you can make several gross assumptions about no data values being missing, I think that the following should be trivial to import into Excel:

for FILE in customer*
do
DATE=${FILE%-*}
DATE=${DATE#*_}
print -n "$DATE,"
grep -v "customer count" $FILE |\
sort -n |\
while read HOUR HYPEN COUNT
do
print -n "$COUNT,"
done
print
done
20090131,6,3,4,6,5,9,6,4,3,4,9,3,3,
20090201,5,4,3,2,3,1,2,3,5,6,4,4,1,

[Allanm]

grep -v "Customer count" customercount_20090201-155501 |egrep '14|15|16|17|18|19|20|21|22'|awk -F- '{print $2}'| tr -d '\n'|tr ' ' '\t'

I got the tab in between the output but I am still not able to paste the text in different columns in excel. How do I get the output so that I am able to paste each on the tab separated digits in separate columns.

Thanks,
Allan.

[Bob E Campbell]

If you use my example and save the output into a file you can import the data into Excel using "Data | Import External Data | Import Data"

At that point a pop-up window will ask for the source. Select your file.

A new pop-up window will ask for settings. You tell in Delimited and select "Next>".

At this pop-up window you select that the data is delimited by commas and again select "Next>".

At the final window let Excel know that the data in column 1 is a Date using the YMD format and select "Finish". OK, the last window asks if you want a new worksheet.

There are lots of assumptions and potential problems that we cannot solve for you. This is intended to show you enough to get started. Good luck!