How Can I Save a Single Excel Worksheet to a CSV File?

How Can I Save a Single Excel Worksheet to a CSV File?

  • Comments 10
  • Likes

Hey, Scripting Guy! Question

Hey, Scripting Guy! I’d like to be able to open an Excel spreadsheet, get all the information off one of the worksheets, and then save that worksheet data to a comma-separated values file. How can I do that?

-- SS

SpacerHey, Scripting Guy! AnswerScript Center

Hey, SS. You know, you just have to love Excel. Yes, we know, to be “cool” these days you’re supposed to say that you don’t use Excel, that you use one of those Office wannabes instead. But we’d like to see one of those Office wannabes carry out a task like this, and in just a few lines of code to boot.

Now, to be honest, when we first read the question we weren’t sure how easy this was going to be. Turns out it was this easy:

Const xlCSV = 6

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\Testsheet.xls")
objExcel.DisplayAlerts = FALSE
objExcel.Visible = TRUE

Set objWorksheet = objWorkbook.Worksheets("Sheet1")
objWorksheet.SaveAs "c:\scripts\test.csv", xlCSV

objExcel.Quit

That’s it; that’s the whole script right there. We begin by creating a constant named xlCSV and setting the value to 6; we’ll use this constant later on to specify the file format for our new file. We then create an instance of the Excel.Application object and use the Workbooks.Open method to open the spreadsheet C:\Scripts\Testsheet.xls. (We also set the Visible property to TRUE, but that’s just so you can see what’s going on. If you’d prefer to have Excel run in an invisible window, then just leave this line out.)

Oh, and we have one other line of code that might be of interest:

objExcel.DisplayAlerts = FALSE

This line of code tells Excel not to display any message boxes, but to instead go with the default value any time it has a question. Why do we bother with this? Well, we’re going to save worksheet information to a CSV (comma-separated values) file. If that file already exists Excel will pop up a message box asking if we want to overwrite the existing file. For this script we’re assuming that we always want to overwrite the existing file. By setting the DisplayAlerts property to FALSE we suppress the display of that message box and tell Excel to go ahead and carry out the default operation. In this case, the default operation just happens to be, “Go ahead and overwrite any existing file.”

After we have Excel up and running it takes just two lines of code to save worksheet data to a CSV file. First, we bind to the desired worksheet (in this case, the worksheet named “Sheet1”):

Set objWorksheet = objWorkbook.Worksheets("Sheet1")

After that we simply call the SaveAs method, passing two parameters: the path to CSV file (C:\Scripts\Test.csv) and the format for the new file. Because we want this saved as a CSV file, we use the xlCSV constant we defined way back when. Thus:

objWorksheet.SaveAs "c:\scripts\test.csv", xlCSV

All we have to do now is call the Quit method to terminate our Excel instance and we’re done. Now try and tell us that one of those Excel wannabes is as good as the real thing!

Shameless Self-Promotion: If you’re interested in scripting Microsoft Office applications, be sure and check out the Learn about Scripting for Microsoft Office.  

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • Thanks Scripting Guy, a lot of the code above was really helpful. However in the .csv file, cells with dates starting with a zero (like 01-Feb-2011) get the leading zeroes chopped off (to 1-Feb-2011). This is a real problem for me as I need to import the CSV file into a database and really must have fixed length records to do that. Is there any way in the above code to keep leading zeroes?

  • There is bound to be a number style enumeration that will tell it to permit leading zeroes but I do not know what it is off the top of my head. It will be documented on MSDN.

  • I have one Excel file with multiple worksheets - can I save the worksheets

    as individual Excel files?

  • Can we do in just in excel as a save as?

  • This is great stuff. What if I want to save the file as PDF? What other parameters can be scripted for SaveAs PDF file? Excel GUI shows that there are many options available for SaveAs PDF but neither TechNet nor MSDN mentioned how to script them. Thanks!

    Regards,

    Wei-Keong (cybercwk @ outlook.com)

  • # save as a PDF

    $pdffilename='c:\temp\book1.pdf'

    $excel.ActiveWorkbook.ExportAsFixedFormat([Microsoft.Office.Interop.Excel.XlFixedFormatType]::xlTypePDF,$pdffilename)

  • @Betsy yes you can save as a CSV from Excel.

  • @Carmen, that is an interesting idea. I will try to write a blog post about that.

  • Try this Excel Converter : www.aspose.com/.../excel-component.aspx

    it can convert excel file to csv i hope it will also solve your problem. It uses .NET language to convert excel file i hope the language is not a problem. you can also select java language for this purpose also. All the best.

  • Thanks, this is a great post and saved me lot of time. Is there a way to have quotes around all the fields (not only if it contains ,) while converting to csv?