+ Reply to Thread
Results 1 to 30 of 30

importing multiple csv files into single workbook

  1. #1
    Registered User
    Join Date
    03-03-2011
    Location
    Boston, MA
    MS-Off Ver
    Excel 2011
    Posts
    4

    importing multiple csv files into single workbook

    Hello!

    I could use some help getting a macro together to import a set of .csv files (all in the same folder) into a workbook. Each csv files should have its own tab/worksheet, and the name of the tab should have the name of the csv file. I'm using Excel for Mac 2011 (as far as I know, it has about the same functionality as Excel 2007+).

    Many thanks!

  2. #2
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: importing multiple csv files into single workbook

    Something like this:

    Please Login or Register  to view this content.

    Dom
    "May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."

    Use code tags when posting your VBA code: [code] Your code here [/code]

    Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.

  3. #3
    Registered User
    Join Date
    03-03-2011
    Location
    Boston, MA
    MS-Off Ver
    Excel 2011
    Posts
    4

    Re: importing multiple csv files into single workbook

    Dom, thank you! This is exactly what I needed.

    One question: I can't get Dir to work with the wild card. I can work around this by moving my csv files to their own directory and running the code without the filter, but it seems odd that that's the only way it works.

    I get a run-time error 68 - Device unavailable. Googling around, it looks like it might be a bug specifically with the 2011 Mac version.... any thoughts?

    Thanks again! you just saved me an immense amount of time!

  4. #4
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: importing multiple csv files into single workbook

    To be honest I haven't got the first clue about Excel for Mac so I'm really not sure.

    Dom

  5. #5
    Registered User
    Join Date
    03-03-2011
    Location
    Boston, MA
    MS-Off Ver
    Excel 2011
    Posts
    4

    Re: importing multiple csv files into single workbook

    Hm... It looks like I actually spoke a little too soon. Its making all of the tabs beautifully, but the data that its copying comes from the open sheet of the target workbook, rather than the newly opened csv file (so each tab is an identical copy of the first sheet in the target workbook.)

    Any suggestions on making it copy from the newly opened file instead of the target one?

    thanks for your help!

    (and yeah.... excel for mac is a little screwy, as far as I can tell)

  6. #6
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: importing multiple csv files into single workbook

    My fault, it should be:

    Please Login or Register  to view this content.

    Dom

  7. #7
    Registered User
    Join Date
    03-03-2011
    Location
    Boston, MA
    MS-Off Ver
    Excel 2011
    Posts
    4

    Re: importing multiple csv files into single workbook

    worked beautifully - thank you!!!

  8. #8
    Registered User
    Join Date
    05-24-2012
    Location
    Greenville, SC
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: importing multiple csv files into single workbook

    I changed this line strFileName = Dir(strDir & "*.csv")

    to a .txt extension to import a folder full of txt files. It's only creating a new workbook but it comes out empty. Any help on why this doesn't work?

    ---------------------------------------------------------------------------------
    DISREGARD. I got it to work; it was blocking the macro due to computer settings. thanks
    Last edited by gtparce; 05-24-2012 at 01:40 PM. Reason: Solved Problem

  9. #9
    Registered User
    Join Date
    06-14-2012
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: importing multiple csv files into single workbook

    Hello,

    Brand new to the forum. This macro looks perfect for me but I have the same problems as above. A new blank worksheet is produced. I don't know anything about unblocking macro's so was hoping for a little guidance if possible please?

    Cheers

  10. #10
    Registered User
    Join Date
    02-14-2013
    Location
    MA
    MS-Off Ver
    MAC 2011
    Posts
    1

    Re: importing multiple csv files into single workbook

    Hi! Could you explain a bit more how you got rid of the error 68 problem? I am getting the same problem. Thanks!

  11. #11
    Registered User
    Join Date
    04-09-2014
    Location
    USA
    MS-Off Ver
    2010
    Posts
    6

    Re: importing multiple csv files into single workbook

    Hello, I was wondering if anyone could look into this again. I'm using Excel 2010 and when I run the macro above, it just opens a blank workbook. Thanks

  12. #12
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: importing multiple csv files into single workbook

    Do you have csv files in your folder or some other type?
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  13. #13
    Registered User
    Join Date
    04-09-2014
    Location
    USA
    MS-Off Ver
    2010
    Posts
    6

    Re: importing multiple csv files into single workbook

    The csv files are in a subfolder at C:\Users\mpiet\Documents\Logs

  14. #14
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: importing multiple csv files into single workbook

    Please post your code here so we can help you.

  15. #15
    Registered User
    Join Date
    04-09-2014
    Location
    USA
    MS-Off Ver
    2010
    Posts
    6

    Re: importing multiple csv files into single workbook

    Please Login or Register  to view this content.

  16. #16
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,784

    Re: importing multiple csv files into single workbook

    Try changing

    Please Login or Register  to view this content.
    to

    Please Login or Register  to view this content.
    and see if this works better.

    Alf

  17. #17
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: importing multiple csv files into single workbook

    Change this row from
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

  18. #18
    Registered User
    Join Date
    04-09-2014
    Location
    USA
    MS-Off Ver
    2010
    Posts
    6

    Re: importing multiple csv files into single workbook

    I made the change, but it still just opens a new, blank workspace. If it makes a difference, I am using Excel 2010.

  19. #19
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,784

    Re: importing multiple csv files into single workbook

    Then I would suggest that you record a macro while importing one csv file from the folder where you keep the csv files and post the result here for futher help.

    Alf

  20. #20
    Registered User
    Join Date
    04-09-2014
    Location
    USA
    MS-Off Ver
    2010
    Posts
    6

    Re: importing multiple csv files into single workbook

    I recorded a macro of importing a csv file.
    Please Login or Register  to view this content.
    So what I would like to do is to have it do the whole folder, if possible.

    [Edit] Thanks for all the help so far.

  21. #21
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,784

    Re: importing multiple csv files into single workbook

    You could try someting like this, not absolutely sure as I've not tested it.

    Please Login or Register  to view this content.
    Managed to test the code and it works ok. Made two minore changes. All new sheets will be added at the end in the workbook and the tab name for the sheet will be the file name minus the ".csv" part. Hopefully you have too many letters in the csv file name as there is a limit to the length of the tab name.
    Alf
    Last edited by Alf; 04-09-2014 at 06:33 PM. Reason: Improved the code a bit

  22. #22
    Registered User
    Join Date
    04-09-2014
    Location
    USA
    MS-Off Ver
    2010
    Posts
    6

    Re: importing multiple csv files into single workbook

    Alf, you are the man/alien! That worked exactly how I needed it to.

  23. #23
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,784

    Re: importing multiple csv files into single workbook

    Alf, you are the man/alien!
    Lol yes we had that TV serie in Sweden also.

    Thank for feedback and rep.

    Alf
    Last edited by Alf; 04-10-2014 at 10:21 AM.

  24. #24
    Registered User
    Join Date
    10-14-2014
    Location
    Swansea, Wales
    MS-Off Ver
    Mac 2011
    Posts
    3

    Re: importing multiple csv files into single workbook

    Hi

    Im totally new to this forum, looking for soem help on the code below, im currently getting Error 76.

    Many Thanks

    Sub LoopFiles()
    Dim strDir As String, strFileName As String
    Dim wbSourceBook As Workbook
    Dim wbWriteBook As Workbook
    Dim wsWriteSheet As Worksheet

    strDir = "C:\Users\Lee\Desktop\Jamie" 'specify folder to search
    strFileName = Dir(strDir & "*.csv")

    Set wbWriteBook = Workbooks.Add

    Do While strFileName <> ""
    Set wbSourceBook = Workbooks.Open(strDir & strFileName)
    Set wsWriteSheet = wbWriteBook.Sheets.Add
    wsWriteSheet.Name = strFileName
    wbSourceBook.Sheets(1).UsedRange.Copy wsWriteSheet.Range("A1")
    wbSourceBook.Close False
    strFileName = Dir
    Loop

    End Sub

  25. #25
    Registered User
    Join Date
    10-14-2014
    Location
    Swansea, Wales
    MS-Off Ver
    Mac 2011
    Posts
    3

    Re: importing multiple csv files into single workbook

    Hi Alf

    Can you give me a little help here please?

    Im etting runtime error 76 when running your code, please see code below.

    Im a newbie to the forum.

    Option Explicit

    Sub ExtrFil()
    Dim sPath As String
    Dim fName As String

    Application.ScreenUpdating = False

    sPath = "C:\Users\lee\Desktop\Jamie\"
    fName = Dir("C:\Users\lee\Desktop\Jamie\*.csv")

    Do While Len(fName) > 0

    Sheets.Add After:=Sheets(Sheets.Count)

    ActiveSheet.Name = Mid(fName, 1, Len(fName) - 4)

    With ActiveSheet.QueryTables.Add(Connection:= _
    "TEXT;" & sPath & fName, Destination:=Range("$A$1"))
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 437
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
    End With

    fName = Dir

    Loop

    Application.ScreenUpdating = True

    End Sub

  26. #26
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: importing multiple csv files into single workbook


    Maybe if you create your own thread and post code between tags, both accordingly to forum rules, must read !

  27. #27
    Registered User
    Join Date
    10-14-2014
    Location
    Swansea, Wales
    MS-Off Ver
    Mac 2011
    Posts
    3

    Re: importing multiple csv files into single workbook

    Quote Originally Posted by Marc L View Post

    Maybe if you create your own thread and post code between tags, both accordingly to forum rules, must read !
    Like said Marc im a total newbie and was unaware of the rules.

    Apologies if ive offended you.

    Cheers

  28. #28
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,784

    Re: importing multiple csv files into single workbook

    Hi leejac11

    As Marc said you should post your problem in a thread of your own because "old threads" don't get as much attention as a "new thread" also it's against forum rules.

    As my macro was written for "Excel 2007 - 2010" and you are running the "Mac 2011" version of office I really don't know what the problem could be.

    My best advice is that you start by recording a macro when importing a csv file from the desktop folder to your workbook. You can then compare your macro with the one I wrote and see if you could modify a macro to suit your needs.

    Alf

  29. #29
    Registered User
    Join Date
    01-14-2015
    Location
    Sri Lanka
    MS-Off Ver
    2010
    Posts
    8

    Re: importing multiple csv files into single workbook

    Hi, thanks for th above macro. If the files are saved in a read-only folder. What would be the code?

  30. #30
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,784

    Re: importing multiple csv files into single workbook

    The files could be saved as "read only" files to whatever folder you chose using the "SetAttr" command. Something like

    Please Login or Register  to view this content.
    where "ThisWorkbook.FullName" is a string comtaning path and file name i.e. “C:\Whatever_folder\MyReadOnly_file.xlsx”

    Alf

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1