vba to find and replace in text ( csv ) file

Hi people

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.

thank you,

d.
LVL 3
Dan_WongAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

patrickabCommented:
Dan_Wong,
You should import the CSV data into Excel, modify the data as needed and then 'Save As' the file in CSV format.
Patrick
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
stephanklemensCommented:
Dan_Wong,
Hard to comment withou having a file sample - maybe post your file on the site.
Some general comments:
To open a txt file in VBA you can use something like this:
Workbooks.OpenText FileName:= _
        fileToOpen, Origin _
        :=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=False, _
        Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 4), _
        Array(2, 1), Array(3, 9), Array(4, 9),)
where you can specify for each column what you want to do with it (<FieldInfo>)
The best way to find out what parameters you need is to record a macro when you open the file.
The description for FieldInfo from VBA help reads:

FieldInfo Optional xlColumnDataType. An array containing parse information for individual columns of data. The interpretation depends on the value of DataType. When the data is delimited, this argument is an array of two-element arrays, with each two-element array specifying the conversion options for a particular column. The first element is the column number (1-based), and the second element is one of the XlColumnDataType constants specifying how the column is parsed.
XlColumnDataType can be one of these XlColumnDataType constants.
xlGeneralFormat General
xlTextFormat Text
xlMDYFormat MDY date
xlDMYFormat DMY date
xlYMDFormat YMD date
xlMYDFormat MYD date
xlDYMFormat DYM date
xlYDMFormat YDM date
xlEMDFormat EMD date
xlSkipColumn Skip Column

You can then format the date column to your needs. E.g.
Range(yourRange).NumberFormat = "dd/mm/yyyy"
Hope this helps
cheers
Stephan
0
Dan_WongAuthor Commented:
Hi Patrick

Oh NOW it works -  I actually thought I'd tried that already  - but it undeniably does appear to be working now.....
shall test properly when back in office.

thanks very much for swift response.

[red faced one] d.


on a similar note  I was attempting to get into my car the other day after a long day - and for some reason the door wouldn't open - I was just winding up to give it a good thrashing when a concerned car owner informed me my car was parked accross the street......
0
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

patrickabCommented:
Dan_Wong
That's a great car story. By the sounds of it you need a good rest. Take it easy.
Patrick
0
Dan_WongAuthor Commented:
Hi Stephan

Thanks for your post - helpful info.  

However I strongly suspect I'm being stupid here.
I don't have access to original files at the moment but would like to discourage anyone else wasting time on what does seem most likely to be my idiocy.

Let me check properly and I'll get back to everyone.

thanks and best regards,

dan

0
Dan_WongAuthor Commented:
Thanks Patrick - appreciated.
0
patrickabCommented:
Dan - Thanks for the grade. Pleased you solved the problem. - Patrick
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.