Ugly file import into Excel

T

Tahlmorrah

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---
 
C

crispbd

You are most likely going to need to write some sort of parsing engin
to deal with the unique nature of the imported file. I have done thi
in several different capacities, but the methods I would follow to wor
with your case would be to do the following:

1) write a loop that will input line by line from the original file
stripping out bogus characters (such as all the ====+====+===)
2) replace the ||||| with spaces
3) look for certain keys that do not change - "Days Taken" or somethin
similar as your start point
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top