?
Solved

combine 13 similar files  (without opening each and copy paste)

Posted on 2013-01-13
6
Medium Priority
?
198 Views
Last Modified: 2013-01-13
my bank has csv statements for each month (I called and they do not have for all year)


so how can I combine 13 files

first file downloaded is datafile0.csv to datafile12.cvs
0
Comment
Question by:rgb192
[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
  • 4
  • 2
6 Comments
 
LVL 26

Expert Comment

by:redmondb
ID: 38772855
Hi, rgb192.

Please see attached. Let me know if you want the second and subsequent files to skip a header row.

The code is...
Option Explicit

Sub Process_CSV()
Dim xCSVFileName  As Variant
Dim xCSVFile      As Workbook
Dim xBook         As Workbook
Dim xSheet        As Worksheet
Dim xOut_New_Last As Long

Set xSheet = Sheets.Add
xOut_New_Last = 0

Application.ScreenUpdating = False

    For Each xCSVFileName In Array("datafile0.csv", "datafile1.csv", "datafile2.csv", "datafile3.csv", "datafile4.csv", "datafile5.csv", "datafile6.csv", "datafile7.csv", "datafile8.csv", "datafile9.csv", "datafile10.csv", "datafile11.csv", "datafile12.csv")
        Set xCSVFile = Workbooks.Open("D:\" & xCSVFileName)
    
        ActiveSheet.UsedRange.Copy Destination:=xSheet.Range("A" & xOut_New_Last + 1)
        xOut_New_Last = xSheet.Range("A1").SpecialCells(xlLastCell).Row
        
        xCSVFile.Close savechanges:=False
    Next
  
Application.ScreenUpdating = True
  
End Sub

Open in new window

Regards,
Brian.Merge-CSV-s.xlsm
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38772882
rgb192,

The attached skips row 1 for all files except the first. The code is...
Option Explicit

Sub Merge_CSVs()
Dim xCSVFileName As Variant
Dim xCSVFile     As Workbook
Dim xBook        As Workbook
Dim xSheet       As Worksheet
Dim xOut_Last    As Long
Dim xCSV_Last    As Long
Dim xCount       As Long

Set xSheet = Sheets.Add

Application.ScreenUpdating = False

    For Each xCSVFileName In Array("datafile0.csv", "datafile1.csv", "datafile2.csv", "datafile3.csv", "datafile4.csv", "datafile5.csv", "datafile6.csv" _
                                 , "datafile7.csv", "datafile8.csv", "datafile9.csv", "datafile10.csv", "datafile11.csv", "datafile12.csv")
        Set xCSVFile = Workbooks.Open("D:\" & xCSVFileName)

        xCount = xCount + 1
        If xCount <> 1 Then xOut_Last = xSheet.Range("A1").SpecialCells(xlLastCell).Row
        xCSV_Last = ActiveSheet.Range("A1").SpecialCells(xlLastCell).Row
        
        ActiveSheet.Range(IIf(xCount = 1, 1, 2) & ":" & xCSV_Last).Copy Destination:=xSheet.Range("A" & xOut_Last + 1)
        
        xCSVFile.Close savechanges:=False
    Next
  
Application.ScreenUpdating = True
  
End Sub

Open in new window

Regards,
Brian.Merge-CSV-s-V2.xlsm
0
 

Author Comment

by:rgb192
ID: 38772898
yes row 1 is the header


how do I run your code
can I create a file in notepad and right click on your code on the desktop
0
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!

 
LVL 26

Accepted Solution

by:
redmondb earned 2000 total points
ID: 38772919
rgb192,

Just open Merge-CSV-s-V2.xlsm and click on the blue button.

Alternatively, simply opening the attached version causes the macro to run. So you could leave this on your Desktop and just double-click on it to do the job.

Edit: Please let me know which folder the CSV's are in and I'll update the macro - currently it looks for them in "D:\". If you prefer, the macro could prompt you each time for the folder.

Edit2: I've just remembered that there's a bug in at least some versions of Excel for users whose system date is not in "mm/dd/yyyy" format - if a macro reads in a CSV file with a date column then the date is messed up. What format is your system date?

Edit3: The blue button in the attached now points to the correct macro.

Regards,
Brian.Merge-CSV-s-V3.xlsm
0
 

Author Closing Comment

by:rgb192
ID: 38773162
thanks
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38773169
Thanks, rgb192.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

800 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