vba code for sort data from .prn file

[YOGESHP]

Hi all of u all...

I am trying to convert .prn file (which openswith note pad ) to excell file . Each time different .prn file is to be converted to excell ,
how should I write VBA code for this..
I am sorting the data by available sr nos in .prn file which can be from 1 to last (variable) sr no.. viz 1000, sometimes 1207, or 7658
also after sorting the data the garbage /unwanted data should get automatically deleted and only useful sorted data should remain..
Thanks in advance for suggestions...

result file is like this..
Thanks in advance

[LJMetzger]

Hi YOGESHP,

a. Look in the file for repeating patterns.
b. Start of each Consumer is a numerical value in columns 1 thru 8. All other rows do not contain this pattern.
c. Each consumer has 3 data rows (only looks like a BLANK line in between data rows).
d. Stop processing at the 'End of Data' Sentinel.

See the attached file which contains the following code:

Please Login or Register  to view this content.

[LJMetzger]

The repeating patterns look like this:

	A	B	C	D	E	F
4		.prn file	.prn file	.prn file		Excel File Column
5		Row	Start Column	End Column	No. of characters
6	SR NO	1	1	8	8	A
7	Consumer No	1	11	22	12	B
8	MR-ROUTE-SEQ	2	11	22	12	C
9	Consumer Name OLD CONS NO	1	26	73	48	D
10	Address	2	26	73	48	E
11	Tariff	1	75	81	7	F
12	Bill Amt	1	82	90	9	G
13	Due Date	2	82	90	9	H
14	Op Arrs	1	91	99	9	I
15	Op Int	2	91	99	9	J
16	Rcpt Dt	1	102	110	9	K
17	Rcpt Amt	1	111	121	11	L
18	Arrears	2	122	130	9	M
19	mobile no	3	116	130	15	N

[YOGESHP]

Thanks sir for ur valuable time on the issue..
code works perfectly .
Just a reuest to update the line of code .. as desired, if u could get a time to do that,,
Will reply u soon with a little updation required in result file..
Have a great new year ahed.

[LJMetzger]

I'm glad I was able to help. If you post your update request here, I will try to do what you want.

Lewis

[YOGESHP]

Sir,
please find new result file and requested to update as per file..
Thanks and have a nice day..

[LJMetzger]

Hi,

Sorry for the delay, but I fell in love with your project and tried to do everything. This copy implements everything you asked for except for the Pivot Table. I had to delete the Pivot Sheet from the file, because my Excel file crashed when the Pivot Sheet was present. I am still working on using the Pivot Sheet.

Please note, that I have had chronic problems with the file being corrupted when trying to save. Please let me know if you come across the same behavior. The file I uploaded seemed to be stable at the time of uploading.

NOTE: Sheet 'SECTION" in your file seems to have duplicate values in Column 'A' on rows 31 and 39.

The following capabilities are included in the attached file in addition to all the items you requested:
a. User selection of .PRT folder and file name
b. Filter on any item (including 'Rcpt Dt' aging [either days or months])
c. Ability to exclude any field from Sheet 'Filtered Results'.
d. Ability to Sort using up to 6 'Sort Keys' with any combination of 'Ascending' or 'Descending' sorts.
e. Ability to control Column Widths on Sheet 'Control'.

Sheet 'Control' in the file is the 'Main Sheet' where all selections are made. There are instructions at the bottom of Sheet 'Control' if you need them.

Sheets used:
Control - Contains CommandButtons and data to control Output on Sheet 'Filtered Results'.
Lists - Contains lists of saved Configuration Data
Results - Raw Data Output
Filtered Results - Filtered Data Output
STAFF - Used As is. Read Only. Used to obtain Staff Names from DTC Code
SECTION - Used As Is. Read Only. Used to obtain Section Names from Section Numbers
Pivot - The Pivot Table Range is adjusted each time data is written to Sheet 'Results'.


I am actively working on the following features:
a. Saved Lists
b. Printing
c. Protecting Cells that do not change on Sheet 'Control'
d. Pivot Table

It will take me at least a two weeks to finish everything on my list. Please let me know if you have any problems, questions, corrections, and/or additional requests.

Lewis

[YOGESHP]

thanks for reply, I will reply soon.. just getting/understanding the new reply file ..
Thanks for amazing control file and efforts taken sir..

[JBeaucaire]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[YOGESHP]

Sir,
Query from ur side
What size paper do you use 8.5 x 11 , A4(8.27 x 11.69) or other (dimensions in inches)? A4
Which Sheet does the Pivot Table Use - 'Results' or 'Filtered Results'. Filtered Results

Regarding data crushing / error while opening sheet "Pivot" in my old result file … is not seen on my PC.. Sheet opens smoothly

observations while running code
1.. blank data in payable (formerly arrears) while processing .PRT file to put data on on sheet "results"… Due to this blank values in sheet "Filtered Results" found nil even if the comparator set for values in payable/arraers results in "0 data rows found"
2.. Yellow input box for running code named "Put Data on sheet "Filtered Results" and sort and filter the data " and Yellow input box for running code named "Put Data on sheet "Filtered Results" only " both runs but error message is displayed as Run time error"-2147417848(80010108) Method horizontal alignment of object" Text frame Failed,,
3. If by mistake date is entered in cell E 44 and E45 in sheet named "Control" , the number format is shifted to date format and further entries of number/value shifts as date formats..

Addendums to sheet "Results" for new additional data headers
1> PC( value is always from 1 to 8 ),
2>MeterNo: (spiltted in two parts : 3 digits & 8 digits) i.e. two separate header column named as "Meter make code" & "Meter no,, "-" in between 3&8 digits omitted.
3>in sheet "Results",under the "pole no" header data column , TD (status flag which is set for consumer with indicates as "Temporarily Disconnected")is occuring which is to be put under separate head named "TD" and coroesponding status to be shown for all data rows, if written TD then value in cell as TD or blank..
4>"Effective Payable" .. If "Due Date" has passed away on run date of processing .PRT file to put data on sheet "Results" then the value coroesponding should be sum of "Bill amount" & "Payable", else value coroesponding should be value in "Payable"
5> "Remarks" heder underwhich mannualy action taken will be written,, all cells will blank for mannaul writing.. Which can be put at the last column header

Addendum to Sheet"Filtered Results"…
1>Section wise list generation.. E.g, If asked for "Raver U" only data pertaining to this section should be shown in "filtered Results"
2>Staff wise list Generation.. E.g. , If asked for "bhushan subhashsinh pardeshi" only data pertaing to this staff should be shown in "filtered Results"
3>Tarrif wise list Generation only for tarrifs, various tarrif are available out of which LT -II A,LT-II B,LT-VA,LT-VB list is often needed,, other tarrif very rare ..
ailable tarrifs LT- I A,LT -I B,LT -II A,LT- II B,LT- III,LT- IV A,LT- IV B,LT- IV C,LT- IV D,LT- IV E,LT- V A,LT- V B,LT- VI,LT- VII A,LT- VII B,LT- VIII,LT- IX,LT- X A,LT- X B,HT- I,HT- II,HT- III,HT- IV,HT- V,HT- VI,HT- VII A,HT- VII B,HT- IX A,HT- IX B,HT- X,HT- XI,
4> Auto "all bordering" to be done for cells having values or data and also for all cells under last column header "Remarks"
5> Pivot table should automatically refreshed /updated as per the sheet "Filtered Results" and should contain tarrif in report filter, section and staff in Row lables ,count lables contain count of consumer no and sum of payable

Printing prefrences..
1> Staff name e.g. "Bhushan Subhashsing Pardeshi" should come in every page printed.., alongwith statistics of count of consumers and effective payable sum of all consumers ..
2> Heder rows to be repetative per page,,
"3> LT_DDL_REPORT date and header named as in .PRT file like "" List of Consumers in Arrears between Rs. 100 and 99999999 RCI CATEGORY"" should also be added

"
4> enable for columns to exclude/hide in print formatting..

IMP… the data in sheet named "SECTION "& "STAFF" can change , can get addition of data, deletion of data,,
Thanks for help in advance , have a nice day!

[LJMetzger]

Hi,

Thanks for your detailed response. I will work on your requests along with the other items I told you I would be working on. I have a few questions based on your response:

Results 4>"Effective Payable". If "Due Date" has passed away on run date of processing .PRT file to put data on sheet "Results" then the value corresponding should be sum of "Bill amount" & "Payable", else value corresponding should be value in "Payable"

Do you want an additional column on Sheet 'Results' called 'Effective Payable' or do you want the proper value to be in Column 'Payable'?
----------

Filtered Results 3> Tariff wise list Generation only for tariffs, various tariff are available out of which LT -II A,LT-II B,LT-VA,LT-VB list is often needed,, other tariff very rare ..
ailable tarrifs LT- I A,LT -I B,LT -II A,LT- II B,LT- III,LT- IV A,LT- IV B,LT- IV C,LT- IV D,LT- IV E,LT- V A,LT- V B,LT- VI,LT- VII A,LT- VII B,LT- VIII,LT- IX,LT- X A,LT- X B,HT- I,HT- II,HT- III,HT- IV,HT- V,HT- VI,HT- VII A,HT- VII B,HT- IX A,HT- IX B,HT- X,HT- XI,

I'm not sure I understand. Do you want to be able to select one or more specific items from the list?

If the answer is yes, do you want 'LT -II A,LT-II B,LT-VA,LT-VB' at the top of the list?
----------

Filtered Results 4> Auto "all bordering" to be done for cells having values or data and also for all cells under last column header "Remarks"

I do not understand. You may have to provide an example.

------------

Additional Questions/Items:

a. Please let me know what order you want for the columns on Sheet 'Results'.

b. Please let me know what 'Original Order' you want for the columns on Sheet 'Filtered Results'.

c. The Meter No. code has formats like ' 22-50029436' (leading space) and '022-50027581' (leading zero). Do you want the Meter code to be '22' or '022'?

[YOGESHP]

Sir,
thanks for response,
reply to ur queries
1. Do you want an additional column on Sheet 'Results' called 'Effective Payable' or do you want the proper value to be in Column 'Payable'?.. "additional column needed
2. Do you want to be able to select one or more specific items from the list?

If the answer is yes, do you want 'LT -II A,LT-II B,LT-VA,LT-VB' at the top of the list?....
sir I want result in sheet" filtered results" by input command in control sheet which will generate list of consumers whose tarrifs are mentioned as "LT -II A,LT-II B,LT- VA,LT-VB", name in control sheet may be taken as "list of RCI category consumers" for box.
3. Filtered Results 4> Auto "all bordering" to be done for cells having values or data and also for all cells under last column header "Remarks"
I just want to apply borders to all data cells.. Icon found in home tab.. square box besides fill colour option..

Additional Questions/Items:

a. Please let me know what order you want for the columns on Sheet 'Results'. sr no,PC,section,staff,consu,consumer no,MR-ROUTE-SEQ,DTC
,Consumer Name OLD CONS NO,Address,Meter Make Code, Meter no,Tarrif,Bill Amt,Due Date,Op Arrs, Op Int,Rcpt Dt,Pole no,Rcpt Amt,Payable,Effective Payable,Mobile no,Email,TD status,Remarks
b. Please let me know what 'Original Order' you want for the columns on Sheet 'Filtered Results'.sr no,consumer no,DTC,Consumer Name OLD CONS NO,Adress,Tarrif,Bill Amt,Rcpt Dt,Effective Payable,mobile No,Remarks

c. The Meter No. code has formats like ' 22-50029436' (leading space) and '022-50027581' (leading zero). Do you want the Meter code to be '22' or '022'?
as 22..
I tried my best to convey you what I want and hope that u got my query rightly.. Also I have little question to ask .. for those cells where value has no data in heder named rcpt dt, it indicates to me that consumer has not paid money from date of connection. as the rcpt dt is blank.. . So whether the care of blank data is taken for "Rcpt Dt (aging Days)" & "Rcpt Dt (aging Months)" & Rcpt dt in column heder Parameter name(Print order) in control sheet, Also I think as the date of connection is not mentioned in the .PRT file it may not possible to generate list of consumers whose rcpt dt value is "blank"... Its not needed, but just asking for intrest..
I am amazed to see what u can do with VBA codes..
Thanks for ur helpful efforts and logical analysis of input data which probably is beyond my scope with a little knowledge of VBA..

[LJMetzger]

Hi,

Try the attached file. It has most of the things you requested.

Items I am still working on include:
a. Subtotals
b. Protect Unused cells in Worksheet
c. Correct Errors

The following anomalies/errors exist which I am trying to correct:
a. Autofilter a Column with 'Discrete values', with some values in the column BLANK. When looking for
one specific value, a BLANK item shows up as the last item.

b. Current Print Limitation - 1st 4 columns CAN NOT BE Excluded from Printing

[YOGESHP]

sir, thanks for valuable reply..
u have reduced my cumbersome processess and helped me to save time for this works..
Thanks and waiting for final result .
Have a nice day ahed

[YOGESHP]

sir, please remove effective payable from parse text file as the wrong effective payable is seen for some of the events like for receipt date after due date ..
I will use simple excell formula to do that ..
also make code and meter no is missing for some consumers,, please try to rectify..
Thanks for help

[LJMetzger]

please remove effective payable from parse text file as the wrong effective payable is seen for some of the events like for receipt date after due date

OK. I will leave it in, if you tell me what the rules are to correct the errors.

make code and meter no is missing for some consumers,, please try to rectify..

OK.

[LJMetzger]

Hi,

I think I corrected all known issues and enhancement requests. Since there the workbook does lots of things, there may be some items one or both of us overlooked. Please let me know if you have any problems, questions, or additional requests.

The workbook is currently set to read only 2000 data lines for two reasons:
a. To be able to upload the workbook to ExcelForum
b. For ease of testing

There is a User Option you can change to read the entire .prt file which is explained each time only 2000 lines is processed.

Lewis

NOTE: For anyone wanting to use this file, the .zip data file from post #1 in this thread is required.

[YOGESHP]

thanks for help sir,
will reply soon after data interpretation and understanding of codes
Have a nice day

[YOGESHP]

Sir, as march ending is in course,, need some time to evaluate ,, please bear the time lapsed..