Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Count Total of Records in Text File (.txt)

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    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

    Comment


    • #3
      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

      Comment


      • #4
        Re: Count Total of no of records from txt file

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

        Check out our new reputation system. Click on the "star" under the post!
        _______________________________________________

        There are 10 types of people in the world. Those that understand Binary and those that dont.

        Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

        The BEST Lookup function of all time

        Dynamic Named Ranges are your bestest friend

        _______________________________________________

        Comment


        • #5
          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, 23:53. Reason: corrected

          Comment


          • #6
            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.

            Comment


            • #7
              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.

              Comment


              • #8
                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.

                Comment


                • #9
                  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

                  Check out our new reputation system. Click on the "star" under the post!
                  _______________________________________________

                  There are 10 types of people in the world. Those that understand Binary and those that dont.

                  Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

                  The BEST Lookup function of all time

                  Dynamic Named Ranges are your bestest friend

                  _______________________________________________

                  Comment


                  • #10
                    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.

                    Comment


                    • #11
                      Re: Count Total of no of records from txt file

                      Yeah with 2 Million lines it would be quite slow to update. Sorry did not see that it will million it looked in the 100 thousands

                      Comment


                      • #12
                        Re: Count Total of no of records from txt file

                        Hi Venu - I will certainly give the code a shot... I have some additional questions in my post above... and I sent you a personal message as well to this forum, check your messages with the links on the left of the screen.

                        Ger

                        Check out our new reputation system. Click on the "star" under the post!
                        _______________________________________________

                        There are 10 types of people in the world. Those that understand Binary and those that dont.

                        Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

                        The BEST Lookup function of all time

                        Dynamic Named Ranges are your bestest friend

                        _______________________________________________

                        Comment


                        • #13
                          Re: Count Total of no of records from txt file

                          Alright Venu,

                          Here we go....

                          Just a note about your request for some VBA to solve your problem. To be honest, my understanding is that VBA is no better than any other language at processing large amounts of strings and parsing data in a specific format. In fact, you would probably be better off with a Perl script or Unix script as it has better inbuilt functionality (for example, "relational expressions" in Perl would be more efficient to parse out the Country code and year from the 2 million records you are expecting in the text file). Even the format with which you requested the results to appear in, didnt really suit a 'spreadsheet ' solution.

                          In any case I wrote a VBA APPLICATION to allow you to extract the data and store it in a more meaningful form in Excel. This will allow you to pull statistics and even draw graphs very quickly. You can use it as often as you want, as the Application Macro is launched from a Command Button. You need to enter the path to the file and the filename itself in cell B1 and click on start.

                          I ran this application on a sample of 65,000 records in a text file and it took about 1 minute to complete and produce the results. This was done interactively with the screen updating all the time. At that rate I would estimate it will take about 30 minutes to process the 2,000,000 records you anticipate. If you chose to run the application in the background (generally recommended), it will run about 4 times quicker, and it should process the 2,000,000 record in around 10 minutes. This of course depends on the spec of the machine in terms of Ghz and RAM and if any other Applications are accessing the hard drive.

                          Without seeing the final text file with the 2,000,000 records, its ALMOST impossible for me to capture 'exceptions' in the data... so while my code did NOT crash for 65,000 records, I really dont know what will happen with 2,000,000.

                          The Following rules must ALWAYS be true for the code to finish successfully:

                          The text file must be sorted by Year (ascending or descending is not important)
                          The year must ALWAY be in the format YYYY.
                          The year must ALWAYS follow by the last period (.YYYY)
                          The country code must ALWAYS be before the first space.
                          The length of the country code does not matter (2,3,4,5 etc characters long).
                          Blank/empty records the in the text file will be ignored.

                          I'm really anxious to find out how the macro performs, and I would dearly love to get my hands on the text file to see if I could tweak the code and code for "all eventualities". I sent you a private message to the forum where I left my email address and I would like you to email the text file to me (.zipped) to allow me to test further.

                          I will be in the office at 8 AM (GMT) Monday morning and I will watch the forum and email.

                          Fingers crossed it will work fine. Let me know if you are having any problems. I'll bump this post to the top again when I get in, in case you dont see it.

                          Ger
                          Attached Files

                          Check out our new reputation system. Click on the "star" under the post!
                          _______________________________________________

                          There are 10 types of people in the world. Those that understand Binary and those that dont.

                          Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

                          The BEST Lookup function of all time

                          Dynamic Named Ranges are your bestest friend

                          _______________________________________________

                          Comment


                          • #14
                            Re: Count Total of no of records from txt file

                            <BUMP> for Venu

                            Check out our new reputation system. Click on the "star" under the post!
                            _______________________________________________

                            There are 10 types of people in the world. Those that understand Binary and those that dont.

                            Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

                            The BEST Lookup function of all time

                            Dynamic Named Ranges are your bestest friend

                            _______________________________________________

                            Comment


                            • #15
                              Re: Count Total of Records in Text File (.txt)

                              Hi Ger Plante,

                              Sorry for not getting back to you until now because i have not seen my private message.

                              I have tried the process record text file.xls.

                              really I think the vba code is fantastic it is full filling my requirement.

                              and to get the result it is taking for me 1 minute 27 sec and in back ground process it is taking 49 seconds and the total no of records are 1,92,000

                              as u said all my records are in ascending order(year)

                              really
                              once again thanks a ton

                              i am sorry to send the file bcause my txt file is more than 40 mb so i could not send you.

                              but it is working fine with out any problem.

                              Thanks & regards,
                              Venu.

                              Comment

                              Trending

                              Collapse

                              There are no results that meet this criteria.

                              Working...
                              X