Macro to import .csv files into workbook

L

legend_dairy

New Member
Joined
May 17, 2011
Messages
3
Hello!
I'm looking for a macro that will import multiple .csv files from c:\temp each into their own separate worksheet. Any help would be greatly appreciated!
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
After some research i found this macro:
Dim Fnum As Long
Dim mybook As Workbook
Dim basebook As Workbook
Dim CSVFileNames As Variant
Dim SaveDriveDir As String
Dim ExistFolder As Boolean

'Save the current dir
SaveDriveDir = CurDir

ExistFolder = ChDirNet("C:\temp")
If ExistFolder = False Then
MsgBox "Error changing folder"
Exit Sub
End If

CSVFileNames = Application.GetOpenFilename _
(filefilter:="CSV Files (*.csv), *.csv", MultiSelect:=True)

If IsArray(CSVFileNames) Then

On Error GoTo CleanUp

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Add workbook with one sheet
Set basebook = Workbooks.Add(xlWBATWorksheet)

'Loop through the array with csv files
For Fnum = LBound(CSVFileNames) To UBound(CSVFileNames)

Set mybook = Workbooks.Open(CSVFileNames(Fnum))

'Copy the sheet of the csv file after the last sheet in
'basebook (this is the new workbook)
mybook.Worksheets(1).Copy After:= _
basebook.Sheets(basebook.Sheets.Count)
On Error Resume Next
ActiveSheet.Name = Right(CSVFileNames(Fnum), Len(CSVFileNames(Fnum)) - _
InStrRev(CSVFileNames(Fnum), "\", , 1))
On Error GoTo 0

mybook.Close savechanges:=False

Next Fnum

'Delete the first sheet of basebook
On Error Resume Next
Application.DisplayAlerts = False
basebook.Worksheets(1).Delete
Application.DisplayAlerts = True
On Error GoTo 0

CleanUp:

ChDirNet SaveDriveDir

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End If
Click to expand...
But it requires that I manually select the files and then press open. Any insight in how to change the macro to automatically take all of the .csv files from C:\temp?

Once again thank you for the help!
 
Upvote 0
legend_dairy said:
Hello!
I'm looking for a macro that will import multiple .csv files from c:\temp each into their own separate worksheet. Any help would be greatly appreciated!
Click to expand...


Here is something you may be able to work with?

Code: 
'Combine Workbooks
'By Tommy Miles
'This sample goes through all the Excel files in a specified directory and combines them into
'a single workbook.  It renames the sheets based on the name of the original workbook:
Sub CombineWorkbooks()
Dim CurFile As String, DirLoc As String
Dim DestWb As Workbook
Dim ws As Object 'allows for different sheet types

DirLoc = ThisWorkbook.Path & "\tst\" 'location of files
CurFile = Dir(DirLoc & "*.xls")

Application.ScreenUpdating = False
Application.EnableEvents = False

Set DestWb = Workbooks.Add(xlWorksheet)

Do While CurFile <> vbNullString
    Dim OrigWb As Workbook
    Set OrigWb = Workbooks.Open(Filename:=DirLoc & CurFile, ReadOnly:=True)
    
    ' Limit to valid sheet names and remove .xls*
    CurFile = Left(Left(CurFile, Len(CurFile) - 5), 29)
    
    For Each ws In OrigWb.Sheets
        ws.Copy After:=DestWb.Sheets(DestWb.Sheets.Count)
        
        If OrigWb.Sheets.Count > 1 Then
            DestWb.Sheets(DestWb.Sheets.Count).Name = CurFile & ws.Index
        Else
            DestWb.Sheets(DestWb.Sheets.Count).Name = CurFile
        End If
    Next
    
    OrigWb.Close SaveChanges:=False
    CurFile = Dir
Loop

Application.DisplayAlerts = False
    DestWb.Sheets(1).Delete
Application.DisplayAlerts = True

Application.ScreenUpdating = True
Application.EnableEvents = True

Set DestWb = Nothing

End Sub
 
Upvote 0
Maybe something like this, which does the following...

1) Creates a new workbook with a single worksheet.

1) Opens each CSV file from the specified folder.

2) For each CSV file, it creates a new worksheet, names this new worksheet after the CSV file, and copies the data to this new worksheet.

3) Closes each CSV file.

4) Deletes the original worksheet from the newly created workbook.

Rich (BB code): 
Option Explicit

Sub test()

    Dim wkbDest As Workbook
    Dim wksDest As Worksheet
    Dim strPath As String
    Dim strFile As String
    Dim strData As String
    Dim x As Variant
    Dim Cnt As Long
    Dim r As Long
    Dim c As Long
    Dim i As Long
   
    Application.ScreenUpdating = False

    strPath = "C:\Temp\"
   
    If Right(strPath, 1) <> "\" Then strPath = strPath & "\"
   
    strFile = Dir(strPath & "*.csv")
   
    Do While Len(strFile) > 0
   
        Cnt = Cnt + 1
       
        If Cnt = 1 Then
            Set wkbDest = Workbooks.Add(xlWBATWorksheet)
        End If
   
        Open strPath & strFile For Input As #1
       
            Set wksDest = wkbDest.Worksheets.Add
           
            wksDest.Name = Left(strFile, InStr(1, strFile, ".csv") - 1)
           
            r = 2
            c = 1
            Do Until EOF(1)
                Line Input #1, strData
                x = Split(strData, ",")
                For i = LBound(x) To UBound(x)
                    Cells(r, c).Value = x(i)
                    c = c + 1
                Next i
                r = r + 1
                c = 1
            Loop
           
        Close #1
       
        strFile = Dir
       
    Loop
   
   If Cnt > 0 Then
        Application.DisplayAlerts = False
        wkbDest.Worksheets(wkbDest.Worksheets.Count).Delete
        Application.DisplayAlerts = True
        Application.ScreenUpdating = True
        MsgBox "Completed...", vbInformation
    Else
        Application.ScreenUpdating = True
        MsgBox "No CSV files found...", vbExclamation
    End If
   
End Sub
 
Last edited by a moderator:
Upvote 0
I'm using the macro by Tommy Miles for combining two Excel files. I have two Excel files, each with 2 work sheets. Nothing fancy just rows and columns of data. The macro seems to work fine with one exception. In my 'New' combined Excel file, in one of the worksheets there are some odd text in row 2 and columns CQ, CR, CS, CT. The text are values from within that particular worksheet, but not sure why they are showing up in this row and far out in the columns? Any ideas? Thanks.
 
Upvote 0
I tried this code on a Macbook Pro with Excel for Mac 2011. However, the code won't work. I get an error: error 68 device unavailable.

I changed the path to where the files are located:

strPath = "Oliver:Users:Isa:Desktop:Participants:Try:"

I get the error on this command line:

strFile = Dir(strPath & "*.csv")

I tried removing the * since Mac doesn't accept wild cards (as it was mentioned on a website).

Could anyone help?

Thanks


Domenic said:
Maybe something like this, which does the following...

1) Creates a new workbook with a single worksheet.

1) Opens each CSV file from the specified folder.

2) For each CSV file, it creates a new worksheet, names this new worksheet after the CSV file, and copies the data to this new worksheet.

3) Closes each CSV file.

4) Deletes the original worksheet from the newly created workbook.

Code: 
[FONT=Verdana][COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]

[COLOR=darkblue]Sub[/COLOR] test()

    [COLOR=darkblue]Dim[/COLOR] wkbDest [COLOR=darkblue]As[/COLOR] Workbook
    [COLOR=darkblue]Dim[/COLOR] wksDest [COLOR=darkblue]As[/COLOR] Worksheet
    [COLOR=darkblue]Dim[/COLOR] strPath [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] strFile [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] strData [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] x [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] Cnt [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] r [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] c [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    Application.ScreenUpdating = [COLOR=darkblue]False[/COLOR]

    strPath = "C:\Temp\"
    
    [COLOR=darkblue]If[/COLOR] Right(strPath, 1) <> "\" [COLOR=darkblue]Then[/COLOR] strPath = strPath & "\"
    
    strFile = Dir(strPath & "*.csv")
    
    [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]While[/COLOR] Len(strFile) > 0
    
        Cnt = Cnt + 1
        
        [COLOR=darkblue]If[/COLOR] Cnt = 1 [COLOR=darkblue]Then[/COLOR]
            [COLOR=darkblue]Set[/COLOR] wkbDest = Workbooks.Add(xlWBATWorksheet)
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
        [COLOR=darkblue]Open[/COLOR] strPath & strFile [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Input[/COLOR] [COLOR=darkblue]As[/COLOR] #1
        
            [COLOR=darkblue]Set[/COLOR] wksDest = wkbDest.Worksheets.Add
            
            wksDest.Name = Left(strFile, InStr(1, strFile, ".csv") - 1)
            
            r = 2
            c = 1
            [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]Until[/COLOR] EOF(1)
                Line [COLOR=darkblue]Input[/COLOR] #1, strData
                x = Split(strData, ",")
                [COLOR=darkblue]For[/COLOR] i = [COLOR=darkblue]LBound[/COLOR](x) [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](x)
                    Cells(r, c).Value = x(i)
                    c = c + 1
                [COLOR=darkblue]Next[/COLOR] i
                r = r + 1
                c = 1
            [COLOR=darkblue]Loop[/COLOR]
            
        [COLOR=darkblue]Close[/COLOR] #1
        
        strFile = Dir
        
    [COLOR=darkblue]Loop[/COLOR]
    
   [COLOR=darkblue]If[/COLOR] Cnt > 0 [COLOR=darkblue]Then[/COLOR]
        Application.DisplayAlerts = [COLOR=darkblue]False[/COLOR]
        wkbDest.Worksheets(wkbDest.Worksheets.Count).Delete
        Application.DisplayAlerts = [COLOR=darkblue]True[/COLOR]
        Application.ScreenUpdating = [COLOR=darkblue]True[/COLOR]
        MsgBox "Completed...", vbInformation
    [COLOR=darkblue]Else[/COLOR]
        Application.ScreenUpdating = [COLOR=darkblue]True[/COLOR]
        MsgBox "No CSV files found...", vbExclamation
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
[/FONT]
Click to expand...
 
Upvote 0
Domenic i got error in your code. Do you mind pasting in a text file and upload it.
 
Upvote 0
Domenic said:
Maybe something like this, which does the following...

1) Creates a new workbook with a single worksheet.

1) Opens each CSV file from the specified folder.

2) For each CSV file, it creates a new worksheet, names this new worksheet after the CSV file, and copies the data to this new worksheet.

3) Closes each CSV file.

4) Deletes the original worksheet from the newly created workbook.

Code: 
[font=Verdana][color=darkblue]Option[/color] [color=darkblue]Explicit

Sub[/color] test()

    [color=darkblue]Dim[/color] wkbDest [color=darkblue]As[/color] Workbook
    [color=darkblue]Dim[/color] wksDest [color=darkblue]As[/color] Worksheet
    [color=darkblue]Dim[/color] strPath [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] strFile [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] strData [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] x [color=darkblue]As[/color] [color=darkblue]Variant[/color]
    [color=darkblue]Dim[/color] Cnt [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] r [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] c [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Long[/color]
   
    Application.ScreenUpdating = [color=darkblue]False[/color]

    strPath = "C:\Temp\"
   
    [color=darkblue]If[/color] Right(strPath, 1) <> "\" [color=darkblue]Then[/color] strPath = strPath & "\"
   
    strFile = Dir(strPath & "*.csv")
   
    [color=darkblue]Do[/color] [color=darkblue]While[/color] Len(strFile) > 0
   
        Cnt = Cnt + 1
       
        [color=darkblue]If[/color] Cnt = 1 [color=darkblue]Then[/color]
            [color=darkblue]Set[/color] wkbDest = Workbooks.Add(xlWBATWorksheet)
        [color=darkblue]End[/color] [color=darkblue]If[/color]
   
        [color=darkblue]Open[/color] strPath & strFile [color=darkblue]For[/color] [color=darkblue]Input[/color] [color=darkblue]As[/color] #1
       
            [color=darkblue]Set[/color] wksDest = wkbDest.Worksheets.Add
           
            wksDest.Name = Left(strFile, InStr(1, strFile, ".csv") - 1)
           
            r = 2
            c = 1
            [color=darkblue]Do[/color] [color=darkblue]Until[/color] EOF(1)
                Line [color=darkblue]Input[/color] #1, strData
                x = Split(strData, ",")
                [color=darkblue]For[/color] i = [color=darkblue]LBound[/color](x) [color=darkblue]To[/color] [color=darkblue]UBound[/color](x)
                    Cells(r, c).Value = x(i)
                    c = c + 1
                [color=darkblue]Next[/color] i
                r = r + 1
                c = 1
            [color=darkblue]Loop[/color]
           
        [color=darkblue]Close[/color] #1
       
        strFile = Dir
       
    [color=darkblue]Loop[/color]
   
   [color=darkblue]If[/color] Cnt > 0 [color=darkblue]Then[/color]
        Application.DisplayAlerts = [color=darkblue]False[/color]
        wkbDest.Worksheets(wkbDest.Worksheets.Count).Delete
        Application.DisplayAlerts = [color=darkblue]True[/color]
        Application.ScreenUpdating = [color=darkblue]True[/color]
        MsgBox "Completed...", vbInformation
    [color=darkblue]Else[/color]
        Application.ScreenUpdating = [color=darkblue]True[/color]
        MsgBox "No CSV files found...", vbExclamation
    [color=darkblue]End[/color] [color=darkblue]If[/color]
   
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]
Click to expand...
I got error in this code. Please upload a text file
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Importing data to a table
Replies
4
Views
181
rneilson52
R
F
  • Question Question
Looking for help or guidance on a potential Macro project
Replies
1
Views
90
Rijnsent
Rijnsent
R
  • Question Question
Create an CSV or ICS File
Replies
2
Views
95
RRaively1
R
T
  • Solved
CSV import overwriting the next column after the last one in the CSV file
Replies
7
Views
328
Joe4
Joe4
TryingToLearn
  • Question Question
Open a csv file but don't import all data and don't insert ""
Replies
1
Views
127
DanteAmor
DanteAmor

Forum statistics

Threads
1,224,590
Messages
6,179,750
Members
452,940
Latest member
rootytrip

Share this page

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
Back
Top