Import multiple .csv files to Excel with semi-colon delimiter

[smugcool]

HI ALL,

I am having multiple .csv files in a single directry. I wanted to import all these files in a excel file with semicolon delimeted by a single click of a button. Can anyone please help me to get rid of this problem. I am in urgent need. plz help.

Regards
Anup kumar

[smugcool]

Can anybody help plzzzzzzzzzz... ..........

[kadghar]

I hope you're using VBA, in that case this code might help you. just add it to a button.

[CODE=vb] Sub Importar()
Dim Str1 As String
Dim i As Integer
With Application.Fil eDialog(msoFile DialogFilePicke r)
.AllowMultiSele ct = True
.Filters.Clear
.Filters.Add "Archivos Punto y Coma (*.cvs)", "*.cvs"
.Filters.Add "Archivos Texto (*.txt)", "*.txt"
.Filters.Add "Todos los Archivos (*.*)", "*.*"
.Show
If .SelectedItems. Count > 0 Then
For i = 1 To .SelectedItems. Count
If Worksheets.Coun t < i Then
Worksheets.Add After:=Workshee ts(i - 1)
End If
Worksheets(i).A ctivate
Str1 = "TEXT;" & .SelectedItems. Item(i)
With ActiveSheet.Que ryTables.Add(Co nnection:=Str1, Destination:=Ra nge("A1"))
.TextFileSemico lonDelimiter = True
.Refresh BackgroundQuery :=False
End With
Next
End If
End With
End Sub[/CODE]

Good Luck

[smugcool]

I hope you're using VBA, in that case this code might help you. just add it to a button.

Sub Importar()
Dim Str1 As String
Dim i As Integer
With Application.Fil eDialog(msoFile DialogFilePicke r)
.AllowMultiSele ct = True
.Filters.Clear
.Filters.Add "Archivos Punto y Coma (*.cvs)", "*.cvs"
.Filters.Add "Archivos Texto (*.txt)", "*.txt"
.Filters.Add "Todos los Archivos (*.*)", "*.*"
.Show
If .SelectedItems. Count > 0 Then
For i = 1 To .SelectedItems. Count
If Worksheets.Coun t < i Then
Worksheets.Add After:=Workshee ts(i - 1)
End If
Worksheets(i).A ctivate
Str1 = "TEXT;" & .SelectedItems. Item(i)
With ActiveSheet.Que ryTables.Add(Co nnection:=Str1, Destination:=Ra nge("A1"))
.TextFileSemico lonDelimiter = True
.Refresh BackgroundQuery :=False
End With
Next
End If
End With
End Sub

Good Luck

thanx dude for your help. But i want to import the data's in a single worksheets. The code given by you is puting the data's in multiple worksheets. Thats what i dont want. Is thereanyway then plz suggest.
Thanx a lot again.

[Killer42]

thanx dude for your help. But i want to import the data's in a single worksheets. The code given by you is puting the data's in multiple worksheets. Thats what i dont want. Is thereanyway then plz suggest.
Thanx a lot again.

Perhaps you could import each into a temporary worksheet, then copy/paste to the end of the "real" one.

By the way, CSV files are supposed to hold comma-delimited data. If it's delimited with semi-colons, you probably should name the file with a plain old TXT extension or something, rather than CSV. In fact, I'm pretty sure CSV stands for something like "Comma Separated Values".

[smugcool]

Perhaps you could import each into a temporary worksheet, then copy/paste to the end of the "real" one.

By the way, CSV files are supposed to hold comma-delimited data. If it's delimited with semi-colons, you probably should name the file with a plain old TXT extension or something, rather than CSV. In fact, I'm pretty sure CSV stands for something like "Comma Separated Values".

I could have done so. But problem is that the quanity of file is more than 100.So, it wud be too much manual work to copy and paste the same.

you are absolutly correct csv stands for Comma sepereated Values, but its delimated with semicolon.

is there anycode where i can import these data in a single excel instead of multiple.

[Killer42]

I could have done so. But problem is that the quanity of file is more than 100.So, it wud be too much manual work to copy and paste the same.

What I meant is that you could modify the code so that after importing each file it then does the copy & paste operation.

[smugcool]

What I meant is that you could modify the code so that after importing each file it then does the copy & paste operation.

what would be the code coz here i have got multiple sheets for copy and paste.

[kadghar]

I could have done so. But problem is that the quanity of file is more than 100.So, it wud be too much manual work to copy and paste the same.

you are absolutly correct csv stands for Comma sepereated Values, but its delimated with semicolon.

is there anycode where i can import these data in a single excel instead of multiple.

Hi,
You can put everything in the same worksheet, the only thing you have to change is (inside the FOR) put everything in Worksheets(1), and put the "i" into the range. So instead of changing the worksheet everytime you import a file, you just change the start cell (oh, and dont forget to delete the "worksheets.add " line)

Good Luck

[smugcool]

Hi,
You can put everything in the same worksheet, the only thing you have to change is (inside the FOR) put everything in Worksheets(1), and put the "i" into the range. So instead of changing the worksheet everytime you import a file, you just change the start cell (oh, and dont forget to delete the "worksheets.add " line)

Good Luck

you mean to say sud i delete the entire line
Worksheets.Add after:=Workshee ts(i - 1)
and needed to put
worksheets=Ever ytthing
And for Range I needed to put
With ActiveSheet.Que ryTables.Add(Co nnection:=Str1, Destination:=Ra nge("i"))

I did so but it doesnot work.

Kindly suggest if i am wrong.

[smugcool]

you mean to say sud i delete the entire line
Worksheets.Add after:=Workshee ts(i - 1)
and needed to put
worksheets=Ever ytthing
And for Range I needed to put
With ActiveSheet.Que ryTables.Add(Co nnection:=Str1, Destination:=Ra nge("i"))

I did so but it doesnot work.

Kindly suggest if i am wrong.

hi,

Plzzzzzzz revert

[kadghar]

hi,

Plzzzzzzz revert

Sorry for the delay, here you are

Sub Importar()
Dim Str1 As String
Dim i As Integer
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = True
.Filters.Clear
.Filters.Add "Archivos Punto y Coma (*.cvs)", "*.cvs"
.Filters.Add "Archivos Texto (*.txt)", "*.txt"
.Filters.Add "Todos los Archivos (*.*)", "*.*"
.Show
If .SelectedItems.Count > 0 Then
Worksheets(1).Activate
For i = 1 To .SelectedItems.Count
Str1 = "TEXT;" & .SelectedItems.Item(i)
With ActiveSheet.QueryTables.Add(Connection:=Str1, Destination:=Cells(1, 2 * i))
.TextFileSemicolonDelimiter = True
.Refresh BackgroundQuery:=False
End With
Next
End If
End With
End Sub

Just check that in the destination I wrote cells ( 1 , 2 * i) , there is where you can change the start cell of each document.

Good Luck

[Killer42]

Plzzzzzzz revert

I'd like to make two points.

1. Using "Plzzzzzzz" will not make you any friends on TheScripts. We are trying to maintain a standard of clear, readable English. Consequently, txt-style abbreviations such as "plz" and "ur" are frowned upon.
2. Definition of revert: go back to a previous state. As in "We reverted to the old rules"

[smugcool]

I'd like to make two points.

1. Using "Plzzzzzzz" will not make you any friends on TheScripts. We are trying to maintain a standard of clear, readable English. Consequently, txt-style abbreviations such as "plz" and "ur" are frowned upon.
2. Definition of revert: go back to a previous state. As in "We reverted to the old rules"

Points are noted Killer.Will be followed henceforth.

The code which is given by you is working but i want it in a proper way.As soon as it finishes importing first file. Second files details should be captured just below the first file and so on.
for your example:-
i am having two files and details are as follows
file 1 contains data like below
700000747920061 0;VISHA TRANSPORT CO ;MH 04BG 163 ;05;0;03;00;200 80331;00;5164;2 147483647;0;0;0 ;2147483647;0;9 99999999;999999 999;0;2500000;0 ;0;0;999999999; 0;0;03;20080331 ;2124560708;200 70505;20070505
700000747920011 5;VISHA TRANSPORT CO ;MH 04BG 9268 ;05;0;03;00;200 80331;00;8427;2 147483647;0;0;0 ;2147483647;0;9 99999999;999999 999;0;2500000;0 ;0;0;999999999; 0;0;03;20080331 ;2124556116;200 70505;20070505
700000747920081 8;VISHA TRANSPORT CO ;MH 04BU 3646 ;05;0;03;00;200 80331;00;7743;2 147483647;0;0;0 ;2147483647;0;9 99999999;999999 999;0;2500000;0 ;0;0;999999999; 0;0;03;20080331 ;2124552932;200 70505;20070505
700000747920051 1;VISHA TRANSPORT CO ;MH 04BU 414

second file is having the same format and data.

Now how it will import first file data and then second file data.

[kadghar]

Points are noted Killer.Will be followed henceforth.

The code which is given by you is working but i want it in a proper way.As soon as it finishes importing first file. Second files details should be captured just below the first file and so on.
for your example:-
i am having two files and details are as follows
file 1 contains data like below
700000747920061 0;VISHA TRANSPORT CO ;MH 04BG 163 ;05;0;03;00;200 80331;00;5164;2 147483647;0;0;0 ;2147483647;0;9 99999999;999999 999;0;2500000;0 ;0;0;999999999; 0;0;03;20080331 ;2124560708;200 70505;20070505
700000747920011 5;VISHA TRANSPORT CO ;MH 04BG 9268 ;05;0;03;00;200 80331;00;8427;2 147483647;0;0;0 ;2147483647;0;9 99999999;999999 999;0;2500000;0 ;0;0;999999999; 0;0;03;20080331 ;2124556116;200 70505;20070505
700000747920081 8;VISHA TRANSPORT CO ;MH 04BU 3646 ;05;0;03;00;200 80331;00;7743;2 147483647;0;0;0 ;2147483647;0;9 99999999;999999 999;0;2500000;0 ;0;0;999999999; 0;0;03;20080331 ;2124552932;200 70505;20070505
700000747920051 1;VISHA TRANSPORT CO ;MH 04BU 414

second file is having the same format and data.

Now how it will import first file data and then second file data.

Hi!!

Yeap, you just had to change the cells( , ) part since they are all of the same size, anyway i put some blank spaces finder so it'll be mor generalized. so check it out

Sub Importar()
Dim Str1 As String
Dim i As Integer
Dim j As Long
Dim Boo1 As Boolean

With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = True
.Filters.Clear
.Filters.Add "Archivos Punto y Coma (*.cvs)", "*.cvs"
.Filters.Add "Archivos Texto (*.txt)", "*.txt"
.Filters.Add "Todos los Archivos (*.*)", "*.*"
.Show
If .SelectedItems.Count > 0 Then
Worksheets(1).Activate
j = 1
For i = 1 To .SelectedItems.Count
    Str1 = "TEXT;" & .SelectedItems.Item(i)
    With ActiveSheet.QueryTables.Add(Connection:=Str1, Destination:=Cells(j, 1))
    .TextFileSemicolonDelimiter = True
    .Refresh BackgroundQuery:=False
    End With
    Boo1 = False
    While Boo1 = False
        j = j + 1
        If Cells(j, i).Value = "" Then
            Boo1 = True
        End If
    Wend
Next
End If
End With
End Sub