Change Multiple csv files into xlsx files

Short on time today, but here is a VBS approach (not VBA) I did for another question a short while ago.  Sharing it here in case it's useful for you...

Save as a VBS file, and then run as follows, where the parameter passed in to the VBS is the folder where the CSV files exist (and where the new XLSX files will be placed).

cscript EE29088597.vbs "B:\EE\EE29088597\Files"

'Constants 
Const xlOpenXMLWorkbook = 51             '(without macro's in 2007-2016, xlsx)
Const xlOpenXMLWorkbookMacroEnabled = 52 '(with or without macro's in 2007-2016, xlsm)
Const xlExcel12 = 50                     '(Excel Binary Workbook in 2007-2016 with or without macro's, xlsb)
Const xlExcel8 =56                       '(97-2003 format in Excel 2007-2016, xls)

' Extensions for old and new files
strExcel = "xlsx"
strCSV = "csv"

' Set up filesystem object for usage
Set objFSO = CreateObject("Scripting.FileSystemObject")

' Get folder name to process off the command line, make sure it's valid
If (WScript.Arguments.Count > 0) Then
    strFolder = Wscript.Arguments(0)
    If Not objFSO.FolderExists(strFolder) Then
        WScript.StdErr.WriteLine "Specified folder does not exist."
        WScript.Quit
    End If
Else
    WScript.StdErr.WriteLine "No folder name specified to process."
    WScript.Quit
End If

' Access the folder to process
Set objFolder = objFSO.GetFolder(strFolder)

' Load Excel (hidden) for conversions
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
objExcel.DisplayAlerts = False

' Process all files
For Each objFile In objFolder.Files
    ' Get full path to file
    strPath = objFile.Path
    ' Only convert CSV files
    If LCase(objFSO.GetExtensionName(strPath)) = LCase(strCSV) Then
        ' Display to console each file being converted
        Wscript.Echo "Converting """ & strPath & """"
        ' Load CSV into Excel and save as native Excel file
        Set objWorkbook = objExcel.Workbooks.Open(strPath, False, True)
        objWorkbook.SaveAs Replace(strPath, strCSV, strExcel), xlOpenXMLWorkbook
        objWorkbook.Close False
        Set objWorkbook = Nothing
    End If
Next

'Wrap up
objExcel.Quit
Set objExcel = Nothing
Set objFSO = Nothing

Select allOpen in new window

---

»bp

I'm short of time too and I haven't tested it, but this is Excel VBA. Save in a Standard module in Excel

Option Explicit

Public Sub UpdateCSV()

    Dim fso, sFolder, sFil
    On Error GoTo exit_proc
    Application.ScreenUpdating = False
    Set fso = CreateObject("Scripting.FileSystemObject")

    ''///Change this folder to the one you use.
    Set sFolder = fso.getFolder("C:\Users\Roy Cox\Documents\Shred")

    For Each sFil In sFolder.Files
        If (sFil.Name Like "*.csv") Then
            Workbooks.OpenText Filename:=sFil, _
                               DataType:=xlDelimited, Comma:=True, Local:=True
            sFil.SaveAs sFolder & Application.PathSeparator & sFil & ".xlsx", FileFormat:=51
            sFil.Close True
        End If
    Next sFil
exit_proc:
    Application.ScreenUpdating = True
End Sub

Select allOpen in new window

Hi Bill,

Thank you for your response, this is what is currently happening:

1) I copied the code above and saved it as csv.vbs
2) I went to CMD and typed in cscript csv.vbs "C:\Users\Eitel\Desktop\3rd Party\Work Folder" - This is the path where the csv files are
3) I received this error: Input Error: Can not find script file "C:\Users\Eitel\csv.vbs"
4) I went to "C:\Users\Eitel\csv.vbs" and pasted the csv.vbs file in this location
5) I ran the command again and this is what was displayed:

1. "C:\Users\Eitel\Desktop\3rd Party\Work Folder\TestFile.CSV"

1. C:\Users\Eitel\csv.vbs(44.9) Microsoft Excel: Cannot save as that name. Document was opened as read-only.

I am quite new to scripting and VBA, and I am not the one going to be making use of this script in the future, so would you be able to assist me in making this a "clickable" script rather than requiring someone to open CMD and type in a command? Or perhaps advise me how I can achieve this please?

Thank you.

Hi Roy,

Thank you for your response, this is what is currently happening:

1) I copied the code above and saved it as csv.vbs
2) I run the script and receive the following error:

1. Line 6: Syntax Error

This line is the On Error GoTo exit_proc

3) I comment out Line 6 and run the code again - this time I receive the following error:

1. Line 15: Expected Statement

This line is the Workbooks.OpenText Filename:=sFil, _

You need Bill's code for VBS, my code is to run from Excel VBA

You either need to CD to the folder where the VBS script is, or specify its fully qualified path on that command line, enclosed in double quotes if there are spaces in it.

~bp

Did you get this working?

---

»bp

Hi Bill,

No I have not yet got this working. I apologize for not updating the thread as of yet, was planning to do so once I had a solution.

I have 3 issues:

1) Using CMD to execute the code wouldn't be practical, because it wont be me using it. I am trying to "automate" as much of the workload we have and the other users are not as technically inclined as what I am. Therefore, the easiest (I think - but could be wrong) way of doing this is by having the string path hard coded in the script for execution. That way the user only needs to click on the script file and it will execute.

2) I noticed that while most of the files are csv extensions, some of the files extensions are displayed as .CSV and some are .csv. I am wondering if this will affect the way in which the script is executed?

3) The script above only includes csv extension and not xls. I presume that for me to include xls extension, I would need to have a second IF Statement within the For Each loop?

I have not yet made any progress since my last feedback as I have been working on the rest of this task by automating some of the excel tasks that need to be completed.

I also have this code which works perfectly, however, it's not very dynamic as it literally needs to look for specific file names. I was hoping to shorten this code and make it more dynamic to be able to simply scan the directory based on file type (rather than file name) then perform the task.

Const xlDelimited                =  1
Const xlTextQualifierDoubleQuote =  1
Const xlOpenXMLWorkbook          = 51

Set xl = CreateObject("Excel.Application")

xl.Workbooks.OpenText "C:\Users\xy56079\Desktop\Terminations\Daily Terminations Non HR.csv", , , xlDelimited _
  , xlTextQualifierDoubleQuote, True, False, False, True, False, False, _
  , Array(Array(1,2), Array(2,2), Array(3,2), Array(4,1), Array(5,2) _
  , Array(6,1), Array(7,1), Array(8,1), Array(9,1), Array(10,1), Array(11,1))
Set wb = xl.ActiveWorkbook

wb.SaveAs "C:\Users\xy56079\Desktop\Terminations\Daily Terminations Non HR.xlsx", xlOpenXMLWorkbook, , , , False
wb.Close

xl.Workbooks.OpenText "C:\Users\xy56079\Desktop\Terminations\Daily Terminations SP TOOL", , , xlDelimited _
  , xlTextQualifierDoubleQuote, True, False, False, True, False, False, _
  , Array(Array(1,2), Array(2,2), Array(3,2), Array(4,1), Array(5,2) _
  , Array(6,1), Array(7,1), Array(8,1), Array(9,1), Array(10,1), Array(11,1))
Set wb = xl.ActiveWorkbook

wb.SaveAs "C:\Users\xy56079\Desktop\Terminations\Daily Terminations SP TOOL.xlsx", xlOpenXMLWorkbook, , , , False
wb.Close

xl.Workbooks.OpenText "C:\Users\xy56079\Desktop\Terminations\Daily Terminations.csv", , , xlDelimited _
  , xlTextQualifierDoubleQuote, True, False, False, True, False, False, _
  , Array(Array(1,2), Array(2,2), Array(3,2), Array(4,1), Array(5,2) _
  , Array(6,1), Array(7,1), Array(8,1), Array(9,1), Array(10,1), Array(11,1))
Set wb = xl.ActiveWorkbook

wb.SaveAs "C:\Users\xy56079\Desktop\Terminations\Daily Terminations.xlsx", xlOpenXMLWorkbook, , , , False
wb.Close

xl.Quit

Select allOpen in new window

And I am aware it's not good programming practice to have 3 IF Statements doing the same job, I just haven't had the chance to try create a piece of code that will loop through it.

With regard to your "three issues" above:

1) Using CMD to execute the code wouldn't be practical, because it wont be me using it. I am trying to "automate" as much of the workload we have and the other users are not as technically inclined as what I am. Therefore, the easiest (I think - but could be wrong) way of doing this is by having the string path hard coded in the script for execution. That way the user only needs to click on the script file and it will execute.

Sure, just replace this code:

' Get folder name to process off the command line, make sure it's valid
If (WScript.Arguments.Count > 0) Then
    strFolder = Wscript.Arguments(0)
    If Not objFSO.FolderExists(strFolder) Then
        WScript.StdErr.WriteLine "Specified folder does not exist."
        WScript.Quit
    End If
Else
    WScript.StdErr.WriteLine "No folder name specified to process."
    WScript.Quit
End If

Select allOpen in new window

With this code (assuming you know the folder will exist):

strFolder = "c:\yourfolderpath"

Select allOpen in new window

2) I noticed that while most of the files are csv extensions, some of the files extensions are displayed as .CSV and some are .csv. I am wondering if this will affect the way in which the script is executed?

No, that will not be a problem, all .csv files will be processed, case insentive of extension.  Notice the following line that uses LCase() to effectively do a case insensitive compare of the two extensions.

    If LCase(objFSO.GetExtensionName(strPath)) = LCase(strCSV) Then

Select allOpen in new window

3) The script above only includes csv extension and not xls. I presume that for me to include xls extension, I would need to have a second IF Statement within the For Each loop?

No, you could do this with a single IF statement, with a compound condition, such as (make sure you define strXLS above as well):

    If LCase(objFSO.GetExtensionName(strPath)) = LCase(strCSV) Or LCase(objFSO.GetExtensionName(strPath)) = LCase(strXLS) Then

Select allOpen in new window

---

»bp

I also have this code which works perfectly, however, it's not very dynamic as it literally needs to look for specific file names. I was hoping to shorten this code and make it more dynamic to be able to simply scan the directory based on file type (rather than file name) then perform the task.

I don't think I understand this.  Based on the code I provided it doesn't need the full paths for each file.  Or this a different effort?  A little confused, sorry...

---

»bp

Hi Bill,

Thank you for your comments. I had incidentally made the changes you suggested above on Tuesday shortly after I read your reply.. I took a bit of time to read the code a little and understand what is going on.

Here is the code I put together (before your latest reply):

' Require variables to be defined
Option Explicit

' Global variables
Dim strBaseFolder
Dim strDestFolder
Dim objFSO		
Dim objFolder
Dim objFile

' Define folders to work with
strBaseFolder = "C:\Users\Desktop\3rd Party"
strDestFolder = "C:\Users\Desktop\3rd Party\Work Folder"

' Create filesystem object
Set objFSO = CreateObject("Scripting.FileSystemObject")

' Exit if base folder does not exist
If Not objFSO.FolderExists(strBaseFolder) Then
    Wscript.Echo "Missing base folder : """ & strBaseFolder & """"
    Wscript.Quit
End If

' Exit if dest folder does not exist
If Not objFSO.FolderExists(strDestFolder) Then
    Wscript.Echo "Missing dest folder : """ & strDestFolder & """"
    Wscript.Quit
End If

' Look at each subfolder of base folder
For Each objFolder In objFSO.GetFolder(strBaseFolder).SubFolders
    
        For Each objFile In objFolder.Files
            ' Continue if we want this file
            If IncludeFile(objFile) Then
                
            End If
        Next
Next

' Logic to determine if we process a file
Function IncludeFile(objFile)
    
	IncludeFile = False
    Select Case LCase(objFSO.GetExtensionName(objFile.Path))
        
		' Include only these extensions
        Case "csv", "xls", "xlsx"
		
    End Select
	
End Function

Select allOpen in new window

The above code runs but nothing happens - no errors and no converting of files.

Below is your code, with the amendments made:

'Constants 
Const xlOpenXMLWorkbook = 51             '(without macro's in 2007-2016, xlsx)
Const xlOpenXMLWorkbookMacroEnabled = 52 '(with or without macro's in 2007-2016, xlsm)
Const xlExcel12 = 50                     '(Excel Binary Workbook in 2007-2016 with or without macro's, xlsb)
Const xlExcel8 =56                       '(97-2003 format in Excel 2007-2016, xls)

' Extensions for old and new files
strExcel = "xlsx"
strCSV = "csv"
strXLS = "xls"

' Set up filesystem object for usage
Set objFSO = CreateObject("Scripting.FileSystemObject")

' Get folder name to process off the command line, make sure it's valid
strFolder = "C:\Users\Desktop\3rd Party\Work Folder"

' Access the folder to process
Set objFolder = objFSO.GetFolder(strFolder)

' Load Excel (hidden) for conversions
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
objExcel.DisplayAlerts = False

' Process all files
For Each objFile In objFolder.Files
    ' Get full path to file
    strPath = objFile.Path
    ' Only convert CSV files
    If LCase(objFSO.GetExtensionName(strPath)) = LCase(strCSV) or LCase(objFSO.GetExtensionName(strPath)) = LCase(strXLS) Then
        ' Display to console each file being converted
        Wscript.Echo "Converting """ & strPath & """"
        ' Load CSV into Excel and save as native Excel file
        Set objWorkbook = objExcel.Workbooks.Open(strPath, False, True)
        objWorkbook.SaveAs Replace(strPath, strCSV, strExcel), xlOpenXMLWorkbook
        objWorkbook.Close False
        Set objWorkbook = Nothing
    End If
Next

'Wrap up
objExcel.Quit
Set objExcel = Nothing
Set objFSO = Nothing

Select allOpen in new window

This code executes perfectly, however, I get an error at Line 36:

Cannot save file as that name. Document was opened as read-only.

With regards to your comment below:

I don't think I understand this.  Based on the code I provided it doesn't need the full paths for each file.  Or this a different effort?  A little confused, sorry...

Apologies for the confusion. The code I provided in my last comment was code I was making use of before I came here to look for help. So in actual fact, once I have a working solution for my original post (converting multiple csv files) I will be able to use it anywhere.

I only posted that code to show the logic. But its of no real importance right now..

Hi Bill,

Just an update. I made an amendment in this line:

Set objWorkbook = objExcel.Workbooks.Open(strPath, False, True)

Select allOpen in new window

I changed, the "True" to "False" and it runs. However, it only converts the csv files with the lower case csv extension and excludes the upper case csv extension files as well as the xls files.

The above code runs but nothing happens - no errors and no converting of files.

I realized now that it wont do anything because it has no code to do anything. Sorry about that,

Hi Bill,

Thank you very much for your continuous help! This is exactly what I needed. Thank you again :)