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
LVL 23
Thomas GrassiSystems AdministratorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SteveCommented:
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
0
Thomas GrassiSystems AdministratorAuthor Commented:
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
0
SteveCommented:
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.
0
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Thomas GrassiSystems AdministratorAuthor Commented:
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
0
SteveCommented:
I will look to change the code to run as a script (vbs) rather than Excel macro (VBA).
0
Thomas GrassiSystems AdministratorAuthor Commented:
should I post another question and close this one?
0
SteveCommented:
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?
0
Thomas GrassiSystems AdministratorAuthor Commented:
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
0
SteveCommented:
Can you test:

    Dim FromWorkbook
    Dim ToWorkbook
    Dim fso
    Dim CurrentFolder
    Dim objExcel
    Dim boolFirst
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set CurrentFolder = fso.GetFolder("c:\test")
    Set objExcel = CreateObject("Excel.Application")
    ProcessFolder CurrentFolder

Sub ProcessFolder(ByVal Folder)
   Dim Files
   Set Files = Folder.Files
   Dim File
    For Each File In Files
        If right(File.Name,3) = "csv" Then
            If not boolfirst Then
                Set ToWorkbook = objExcel.Workbooks.Open(File)
                boolFirst = true
            Else
                Set FromWorkbook = objExcel.Workbooks.Open(File)
                FromWorkbook.Sheets(1).Copy ToWorkbook.Sheets(1)
                FromWorkbook.Close
            End If
        End If
    Next
    ToWorkbook.SaveAs "c:\test\test.xls",51
    ToWorkbook.Close False
    Set ToWorkbook = Nothing
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Thomas GrassiSystems AdministratorAuthor Commented:
The Barman

Works great thanks a million.
0
SteveCommented:
happy to help.
ATB
Steve.
0
Thomas GrassiSystems AdministratorAuthor Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.