Help with comparing 2 txt files

[dreamscapeuk]

Hi,

New to VBScripting and hope someone can help. I have 2 text files from different sources and the idea is to automate the validation for them. Basically I need to read both files line by line and match the values for each account. Sounds simple but there's a twist and that's where I am stuck on. As the data is pulled on a server without office I can't simply use VBA to clean, sort and tie them up. It needs to be done without installing anything on the server.

The format of the files is not perfect i.e accounts are not always in the same order and values are in different places. Here's what I mean:

File 1 e.g
Account QTR YTD
10000 20,000 50,000.79
20000 57.00 250.99
30000 0 750.00
40000 1,500.00 2,500.00

File 2 e.g

Account QTR YTD
10000 Revenue 20,000.00 50,000.79
20000 Opex 5,700.00 27,250.99
40000 Other Account 1,500.00 2,500.00
30000 COGS 0 30,000.00

As you can see there are several issues here. Values are different, accounts not necesarily in the right order and the formatting doesn't seem to allow for any logical steps. I'm sure it's possible but am totally new to VBScripting so please help!

 

[teklan]

What are the text files to be validated against?

Are there kind of columns in the text files? File1 looks to me like a table but File 2 confuses me with the terms Revenue, Opex, etc..

 

[macropod]

Hi dreamscapeuk,

If the data are space-padded or have tabs separating the fields, you could do the analysis fairly easily in Excel. Simply load the data onto two sheets, sort the data (not absolutely necessary), then do the comparisons. All this could be automated fairly easily.

Cheers
Paul Edstein
[MS MVP - Word]

 

[dreamscapeuk]

Thanks for your responses. The text files are being validated against each other. Both are tables in essence but table 2 has the account descriptions next to the account.

Unfortunately the server we are performing this on does not have office otherwise it would be an easy solve with vba. I'm not getting much luck myself or a good response on this and focussed more on output format from the other sources. Here's what I have now:

File 1:

Account QTR YTD
10000 20,000 50,000.79
20000 57.00 250.99
30000 0 750.00
40000 1,500.00 2,500.00

File 2:

Account QTR YTD
10000 20,000 50,000.79
30000 0 600.00
40000 1,500.00 2,500.00


Both are in tables with slighly varied formats. Both files will have the accounts in numerical list so should be in order but I want to identify when an account is missing from one of them i.e a/c 20000 in file 2. Also I want to identify when the values are different i.e a/c 30000 YTD in file 2.

I think it's a case of reading them into an array and then looking through the 2nd file and matching them up. The main concern for me is reading the values and tying them up.

Any help?

Many thanks in advance.

 

[macropod]

Hi dreamscapeuk,

Unfortunately the server we are performing this on does not have office otherwise it would be an easy solve with vba.

Yes, but does it have to be done on that server instead of somewhere else? Cannot the data be analysed elsewhere? And, if all the data reside on the same server, can't the app(s) generating the data do the analysis?

Cheers
Paul Edstein
[MS MVP - Word]

 

[dreamscapeuk]

Hey Paul,

for efficiency purposes if one vbscript can pull data from 2 sources, then validate the data and send an email, this would be the ideal solution. It's one job on one server that needs to be run and maintained. Running this from Excel from another server means relying on another resource, maintaining that resource and another process.

Thanks

 

[teklan]

Both are in tables with slighly varied formats. Both files will have the accounts in numerical list so should be in order but I want to identify when an account is missing from one of them i.e a/c 20000 in file 2. Also I want to identify when the values are different i.e a/c 30000 YTD in file 2.

How about using the cmd-command "fc" to compare the two files? "filecompare" is similar to "diff" on Linux and comes with options concerning line numbers, case sensitivity, tabs, whitespaces etc..

That means you could use vbscript to execute "fc", redirect the "fc"-output and depending on the content of the output you could init further processing.