Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
Help...Need to modify data within a column in a .csv file
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
|
|||
|
|||
Help...Need to modify data within a column in a .csv file
Part 1 anyway :-)
-- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------*------------------------------*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------*------------------------------*---------------- snip |
#4
|
|||
|
|||
Help...Need to modify data within a column in a .csv file
Is there some reason you need to use Excel for this? CSV processing is
more quickly and effectively handled by a full blown language like python. I work with python to manipulate CSV files on a routine basis. Excel is limited to 65,536 records which can be quite small at times. The operations you describe above can be quite easily handled by about 10 lines of python script. |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
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 |
#11
|
|||
|
|||
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 :-) ------------------------------*------------------------------*---------------- |
#12
|
|||
|
|||
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 | |||
Importing csv file all data is in first column | Excel Worksheet Functions | |||
how to put data in the column A to the next row of the column B | Excel Discussion (Misc queries) | |||
match and count words | Excel Worksheet Functions | |||
Getting data from another excel file | Excel Discussion (Misc queries) | |||
Weekly Transaction Processing | Excel Worksheet Functions |