G
Guest
Using an Ansi report to be imported and final tallies MUST be done using only
Excel (licensing and system restrictions). Problem in the file is REAL ugly.
It is comprised of several different sections with different widths, not tab
or comma delimitated and need some information from at least two different
sections. Really need a hand with ideas I can use to capture this data.
Information needed:
Date From and To
Agent ID (5 digit ID starting in this case with the 57xxx)
Full Name (would be nice but willing to cross reference off of other tabs)
....and all the information under Taken Days
Having additional information such as:
Total Earned
Max Partial Hours
Total Taken
Total Debited
Remaining to Select
Would be ideal, but I am willing to forgo these if necessary.
Unfortunatly the report can not be modified in any way (otherwise I might
have an easier time) and no the import can not set to Export to Excel.
Can send the file to whomever request and have included a sample that would
be best to copy/paste into notepad before you look at it. The information has
been changed to protect the innocent.
---Report Start---
From: 12/28/03 Application
10/29/04
To: 12/31/04 Company Name
09:06
Agent
Vacations Vacation Group
Page: 1
Vacation Group: LV Skills 1,2,4,7 Vacation Year:
2004 Master From Date: 11/07/04
Sorted by: Agent Name
Master To Date: 12/31/04
Show: Cancelled Selections - No, Comments - No, Detail and Summary, Summary
Times in HH:MM
Report Agent Moves: No
57071 - Last Name, First Name Initial
Bidding Date: 09/11/00- Accrual Date: 01/01/0
====================|=========|=========|=========|=========||=====|=========|=========||=========|=========|=========|=========|=========
| | | |Carryover|| Min|
Max| Partial|| | | |Carryover|Remaining
| | | Total| From|| Full|
Partial| Hours|| Total| Total| Total| To| to
Vacation Type | From| To| Earned|Last Year||Weeks|
Hours| Selected|| Selected| Taken| Debited|Next Year| Selec
====================|=========|=========|=========|=========||=====|=========|=========||=========|=========|=========|=========|=========
Vacation Day Paid | 01/01/04| 12/31/04| 66:40| 04:00|| 0|
80:00| 00:00|| 00:00| 49:07| 15:59| 00:00| 05:34
Discretionary Day Pd| 01/01/04| 12/31/04| 52:00| 00:00|| 0|
56:00| 00:00|| 00:00| 19:55| 32:00| 00:00| 00:0
--------------------+---------+---------+---------+---------++-----+---------+---------++---------+---------+---------+---------+---------
Totals | | | 118:40| 04:00|| 0|
136:00| 00:00|| 00:00| 69:02| 47:59| 00:00| 05:3
--------------------+---------+---------+---------+---------++-----+---------+---------++---------+---------+---------+---------+---------
Wait List | 01/01/04| 12/31/04| 40:00| 00:00|| 0|
00:00| 00:00|| 00:00| | | 00:00| 40:0
====================|=========|=========|=========|=========||=====|=========|=========||=========|=========|=========|=========|=========
Taken Days
Taken
---------------------------------Selection------------------
Day Date Range Type Length Year Time Length
Last Changed Selected By
=== ================= ==================== ====== ==== =========== ======
============== ====================
Wed 06/02/04 Vacation Day Paid 08:00
Fri 06/04/04 Vacation Day Paid 08:00
Wed 06/16/04 Vacation Day Paid 08:00
Thu 07/08/04 Discretionary Day Pd 01:00
Tue 07/13/04 Discretionary Day Pd 02:00
Fri 07/23/04 Discretionary Day Pd 00:55
Fri 07/30/04 Discretionary Day Pd 02:15
Tue 08/17/04 Vacation Day Paid 08:00
Wed 08/18/04 Vacation Day Paid 08:00
Wed 08/25/04 Discretionary Day Pd 05:50
Wed 08/25/04 Vacation Day Paid 01:21
Tue 09/07/04 Vacation Day Paid 04:00
Tue 09/07/04 Discretionary Day Pd 04:00
Tue 09/21/04 Vacation Day Paid 02:40
Thu 10/14/04 Discretionary Day Pd 02:00
Mon 10/25/04 Discretionary Day Pd 00:55
Thu 10/28/04 Vacation Day Paid 01:06
Thu 10/28/04 Discretionary Day Pd 01:00
57009 - Last Name2, First Name Initial
Bidding Date: 01/04/04- Accrual Date: 01/01/04
====================|=========|=========|=========|=========||=====|=========|=========||=========|=========|=========|=========|=========
| | | |Carryover|| Min|
Max| Partial|| | | |Carryover|Remaining
| | | Total| From|| Full|
Partial| Hours|| Total| Total| Total| To| to
Vacation Type | From| To| Earned|Last Year||Weeks|
Hours| Selected|| Selected| Taken| Debited|Next Year| Select
====================|=========|=========|=========|=========||=====|=========|=========||=========|=========|=========|=========|=========
Vacation Day Paid | 01/01/04| 12/31/04| 26:40| 00:00|| 0|
40:00| 00:00|| 00:00| 16:00| 00:00| 00:00| 10:40
Discretionary Day Pd| 02/01/04| 12/31/04| 52:00| 00:00|| 0|
40:00| 00:00|| 00:00| 18:31| 22:00| 00:00| 11:29
--------------------+---------+---------+---------+---------++-----+---------+---------++---------+---------+---------+---------+---------
Totals | | | 78:40| 00:00|| 0|
80:00| 00:00|| 00:00| 34:31| 22:00| 00:00| 22:09
--------------------+---------+---------+---------+---------++-----+---------+---------++---------+---------+---------+---------+---------
Wait List | 01/01/04| 12/31/04| 40:00| 00:00|| 0|
00:00| 00:00|| 00:00| | | 00:00| 40:00
====================|=========|=========|=========|=========||=====|=========|=========||=========|=========|=========|=========|=========
Taken Days
Taken
---------------------------------Selection------------------
Day Date Range Type Length Year Time Length
Last Changed Selected By
=== ================= ==================== ====== ==== =========== ======
============== ====================
From: 12/28/03 IEX
TotalView 10/29/04
To: 12/31/04 United Parcel
Service 09:06
Agent
Vacations Vacation Group
Page: 2
Vacation Group: LV Skills 1,2,4,7 Vacation Year:
2004 Master From Date: 11/07/04
Sorted by: Agent Name
Master To Date: 12/31/04
Show: Cancelled Selections - No, Comments - No, Detail and Summary, Summary
Times in HH:MM
Report Agent Moves: No
Sun 08/15/04 Discretionary Day Pd 01:00
Sun 08/29/04 Discretionary Day Pd 01:00
Thu 09/09/04 Discretionary Day Pd 00:31
Sat 09/25/04 Vacation Day Paid 08:00
Tue 10/12/04 Discretionary Day Pd 08:00
Tue 10/19/04 Discretionary Day Pd 08:00
Mon 10/25/04 Vacation Day Paid 08:00
---Report Continues with like---
Excel (licensing and system restrictions). Problem in the file is REAL ugly.
It is comprised of several different sections with different widths, not tab
or comma delimitated and need some information from at least two different
sections. Really need a hand with ideas I can use to capture this data.
Information needed:
Date From and To
Agent ID (5 digit ID starting in this case with the 57xxx)
Full Name (would be nice but willing to cross reference off of other tabs)
....and all the information under Taken Days
Having additional information such as:
Total Earned
Max Partial Hours
Total Taken
Total Debited
Remaining to Select
Would be ideal, but I am willing to forgo these if necessary.
Unfortunatly the report can not be modified in any way (otherwise I might
have an easier time) and no the import can not set to Export to Excel.
Can send the file to whomever request and have included a sample that would
be best to copy/paste into notepad before you look at it. The information has
been changed to protect the innocent.
---Report Start---
From: 12/28/03 Application
10/29/04
To: 12/31/04 Company Name
09:06
Agent
Vacations Vacation Group
Page: 1
Vacation Group: LV Skills 1,2,4,7 Vacation Year:
2004 Master From Date: 11/07/04
Sorted by: Agent Name
Master To Date: 12/31/04
Show: Cancelled Selections - No, Comments - No, Detail and Summary, Summary
Times in HH:MM
Report Agent Moves: No
57071 - Last Name, First Name Initial
Bidding Date: 09/11/00- Accrual Date: 01/01/0
====================|=========|=========|=========|=========||=====|=========|=========||=========|=========|=========|=========|=========
| | | |Carryover|| Min|
Max| Partial|| | | |Carryover|Remaining
| | | Total| From|| Full|
Partial| Hours|| Total| Total| Total| To| to
Vacation Type | From| To| Earned|Last Year||Weeks|
Hours| Selected|| Selected| Taken| Debited|Next Year| Selec
====================|=========|=========|=========|=========||=====|=========|=========||=========|=========|=========|=========|=========
Vacation Day Paid | 01/01/04| 12/31/04| 66:40| 04:00|| 0|
80:00| 00:00|| 00:00| 49:07| 15:59| 00:00| 05:34
Discretionary Day Pd| 01/01/04| 12/31/04| 52:00| 00:00|| 0|
56:00| 00:00|| 00:00| 19:55| 32:00| 00:00| 00:0
--------------------+---------+---------+---------+---------++-----+---------+---------++---------+---------+---------+---------+---------
Totals | | | 118:40| 04:00|| 0|
136:00| 00:00|| 00:00| 69:02| 47:59| 00:00| 05:3
--------------------+---------+---------+---------+---------++-----+---------+---------++---------+---------+---------+---------+---------
Wait List | 01/01/04| 12/31/04| 40:00| 00:00|| 0|
00:00| 00:00|| 00:00| | | 00:00| 40:0
====================|=========|=========|=========|=========||=====|=========|=========||=========|=========|=========|=========|=========
Taken Days
Taken
---------------------------------Selection------------------
Day Date Range Type Length Year Time Length
Last Changed Selected By
=== ================= ==================== ====== ==== =========== ======
============== ====================
Wed 06/02/04 Vacation Day Paid 08:00
Fri 06/04/04 Vacation Day Paid 08:00
Wed 06/16/04 Vacation Day Paid 08:00
Thu 07/08/04 Discretionary Day Pd 01:00
Tue 07/13/04 Discretionary Day Pd 02:00
Fri 07/23/04 Discretionary Day Pd 00:55
Fri 07/30/04 Discretionary Day Pd 02:15
Tue 08/17/04 Vacation Day Paid 08:00
Wed 08/18/04 Vacation Day Paid 08:00
Wed 08/25/04 Discretionary Day Pd 05:50
Wed 08/25/04 Vacation Day Paid 01:21
Tue 09/07/04 Vacation Day Paid 04:00
Tue 09/07/04 Discretionary Day Pd 04:00
Tue 09/21/04 Vacation Day Paid 02:40
Thu 10/14/04 Discretionary Day Pd 02:00
Mon 10/25/04 Discretionary Day Pd 00:55
Thu 10/28/04 Vacation Day Paid 01:06
Thu 10/28/04 Discretionary Day Pd 01:00
57009 - Last Name2, First Name Initial
Bidding Date: 01/04/04- Accrual Date: 01/01/04
====================|=========|=========|=========|=========||=====|=========|=========||=========|=========|=========|=========|=========
| | | |Carryover|| Min|
Max| Partial|| | | |Carryover|Remaining
| | | Total| From|| Full|
Partial| Hours|| Total| Total| Total| To| to
Vacation Type | From| To| Earned|Last Year||Weeks|
Hours| Selected|| Selected| Taken| Debited|Next Year| Select
====================|=========|=========|=========|=========||=====|=========|=========||=========|=========|=========|=========|=========
Vacation Day Paid | 01/01/04| 12/31/04| 26:40| 00:00|| 0|
40:00| 00:00|| 00:00| 16:00| 00:00| 00:00| 10:40
Discretionary Day Pd| 02/01/04| 12/31/04| 52:00| 00:00|| 0|
40:00| 00:00|| 00:00| 18:31| 22:00| 00:00| 11:29
--------------------+---------+---------+---------+---------++-----+---------+---------++---------+---------+---------+---------+---------
Totals | | | 78:40| 00:00|| 0|
80:00| 00:00|| 00:00| 34:31| 22:00| 00:00| 22:09
--------------------+---------+---------+---------+---------++-----+---------+---------++---------+---------+---------+---------+---------
Wait List | 01/01/04| 12/31/04| 40:00| 00:00|| 0|
00:00| 00:00|| 00:00| | | 00:00| 40:00
====================|=========|=========|=========|=========||=====|=========|=========||=========|=========|=========|=========|=========
Taken Days
Taken
---------------------------------Selection------------------
Day Date Range Type Length Year Time Length
Last Changed Selected By
=== ================= ==================== ====== ==== =========== ======
============== ====================
From: 12/28/03 IEX
TotalView 10/29/04
To: 12/31/04 United Parcel
Service 09:06
Agent
Vacations Vacation Group
Page: 2
Vacation Group: LV Skills 1,2,4,7 Vacation Year:
2004 Master From Date: 11/07/04
Sorted by: Agent Name
Master To Date: 12/31/04
Show: Cancelled Selections - No, Comments - No, Detail and Summary, Summary
Times in HH:MM
Report Agent Moves: No
Sun 08/15/04 Discretionary Day Pd 01:00
Sun 08/29/04 Discretionary Day Pd 01:00
Thu 09/09/04 Discretionary Day Pd 00:31
Sat 09/25/04 Vacation Day Paid 08:00
Tue 10/12/04 Discretionary Day Pd 08:00
Tue 10/19/04 Discretionary Day Pd 08:00
Mon 10/25/04 Vacation Day Paid 08:00
---Report Continues with like---