Change a comma separated text file and save it.

Hello All,

I need some help on comma separated text files, changing them and
saving them.


I have got two text files.


The first file "ES_EF_AIG_20141440_105708_d.txt" has employee payment
record exactly like below:
"C0000000519","","","MR STEPHEN AGUTTER","10 COLCOKES
ROAD","BANSTEAD","SURREY","","","","","","SM7
2EW","MAL","AGUTTER","NS686159A","0000153363","CDN","E","EMPLOYEE","29/10/2=
=AD001","","","","","","","","","AIG

EUROPE (UK) LTD","AIG CLAIMS
EXECUTIVE","","","","","","","","EEE","","GBR","GBR","","","SSS","23B","200=
=AD30601ES","01/06/2003","01/06/2006",355,+00000942525,0026550000,000000210=
8,0=AD0025000,00000850000,034,000,0,0,0,,"",,"",,"",,"",,"",,"",,"",,"",,""=
,,"",,=AD"",,"",,"",,"",,"",,"",,"",,"",,"",,"",0026550000,"",,,""



The main fields in this record for me are:
1st field - "C0000000519"
54th field - 00025000
55th field - 00000850000
56th field - 034


The second file "ES_FE_AIG_20143416_142901_d.txt" is a payment file
with record exactly like below:
"C0000000519","","","NS686159A","0000153363","CDN","E","EMPLOYEE","29/10/20=
=AD01","","","","","","","","AIG

EUROPE (UK) LTD","AIG CLAIMS
EXECUTIVE","","","","","","","","EEE","","GBR","GBR","","","SSS","23B","200=
=AD30601ES","01/06/2003","01/06/2006",0000002108,"

250.00","
8500.00",034,000,1208,10185844,"T",00001,"01/03/2006","ESPAY",001,034,"

250.00"," 0.00","","","","","CONTRIBSYS","
0=2E00000000",""


Once again the main fields in this record for me are:
1st field - ("C0000000519")
50th field - " 250.00"


My task is to open the first file and, if there is a corresponding
record in the second file, then update the first file by removing one
payment and decrementing the total no. of payments by 1. In the above
example I want to basically change the 55th and 56th fields to
00000825000 and 033 respectively and save the file in the same format
(that is a csv text file).


I worked manually for the last two days on 60 files that are small
(with upto 20 records). Now there are exactly 60 files that have around

1000 records each.


Can someone please guide me in writing a code or please supply code for

the above. I tried opening both these files from Excel and then I am
haqving problems while saving them. I cannot tamper with the format of
the files as they need to be uploaded onto my system.


I hope I have explained the problem. The solution might be simple but I

am not getting it. Moreover I don't want to mess up the data. Therefore

please someone help me.


Hoping to get an early reply.=20


Thanks & Regards,=20
Prasad

Open the file in excel
Save the file in another name
Now,
Go to DATA menu
Select TEXT TO COLUMNS
Select the DELIMITED options
Under the DELIMITERS options,select TAB and COMMA and click FINISH
A message box would appear if any value is present in the next cell
prompting to overwrite.Click YES
Now you have your data organized in columns
Delete the columns you don't want and format in your style

Under the DELIMITERS options you can also enter other characters if any
to delimit.I hope this could be sufficient

If you like this solution, you may also like
http://groups.google.co.in/group/Ans..._thread/threa=
d/946502c223298627
http://groups.google.co.in/group/Ans..._thread/threa=
d/f31ad015c37603ae


For more,post your questions on
http://groups.google.co.in/group/answers-for-everything
or e-mail to
[email protected]

BristolBreeze wrote:
> Hello All,
>
> I need some help on comma separated text files, changing them and
> saving them.
>
>
> I have got two text files.
>
>
> The first file "ES_EF_AIG_20141440_105708_d.txt" has employee payment
> record exactly like below:
> "C0000000519","","","MR STEPHEN AGUTTER","10 COLCOKES
> ROAD","BANSTEAD","SURREY","","","","","","SM7
> 2EW","MAL","AGUTTER","NS686159A","0000153363","CDN","E","EMPLOYEE","29/10=
/2=AD001","","","","","","","","","AIG
>
> EUROPE (UK) LTD","AIG CLAIMS
> EXECUTIVE","","","","","","","","EEE","","GBR","GBR","","","SSS","23B","2=
00=AD30601ES","01/06/2003","01/06/2006",355,+00000942525,0026550000,0000002=
108,0=AD0025000,00000850000,034,000,0,0,0,,"",,"",,"",,"",,"",,"",,"",,"",,=
"",,"",,=AD"",,"",,"",,"",,"",,"",,"",,"",,"",,"",0026550000,"",,,""
>
>
>
> The main fields in this record for me are:
> 1st field - "C0000000519"
> 54th field - 00025000
> 55th field - 00000850000
> 56th field - 034
>
>
> The second file "ES_FE_AIG_20143416_142901_d.txt" is a payment file
> with record exactly like below:
> "C0000000519","","","NS686159A","0000153363","CDN","E","EMPLOYEE","29/10/=
20=AD01","","","","","","","","AIG
>
> EUROPE (UK) LTD","AIG CLAIMS
> EXECUTIVE","","","","","","","","EEE","","GBR","GBR","","","SSS","23B","2=
00=AD30601ES","01/06/2003","01/06/2006",0000002108,"
>
> 250.00","
> 8500.00",034,000,1208,10185844,"T",00001,"01/03/2006","ESPAY",001,034,"
>
> 250.00"," 0.00","","","","","CONTRIBSYS","
> 0.00000000",""
>
>
> Once again the main fields in this record for me are:
> 1st field - ("C0000000519")
> 50th field - " 250.00"
>
>
> My task is to open the first file and, if there is a corresponding
> record in the second file, then update the first file by removing one
> payment and decrementing the total no. of payments by 1. In the above
> example I want to basically change the 55th and 56th fields to
> 00000825000 and 033 respectively and save the file in the same format
> (that is a csv text file).
>
>
> I worked manually for the last two days on 60 files that are small
> (with upto 20 records). Now there are exactly 60 files that have around
>
> 1000 records each.
>
>
> Can someone please guide me in writing a code or please supply code for
>
> the above. I tried opening both these files from Excel and then I am
> haqving problems while saving them. I cannot tamper with the format of
> the files as they need to be uploaded onto my system.
>
>
> I hope I have explained the problem. The solution might be simple but I
>
> am not getting it. Moreover I don't want to mess up the data. Therefore
>
> please someone help me.
>
>=20
> Hoping to get an early reply.=20
>=20
>=20
> Thanks & Regards,=20
> Prasad

Raman,

I am sorry that doesn't work for me. Please read my post again. I want
to edit and update the first comma separated text file if it has a
corresponding record in the second comma separated text file. The
solution you provided allows me to view the file fields in columns but
that is not what I want.

Thanks & Regards,
Prasad