Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ugly file import into Excel
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--- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ugly file import into Excel
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 -- crispb ----------------------------------------------------------------------- crispbd's Profile: http://www.excelforum.com/member.php...fo&userid=1088 View this thread: http://www.excelforum.com/showthread.php?threadid=27361 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I import text file of cash flow to excel file then use formula | Excel Discussion (Misc queries) | |||
import file so that it is a actual csv file, no excel cell version | New Users to Excel | |||
Ugly Problem | Excel Discussion (Misc queries) | |||
6E+02 what is this ugly scientific format? | Excel Programming | |||
Import text file into excel with preset file layout, delimeters VBA | Excel Programming |