Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help...Need to modify data within a column in a .csv file
I have been given a .csv file of several thousand rows, and I need to
change column A of each. The brief cutout below shows column A of the first two rows. (I need to skip the first row, which is the headers.) Kinda clumsy to have such a wide column, but the application this file is to be fed after I change the contents requires all the data separated by double-quotes to be in the one column. Cell A2: 09/12/0509/25/05"1417385"121"307"111-11-1111"09/30/05"LASTNAME1 Cell A3: 09/12/0509/25/05"1417386"101"900000005"222-22-2222"09/30/05"LASTNAME2 .. .. .. etc I need to do two things: 1. Add a constant to the 7-digit number which always appears after the first double-quote; e.g. 1417385 becomes 1427385 and 1417386 becomes 1427386 (by adding constant of my choice in this case 10000). I need to apply this constant to column A of all rows. 2. Change the date which appears after the fifth double-quote to a new, constant date. 09/30/05 would become 10/14/05 for all rows. I don't care what date is in that position, I just want to write over it with the new one. Note that the number after the third quote can be variable in length (e.g., 307 vs 900000005). I suppose part of the solution could be to count (parse?) over five double-quotes to get to this date. All other data in the column remains the same. Here's the after-image of the above example: Cell A2: 09/12/0509/25/05"1427385"121"307"111-11-1111"10/14/05"LASTNAME1 Cell A3: 09/12/0509/25/05"1427386"101"900000005"222-22-2222"10/14/05"LASTNAME2 TIA for any help you can provide. Ed |
#2
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help...Need to modify data within a column in a .csv file
And assuming your helper column is in Column B, with your date to go in say
C1 use this in another helper column =SUBSTITUTE(B2,MID(B2,FIND("%",SUBSTITUTE(B2,CHAR( 34),"%",5))+1,8),TEXT($C$1,"dd/mm/yy")) Also note that the first formula I gave you had a constant hardwired into it which was at odds with me describing the constant being in A1 - apologies for that - it should have read =LEFT(A2,17)&TEXT((--MID(A2,18,7)+$A$1),"0000000")&MID(A2,25,LEN(A2)) -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------*------------------------------*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------*------------------------------*---------------- "Ken Wright" wrote in message ... Assuming your data is all in Col A, that the data you have given is representative of all your data in as much as the constant that needs changing is in position 18 with a length of 7 digits, then assuming you have the constant you want in say A1, then in any helper column try the following and copy down as far as your data goes:- =LEFT(A2,17)&TEXT((--MID(A2,18,7)+10000),"0000000")&MID(A2,25,LEN(A2)) -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------*------------------------------*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------*------------------------------*---------------- wrote in message ups.com... I have been given a .csv file of several thousand rows, and I need to change column A of each. The brief cutout below shows column A of the first two rows. (I need to skip the first row, which is the headers.) Kinda clumsy to have such a wide column, but the application this file is to be fed after I change the contents requires all the data separated by double-quotes to be in the one column. Cell A2: 09/12/0509/25/05"1417385"121"307"111-11-1111"09/30/05"LASTNAME1 Cell A3: 09/12/0509/25/05"1417386"101"900000005"222-22-2222"09/30/05"LASTNAME2 . . . etc I need to do two things: 1. Add a constant to the 7-digit number which always appears after the first double-quote; e.g. 1417385 becomes 1427385 and 1417386 becomes 1427386 (by adding constant of my choice in this case 10000). I need to apply this constant to column A of all rows. 2. Change the date which appears after the fifth double-quote to a new, constant date. 09/30/05 would become 10/14/05 for all rows. I don't care what date is in that position, I just want to write over it with the new one. Note that the number after the third quote can be variable in length (e.g., 307 vs 900000005). I suppose part of the solution could be to count (parse?) over five double-quotes to get to this date. All other data in the column remains the same. Here's the after-image of the above example: Cell A2: 09/12/0509/25/05"1427385"121"307"111-11-1111"10/14/05"LASTNAME1 Cell A3: 09/12/0509/25/05"1427386"101"900000005"222-22-2222"10/14/05"LASTNAME2 TIA for any help you can provide. Ed |
#3
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help...Need to modify data within a column in a .csv file
I'm all for using something easier than Excel, except I'm short on
language software here :) Can Python (or any such language) be downloaded free, and be relatively fast to learn? Meantime, I will try Ken's Excel solution. Thanks, Ed |
#4
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help...Need to modify data within a column in a .csv file
Thanks for you quick reply, Ken. I will try it and let you know how it
goes. Ed |
#5
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help...Need to modify data within a column in a .csv file
You're welcome - any problems simply report back with details of what
doesn't work and I'll pick it up or someone else will. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------*------------------------------*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------*------------------------------*---------------- wrote in message oups.com... Thanks for you quick reply, Ken. I will try it and let you know how it goes. Ed |
#6
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help...Need to modify data within a column in a .csv file
Yes it can, and yes it can. It is another scripting language, so you
probably know the fundamentals. -- HTH RP (remove nothere from the email address if mailing direct) wrote in message oups.com... I'm all for using something easier than Excel, except I'm short on language software here :) Can Python (or any such language) be downloaded free, and be relatively fast to learn? Meantime, I will try Ken's Excel solution. Thanks, Ed |
#7
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help...Need to modify data within a column in a .csv file
Your formulas worked to perfection, Ken. The only nitpick was that I
had to change the picture of the date at the end of the 2nd formula to "mm/dd/yy", very minor thing. Clever the way you had to handle the double-quotes with their character representation. Many thanks, Ed |
#8
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help...Need to modify data within a column in a .csv file
Your formulas worked to perfection, Ken. The only nitpick was that I
had to change the picture of the date at the end of the 2nd formula to "mm/dd/yy", very minor thing. Clever the way you had to handle the double-quotes with their character representation. Many thanks, Ed "Ken Wright" wrote: And assuming your helper column is in Column B, with your date to go in say C1 use this in another helper column =SUBSTITUTE(B2,MID(B2,FIND("%",SUBSTITUTE(B2,CHAR( 34),"%",5))+1,8),TEXT($C$1,"dd/mm/yy")) Also note that the first formula I gave you had a constant hardwired into it which was at odds with me describing the constant being in A1 - apologies for that - it should have read =LEFT(A2,17)&TEXT((--MID(A2,18,7)+$A$1),"0000000")&MID(A2,25,LEN(A2)) -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------*------------------------------*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------*------------------------------*---------------- |
#9
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help...Need to modify data within a column in a .csv file
You're very welcome - glad it worked for you. :-)
-- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------*------------------------------*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------*------------------------------*---------------- wrote in message oups.com... Your formulas worked to perfection, Ken. The only nitpick was that I had to change the picture of the date at the end of the 2nd formula to "mm/dd/yy", very minor thing. Clever the way you had to handle the double-quotes with their character representation. Many thanks, Ed |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to modify specific lines in a .ini file? | Excel Discussion (Misc queries) | |||
Help...Need to modify data within a column in a .csv file | Excel Discussion (Misc queries) | |||
Help...Need to modify data within a column in a .csv file | Excel Worksheet Functions | |||
Modify date of a file via hyperlink? | Excel Programming | |||
Code for modify a column | Excel Programming |