asked on

Text File to Excel mutiple lines per record

Hi Experts,
I have a text file to convert to Excel. This file is special because there are multiple lines per record.
Ex: 1st record 1st line in file is Plate Number: AZAB34596
2nd line of same record # 149885665 date: 07/05/13 type: License Plate
3rd line same record Time: 12:49A location: 1738 Welton St
The ticket amount is not required. All the other extraneous lanaguage is not required.
Any way to do this with VBA?

als315

Can you upload sample file and show expected result in excel?

Robberbaron (robr)

definitely need sample data as we need to know how the records and fields are separated.

are there ALWAYS 3 lines per record ? or sometime 4 or 5 ?

rhadash

ASKER

Please see attached file. Each record always the same 3 lines. It does not change.

rhadash

ASKER

This is a snipet from the file. Not sure why but it will not upload as attachment. That is probably bad omen.

PROGRAM: DVRNTC26 XXXXX STATE & LOCAL SOLUTIONS, INC RUNDATE: 08/04/2013
RUNTIME: 07:35:34
CLIENT : XXXXX, XXXXXXXX PARKING VIOLATIONS BUREAU OPEN CITATION REPORT - R410C PAGE NO: 1
REPORT DATE: 08/04/2013 AGENCY PAGE NO: 1 DUE DATE: 09/15/2013
===================================================================================================================================
020000003 XXXXX CORPORATION INSTRUCTIONS: THE FUNCTION OF THIS RENTAL TICKET REPORT IS TO ADVISE YOU OF THE
PO BOX 24130 LICENSE PLATE AND EMISSIONS VIOLATIONS ON VEHICLES OWNED BY YOUR AGENCY.
AS OF THE ABOVE REPORT DATE, YOUR AGENCY IS RESPONSIBLE FOR PAYMENT IN
CITY OK 11124 FULL OF THESE PARKING CITATIONS. PLEASE SEND CHECK OR MONEY ORDER MADE
PAYABLE TO XXXXXX XXXXXXX, P.O. BOX 46500 XXXX, XX
80201-6500, ATTN: FLEET PROCESSING DEPT. ENCLOSE A COPY OF THIS REPORT
WITH YOUR PAYMENT TO ENSURE PROPER CREDIT.
*** FAILURE TO COMPLY WITH THIS REQUEST WILL PLACE YOUR AGENCY IN
VIOLATION OF TITLE 42-4-1110 OF THE XXXXXX REVISED STATUTES. PAYMENT
IN FULL MUST BE RECEIVED NO LATER THAN THE DUE DATE ABOVE. FAILURE TO
RESPOND MAY SUBJECT YOU TO SUCH OTHER PENALTIES AS PRESCRIBED BY LAW,
INCLUDING SEIZURE OF YOUR VEHICLE(S) BY BOOTING OR TOWING.***
**================================================================================================================================**
TICKET-NO DATE VIOLATION DESCRIPTION FINE/ PEN/ RED/ PAID DUE PAYMENT
TIME LOCATION ($)
PLATE NUMBER : AZZB34596
| | |
* 149885665 07/05/13 LICENSE PLATE | $75 $75 $0 $0 $150| |
| | |
12:49A 1738 WELTON ST | |$_____________|
| | |
# OF TICKETS : 1 DUE : $150 | |______________|
====================================================================================================================================
PLATE NUMBER : CA6SDY237
| | |
* 143924653 07/30/12 LICENSE PLATE | $75 $75 $0 $75 $75| |
| | |
01:14P 1171 XANTHIA ST N | |$_____________|
| | |
# OF TICKETS : 1 DUE : $75 | |______________|
====================================================================================================================================
PLATE NUMBER : CA6VES219
| | |
* 151088280 07/31/13 LICENSE PLATE | $75 $75 $0 $0 $150| |
| | |
07:41P 1721 LAWRENCE ST | |$_____________|
| | |
# OF TICKETS : 1 DUE : $150 | |______________|
====================================================================================================================================
PLATE NUMBER : COADA8702
| | |
* 42523681 04/10/00 LICENSE PLATE | $25 $25 $0 $0 $50| |
| | |
02:45P DIA 4W ROW C | |$_____________|
| | |
# OF TICKETS : 1 DUE : $50 | |______________|
====================================================================================================================================
PROGRAM: DVRNTC26 XEROX STATE & LOCAL SOLUTIONS, INC RUNDATE: 08/04/2013
RUNTIME: 07:35:34
CLIENT : DENVER, COLORADO PARKING VIOLATIONS BUREAU OPEN CITATION REPORT - R410C PAGE NO: 2
REPORT DATE: 08/04/2013 AGENCY PAGE NO: 2 DUE DATE: 09/15/2013
AGENCY NO : 020000003 AGENCY NAME : HERTZ CORPORATION
===================================================================================================================================
TICKET-NO DATE VIOLATION DESCRIPTION FINE/ PEN/ RED/ PAID DUE PAYMENT
TIME LOCATION ($)
PLATE NUMBER : COADD8738
| | |
* 60978002 08/06/02 LICENSE PLATE | $30 $30 $0 $0 $60| |
| | |
12:02P 1419 19TH ST. | |$_____________|
| | |
# OF TICKETS : 1 DUE : $60 | |______________|
====================================================================================================================================
PLATE NUMBER : COADK7423
| | |
* 86755233 01/08/02 LICENSE PLATE | $30 $30 $0 $0 $60| |
| | |
09:29A 1054 DOWNING ST. | |$_____________|
| | |
__________________________________________|________________________|______________|
| | |

| | |
* 102284136 03/02/06 LICENSE PLATE | $30 $30 $0 $0 $60| |
| | |
11:43A 4200 HALE PKWY E | |$_____________|
| | |
# OF TICKETS : 1 DUE : $60 | |______________|
====================================================================================================================================
PROGRAM: DVRNTC26 XXXXXX & LOCAL SOLUTIONS, INC RUNDATE: 08/04/2013
RUNTIME: 07:35:34
CLIENT : XXXXXXXXXXXX XXXXXXXXXXXXXXXXXXX OPEN CITATION REPORT - R410C PAGE NO: 4
REPORT DATE: 08/04/2013 AGENCY PAGE NO: 4 DUE DATE: 09/15/2013
AGENCY NO : 020000003 AGENCY NAME : XXXXXX CORPORATION
===================================================================================================================================
TICKET-NO DATE VIOLATION DESCRIPTION FINE/ PEN/ RED/ PAID DUE PAYMENT
TIME LOCATION ($)
PLATE NUMBER : CO209XWM
| | |
* 149585671 06/10/13 LICENSE PLATE | $75 $75 $0 $0 $150| |
| | |
06:32P 104 STEELE ST | |$_____________|
| | |
# OF TICKETS : 1 DUE : $150 | |______________|
====================================================================================================================================
PLATE NUMBER : CO255MPR
| | |
* 111306215 10/30/07 LICENSE PLATE | $30 $30 $0 $0 $60| |
| | |
09:38A 1447 CHERRY ST N | |$_____________|
| | |
# OF TICKETS : 1 DUE : $60 | |______________|
====================================================================================================================================
* 106315462 11/13/06 LICENSE PLATE | $30 $30 $0 $0 $60| |
| | |
10:49A 5143 ENID WAY N | |$_____________|
| | |
__________________________________________|________________________|______________|
| | |
* 106879054 02/21/07 LICENSE PLATE | $30 $30 $0 $0 $60| |
| | |
11:20A 5143 ENID WAY N | |$_____________|
| | |
__________________________________________|________________________|______________|

AGENCY : 020000003 NO. OF PLATES : 59 NO. OF TICKETS : 72 AMOUNT DUE : $5,215

(RETURNED) # OF PAYMENTS : ______, TOTAL PAYMENT : $_______

Robberbaron (robr)

1. should have been able to save your section in a text file and then upload that. Otherwise, paste it into the question as a 'code snippet' as that keeps formatting.

2. having said that, when i save it into a notepad file, it does make sense.... there are actually 7 lines for each ticket section. The page headers make it a bit tricky as expected.

3. ill try to put together a VBA method that puts all info on one row of excel. Please advise if there is a special format needed.

Robberbaron (robr)

try this out.

I had to make a fix to your sample data as there appears to be an option for multiple tickets for a single plate, and the data wasn't consistent.

note i have hardcoded the licticket.txt filename and folder. you will need to change these.
lictickets.txt
ticket-summary.xls

rhadash

ASKER

Hi, Thanks for response. Its working pretty good but there is one issue when there are mulitple tickets one plate with a header section in between the tickets. How often does it happen? Probably not often but it happens. I've pasted below. There was not an instance like this in the original sample I posted - sorry about that. if I were smarter I might be able to figure it out :).

PLATE NUMBER : CO756BLF
| | |
* 106315462 11/13/06 LICENSE PLATE | $30 $30 $0 $0 $60| |
| | |
10:49A 5143 ENID WAY N | |$_____________|
| | |
__________________________________________|________________________|______________|
| | |
* 106879054 02/21/07 LICENSE PLATE | $30 $30 $0 $0 $60| |
| | |
11:20A 5143 ENID WAY N | |$_____________|
| | |
__________________________________________|________________________|______________|
PROGRAM: DVRNTC26 XEROX STATE & LOCAL SOLUTIONS, INC RUNDATE: 08/04/2013
RUNTIME: 07:35:34
CLIENT : DENVER, COLORADO PARKING VIOLATIONS BUREAU OPEN CITATION REPORT - R410C PAGE NO: 9
REPORT DATE: 08/04/2013 AGENCY PAGE NO: 9 DUE DATE: 09/15/2013
AGENCY NO : 020000003 AGENCY NAME : HERTZ CORPORATION
===================================================================================================================================
TICKET-NO DATE VIOLATION DESCRIPTION FINE/ PEN/ RED/ PAID DUE PAYMENT
TIME LOCATION ($)
PLATE NUMBER : CO756BLF (CONTINUED)
| | |
* 106879054 02/21/07 LICENSE PLATE | $30 $30 $0 $0 $60| |
| | |
11:20A 5143 ENID WAY N | |$_____________|
| | |
__________________________________________|________________________|______________|
| | |
* 900898924 03/26/07 LICENSE PLATE | $30 $30 $0 $0 $60| |
| | |
09:25A E 51ST & N ENID WAY | |$_____________|
| | |
# OF TICKETS : 4 DUE : $240 | |______________|

als315

Try this modification of robberbaron's code
ticket-summary.xls

Robberbaron (robr)

sorry got distracted.

Yes, als315 mod gets over the section of header, but it seems your data repeats after the header with the same ticket number. I doubt you want double rows.

Do we need to check for duplicate ticket numbers and ignore ?

can you search the data input for any other

(CONTINUED)

over a header as I think these are not a simple as we would like.

rhadash

ASKER

Hi,
I donot want to delete duplicate values. The header problem does not happen every file but obviously happens. No other issues to report.
Thanks,

ASKER CERTIFIED SOLUTION

Robberbaron (robr)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rhadash

ASKER

I am sorry about the delayed response. Thank you! I ran the revised code on several files and it looks good. I appreciate your time to work this out.