Changing a comma separated text file and save it.

[BristolBreeze]

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­001","","","","","","","","","AIG

EUROPE (UK) LTD","AIG CLAIMS
EXECUTIVE","","","","","","","","EEE","","GBR","GBR","","","SSS","23B","200­30601ES","01/06/2003","01/06/2006",355,+00000942525,0026550000,0000002108,0­0025000,00000850000,034,000,0,0,0,,"",,"",,"",,"",,"",,"",,"",,"",,"",,"",,­"",,"",,"",,"",,"",,"",,"",,"",,"",,"",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­01","","","","","","","","AIG

EUROPE (UK) LTD","AIG CLAIMS
EXECUTIVE","","","","","","","","EEE","","GBR","GBR","","","SSS","23B","200­30601ES","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.


Hoping to get an early reply.


Thanks & Regards,
Prasad

 

[pras1973]

Hi Prasad

This is Prasad here too. One way of solving this problem is by writing
code. One other way to do it is as follows.

Open the sheets in xls or paste the txt files to excel. After that use
the function <<Data (on the toolbar)<<Text to
columns<<Delimited<<comma>> as an option and all your data will be
seperated into differtent columns. Repeat this activity for the second
sheet.

After that you can use the Vlookup function to get the data you want.
Excel help will tell you how to use Vlookup.

Best
Prasad

 

[BristolBreeze]

Thanks yaar,

I am not sure whether you received my reply. So sending it again.

See, I have no problem in opening those files in Excel and get all the
data in column format. My basic worry is to, after doing necessary
changes, save the file back in the same format. This is very important
for me. If not the application here won't upload the file.

Regards,
Prasad

 

[Pete_UK]

Have you tried to use File | Save As and checking the file type panel
at the bottom that CSV file is selected? You can give it a different
name so that you can then check if it is in the format you require.

Hope this helps.

Pete

 

[BristolBreeze]

Sorry Pete, your option almost did everything. I could change the two
numeric fields I wanted to but it's still causing problems with the
character fields. I tried the following two ways:

1. While opening the file I opened with delimited option and selected
comma and tab and also specified the text qualifier as ". Then the file
opened in column format. Did the changes to the necessary fields. While
saving I selected the csv format and saved it. What it has done is for
every character field it put 3 double quotes in place of one.

For eg., "PETE" became """PETE"""

2. While opening the file I opened with delimited option and selected
comma and tab and specified the text qualifier as {none}. Then the file
opened in column format. Did the changes to the necessary fields. While
saving I selected the csv format and saved it. In this case it removed
the double quotes for all character field.

For eg., "PETE" became """PETE"""


I need a way that would allow me to change the numeric fields and also
doesn't tamper with the format of the character fields.


I have no idea who to get this done.

Thanks for your help anyways,
Prasad

 

[Pete_UK]

If you have almost what you want, then you could get rid of the triple
quotes by opening the derived CSV file in Wordpad and doing a Find and
Replace, searching for """ and replacing with ". Then save the file
with the same name and see if this is now in the correct format.

Hope this helps.

Pete