CSV files

Patrick Alexander

Board Regular
Joined
Feb 23, 2004
Messages
75
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?
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
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.
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Yes the , to . was to change the decimal. The ; to , was to change the list separator.
 

Jak

Well-known Member
Joined
Apr 5, 2002
Messages
825

ADVERTISEMENT

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:

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

Board Regular
Joined
Feb 23, 2004
Messages
75
thanks Jak,

can i also change the date formats,

the American format is MMDDYYYY
The Belgium format is DDMMYYYY
 

Jak

Well-known Member
Joined
Apr 5, 2002
Messages
825

ADVERTISEMENT

Hi Patrick

What column does your date format reside in?

Let me know and I will tweak the macro.
 

Jak

Well-known Member
Joined
Apr 5, 2002
Messages
825
Hi Patrick

See if this does the job

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))
Columns("E:E").Select
    Selection.NumberFormat = "dd/mm/yyyy"
[a1].Select 
Application.ScreenUpdating = True 

End Sub
 

Patrick Alexander

Board Regular
Joined
Feb 23, 2004
Messages
75
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,161
Messages
5,768,547
Members
425,481
Latest member
ihumanl

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top