By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
462,303 Members | 727 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 462,303 IT Pros & Developers. It's quick & easy.

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

P: 81
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
May 31 '07 #1
Share this Question
Share on Google+
19 Replies

P: 81
Can anybody help plzzzzzzzzzz.............
May 31 '07 #2

kadghar
Expert 100+
P: 1,295
I hope you're using VBA, in that case this code might help you. just add it to a button.

Expand|Select|Wrap|Line Numbers
  1.     Sub Importar()
  2.     Dim Str1 As String
  3.     Dim i As Integer
  4.         With Application.FileDialog(msoFileDialogFilePicker)
  5.             .AllowMultiSelect = True
  6.             .Filters.Clear
  7.             .Filters.Add "Archivos Punto y Coma (*.cvs)", "*.cvs"
  8.             .Filters.Add "Archivos Texto (*.txt)", "*.txt"
  9.             .Filters.Add "Todos los Archivos (*.*)", "*.*"
  10.             .Show
  11.             If .SelectedItems.Count > 0 Then
  12.                 For i = 1 To .SelectedItems.Count
  13.                     If Worksheets.Count < i Then
  14.                         Worksheets.Add After:=Worksheets(i - 1)
  15.                     End If
  16.                     Worksheets(i).Activate
  17.                     Str1 = "TEXT;" & .SelectedItems.Item(i)
  18.                     With ActiveSheet.QueryTables.Add(Connection:=Str1, Destination:=Range("A1"))
  19.                                 .TextFileSemicolonDelimiter = True
  20.                                 .Refresh BackgroundQuery:=False
  21.                     End With
  22.                 Next
  23.             End If
  24.         End With
  25.     End Sub
Good Luck
May 31 '07 #3

P: 81
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.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
For i = 1 To .SelectedItems.Count
If Worksheets.Count < i Then
Worksheets.Add After:=Worksheets(i - 1)
End If
Worksheets(i).Activate
Str1 = "TEXT;" & .SelectedItems.Item(i)
With ActiveSheet.QueryTables.Add(Connection:=Str1, Destination:=Range("A1"))
.TextFileSemicolonDelimiter = 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.
Jun 1 '07 #4

Expert 5K+
P: 8,434
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".
Jun 1 '07 #5

P: 81
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.
Jun 1 '07 #6

Expert 5K+
P: 8,434
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.
Jun 1 '07 #7

P: 81
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.
Jun 1 '07 #8

kadghar
Expert 100+
P: 1,295
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
Jun 1 '07 #9

P: 81
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:=Worksheets(i - 1)
and needed to put
worksheets=Everytthing
And for Range I needed to put
With ActiveSheet.QueryTables.Add(Connection:=Str1, Destination:=Range("i"))

I did so but it doesnot work.

Kindly suggest if i am wrong.
Jun 2 '07 #10

P: 81
you mean to say sud i delete the entire line
Worksheets.Add after:=Worksheets(i - 1)
and needed to put
worksheets=Everytthing
And for Range I needed to put
With ActiveSheet.QueryTables.Add(Connection:=Str1, Destination:=Range("i"))

I did so but it doesnot work.

Kindly suggest if i am wrong.

hi,

Plzzzzzzz revert
Jun 4 '07 #11

kadghar
Expert 100+
P: 1,295
hi,

Plzzzzzzz revert
Sorry for the delay, here you are

Expand|Select|Wrap|Line Numbers
  1. Sub Importar()
  2. Dim Str1 As String
  3. Dim i As Integer
  4. With Application.FileDialog(msoFileDialogFilePicker)
  5. .AllowMultiSelect = True
  6. .Filters.Clear
  7. .Filters.Add "Archivos Punto y Coma (*.cvs)", "*.cvs"
  8. .Filters.Add "Archivos Texto (*.txt)", "*.txt"
  9. .Filters.Add "Todos los Archivos (*.*)", "*.*"
  10. .Show
  11. If .SelectedItems.Count > 0 Then
  12. Worksheets(1).Activate
  13. For i = 1 To .SelectedItems.Count
  14. Str1 = "TEXT;" & .SelectedItems.Item(i)
  15. With ActiveSheet.QueryTables.Add(Connection:=Str1, Destination:=Cells(1, 2 * i))
  16. .TextFileSemicolonDelimiter = True
  17. .Refresh BackgroundQuery:=False
  18. End With
  19. Next
  20. End If
  21. End With
  22. 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
Jun 4 '07 #12

Expert 5K+
P: 8,434
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"
Jun 4 '07 #13

P: 81
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
7000007479200610;VISHA TRANSPORT CO ;MH 04BG 163 ;05;0;03;00;20080331;00;5164;2147483647;0;0;0;2147 483647;0;999999999;999999999;0;2500000;0;0;0;99999 9999;0;0;03;20080331;2124560708;20070505;20070505
7000007479200115;VISHA TRANSPORT CO ;MH 04BG 9268 ;05;0;03;00;20080331;00;8427;2147483647;0;0;0;2147 483647;0;999999999;999999999;0;2500000;0;0;0;99999 9999;0;0;03;20080331;2124556116;20070505;20070505
7000007479200818;VISHA TRANSPORT CO ;MH 04BU 3646 ;05;0;03;00;20080331;00;7743;2147483647;0;0;0;2147 483647;0;999999999;999999999;0;2500000;0;0;0;99999 9999;0;0;03;20080331;2124552932;20070505;20070505
7000007479200511;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.
Jun 5 '07 #14

kadghar
Expert 100+
P: 1,295
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
7000007479200610;VISHA TRANSPORT CO ;MH 04BG 163 ;05;0;03;00;20080331;00;5164;2147483647;0;0;0;2147 483647;0;999999999;999999999;0;2500000;0;0;0;99999 9999;0;0;03;20080331;2124560708;20070505;20070505
7000007479200115;VISHA TRANSPORT CO ;MH 04BG 9268 ;05;0;03;00;20080331;00;8427;2147483647;0;0;0;2147 483647;0;999999999;999999999;0;2500000;0;0;0;99999 9999;0;0;03;20080331;2124556116;20070505;20070505
7000007479200818;VISHA TRANSPORT CO ;MH 04BU 3646 ;05;0;03;00;20080331;00;7743;2147483647;0;0;0;2147 483647;0;999999999;999999999;0;2500000;0;0;0;99999 9999;0;0;03;20080331;2124552932;20070505;20070505
7000007479200511;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

Expand|Select|Wrap|Line Numbers
  1. Sub Importar()
  2. Dim Str1 As String
  3. Dim i As Integer
  4. Dim j As Long
  5. Dim Boo1 As Boolean
  6.  
  7. With Application.FileDialog(msoFileDialogFilePicker)
  8. .AllowMultiSelect = True
  9. .Filters.Clear
  10. .Filters.Add "Archivos Punto y Coma (*.cvs)", "*.cvs"
  11. .Filters.Add "Archivos Texto (*.txt)", "*.txt"
  12. .Filters.Add "Todos los Archivos (*.*)", "*.*"
  13. .Show
  14. If .SelectedItems.Count > 0 Then
  15. Worksheets(1).Activate
  16. j = 1
  17. For i = 1 To .SelectedItems.Count
  18.     Str1 = "TEXT;" & .SelectedItems.Item(i)
  19.     With ActiveSheet.QueryTables.Add(Connection:=Str1, Destination:=Cells(j, 1))
  20.     .TextFileSemicolonDelimiter = True
  21.     .Refresh BackgroundQuery:=False
  22.     End With
  23.     Boo1 = False
  24.     While Boo1 = False
  25.         j = j + 1
  26.         If Cells(j, i).Value = "" Then
  27.             Boo1 = True
  28.         End If
  29.     Wend
  30. Next
  31. End If
  32. End With
  33. End Sub
Jun 5 '07 #15

P: 81
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

Expand|Select|Wrap|Line Numbers
  1. Sub Importar()
  2. Dim Str1 As String
  3. Dim i As Integer
  4. Dim j As Long
  5. Dim Boo1 As Boolean
  6.  
  7. With Application.FileDialog(msoFileDialogFilePicker)
  8. .AllowMultiSelect = True
  9. .Filters.Clear
  10. .Filters.Add "Archivos Punto y Coma (*.cvs)", "*.cvs"
  11. .Filters.Add "Archivos Texto (*.txt)", "*.txt"
  12. .Filters.Add "Todos los Archivos (*.*)", "*.*"
  13. .Show
  14. If .SelectedItems.Count > 0 Then
  15. Worksheets(1).Activate
  16. j = 1
  17. For i = 1 To .SelectedItems.Count
  18.     Str1 = "TEXT;" & .SelectedItems.Item(i)
  19.     With ActiveSheet.QueryTables.Add(Connection:=Str1, Destination:=Cells(j, 1))
  20.     .TextFileSemicolonDelimiter = True
  21.     .Refresh BackgroundQuery:=False
  22.     End With
  23.     Boo1 = False
  24.     While Boo1 = False
  25.         j = j + 1
  26.         If Cells(j, i).Value = "" Then
  27.             Boo1 = True
  28.         End If
  29.     Wend
  30. Next
  31. End If
  32. End With
  33. End Sub

Thank you very much. Its working fine in Excel. Is there any way if the same codes i will put in for a click button in VB 6.0 and it will work. Coz i have added reference of Microsoft excel as well as Microsoft Office in Vb 6.0. And when i put these same code i got error of method "filedialogue of object" application failed. Can u help me out in this issue?
Jun 6 '07 #16

kadghar
Expert 100+
P: 1,295
Thank you very much. Its working fine in Excel. Is there any way if the same codes i will put in for a click button in VB 6.0 and it will work. Coz i have added reference of Microsoft excel as well as Microsoft Office in Vb 6.0. And when i put these same code i got error of method "filedialogue of object" application failed. Can u help me out in this issue?
well, sure you can do it, just remember to create an Excel object from VB so you can work on it. like this:

Expand|Select|Wrap|Line Numbers
  1. Sub Importar()
  2. Dim Str1 As String
  3. Dim i As Integer
  4. Dim j As Long
  5. Dim Boo1 As Boolean
  6. Dim Obj1 As Object
  7.  
  8. Set Obj1 = CreateObject("excel.application")
  9. Obj1.Workbooks.Add
  10.  
  11. With Application.FileDialog(msoFileDialogFilePicker)
  12. .AllowMultiSelect = True
  13. .Filters.Clear
  14. .Filters.Add "Archivos Punto y Coma (*.cvs)", "*.cvs"
  15. .Filters.Add "Archivos Texto (*.txt)", "*.txt"
  16. .Filters.Add "Todos los Archivos (*.*)", "*.*"
  17. .Show
  18. If .SelectedItems.Count > 0 Then
  19. Obj1.Worksheets(1).Activate
  20. j = 1
  21. For i = 1 To .SelectedItems.Count
  22.     Str1 = "TEXT;" & .SelectedItems.Item(i)
  23.     With Obj1.ActiveSheet.QueryTables.Add(Connection:=Str1, Destination:=Obj1.Worksheets(1).Cells(j, 1))
  24.     .TextFileSemicolonDelimiter = True
  25.     .Refresh BackgroundQuery:=False
  26.     End With
  27.     Boo1 = False
  28.     While Boo1 = False
  29.         j = j + 1
  30.         If Obj1.Worksheets(1).Cells(j, i).Value = "" Then
  31.             Boo1 = True
  32.         End If
  33.     Wend
  34. Next
  35. End If
  36. End With
  37.  
  38. Obj1.Visible = True
  39. End Sub
I'm at work right now and i dont have VB6 here, i did the code with vba, but im almost sure it should work for vb6. good luck.
Jun 6 '07 #17

P: 81
well, sure you can do it, just remember to create an Excel object from VB so you can work on it. like this:

Expand|Select|Wrap|Line Numbers
  1. Sub Importar()
  2. Dim Str1 As String
  3. Dim i As Integer
  4. Dim j As Long
  5. Dim Boo1 As Boolean
  6. Dim Obj1 As Object
  7.  
  8. Set Obj1 = CreateObject("excel.application")
  9. Obj1.Workbooks.Add
  10.  
  11. With Application.FileDialog(msoFileDialogFilePicker)
  12. .AllowMultiSelect = True
  13. .Filters.Clear
  14. .Filters.Add "Archivos Punto y Coma (*.cvs)", "*.cvs"
  15. .Filters.Add "Archivos Texto (*.txt)", "*.txt"
  16. .Filters.Add "Todos los Archivos (*.*)", "*.*"
  17. .Show
  18. If .SelectedItems.Count > 0 Then
  19. Obj1.Worksheets(1).Activate
  20. j = 1
  21. For i = 1 To .SelectedItems.Count
  22.     Str1 = "TEXT;" & .SelectedItems.Item(i)
  23.     With Obj1.ActiveSheet.QueryTables.Add(Connection:=Str1, Destination:=Obj1.Worksheets(1).Cells(j, 1))
  24.     .TextFileSemicolonDelimiter = True
  25.     .Refresh BackgroundQuery:=False
  26.     End With
  27.     Boo1 = False
  28.     While Boo1 = False
  29.         j = j + 1
  30.         If Obj1.Worksheets(1).Cells(j, i).Value = "" Then
  31.             Boo1 = True
  32.         End If
  33.     Wend
  34. Next
  35. End If
  36. End With
  37.  
  38. Obj1.Visible = True
  39. End Sub
I'm at work right now and i dont have VB6 here, i did the code with vba, but im almost sure it should work for vb6. good luck.

Thanx dear. I will try it lets hope it will work here.
Jun 7 '07 #18

P: 1
well, sure you can do it, just remember to create an Excel object from VB so you can work on it. like this:

Expand|Select|Wrap|Line Numbers
  1. Sub Importar()
  2. Dim Str1 As String
  3. Dim i As Integer
  4. Dim j As Long
  5. Dim Boo1 As Boolean
  6. Dim Obj1 As Object
  7.  
  8. Set Obj1 = CreateObject("excel.application")
  9. Obj1.Workbooks.Add
  10.  
  11. With Application.FileDialog(msoFileDialogFilePicker)
  12. .AllowMultiSelect = True
  13. .Filters.Clear
  14. .Filters.Add "Archivos Punto y Coma (*.cvs)", "*.cvs"
  15. .Filters.Add "Archivos Texto (*.txt)", "*.txt"
  16. .Filters.Add "Todos los Archivos (*.*)", "*.*"
  17. .Show
  18. If .SelectedItems.Count > 0 Then
  19. Obj1.Worksheets(1).Activate
  20. j = 1
  21. For i = 1 To .SelectedItems.Count
  22.     Str1 = "TEXT;" & .SelectedItems.Item(i)
  23.     With Obj1.ActiveSheet.QueryTables.Add(Connection:=Str1, Destination:=Obj1.Worksheets(1).Cells(j, 1))
  24.     .TextFileSemicolonDelimiter = True
  25.     .Refresh BackgroundQuery:=False
  26.     End With
  27.     Boo1 = False
  28.     While Boo1 = False
  29.         j = j + 1
  30.         If Obj1.Worksheets(1).Cells(j, i).Value = "" Then
  31.             Boo1 = True
  32.         End If
  33.     Wend
  34. Next
  35. End If
  36. End With
  37.  
  38. Obj1.Visible = True
  39. End Sub
I'm at work right now and i dont have VB6 here, i did the code with vba, but im almost sure it should work for vb6. good luck.



What would need to be changed in this to import .xls files and keep their format? I've tried changing a few things but it just keeps corrupting everything.
Jun 9 '07 #19

kadghar
Expert 100+
P: 1,295
What would need to be changed in this to import .xls files and keep their format? I've tried changing a few things but it just keeps corrupting everything.
Why dont you just open an xls (since you don't have to import it) into a new object and work with it?

Expand|Select|Wrap|Line Numbers
  1. Sub AbrirXls()
  2. Dim Obj1 As Object
  3.     With Application.FileDialog(msoFileDialogFilePicker)
  4.         .Filters.Add "Excel Files (*.xls)", "*.xls"
  5.         .Filters.Add "All Files (*.*)", "*.*"
  6.         .Show
  7.         If .SelectedItems.Count > 0 Then
  8.             Obj1.CreateObject ("excel.application")
  9.             Obj1.Workbooks.Open (.SelectedItems.Item(1))
  10.             Obj1.Visible = True
  11.         End If
  12.     End With
  13. End Sub
Now, i'll strongly recomend you to put the

Obj1.CreateObject ("excel.application")

only once, may be when you load the form., and in this code just close the book you're working with before opening a new one, using:

Obj1.ActiveWorkbook.Close savechanges:=False

Hope this helps.

Kad
Jun 11 '07 #20

Post your reply

Sign in to post your reply or Sign up for a free account.