I have a monstruous amount of data, in several folders and subfolders,
stored in CSV format. Unfortunately, these CSVs are in ISO standard for
punctuation:

. for separating three digits
, to separate the integer and the fraction
; to separate between values

I need to transform all of them to the american standard

no separation between three digits
. to separate the integer and the fraction
, to separate between values

I cannot reconfigure the international config on the computer which
will read the data, it needs to be in the american standard to run
matlab's interface with excel.

So far, i have been handling this by opening the individual CSVs in
word and substituting the relevant chars with macros, then saving and
going back to excel :( Im in a desperate need of a more efficient
manner to batch convert all CSVs to american standard.


--
Nodles
------------------------------------------------------------------------
Nodles's Profile: http://www.excelforum.com/member.php...o&userid=29391
View this thread: http://www.excelforum.com/showthread...hreadid=558251

Try the following VBA

Const SourceFilePath = "C:\tempsource\"
Const TargetFilePath = "C:\temptarget\"


Sub Convert()
Close
Filename = Dir(SourceFilePath & "*.csv")
Do While SourceFilePath < ""
Open SourceFilePath & Filename For Input As #1
Open TargetFilePath & Filename For Output As #2
Do While Not EOF(1)
Line Input #1, FileLine
FileLine = Application.Substitute(FileLine, ".", "") 'changes .
to nothing
FileLine = Application.Substitute(FileLine, ",", ".") 'changes
, to .
FileLine = Application.Substitute(FileLine, ";", ",") 'changes
; to ;
Print #2, FileLine
Loop
Close #1
Close #2
Filename = Dir()
Loop
End Sub

You need to create the tempsource and temptarget directories. Copy the
files into the tempsource folder and the macro will populate the
temptarget folder with the modified files.

Good luck!


--
mrice

Research Scientist with many years of spreadsheet development experience
------------------------------------------------------------------------
mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
View this thread: http://www.excelforum.com/showthread...hreadid=558251

Works very well mrice thx :) However, this code does not seem to scan
subfolders for files.

I would need to scan all folders and subfolders in SourceFilePath and
rebuild the folder structure in TargetFilePath. Can this be done in
VBA?


--
Nodles
------------------------------------------------------------------------
Nodles's Profile: http://www.excelforum.com/member.php...o&userid=29391
View this thread: http://www.excelforum.com/showthread...hreadid=558251

You might find something close to what you need on the third tab on the
workbook on my download page. The macro uses recursion to delve into
the sub folder structure


--
mrice

Research Scientist with many years of spreadsheet development experience
------------------------------------------------------------------------
mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
View this thread: http://www.excelforum.com/showthread...hreadid=558251