Batch converting CSV files from comma-decimal to period-decimal

[Nodles]

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.

 

[mrice]

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!

 

[Nodles]

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?

 

[mrice]

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