bulk csv

pma111
pma111 used Ask the Experts™
on
I have got 40 or so csv files in a folder i need a way to bulk import them into excel to save time. Can this be done?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
yes.  how big are the files.
i mean each of them approx  number of rows?
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:

Author

Commented:
Max file size is 117kb min is 1kb typically between 2 and 50 rows
Microsoft Excel Expert
Top Expert 2014
Commented:
create a folder in C drive name is Import

put all of your csv files there,  then create a new workbook and name the first sheet "MasterCSV"  then insert a vba module in this new workbook and then run it. it will import all of the csv files in that folder into the mastercsv sheet.

Option Explicit

Sub ImportCSVsWithReference()
'Author:    Jerry
'Date:      10/16/2010
' Import all CSV files from a folder into a single sheet
'           adding a field in column A listing the CSV filenames

Dim wbCSV   As Workbook
Dim wsMstr  As Worksheet:   Set wsMstr = ThisWorkbook.Sheets("MasterCSV")
Dim fPath   As String:      fPath = "C:\Import\"    'path to CSV files, include the final \
Dim fCSV    As String

Application.ScreenUpdating = False  

fCSV = Dir(fPath & "*.csv")         'start the CSV file listing

    Do While Len(fCSV) > 0
      'open a CSV file
        Set wbCSV = Workbooks.Open(fPath & fCSV)
      'insert col A and add CSV name
        Columns(1).Insert xlShiftToRight
        Columns(1).SpecialCells(xlBlanks).Value = ActiveSheet.Name
      'copy date into master sheet and close source file
        ActiveSheet.UsedRange.Copy wsMstr.Range("A" & Rows.Count).End(xlUp).Offset(1)
        wbCSV.Close False
      'ready next CSV
        fCSV = Dir
    Loop
 
Application.ScreenUpdating = True
End Sub

Open in new window

Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
Shouldn't line 28 be the same as 16?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial