Solved

Can I change an Excel file to CSV (Comma Delimited) in a batch

Posted on 2014-02-13
4
717 Views
Last Modified: 2014-02-19
I have daily excel files that I would like to convert to CSV (Comma Delimited) with a batch.  When I manually convert the files I get Microsoft advising me some features are not compatible and do I want to keep the workbook in this format, so I click Yes and it converts to CSV.  Then when I try to close the book, it asks do I want to save changes made to CSV file.  I click no and it closes and I have my CSV file ready to go.  

I would like to create a batch to transfer the files to CSV (Comma Delimited) and close the files.  

Also, if possible I would like to add today's date on each file. Example 2014-02-13

File Names:
Daily Red Wagons
Daily Red Trains
Daily Blue Wagons
Daily Blue Trains

Thanks,
0
Comment
Question by:NNiicckk
  • 2
4 Comments
 
LVL 52

Expert Comment

by:Bill Prew
ID: 39855840
No way to do this in a pure batch (BAT) script solution.  Would a vbscript (VBS) script solution be acceptable?

~bp
0
 

Author Comment

by:NNiicckk
ID: 39856445
Yes, a VBS script would be acceptable.
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 39857502
PowerShell is another option. The code is nearly the same as in VBS.
0
 
LVL 52

Accepted Solution

by:
Bill Prew earned 500 total points
ID: 39858018
Here's a VBS approach, let me know if it doesn't make sense.

Const cExcelCSV = 6

strDir = "B:\EE\EE28363894\Files\"
strExt = ".xls"
strCSV = ".csv"
arrList = Array("Daily Red Wagons","Daily Red Trains","Daily Blue Wagons","Daily Blue Trains")

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
objExcel.DisplayAlerts = False

For Each strFile in arrList
   strPath = strDir & strFile & strExt
   If objFSO.FileExists(strPath) Then
      Set objWorkbook = objExcel.Workbooks.Open(strPath, False, True)
      objWorkbook.SaveAs Replace(strPath, strExt, strCSV), cExcelCSV
      objWorkbook.Close False
      Set objWorkbook = Nothing
   End If
Next

objExcel.Quit
Set objExcel = Nothing
Set objFSO = Nothing

Open in new window

~bp
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Generating a graph via Excel 3 22
Hlookup formula help 14 19
Fixing a embedded format 7 29
Excel 2016 formulas 5 21
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

943 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now