Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 16

Thread: Count Total of Records in Text File (.txt)

  1. #1
    Join Date
    13th June 2005
    Posts
    28

    Count Total of Records in Text File (.txt)

    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.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    16th July 2005
    Posts
    40

    Re: Count Total of no of records from txt file

    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.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    13th June 2005
    Posts
    28

    Re: Count Total of no of records from txt file

    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.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    16th June 2005
    Location
    Dublin
    Posts
    4,490

    Re: Count Total of no of records from txt file

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

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    13th June 2005
    Posts
    28

    Re: Count Total of no of records from txt file

    Hi Ger,

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

    regards,
    venu.
    Last edited by venu102; July 22nd, 2005 at 23:53. Reason: corrected

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    22nd July 2005
    Posts
    58

    Re: Count Total of no of records from txt file

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    22nd July 2005
    Posts
    58

    Re: Count Total of no of records from txt file

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    13th June 2005
    Posts
    28

    Re: Count Total of no of records from txt file

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    16th June 2005
    Location
    Dublin
    Posts
    4,490

    Re: Count Total of no of records from txt file

    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

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    13th June 2005
    Posts
    28

    Re: Count Total of no of records from txt file

    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.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 3
    Last Post: May 1st, 2009, 15:37
  2. Total records
    By Metrazal in forum EXCEL HELP
    Replies: 6
    Last Post: March 4th, 2006, 16:53
  3. Count Total of no of records in a sheet
    By excel102 in forum EXCEL HELP
    Replies: 8
    Last Post: February 16th, 2006, 18:28
  4. Total no of different records
    By excel102 in forum EXCEL HELP
    Replies: 8
    Last Post: August 31st, 2005, 01:06

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno