Retrieve data from txt file

[Bob@Sun]

Hello there,

I still do not have my dummy files ready but wanted to start the discussion on Retrieving Specific Data form Txt file.

Later I will post sample files.

For now, I will appreciate any ideas on how can I refer to specific place in the txt file and get the relevant information to excel spreadsheet.

The criteria for searching the text file must be:

1. Look for specific code: ABC
2. right below must be a date: 01/20/2010

3.When those two criteria are met then go down and find the first occurrence of the word: “Test”
4.When you find the word, on the right of this word must find numbers
5.Get those numbers to excel

I am sorry I do not post the sample file. I will do it shortly.

Thanks for the ideas!

[Leith Ross]

Hello Bob@Sun,

I can tell you the easiest way to do this is use the File System Object. This will allow you to read the entire text file into a buffer as a string or line by line with minimal code. Here is the basic code for opening a text file with the FSO.

Please Login or Register  to view this content.

[Bob@Sun]

I am attaching a sample txt file.

Can you please show me what functions I should use to search the text file and get a specific data to excel.

From all the dummy text in the file I need the code to search for the cod: ABC if found...(in this example should find) and if date under the code is 06/01/2010 then, from this stand point start searching for the word "Test". When found, see if there is any numbers on the right of the "Test" word. Then copy the number in excel.

Appreciate your help

[Bob@Sun]

Any Ideas! I would appreciate it.

[Leith Ross]

Hello Bob,

I have tested this macro on your sample file. Provided the real file has the same layout, the macro will work. If not then I'll need to see the original file. Change the file name and path in the macro. If the search terms are different then change those also. The areas to change are in blue.

Please Login or Register  to view this content.

[Bob@Sun]

Hi Leith,

Thank you for working on my file. I will try it will the original file and will get back to you if any problems.

I run the macro with the sample file and it works. Just one thing. How to alter the code so to print the result in cell B3 for example?

[Leith Ross]

Hello Bob,

This will print the results starting at cell "B3" of the active sheet and go down from there. The changes are in blue.

Please Login or Register  to view this content.

[Bob@Sun]

Hi Lieth,

As I need to print not all the occurrences of the code ABC but only the one with the specific date, in this example 06/01/2010 I think that the problem here will be that this date will change every day.

What do you think? How we can go around this issue?

[Bob@Sun]

One more thing Leith,

In my example it is not obvious, but with the real file agains the word that I am searching my have one positive and one negative number, or only one of them. I need the code the check both tabs and get them if any. The code now prints only the first set of numbers.

Thanks!

[Leith Ross]

Hello Bob,

I have made the changes to extract both numbers.

Please Login or Register  to view this content.

[Bob@Sun]

Yes that is perfect.

What abount easily setting different search criteria. I just need to change the date on a daily bases.
The Code ABC will be constant.

[Bob@Sun]

I just thouth that I might just need to set the search date to be = Today - 1 day

Can you please make that?

[Bob@Sun]

And Leith,
The code still prints all Occurrences of the code ABC. There is two search criteria
1. First the code ABC
2. The date below the code: Even if in the txt file ABC occurs more then once, I need the data from the one that has specific Date, which will be Today - 1 day

I hope I explained it well.

[Leith Ross]

Hello Bob,

This version now checks the date against today - 1 day.

Please Login or Register  to view this content.

[Bob@Sun]

Hi Leith, I have tested the code with my original file and something is wrong. It does not give me an error but I am not getting any results in excel.

I told that the structure is the problem so I tried to do another sampel txt file that will be very, almost exactly the same with the original file.

Can you please see what is wrong?

Also I have to change a little the search criteria. What I need to check first is the Key Word in the upper left corner "ABC" to make sure that that is the right file. As you can see
there is some numbers before the Word ABC and I do not care about them.
Second criteria is to check currency: If EUR I need the code to print the result in one cell. What I need as a result is the numbers on the right of "TEST" at the bottom of the Euro section.

If there is another currencies I need their result in another cell.
Can you please help me with that
I am attaching a sample file and I hope it will make things clear.

[Leith Ross]

Hello Bob,

Why is the structure of the first text file and the second one so different? Posting a piece of the original file would be best. Approximations may be close, but close only counts in horseshoes, hand grenades, and atomic weapons.

[Bob@Sun]

Sorry Lieth, It is confidential data so can not post. I just took one original file and replaced all data with sample text..so I bileve the structure is the same.

I realy hope that you can help me with that!

[Bob@Sun]

Leith, Can you please refer me to site or place where I can learn how txt files are structured and how I can work with them...meaning that referring the cells in excel is easy but how you refer to text or number in txt?

[Bob@Sun]

Hi Leith,

Do you think that you can help me with the sample file that I have provided and is the exact structure of the txt file really crucial.

How can I make dummy file with exact same structure as my original file when it comes to txt format?

[Bob@Sun]

I tried to get the logics for working with txt files from all I got from Leith and I ended up with something. It still needs more but for now is ok.

The problem is that the code does not recognize the negative number from the txt file because it is written like this: 234323-

Can you help me with that issue?

I am attaching a sample excel and txt file

Many thanks!

[Leith Ross]

Hello Bob,

It is difficult to help someone who does not disclose details and specifics. You changed the text file and modified the macro code and stated the results were not as expected. You have not provided any information about location of the negative number nor how many numbers are to be parsed. All this wasted time could have been prevented by better preparation. Please do not post impulsively. Take the time to collect your thoughts and questions. Write them down, review them for accuracy and completeness, arrange them in a logical sequence, and then post them. It will save time in the end.

[Bob@Sun]

Ok Leith,

Sorry for that. I will get back to you when I prepare better.

[Leith Ross]

Hello Bob,

Thank you. I'm looking forward to helping you get this done.