CSV files

[Patrick Alexander]

I need to import CSV files in a database, the problem is that I have CSV files created in the american regional options, but also in the Belgian regional options.
Is it possible to create a macro so i can convert these CSV files?

 

[fairwinds]

Hi,

To change from Belgian to American, I think you need to replace , with . and then ; with , you could do this in e.g. Notepad before opening the file in Excel. I don't know how to do it with a macro though.

 

[Patrick Alexander]

also the list separator is different

 

[fairwinds]

Yes the , to . was to change the decimal. The ; to , was to change the list separator.

 

[Jak]

Hi Patrick

You could try a macro along these lines to convert the file format. Not sure what you raw data looks like but using the info supplied from previous posts you could open your CSV file and then run the following code:

Sub Macro2()

Application.ScreenUpdating = False

Cells.Select
    Selection.Replace What:=",", Replacement:=".", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Cells.Select
    Selection.Replace What:=";", Replacement:=",", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
        
Columns(1).TextToColumns _
Destination:=Range("A1"), _
Comma:=True, _
FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), _
Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), _
Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), _
Array(12, 1))
[a1].Select
Application.ScreenUpdating = True

End Sub

Not tested but it should replace the unwanted characters with the desired ones and then move the comma seperated values to their respective columns.  Just add more arrays to include more colums.

HTH

 

[Patrick Alexander]

thanks Jak,

can i also change the date formats,

the American format is MMDDYYYY
The Belgium format is DDMMYYYY

 

[Jak]

Hi Patrick

What column does your date format reside in?

Let me know and I will tweak the macro.

 

[Patrick Alexander]

column 5 has now format mmddyyyy
and should be ddmmyyyy

thanks

 

[Jak]

Hi Patrick

See if this does the job

Sub Macro2() 

Application.ScreenUpdating = False 

Cells.Select 
    Selection.Replace What:=",", Replacement:=".", LookAt:=xlPart, _ 
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ 
        ReplaceFormat:=False 
Cells.Select 
    Selection.Replace What:=";", Replacement:=",", LookAt:=xlPart, _ 
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ 
        ReplaceFormat:=False 
        
        
Columns(1).TextToColumns _ 
Destination:=Range("A1"), _ 
Comma:=True, _ 
FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), _ 
Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), _ 
Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), _ 
Array(12, 1))
Columns("E:E").Select
    Selection.NumberFormat = "dd/mm/yyyy"
[a1].Select 
Application.ScreenUpdating = True 

End Sub

 

[Patrick Alexander]

it is not working
when i run the macro, the list separator is also changed
the american format is
list separator ","
thousand separator ","
decimal separator "."
the belgian format
list separator ";"
thousand separator "."
decimal separator ","

tnx