vba to find and replace in text ( csv ) file
Posted on 2008-11-17
Hope someone can help me.
I'm attempting to re-format a simple csv file using vb.
I'd like to ;
- reformat the date
from dd/mm/yy to dd/mm/yyyy
( this date exists in a specific position within the file and should only be re-formatted when in this position - it may exist elsewhere in the file )
- remove the double quote [ " ] text qualifier
- remove the trailing comma delimiter at the end of each line
Example original file would look something like ;
"header_line_blurb" , "header2" , "header3", "blah",
"field containing a , comma", "Irrelevant field", "pointless number", "25/08/05",
I'd really like to have ;
header_line_blurb, header2, header3, blah
field containing a comma,Irrelevant field,pointless number,25/08/2005
I initially attempted this by opening in Excel and then saving down as csv - which seems to work fine when done manually but if done via VB seems to change the date format.
I've been banging my head off a wall for the last couple hours trying to use the Open for Input method but am struggling.
Any help would be greatly appreciated.