troubleshooting Question

Batch Script needed to parse through a data file and find any missing data and correct it.

Avatar of kcatnip9
kcatnip9 asked on
Windows Batch
16 Comments1 Solution121 ViewsLast Modified:
We have an internal process that downloads a text file from our vendor. The format of the information of this file is as follows – followed by a comma.

Name of data file = TEST.IDX

Field names contained in TEST.IDX:

ID Number = 5 numbers
Loan Type = 2 Characters      
Full Name= varies up to 60 Characters
SSN = 9 numbers
App Number = 4 numbers
Collateral Code = 3 numbers
Loan Type = 4 numbers
Document name = Varies up to 60 characters
Internal  ID = 1 number
Date = 10 characters
Download Path = varies up to 100 characters

The issue is that the file TEST.IDX  - could contain a data line for the document name  called { Full Name + Credit Report }  –  that does not contain data in the COLLATERAL CODE field. The script would need to parse through the data file – TEST.IDX and then look for the data line that contains the document name { Full Name + Credit Report }. Once found it looks for a matching ID number listed in the TEST.IDX file. Once found – it would need to find the COLLATERAL CODE field. The collateral code that is found – would need to be copied onto the Collateral Code Filed for the data line that contains the document name  { Full Name + Credit Report }.

For Example. The second line below – contains the Credit Report document name called “ JOHNATHAN WILLIAMS Credit Report”.  The 6th Field in that line – does not contain a  COLLATERAL CODE. It is listed as zero. The script would look for the ID Number “55555” and look at the 6th field to find the COLLATERAL CODE – which in this data line – is 021. Once found it would update 2nd line with this COLLATERAL CODE.  The updated Data file – could be called TEST.UPDATE.idx

55555,VL,JOHNATHAN WILLIAMS,111111111,4444,021,0061,Risk Based Pricing Notice,6,11/08/2017,c:\FTP-Documents\DocDownloader\processed3\VL-4444-6-Risk Based Pricing Notice.PDF

55555,VL,JOHNATHAN WILLIAMS,111111111,4444,0,0061,JOHNATHAN WILLIAMS Credit Report,7,11/08/2017,c:\FTP-Documents\DocDownloader\processed3\VL-4444-7-JOHNATHAN WILLIAMS Credit Report.PDF
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 16 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 16 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros