Count Total of Records in Text File (.txt)

[venu102]

Hi all,

i am new to VBA programming kindly hlep me regarding this.

i have more than 2 lakh records in txt file
each record is like this
001JP 0010133 0001504612 02.05.1996 04:26:57
001JP 0005257 0001504655 02.05.1996 04:28:04
001GB 207103 0001508366 02.05.1996 12:46:21
001GB 203535 0001508449 02.05.1996 13:01:43
001JP 0010133 0001504612 02.04.1997 04:26:57
001JP 0005257 0001504655 02.06.1997 04:28:04
001JP 207103 0001508366 02.09.1997 12:46:21
001GB 203535 0001508449 02.05.1997 13:01:43
001GB 203535 0001508449 02.06.1997 22:01:03
001JP 207103 0001508366 02.02.1998 12:46:21
001GB 203535 0001508449 22.03.1998 13:01:43
001GB 203535 0001508449 25.04.1998 22:01:03



i want following details of the total count from the above data to be presented in excel.

JP 1996 Total no 2
1997 Total no 3
1998 Total no 1
GB 1996 Total no 2
1997 Total no 2
1998 Total no 1

JP means from the first record 001JP
GB means from the thrid record 001GB

JP and GB are country names.

i want to get total how many times it has been displayed in the txt file.

i am attaching the sample txt file and also the excel file for information.

kindly help me regarding this if possible with vba code or any example.


Thanks in advance,
regards,
venu.

[Robin1981]

Hi!

Open the text document in excel and then use the 'text to columns' tool (which should come up automatically) and check the boxes 'Space' and 'Tab' to convert all the data into columns properly.

Then you need to use a formula to add up all the data:

=SUM(IF(A$1:A$24=G3,IF(VALUE(RIGHT(D$1:D$24,4))=H3,1,0),0))

and hold CTRL+SHIFT when you hit enter!

See the attached for an example.

[venu102]

Hi Robin

Thanks for replying me.

my requirement is like that only

but i have more than 2 lakh records in TXT file how to count from txt file and place the total in excel (since i cannot copy all the records in excel).

i want to do this using vba programme whn a button is clicked because in the txt file some times records will be 1 lakh or above 1 lakh or 2 lakh so i want the same thing to be done in vba programming.

i want the data to be present like in attached file.

kindly look into that attached file and help me out regarding this.

regards,
venu.

[Ger Plante]

forgive my ignorance but what, or how much is a lakh?

[venu102]

Hi Ger,

the records will be around 1,00,000 and above some times equal to 2,00,000

regards,
venu.

[ProjectFocus]

I would be tempted to import the text file into a xls file and read the data for the first part of the line/Cell. Then use Mid function to extract the country code. Then stop the data in a array or couter for the ammount of times each country is mentioned and display the output in the cells at the end.

[ProjectFocus]

Or import the file as a full string into a column for each record to be 1 row. Then take the values for the cells in that column and do as above.

You will need to work on the code as you will need to use a loop to increment the row so that you grab the next row data to increase the counters.

[venu102]

Hi,

Kindly send me a example or code for the above requirement bcause i am new to the vba programming.

thanks in advance,

regards,
venu.

[Ger Plante]

Project Focus, with 2 million lines in the text file, it wouldnt be advisable to try and load/import/copy paste them into Excel...

Venu, can we make any assumptions with regard to the data in the text file - for example, will it be sorted in anyway when it is stored in the text file? The samples you provided below seem to be in ascending order by date... will this always be the case.

How many Country codes are there? This is achievable alright, but it will be tough... I might have time to stick some code together over the weekend.

Will the country code on the left be ALWAYS followed by a space?
Will the country code on the left ALWAYS be two characters?
Will the "Year" always have a space after it?

Finally, will there always be 5 distinct columns of data in the text file (seperated by space(s))

Ger

[venu102]

Hi Ger,

yes all the records were in ascending order and how many country means i cannot say it will be around 100 country codes or more than that.

it would be great help if you could solve this.

have a nice weekend.

if not today, Ger is it possible to send the sample code on monday.

expecting a positive reply from you.

regards,
venu.