The other day, i received the following email:
Hey there, what would be the easiest way to save my Excel 2016 spreadsheet content into a “comma separated value” file that i can pass to a supplier i work with? All in all, it’s just one worksheet that i need to send out to them once a month?
Thanks for the question. There are several options, depending of how much manual work you would like to do. First off, let me start by explaining that CSV stands for Comma Separated Value. In the CSV file the values in each row are delimited by commas.
Export to CSV from Excel
If your spreadsheet has only one worksheet, then it is very simple to write it into Excel.
- First off, open your spreadsheet.
- In the Ribbon, hit the File Menu.
- Then, from the left hand side bar, hit Export.
- Hit Change File Type
- Then pick the CSV format as shown below.
- Hit Save As.
- Specify the location in which the file will be saved, type the new file name and hit OK.
Important Note: If you are trying to save a workbook with multiple worksheets, you’ll be promptly notified that one the active Worksheet will be saved.
- Hit OK and close the Excel file.
- Now you can go ahead and send the CSV file by email or share it on Onedrive, or other file sharing method.
Note that if you would like to save multiple sheets, you will need to save each individually as a CSV file, each having a different name.
Save your Excel as CSV
Here’s an alternative method:
- Open your Excel file.
- Hit File
- Then hit Save As.
- Now pick the saving location.
- Next, specify the file name.
- Finally, select CSV as your file type.
- Then hit OK.
Automating the process
If you have a significant amount of worksheets, you might want to automate the process, either by using Visual Basic for Application Macros, Power Shell or using the Python language and optionally the Pandas library. In case you would like to automate your data exports with Pandas you can look at our tutorials on saving as csv, pickle and Excel in Pandas.