automate / upload a file daily. 1

[ksbrace]

I get an email, daily, with an excel spreadsheet of all calls made by all agents from the day before. I'm looking to have it custom sorted by calling number, then direction, then time, duration. I would like to be able to just upload the file either via .NET app or php, etc...
Then I want the total number of calls per calling number ( both inbound and outbound) divided by 8 (day shift), when first call was made, when last call was made. I would also like to have it broken down by hr (8-9, 20 calls were made, 9-10, x calls were made) Have this outputted to a word doc or email. attached is the file I receive daily.

So, I want it to say
Date:
ext: 101
total calls: 150
average per hr: 18.75
first call: 8:01am
last call: 4:59pm
8-9: 23 calls
9-10: 8 calls
.....
4-5: 0 calls

ext: 102
total calls: 150
average per hr: 18.75
first call: 8:01am
last call: 4:59pm
8-9: 23 calls
9-10: 8 calls
.....
4-5: 2 calls

 

[Andrzejek]

I see you already have it in 'step-by-step' process. Nice.

Do you know how to record a macro in Excel, and how to test it to know if it works the way you want it to? If so, I would start there: take the first step, records a macro, test it, modify if needed, and go to another step.

Ask for help here if you get stuck.


Have fun.

---- Andy

 

[SkipVought]

BTW, if you need help customizing a macro or putting them together in some way, please post back with your question(s) and include your recorded code.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ksbrace]

Skip,
Very cool....did not know I could record macros. Now, I have the sorting part done. How would I go about getting total calls? I think if I know how to get total calls, I can figure out how to do each hr. Thanks again!

 

[SkipVought]

How is a call recorded on your sheet?

Please describe your source data? Normally, data of this type is recorded in a TABLE on one of your sheets like...
[tt]
Ext call Start Call End Duration
101 8:01 8:43 0:42
102 9:01 9:04 0:03
...
[/tt]
From a table like this the COUNTS for each Ext or Total, average duration, counts per hour etc, just using spreadsheet formulas.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ksbrace]

I had put the link in initial post:

http://www.huntingtonamc.com/call_logs.csv

  date	time	time zone	direction	calling number	dialed number	auto attendant	duration	talk

2/7/2012	12:19:01	US/Eastern	outbound	100	817167158429		000:00:01:25	000:00:01:09
2/7/2012	15:06:38	US/Eastern	outbound	100	817168370752		000:00:01:44	000:00:01:30
2/7/2012	15:30:58	US/Eastern	outbound	100	817167158429		000:00:00:39	000:00:00:01
2/7/2012	15:45:46	US/Eastern	outbound	100	817166880025		000:00:01:27	000:00:01:20
2/7/2012	11:58:13	US/Eastern	internal	101	112		000:00:03:32	000:00:03:15

 

[SkipVought]

Many of us cannot download data due to company security restrictions. Thank you for posting this example.

Using this table you could get may summary items using the PivotTable Wizard. Using a PivotTable Group I can get Counts by Hour, as well.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ksbrace]

Skip,
Thanks for the tip.....Can u give me a bit more info on how to use this Pivot Table Wizard? Thanks in advance,
Kelly

 

[SkipVought]

Check Excel Help to begin.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ksbrace]

Yeah, I've been reading it and at some excel tutorial site. I can create the table and choose some columns/fields...but i'm not seeing how to get the sum/count.
Below is what my pivot table looks like.

Row Labels	Sum of calling number
6:53:54	2149820012
inbound	2149820012
7:43:56	9034864020
inbound	9034864020
7:47:32	7165639452
inbound	7165639452
8:15:31	5185261371
inbound	5185261371
8:18:20	7165639452
inbound	7165639452
8:28:02	120
outbound	120
8:28:38	7162486393
inbound	7162486393

 

[SkipVought]

OK.

THAT format is virtually useless, but it's the FIRST STEP.

Select IN the PivotTable (PT) and ...

Options TAB > Pivot Table > Options > Options > Display Tab > Classic PT Layout

Your Actual Field Headings should not be visible: 2 ROW columns and the SUM.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

sorry...

Your Actual Field Headings should [highlight]now[/highlight] be visible: 2 ROW columns and the SUM.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}