Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/2001","","","","","","","","","AIG EUROPE (UK) LTD","AIG CLAIMS EXECUTIVE","","","","","","","","EEE","","GBR","GB R","","","SSS","23B","20030601ES","01/06/2003","01/06/2006",355,+00000942525,0026550000,0000002108,00025 000,00000850000,034,000,0,0,0,,"",,"",,"",,"",,"", ,"",,"",,"",,"",,"",,"",,"",,"",,"",,"",,"",,"",," ",,"",,"",0026550000,"",,,"" The main fields in this record for me a 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/2001","","","","","","","","AIG EUROPE (UK) LTD","AIG CLAIMS EXECUTIVE","","","","","","","","EEE","","GBR","GB R","","","SSS","23B","20030601ES","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 a 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change a comma separated text file and save it
I don't think EXCEL will automatically save the file in your format --
their definition of "comma-delimited" isn't the old LOTUS definition. How about writing a small subroutine to create the comma-delimited file yourself? You just have to make sure you have the definition of each of the 56 fields set up -- a simple case statement inside a loop should work easily. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change a comma separated text file and save it
Hi Prasad
Perhaps someone else will have time to work out some code for you but I think I'd need to be there to get this one straight (I'm just down the road in Dorking but rather busy on another project right now!). If you haven't done macros before, this is a rather complicated problem to start with. However, here's the best I can do as a potted lesson and please forgive me if I've misread your knowledge of VBA... First bit of advice: make sure you make backup copies of all your text files. I find the best way to approach this kind of problem is to: 1. Identify the steps that you need to do for one record and jot them down on a piece of paper to get them straight in your mind. 2. Practice running through these steps. 3. ChooseTools, Macro, Record New Macro (make sure you save the macro to your Personal Macro Workbook so that you don't lose it - you may want to close a data file without saving it). 4. Go through the steps (as in point 2) and, when you have finished, click on Tools, Macro, Stop Recording. 5. Close out of Excel, saying Yes to saving Personal.xls but no to the data files: you now need to see if the macro works. 6. Go back into Excel and choose Tools, Macro, [your macro], Run to run the macro from the same starting position as in point 4 - if it works, try it for another record. 7. Almost invariably there's some sort of problem so try running it again from the same starting position, this time using Tools, Macro, [your macro], Step Into. You can now use the F8 key to step line by line through your code - make the window smaller so you can see the workbook at the same time. This is really helpful to try and spot what needs changing in the code. Best I can do for now and apologies if you're a VBA-whiz and I've just told you a lot of unnecessarily simple stuff! "BristolBloos" 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/2001","","","","","","","","","AIG EUROPE (UK) LTD","AIG CLAIMS EXECUTIVE","","","","","","","","EEE","","GBR","GB R","","","SSS","23B","20030601ES","01/06/2003","01/06/2006",355,+00000942525,0026550000,0000002108,00025 000,00000850000,034,000,0,0,0,,"",,"",,"",,"",,"", ,"",,"",,"",,"",,"",,"",,"",,"",,"",,"",,"",,"",," ",,"",,"",0026550000,"",,,"" The main fields in this record for me a 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/2001","","","","","","","","AIG EUROPE (UK) LTD","AIG CLAIMS EXECUTIVE","","","","","","","","EEE","","GBR","GB R","","","SSS","23B","20030601ES","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 a 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change a comma separated text file and save it
this worked for me with the test data you supplied:
copy your files to a test directory and test it on these copies of your files: Sub ReadStraightTextFile() Dim sStr As String Dim LineofText As String Dim rw As Long, pay As Double Dim v As Variant, s As String Dim v1() As Variant Dim v2() As Variant Dim lPmt As Long, tot As Double Dim i As Long, j As Long ReDim v1(1 To 1) ReDim v2(1 To 1) Open "C:\MYFILES\ES_EF_AIG_20141440_105708_d.txt" _ For Input As #1 Do While Not EOF(1) Line Input #1, LineofText v = Split(LineofText, ",") v1(UBound(v1)) = v ReDim Preserve v1(1 To UBound(v1) + 1) Loop 'Close the file Close #1 ReDim Preserve v1(1 To UBound(v1) - 1) Open "C:\MYFILES\ES_FE_AIG_20143416_142901_d.txt" _ For Input As #1 Do While Not EOF(1) Line Input #1, LineofText v = Split(LineofText, ",") v2(UBound(v2)) = v ReDim Preserve v2(1 To UBound(v2) + 1) Loop 'Close the file Close #1 ReDim Preserve v2(1 To UBound(v2) - 1) For i = LBound(v1) To UBound(v1) For j = LBound(v2) To UBound(v2) Debug.Print v1(i)(0), v2(j)(0) If v1(i)(0) = v2(j)(0) Then s = v2(j)(49) s = Replace(s, """", "") If Abs(CDbl(s) - _ CDbl(v1(i)(53) / 100#)) < 0.0001 Then tot = CDbl(v1(i)(54) / 100#) pay = CDbl(s) lPmt = CLng(v1(i)(55)) lPmt = lPmt - 1 tot = tot - pay Debug.Print tot, pay, lPmt v1(i)(54) = Format(Application.Round(tot * 100#, 0), "00000000000") v1(i)(55) = Format(lPmt, "000") Else MsgBox "Payment Mismatch - stop processing" Exit Sub End If End If Next j Next i Open "C:\MYFILES\ES_EF_AIG_20141440_105708_d.txt" _ For Output As #1 For i = LBound(v1) To UBound(v1) v = v1(i) sStr = Join(v, ",") Print #1, sStr Next i 'Close the file Close #1 End Sub -- Regards, Tom Ogilvy "BristolBloos" 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/2001","","","","","","","","","AIG EUROPE (UK) LTD","AIG CLAIMS EXECUTIVE","","","","","","","","EEE","","GBR","GB R","","","SSS","23B","20030601ES","01/06/2003","01/06/2006",355,+00000942525,0026550000,0000002108,00025 000,00000850000,034,000,0,0,0,,"",,"",,"",,"",,"", ,"",,"",,"",,"",,"",,"",,"",,"",,"",,"",,"",,"",," ",,"",,"",0026550000,"",,,"" The main fields in this record for me a 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/2001","","","","","","","","AIG EUROPE (UK) LTD","AIG CLAIMS EXECUTIVE","","","","","","","","EEE","","GBR","GB R","","","SSS","23B","20030601ES","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 a 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change a comma separated text file and save it
Will try it Tom and will come back to you.
Thanks Prasad |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change a comma separated text file and save it
Thanks you very much Tom. It works (has to when you have sent).
Saved lot of time. Regards, Prasad |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
text string separated by comma | Excel Discussion (Misc queries) | |||
How do I convert an excel file into a comma separated values file | Excel Discussion (Misc queries) | |||
Changing a comma separated text file and save it. | Excel Discussion (Misc queries) | |||
how could I import a text file with comma separated values into ex | Excel Worksheet Functions | |||
Save file with fields within quotes and comma separated | Excel Programming |