vba to find and replace in text ( csv ) file

Posted on 2008-11-17
Last Modified: 2012-05-05
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,

Question by:Dan_Wong
    LVL 45

    Accepted Solution

    You should import the CSV data into Excel, modify the data as needed and then 'Save As' the file in CSV format.
    LVL 1

    Expert Comment

    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
    LVL 3

    Author Comment

    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......
    LVL 45

    Expert Comment

    That's a great car story. By the sounds of it you need a good rest. Take it easy.
    LVL 3

    Author Comment

    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,


    LVL 3

    Author Closing Comment

    Thanks Patrick - appreciated.
    LVL 45

    Expert Comment

    Dan - Thanks for the grade. Pleased you solved the problem. - Patrick

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Opening Files From Excel WB 8 33
    Employees list 3 34
    recovering Excel 2016 file 2 25
    Excel totals by filter - Automated 4 19
    This is pretty cool.  The purpose of this VB Script is to help you document where JAR (Java ARchive) files and specifically java class files are located so that you can address issues seen with a client or that you can speak intelligently with a dev…
    This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now