ExportToCSV command: automatically convert data from Excel to CSV

XLTools Automation tool allows you to automate tasks in Excel by creating automation commands in a table, without having to create VBA macros.

This tutorial will take you through the setup of the ExportToCSV automation command. Use it when you need to convert ranges from Excel to plain text CSV format as a part of your routine process.

Tip: For non-recurring tasks, use the standard Export to CSV tool on XLTools ribbon.

Before you begin, add the Automation tool to Excel

Automation is one of the 20+ features within XLTools Add-in for Excel. Works in Excel 2019, 2016, 2013, 2010, desktop Office 365.

– free trial gives you 14 days of full access to all features.

Step-by-step example: how to use the ExportToCSV

Say, you have an Excel table with product orders. Every week or so, the file gets updated. Each time you need to convert the orders data to a CSV file.

When you automate this process with the ExportToCSV command, you can run in a button click. Follow the steps below to execute the command yourself.

Feel free to download the sample file used in this tutorial.

1. Insert the ExportToCSV

  1. Open the Excel file with data you want to convert to CSV. In our example, the spreadsheet contains the worksheet "Orders" with a table of recent product orders.
  2. Add a new worksheet Name it, for example, “AUTO”. This worksheet will hold automation commands.
    Tip: it’s best to keep automation commands on a separate worksheet, not to interfere with data.
  3. Select a starting cell where to place the command, e.g. A1.
  4. Open XLTools tab In the Automation group, open the drop-down list Select ExportToCSV.
  5. See the table with the automation command is now added on the worksheet.

2. Specify parameters and define how to convert Excel range to CSV file

Once you’ve inserted the ExportToCSV command, specify its parameters. This automation table defines how to export the Orders table to a CSV file.

XLTools.ExportToCSV
Range:Orders!A:D
csvMethod:ActualValues
CopyToMerged:FALSE
symbols:Semicolon
OpenFile:TRUE
OutputTo:Result.csv

What each line in this command reads:

  • Range: take the range A:D on the sheet Orders. This is the source range that we’ll convert to CSV.
  • csvMethod: choose ActualValues, so that the values in the table are converted to CSV as actual values (as displayed in the formula bar).
  • symbols: choose semicolon as a delimiter between values in the resulting CSV file.
  • OpenFile: choose TRUE to open the resulting file after the command is executed.
  • CopyToMerged: there are no merged cells in the source range, so let’s select FALSE.
  • OutputTo: name the resulting file "Result.csv". The file will be saved to the same destination as the file with automation commands.

See other available options to use in parameters.

3. Execute the command and review the resulting CSV file

  1. Select the table of the automation command In the Automation group, click Execute Commands.
  2. The resulting file "Result.csv" will open. It will open either in Notepad, or in Excel. This depends which program is set as default program for *.csv files on your computer.

    • See that values are exported in actual format.
    • Semicolon is used as a delimiter.
    • The resulting CSV file is saved in the same folder as the automation file.

Done! Now you can assign a custom button to this command.

4. Assign a custom button to the command

If you need to run the command over and over again, add a custom button on the ribbon. So that next time you can execute the command in a click.

  1. In the Automation group, click Add Custom Buttons.
  2. Name the button, e.g. "Export Orders" Specify the range of the ExportToCSV command Click Save.
  3. Done! The custom button will appear on XLTools tab. Now every time you click this button, you will execute the command.
Note: each time you execute the command, the new resulting file "Result.csv" will replace the previous file with this name. So before running the command next time, you can change its name in the OutputTo parameter.

Parameters and available options

When you create automation commands in Excel, this file with commands must be located in the same destination as the source data:

  • Add automation commands in the same workbook as your source data. We recommend that you place the commands on a separate worksheet not to interfere with your data.
  • Or, add automation commands in a separate workbook. Save this automation file in the same folder as the source data.

Below are all parameters and available attributes for the ExportToCSV command.

Tip: select a cell and see a helpful tooltip. Some attributes also have a drop-down list of available options to choose from.

Range:

This parameter specifies which data range should be processed.

  • Required parameter, cannot be blank
  • If you refer to a source workbook, it can be open or closed
  • The source workbook must be located in the same destination as the automation file
OptionsMeaning
Sheet1!A1:B2Range A1:B2 on Sheet1 in the active workbook
Sheet1!A:CEntire A, B, C columns on Sheet1 in the active workbook
Sheet1!1:3Entire 1, 2, 3 rows on Sheet1 in the active workbook
A1:B2Range A1:B2 on the current worksheet
Sheet1!Entire Sheet1 in the active workbook
Sheet1!Table1Table1 on Sheet1 in the active workbook, without table headers
Sheet1!Table1[Column1]Column1 in Table1 on Sheet1 in the active workbook, without table headers
Sheet1!Table1[[Column1]:[Column3]]Columns 1, 2, 3 in Table1 on Sheet1 in the active workbook, without table headers
Sheet1!Table1[#All]Table1 on Sheet1 in the active workbook, with table headers
Sheet1!Table1[[#All],[Column1]]Column1 in Table1 on Sheet1 in the active workbook, with table headers
Sheet1!Table1[[#All],[Column1]:[Column3]]Columns 1, 2, 3 in Table1 on Sheet1 in the active workbook, with table headers
[Book.xlsx]Sheet1!A1:B2

'[Book One.xlsx]Sheet1'!A1:B2
Range A1:B2 on Sheet1 in the workbook Book. Book.xlsx must be located in the same destination as the automation file.
'C:\Documents\[Book.xlsx]Sheet1'!A1:B2Range A1:B2 on Sheet1 in the workbook Book. Book.xlsx must be located in the specified file path location.

Use standard rules of range referencing in Excel, for example:

E.g. if names of worksheets or workbooks contain spaces, enclose the name in single quotes, e.g.'Orders April'!A1:H10, or '[All Orders]Orders April'!A1:H10.

Note: in order for automation commands to identify references, do not use spaces in the names of worksheets, workbooks, or tables.

csvMethod:

This parameter defines how to display values in the resulting CSV file.

  • Required parameter, cannot be blank
OptionsMeaning
ActualValuesThe values will be exported in default format - as actual values displayed in Excel formula bar.
E.g. 0.1, 3/14/2012, 40.00
FormattedValuesThe exported values will match your Excel formatting - as formatted values displayed in cells.
E.g. 10%, 14-MAR-2012, $40

CopyToMerged:

If your source Excel range contains merged cells, you can instruct to duplicate values in merged cells in the corresponding positions in the resulting CSV file.

  • Optional parameter, you can leave it blank or remove from the command altogether
OptionsMeaning
TRUEValues in merged cells in Excel will be duplicated in the resulting CSV file. Recommended if your Excel range has merged cells.
FALSEValues in merged cells in Excel will not be duplicated in the resulting CSV file. Use if your range has no merged cells, otherwise CSV string will have blank values.
(blank)Same as FALSE

symbols:

This parameter defines a separator (delimiter) between values in the resulting CSV file.

  • Required parameter, cannot be blank.
OptionsMeaning
CommaUse comma (,) as a separator (standard option for CSV files)
SemicolonUse semicolon (;) as a separator
TabUse tab (tabulation symbol) as a separator
PipeUse pipe (|) as a separator (also called vertical bar or vertical line)

OutputTo:

This parameter specifies how to name the resulting CSV file and where to save it on your computer.

  • Required parameter, cannot be blank
  • The length of a text string in a cell cannot exceed 250 characters
OptionsMeaning
Result.csvName the resulting file Result.csv. The file will be saved to the same destination as the automation file. If the workbook with automation commands is not yet saved, the default location for output is the Documents folder of the current user.
C:\User\Documents\Result.csvName the resulting file Result.csv and save it to the specified destination on the computer.

OpenFile:

With this parameter you can instruct to open the resulting CSV file after the command is executed. You can immediately review the results.

  • Optional parameter, you can leave it blank or remove from the command altogether
OptionsMeaning
TRUEAfter the command is executed, the resulting file will be opened
FALSEAfter the command is executed, the resulting file will not be opened
(blank)Same as FALSE
Note: the resulting file will open either in Notepad, or in Excel. This depends which program is set as default program on your computer to open *.csv files.


Any questions or suggestions?
How can we help?