Extract specific text from a text file (Excel 2003)

Topic

New Reply

Hi all

I have some text files as attached, I need to extract certain values, namely Name, Account No, total amount
under Portfolio Value in the text file and copy these values to a worksheet under column: Name, Account and Portfolio Value
respectively.
I am not sure if it would be better to import these text files into excel and then find and copy the value to a new worksheet
or it is possible to read the required text directly to a worksheet.

Some of the text files may not have any data in it or may not have the Portfolio Value as the monthly transactions
may occupied 2 or 3 text files, while some others files may consist of 2 Names separately in it and hence there will be 2 separate
Total amounts under each Portfolio Values.

I have attached both the Excel file which shows the result and the dummy txt file.

Thank in advance for your assistance.

regards, francis

Reply | Quote

Replies

The text file is a report that has been exported from a database. You should ask the person who creates the export to export data in a table format instead, to a text file or perhaps even as a spreadsheet in exactly the format that you want

Reply | Quote

Hi Hans

We have tried getting the the central team to provide us in a table format but after two weeks of following up with them,
they have responded its can’t be rerun under live production environment.
Should this be import to Excel first and then find the respectives value and copy to a new sheet or this need to be ready directly into Excel
Apology for my asking as I have not encounter this type of text file before, the ones that I have experience with are mostly able to import into
excel with rows and columns aligned nicely.

Appreciate your help in this

regards, francis

Reply | Quote

The response from your central team is nonsense, it arises from laziness or incompetence. You should not let them get away with it.

The data are stored in a structured table format in the database, and you want to have them in a structured table format in your spreadsheet. It is ridiculous to spend time on reconstructing a usable format from a plain text export in an unusable format.

Reply | Quote

Hi Hans

We can’t do anything about it for now even though we knew about it was just an excuses because we are
the party who have fogotten to upload the correct data initially.

I have managed to find some sipplet of codes and have created a macro that would be doing what I want, but it doesn’t seem
to be working properly. Would you have a look the the macro and advise.

I have attached somes files and the result, those in red doesn’t appeared after running the macro. They are
suppose to be appearing as these are with the Portfolio Value. I hope it was just a minor bug as I need this to
produce the report and submit to head office by tomorrow

TIA

regards, francis

Reply | Quote

Hans wasn’t on line last time I looked. Try the attached.
H.T.H.

Reply | Quote

Further to my previous post;

• Don’t forget to revise (myPath = “????”)
• If the order of file processing is important to you, it will be necessary to place the filenames into an array; sort the array; then run the “DoTheWork” procedure for each element of the sorted array.
  [/list]

Reply | Quote

Hi Don

Thanks for assisting in this. but its still doesn’t do what I expected. Not all have been extracted.
One more issue is that I will need to resave my original file to .txt and I am not sure why. These
file are open by Notepad but when I run the macro, it doesn’t extract any data and shows “no files found”
Is there another format that is like Notepad?

regards, francis

Reply | Quote

It sounds to me that you as if you have not revised the “MyPath = ” line.

Reply | Quote

You have to save as *.txt files since you coded the macro to look for these types of files, by using the line:

myFile = Dir(myPath & “*.txt”)

The macro could look for any extension if you chose to change the extension…

Steve

Reply | Quote

Hi Don and Steve

I have change My Path and it seem to be working except that I notice that the spacing of the Name in the text file have different number of spaces.
Some lines have 11 spacing while some have 13 spaces. If the code is hard code to 11 spaces as Str1 = LCase(“Name :”), it does not read
all Name into Excel and if this is hard code to 13 spaces, column Value shows **error** when it should have shows the value amount.

Is it possible not to hard code Str1 so that the code can extract the Name regardless any number of spaces.

How do I test what file format this is? I have look at its properties but it doesn’t show me the file type.

regards, francis

Reply | Quote

Coming up

Reply | Quote

The attached file has the modified code. You will need to ensure that the path identified by the variable myPath agrees with the actual path you are using.

Reply | Quote

You could send your spreadsheet to the central team and ask them to complete it and return it to you within 2 working days.
They could always type in the data manually…

Reply | Quote

Further to Hans’ response.
Your Central Team may not have access to the information you require in the live environment, but they should be able to retrieve it from their backup system.

Reply | Quote