Solved

bulk csv

Posted on 2014-11-10
5
148 Views
Last Modified: 2014-11-25
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?
0
Comment
Question by:pma111
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40432520
yes.  how big are the files.
i mean each of them approx  number of rows?
0
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40432525
0
 
LVL 3

Author Comment

by:pma111
ID: 40432532
Max file size is 117kb min is 1kb typically between 2 and 50 rows
0
 
LVL 26

Accepted Solution

by:
ProfessorJimJam earned 500 total points
ID: 40432542
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

0
 
LVL 70

Expert Comment

by:Qlemo
ID: 40433048
Shouldn't line 28 be the same as 16?
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question