5 Ways to Save Excel Multi-Sheet as CSV Files

If you frequently work with Microsoft Excel, you might find yourself needing to convert or save multiple sheets within a workbook into separate CSV files. This task can be quite repetitive if done manually, but there are several methods to automate or streamline this process. Here are five different ways to accomplish this, each catering to different needs and skill levels:

1. Using Excel's Built-In Functionality

Excel itself offers basic functionality to save a sheet as a CSV:

• Right-click on the sheet tab you want to save.
• Select 'Save As'.
• Choose 'CSV (Comma delimited)' from the 'Save as type' dropdown menu.
• Save the file.

However, this method only allows for saving one sheet at a time.

2. Macro or VBA Automation

For bulk operations, Visual Basic for Applications (VBA) can be employed:

Here's a simple VBA script to save all sheets as separate CSV files:

Sub SaveSheetsAsCSV()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        ws.SaveAs Filename:=ThisWorkbook.Path & “\” & ws.Name & “.csv”, FileFormat:=xlCSV
    Next ws
End Sub

This script iterates through all sheets in the active workbook, saving each as a CSV in the same directory. To use this:

• Press ALT + F11 to open the VBA editor.
• Insert a new module, copy-paste the script, and run it.

🛑 Note: This method requires some understanding of VBA. If you're not familiar, start with the Excel VBA tutorials online.

3. Power Automate (formerly Microsoft Flow)

Microsoft's Power Automate allows you to automate workflows, including file conversions:

1. Sign in to Power Automate.
2. Create a new flow, selecting ‘Excel Online (Business)’ as the trigger.
3. Add an action to ‘List rows present in a table’ from your workbook.
4. Use ‘Create CSV table’ to convert the Excel data to CSV format.
5. Save the CSV file with a ‘Create file’ action in OneDrive or SharePoint.

This method requires a subscription to Power Automate.

4. Command Line with PowerShell

For those comfortable with the command line, PowerShell provides powerful scripting capabilities:

excelFile = "C:\path\to\your\ExcelFile.xlsx"
excel = New-Object -ComObject Excel.Application
workbook = excel.Workbooks.Open($excelFile)

$xlCSV = 6

foreach (ws in workbook.Sheets) {
    ws.SaveAs("excelFile” + ws.Name + ".csv", xlCSV)
}

workbook.Close(false)
$excel.Quit()

This script opens an Excel workbook and saves each sheet as a CSV file.

5. Third-Party Tools or Add-Ins

There are various add-ins and tools available, both free and paid, that can handle bulk CSV conversion:

• ASAP Utilities: An Excel add-in that provides various automation options.
• Excel-to-CSV: An online service or batch file converter tools.
• Openpyxl: A Python library that, when paired with scripts, can convert Excel files to CSV.

Using these tools can significantly reduce manual work by automating the entire process or offering user-friendly interfaces for conversions.

The method you choose depends on your level of technical proficiency, access to software, and the frequency of this task. Each approach has its merits:

• Manual saving is good for occasional needs.
• VBA or PowerShell scripts provide automation for frequent users.
• Power Automate is ideal for those within a Microsoft ecosystem looking to integrate this task into broader workflows.
• Third-party tools offer simplicity at the cost of reliance on external software.

Ultimately, by mastering one or more of these methods, you can streamline your workflow, reduce repetitive tasks, and ensure that your Excel data is efficiently converted to CSV format for various applications.

What is the benefit of saving Excel sheets as CSV?

+

CSV files are universally readable by many different programs, making data exchange and import into other applications seamless. Additionally, CSV files take up less space and are easier to manipulate programmatically.

Can I revert CSV files back to Excel sheets?

+

Yes, you can import CSV files back into Excel or use various online tools to convert CSV back to Excel format, keeping in mind that formatting might not be preserved.

How often should I save my Excel work as CSV?

+

This depends on your workflow. If you regularly need to share data with systems that require CSV format, automating this process would be beneficial. Otherwise, occasional conversion when needed might suffice.

Are there any limitations when using VBA for this task?

+

VBA is powerful for automation within Excel, but it requires manual setup, which can be error-prone, and might not work well in environments where macros are disabled for security reasons.