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

0
3/23/2006 10:31:44 AM
excel 39879 articles. 2 followers. Follow

3 Replies
411 Views

Similar Articles

[PageSpeed] 26

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/Answers-for-everything/browse_thread/threa=
d/946502c223298627
http://groups.google.co.in/group/Answers-for-everything/browse_thread/threa=
d/f31ad015c37603ae


For more,post your questions on
http://groups.google.co.in/group/answers-for-everything
or e-mail to
answers-for-everything@googlegroups.com

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

0
3/23/2006 11:28:53 AM
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

0
3/23/2006 11:54:29 AM
No problem
Repeat the above steps and extract the details
After extracting lets hope your data lies as below

First file details(in sheet1)
cell A1 - "C0000000519"
cell B1 - 00025000
cell C1 - 00000850000
cell D1 - 034

Second file details(in sheet2)
cell A1 - C0000000519
cell B1 -  250.00

Sort the data in both the fields
Now,create a new sheet,sheet3
Copy the value of the first 2 columns(i.e)C0000000519 and 00025000
Then use the VLOOKUP function to extract data.
For help on VLOOKUP see Microsoft Excel

0
3/23/2006 1:25:16 PM
Reply:

Similar Artilces:

send the same e-mail with one or two fields changed.......
I would like to send the same e-mail to many differnet people with one or two fields changed (for example the name of recipient and the date).How canthis be done?? I would also like to be able to save the e-mail and use it again and again. can anyone help cheers john If you have Word installed and it's the same version as Outlook (both 2003, for example), you can do a mail merge between the two. This would allow you to set up the text the way you want it to, and you can save the document for future use. Look at the following page for further information: http://www.slipstick.com/con...

Here is fix for Windows file problems
If you have installed a Windows update and started having problems opening, closing, copying files, see: http://www.microsoft.com/communities/newsgroups/list/en-us/default.aspx?dg=microsoft.public.excel.misc&mid=1fe358b1-c8eb-4217-ad2a-4b9fcf741916 A better solution is offered at http://support.microsoft.com/kb/918165 "Problems in Windows Explorer or the Windows shell after you install security update MS06-015". Patricia Shannon wrote: > If you have installed a Windows update and started having problems opening, > closing, copying files, see: > > http://ww...

Fax Icon on my File menu and toolbar
I had a Fax Icon on my toolbar and also on the file menu. All of a sudden it is gone and I want it back. Where did it go? Was it a dedicated toolbar? Or was it just icons on a builtin toolbar? If it was a dedicated toolbar, you may get lucky and find it under: Tools|customize|toolbars tab (just not selected) But if it was on a builtin toolbar (along with the file menu), then maybe you reset your toolbar--also under: Tools|customize|toolbars tab selecting a toolbar and hitting the reset button. If that's what happened, I think I might try to find the Fax addin/workbook that added t...

exporting outlook 2000 pst files to a database
I need to export outlook 2000 to a database- attachments and all. Is there a simple way to do this so you can search and retreive messages and attachments in a database form? Thanks Jeff You can export individual folders to access or excel to have them available for searching. I have never tried to export my entire .pst file to access or excel however. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. After searching google.groups.com and finding no answer Jeff <laacid@yahoo.com> asked: | I need to export outlook 2000 to a database- ...

if cell is text move left one column
ColB is a long list with sections names followed by category codes I need to move the text into colA leaving colB with codes only (all numbers) ColB. Doors 940590 555998 447006 447008 810697 810705 810706 810707 Windows 619435 525691 525692 Try Sub Macro1() Dim lngRow As Long For lngRow = 1 To Cells(Rows.Count, "B").End(xlUp).Row If Not IsNumeric(Range("B" & lngRow)) Then Range("A" & lngRow).Value = Range("B" & lngRow).Text Range("B" & lngRow).Value = "" End If Next End Sub -- Jacob ...

why does sorting change a scatter plot graph?
Why does the way a spread sheet is sorted change the look of a scatter plot graph??? the graph is just a plot of two points, (X, Y) and these two points are definded by two collumns for a given row. The two collumns don't change, and the row all stays together, so why does it change where points are plotted out on the graph when you re-sort it? AndrewT420 - Usually, for an XY (Scatter) chart, with values of X in a column and corresponding values of Y in an adjacent column, for three or more points, Excel assumes (correctly) "Series in Columns." But, when you have only...

office 2000 [excel] cannot open previously OK file. Help
When trying to open a Microsoft Excel Work Sheet that I had accessed modified and saved to only yesterday [Office 2000] a window pops up with: "name.xls cannot be accessed. The file may be resd-only, or you may be trying toaccess a read only location. Or, the server the document is stored on may not be responding." Older files stored on the same mediums ;HD, Flash memory or 3.5in. diskette are accessed with no problem. This is on a network drive? ...

Errors saving to network drive
When my users open an Excel spreadsheet located on a network drive, make their changes, and then try to save the spreadsheet, they get an error message saying "Your changes could not be saved to Whateverfile.xls, but were saved to XXXXXXXX ( 8 digit hex number ). Close the existing document, then open the temporary document and save it under a new name" The users have been explicitly granted full rights, including Delete and Modify, to the shared folder in which the spreadsheet resides. I have disabled their Symantec Antivirus realtime protection for the time being. This pr...

Changing Prices in HQ.
Hi, I have this little issue. I want to change the put items on promotion using the price wizard using HQ. Unfortunately if I have stores who has differents prices for a same item the wizard do not make the proper change becuase it use the price already stored in the master table. Does anyone saw this issue before? Who was solved?. Thks in advance for your help. Rgds Rodrigo Hi there, The easiest way to look after this is to not change any data on the ITEM in HQ, but to simply do the worksheet for altering the sale price and then send it to the respective stores. Then in the works...

Content of emails is changing without any reason !
Hallo I changed operating system last week. From Win XP to Win 7. Used to work with Outlook Express at full satisfaction. I could transfer most of my emails automatically with export/import features of Microsoft software. But I suddenly discover 1 very big problem (bug ???) I am used to work with several maps, and hereby go to several levels deep. Such as : Saved mails Companyname Projectname Date of action Department Activity Name of patient Different emails So sometimes maps can go several levels deep. When I check ema...

Outlook receiving zip files
Why is it when I try to send zipped files to my address; it gets returned as service unavailable? Hello Omar, your Exchange dont allow you to send this! Please speak with your Domain Admin "Omar" <anonymous@discussions.microsoft.com> schrieb im Newsbeitrag news:0754E7DA-31DC-4966-9FC1-C290A8D8222C@microsoft.com... > Why is it when I try to send zipped files to my address; it gets returned as service unavailable? Hi Omar, your Provider don�t supportet this part of file! -- Gestern lief noch alles, ich habe nichts gemacht! Bitte in der Newsgroup antworten, damit jeder d...

Saving toolbar changes
After spending a lot of time to customize a toolbar in Excel 2003, it disappears when opening up another file, or starting the app again. I repeatedly change it, save it as XLB, XLT, save multiple copies in every possible location...but the damn thing always defaults to its own toolbar settings. This makes toolbars almost useless. How can one insist that PPT use YOUR toolbar setting, rather than its own default Thanks. Hi Jeff, If I have a lot of tool bar changes to make, I close all the workbook that are not hidden then unhide my personal.xls from the Window menu. I don't know why...

unable to read file #7
Does anyone know how to solve this error? This is the second time I have had the same error on the same file. Last time I was able to recover from a saved copy and updated fine. This time whenI opened the copy and updated it, then saved it, I got the same error when I tried to open it again, Now I don't have a good copy. I have downloaded all avaliable updates and tried running "Excel.exe /regserver>ok" This did not help. I also ran defran on the drive. "Randell" wrote > Does anyone know how to solve this error? This is the > second time I have had th...

Change cannot be saved due to sharing violation
Hello I've this message while saving the excel file even if no change ha been done to the file. There is no share on this file (exclusive use) File resides on a network drive It's very disturbing Thanks for your help Vobiscu -- Vobiscu ----------------------------------------------------------------------- Vobiscum's Profile: http://www.msusenet.com/member.php?userid=245 View this thread: http://www.msusenet.com/t-187102186 http://support.microsoft.com/default.aspx?scid=kb;en-us;328170 Thanks for your answer, I will try tomorrow noo Vobiscu -- Vobiscu ----------------...

Importing AOL Email File Cabinet
Is it possible to tranfer a complete AOL Email File Cabinet into outlook? AFAIK, no, but see if anything here will do it: http://www.slipstick.com/config/convmsg.htm - check out Address Magic Plus first, it can convert almost anything. -- Diane Poremsky [MVP - Outlook] Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outlook 2007: http://www.slipstick.com/outlook/ol2007/ Outlook Tips by email: dailytips-subscribe-request@lists.outlooktips.net EMO - a weekly newsletter about Outlook and Exchange: EMO-NEWSLETTER-SUBSCRIBE-REQUEST@PEACH.EASE.LSOFT.COM Outlook Tips: http:/...

Changing query execution sequence
Hi all, I got a spreadsheet which would execute a bunch of queries. It's noted that the queries are executing in the sequence of when it was added to the spreadsheet. Does anyone out there know of a way to switch the order without deleting and recreating them? Thanks! Wing ...

SBS 2003 moving of users files
I run SBS 2003 and due to the amount of data on the users drive it has become chokers and have installed a new 1tb drive to keep up with demand for space. I need to move all the data to the new drive but unsure of the process. Is there an easy way of doing this? As it needs to be done asap Thanks -- JimmyJames ------------------------------------------------------------------------ JimmyJames's Profile: http://forums.techarena.in/members/255792.htm View this thread: http://forums.techarena.in/small-business-server/1357051.htm http://forums.techarena.in You c...

140 MB file went to 5.08 MB after editting 1 table
Hello All - I need some ACCESS insight...please... Several years ago, I built an access db to track my business scheduling and accounts payable/receivable. So this database is EXTREMELY IMPORTANT TO ME. The file has grown to 140 MB. Today I made a copy of the file and then edited my calendar table. I removed all columns which had 2006 data (72 totals columns) - the table had about 144 columns originally. I then added 72 columns with 2008 headers. These columns are now blank since I have not added any 2008 data yet. Afterwards, I looked around and everything looks good - my 2007 data is the...

a few basic question about resource files
Hi, I have an application that uses resource files to contain the string values for each language. I have created the required resource files in my project, but I don't speak the languages I wish to have resources for. What I would like to do, is somehow allow the end user to edit the resource file themselves so they can set the string values as needed. Is there a way to edit the resource files that ship with my application and get installed ? or Is there a way to have external resource files (not embedded into my application) that can be updated/replaced with o...

Exporting contacts in a csv file
I am a mysterious problem exporting my contacts as csv file. The resulting file has only 58 contacts out of almost 1200 contacts in my outlook. These contacts appear to be the last 58 contacts added. (I say appear because the attribute "createdon" is not exported so I can't be exact. Why is this? cinnamngrl <cinnamngrl@gmail.com> wrote: > I am a mysterious problem exporting my contacts as csv file. The > resulting file has only 58 contacts out of almost 1200 contacts in my > outlook. These contacts appear to be the last 58 contacts added. (I > say appea...

is there a way to program my Excel file to do a loop?
Hi all, If I want B10 to B17 all follow the change of the same number(copy cell), let's say I put it in A1, and C10 follows the change of A2(copy cell), and C11 follows the change of A3(copy cell), and C12 follows the change of A4(copy cell), then I have 4 variables in my calculations: A1, A2, A3, A4. I want to loop each of the variables in a different set, then I hope the whole worksheet will be able to refresh following the change of A1, A2, A3, A4, and then I want to find the very set of A1, A2, A3, A4 that gives the smallest value of D10, how do I program the whole procedure...

Publisher can not save file
I recently started having troubles with my Publisher 2003. Whenever I go to save my files now, using save as or just the save button, it gives me a dialog box that says "Can not save file." It does that twice, then it disappears. I also noticed that when it does this, it leaves the .tmp files in the directory where I tried to save. I can change the name of the files, and sometimes it will save it. Most of the time not though. I have NAV, and I noticed that it was said there was an issue with Publisher and NAV. Is this the same with the 2005 version, and is this anything anyone...

Excel 2007 PC damages Mac Excel 2008 Files
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel When I open a Mac Excel 2008 file on Excel 2007 and I can work with the file, however when the file is returned to the Mac, it no longer works correctly, leading to a crash as soon as I try to save or eventually it might change the file to look like a bunch of letters and characters. Here is the error report: <br><br>Microsoft Error Reporting log version: 2.0 <br><br>Error Signature: <br> Exception: EXC_BAD_ACCESS <br> Date/Time: 2010-01-15 17:05:56 -0600 <br> Application N...

Separating Date and Time in a cell
I have a column of cells in the format "11/01/02 06:21". I would like to separate the text into 2 cells - one with the date and the other with the time. My attempts with LEFT and RIGHT have been unsuccesful. Thanks for your help Sameer --- Message posted from http://www.ExcelForum.com/ For the date use =INT(A1) replace A1 with the first cell of your range for time =MOD(A1,1) you probably have to reformat the first to mm/dd/yy (or whatever the setting is) and hh:mm Note that you can do this by just using format but if you want to compare to other cells with just pure d...

Automatic changes in cells
Hi for some reason I now have to save my work for any formlas etc to change when I update a worsheet, how can I stop this as it is a pain and sometimes I need to do changes to see how they work before saving the work. Many thanks Click on Tools | Options | Calculation tab and set to Automatic calculation, as it is probably set to Manual. You can press F9 to force a recalculation under a manual setting. Make sure you save the file with the Automatic setting, to avoid it happening next time. Hope this helps. Pete On Feb 1, 11:42=A0am, Office 2004 Test Drive User <heepenm...@yahoo.co.u...