Help...Need to modify data within a column in a .csv file

[ed0109]

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

 

[Ken Wright]

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
------------------------------­------------------------------­----------------

 

[Ken Wright]

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

 

[bhwithun]

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.

 

[Ken Wright]

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
------------------------------­------------------------------­----------------

 

[ed0109]

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

 

[ed0109]

Thanks for you quick reply, Ken. I will try it and let you know how it
goes. Ed

 

[Ken Wright]

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
------------------------------­------------------------------­----------------

 

[Bob Phillips]

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)

 

[ed0109]

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

 

[ed0109]

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



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]

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
------------------------------­------------------------------­----------------