• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1681
  • Last Modified:

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,

  • 3
  • 3
1 Solution
You should import the CSV data into Excel, modify the data as needed and then 'Save As' the file in CSV format.
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
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......
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

That's a great car story. By the sounds of it you need a good rest. Take it easy.
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_WongAuthor Commented:
Thanks Patrick - appreciated.
Dan - Thanks for the grade. Pleased you solved the problem. - Patrick
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now