Extract and Compile data from two text files

[seema]

Hi All,
I have just joined, and am struggling with the following:
Need to extract and compare data from two text files. Data in these files is separated with pipes. I only need to extract relevant data and while comparing; I need to take out the difference between two data points. The problem that I am facing is that the pipes are not separated uniformly. And I am finding it difficult to extract the relevant data.

I need to do the following steps:

1. Extract relevant data from both text files.
2. Transfer the data to another output file in text format (since the record exceed 100K).
3. Take out difference of values between two data points
4. Prepare a summary.

Data in File 1.

14|FN|AAA|4|3.14|2||1||+1|1800|S|A|1-1974||NA|NA|BSSM|MANAGER

10|VIN|PPP|4|3.0|46||||162|22|AD|D|1-1970||NA|NA|AEBS|BRAN



Need “14” ,“AAA” ,“4”,”3.14”, “BSSM” data points from line 1 in file 1.

Need “10”,“PPP”, “4”, “3.0” , “AEBS” data points from line 2 in file 1.

Data in File 2
Would have similar records as file 1.

Extract : BSSM and AEBS data points are employee names , 3.14 and 3.0 are amounts. 14 and 10 are unique identifiers.

BSSM and AEBS is hypothec data and can vary, but the character length is fixed.
Unique identifier is hypothec data and can vary, by the length is fixed.

I need the difference of amounts in file 1 and 2 for respective employees against their unique identifiers in another text file.

This I want to do a subtotal in excel. The text files have more that 100,000 records in each.

Pls assist.

Thanks

[ChrisMattock]

First things first, if you select Data, Import External Data, then select your file and set "|" as the character to break the columns you will have your data formatted nicely. Then you can play around with it.

This has been posted twice, can you delete your other post please.

[stanleydgromjr]

seema,

This may help you.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Press and hold down the 'ALT' key, and press the 'F11' key.

On the 'Insert' menu, click 'Module'.

Copy the below code, and paste it into the Module.

Please Login or Register  to view this content.

Then run the "ImportText_file" macro.


Have a great day,
Stan

[seema]

Dear Stanley,
Thanks for your response. I had seen this code earlier, it helps breaking the file into various sheets but it picks up all the irrelavant data along. The import takes too much of time. Which is why i was wondering if there could be a way by which i could write only the relevant data to another text file and do the compare there itself. Post which file can be imported to excel when it becomes short and sweet.
Thanks ..Seema

Dear Chris - Your option can help me to get rid of the unwanted rows..but still lot of junk data gets uploaded and takes ages to get imported.

Thanks ..Seema

[seema]

can any body help in this please?