Combining csv files

Posted on 2012-08-14
Medium Priority
Last Modified: 2012-09-25
I have been asked to combine several text (comma delimited files) into an Excel Workbook for analysis purposes.  They are all the exact same format with the same headings.  Is there an way to combine all files in a folder?
Question by:BBlu
LVL 84

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 200 total points
ID: 38294525
Open the first one in a text editor like Notepad.  I would strongly suggest saving it under a new name immediately so you don't lose anything if there is a problem.  Then open the others and copy and paste them into the end of the first / new file and save them.  If they are exactly the same format, they will all load into Excel at once that way.  If for some reason there actually is a difference, then you will still have the original files that you can use.

Author Comment

ID: 38294538
Thanks, Dave.  I thought of that.  Is that the only way you know of?

Accepted Solution

Elton Pascua earned 1200 total points
ID: 38294540
This code should get you started. Save the workbook in the same directory as the CSVs. Record a macro to import one of the files and change in the code accordingly. Also change the path and target of the recorded macro.

Option Explicit

Sub FindCSV()
    Dim strDocPath As String
    Dim strCurrentFile As String
    strDocPath = ThisWorkbook.Path & "\"
    strCurrentFile = Dir(strDocPath & "*.csv*")
    Do While strCurrentFile <> ""
        'Change path and destination accordingly
        With ActiveSheet.QueryTables.Add(Connection:= _
                "TEXT;" & ThisWorkbook.Path & "\" & strCurrentFile, Destination:=Range("$A1000000").End(xlUp).Offset(1, 0))
                .Name = "file1_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 = False
                .TextFileSemicolonDelimiter = False
                .TextFileCommaDelimiter = True
                .TextFileSpaceDelimiter = False
                .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
                .TextFileTrailingMinusNumbers = True
                .Refresh BackgroundQuery:=False
            End With
        strCurrentFile = Dir
End Sub

Open in new window

Industry Leaders: 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 25

Assisted Solution

lwadwell earned 400 total points
ID: 38294611
I would use a combination of DOS command to create a new 'super' file, then open this file in excel and remove the duplicate headers ... see this for instructions http://workawesome.com/productivity/how-to-concatenate-multiple-csv-files-in-excel/

You can use a better directory than c:\ however ... just you the CD command to get to that directory, e.g.
cd c:
cd \path\to\better\directory
LVL 84

Expert Comment

by:Dave Baldwin
ID: 38294908
If you only have to do it once, you'd be done by now.  If you have to do it frequently, then a program is better.
LVL 11

Assisted Solution

by:Sudhakar Kumar
Sudhakar Kumar earned 200 total points
ID: 38294958
You can use simple DOS command to merge multiple csv or text files.

copy *.csv NewCSVFile.csv

Copy all the csv files in a folder and create a batch file copycsv.bat in the same folder and save this command.

Author Closing Comment

ID: 38433867
Thanks, everyone.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

621 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