Link to home
Start Free TrialLog in
Avatar of Member_2_6492660_1
Member_2_6492660_1Flag for United States of America

asked on

How to merge CSV files into spreadsheet via batch

Using Excel 2010

I have an application that creates several .csv files in the same folder.
I would like to combine all the .csv files into one master spreadsheet.

I used copy d:\inventory\*.csv n:\inventory\master.csv

That works but it is not what I want.

That places all the .csv files into one sheet.

I would like to create and individual sheet for each file

d:\inventory\test1.csv
d:\inventory\test2.csv
d:\inventory\test3.csv

the would create a spreadsheet




sheet1  sheet2  sheet3


Thanks in advance for your help
Avatar of Steve
Steve
Flag of United Kingdom of Great Britain and Northern Ireland image

This can be modified to VBS but I have left it in a workbook for ease of use...
Run the file from the location the text files are in

Option Explicit

Sub Main()
    Dim fso
    Dim CurrentFolder
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set CurrentFolder = fso.GetFolder(ThisWorkbook.Path)
   
    Dim NewWb As Workbook
    Set NewWb = Workbooks.Add
    ProcessFolder CurrentFolder, NewWb
    NewWb.SaveAs CurrentFolder & "\test.xlsx"
    NewWb.Close
End Sub

Sub ProcessFolder(ByVal Folder, NewWb As Workbook)
   Dim Files
   Set Files = Folder.Files
   Dim FromWB As Workbook
   Dim File
   
    For Each File In Files
        If File.Name Like "*.csv" Then
            Set FromWB = Workbooks.Open(File)
            FromWB.Sheets(1).Copy Before:=NewWb.Sheets(1)
            FromWB.Close
        End If
    Next
   
    'REMOVE THE LINES BELOW HERE TO REMOVE SUBFOLDERS
    Dim SubFolders: Set SubFolders = Folder.SubFolders
    Dim SubFolder
    For Each SubFolder In SubFolders
        ProcessFolder SubFolder, NewWb
    Next
    'REMOVE THE LINES ABOVE HERE TO REMOVE SUBFOLDERS

End Sub

Open in new window

Rename-test.xlsm
Avatar of Member_2_6492660_1

ASKER

Thanks for the fast response

I ran it but getting this error

C:\test>cscript.exe csvmerge.vbs *.csv
Microsoft (R) Windows Script Host Version 5.8
Copyright (C) Microsoft Corporation. All rights reserved.

C:\test\csvmerge.vbs(7, 40) Microsoft VBScript compilation error: Expected ')'

Option Explicit

Sub Main()
    Dim fso
    Dim CurrentFolder
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set CurrentFolder = fso.GetFolder(c:\test)
   
    Dim NewWb As Workbook
    Set NewWb = Workbooks.Add
    ProcessFolder CurrentFolder, NewWb
    NewWb.SaveAs CurrentFolder & "\test.xlsx"
    NewWb.Close
End Sub

Sub ProcessFolder(ByVal Folder, NewWb As Workbook)
   Dim Files
   Set Files = Folder.Files
   Dim FromWB As Workbook
   Dim File
   
    For Each File In Files
        If File.Name Like "*.csv" Then
            Set FromWB = Workbooks.Open(File)
            FromWB.Sheets(1).Copy Before:=NewWb.Sheets(1)
            FromWB.Close
        End If
    Next
   
    'REMOVE THE LINES BELOW HERE TO REMOVE SUBFOLDERS
    Dim SubFolders: Set SubFolders = Folder.SubFolders
    Dim SubFolder
    For Each SubFolder In SubFolders
        ProcessFolder SubFolder, NewWb
    Next
    'REMOVE THE LINES ABOVE HERE TO REMOVE SUBFOLDERS

End Sub


What did I do wrong with this line?
 Set CurrentFolder = fso.GetFolder(c:\test)


c:\test is the directory where the .csv files are located
Quotation marks will be required for folder name...
Set CurrentFolder = fso.GetFolder("c:\test")

Open in new window


You are running the macro from Excel?
It is not currently in a format to be run as a VBS.
To run as vbs will need a few tweaks to add the excel objects.
Thanks

VBS is not my specialty  

I need to run this in batch not from within excel

I did add the quotes the error I get now is

C:\test>cscript.exe csvmerge.vbs
Microsoft (R) Windows Script Host Version 5.8
Copyright (C) Microsoft Corporation. All rights reserved.

C:\test\csvmerge.vbs(9, 15) Microsoft VBScript compilation error: Expected end o
f statement
I will look to change the code to run as a script (vbs) rather than Excel macro (VBA).
should I post another question and close this one?
No need to post another question, you are still to get the answer you require.

I am working on the vbs atm, should only be about 30 min.

Does the script need to work on sub-folders?
No the directory structure is this

all .csv files are in E:\inventory

the master.csv output file will be placed in N:\Inventory

Thanks
ASKER CERTIFIED SOLUTION
Avatar of Steve
Steve
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
The Barman

Works great thanks a million.
happy to help.
ATB
Steve.
I finally got a chance to run this on my Widows 2003 server

Had to install Excell 2010 first.

I am getting this error

csvmerge.vbs(23,17) Microsoft Excel Cannot rename a sheet to the same name as another sheet a referenced object library or a workbook referenced by visual basic