Wednesday, September 23, 2015

How to create a CSV file?

The present post describes creating a Comma Separated Value (CSV) file using Microsoft Excel.

CSV files are very popular and frequently used data transformation formats since legacy data are usually of this type. In recent times XML and JSON formatted data has replaced them.

However, there is a whole lot of legacy data that needs to be loaded on to more recent databases. Hence, every database vendor provides a program to accomplish this conversion. Also programs exist which takes a CSV file and convert it into an XML file. Perhaps this is another route one can take in data conversions for legacy data.

You can create a CSV file using Microsoft Excel in all versions.
Here is an example of a CSV file.
First Name,Last Name,AGE,Rent
Chris ,Langer,40,2500
Tom ,Higgins,35,4000

The first row in the above are headers (providing column names) and the rest is data.

Step 1:
Create an Excel file as shown by typing in the cell entries after launching the Microsoft Excel (herein Excel 2010).


Step 2:
Click File to display drop-down. You will be saving the file as names in the CSV format.


Step 3: Click Save As to open the Save As dialog as shown. You have a variety of options to choose from. Pick MS-DOS (CSV )as the Excel file type as shown.


Step 4:
Provide a name for the file and accept the default folder. You get the following warning:

Excel Permissions

Accept the provided location (My Documents) by cliking Yes. The document gets saved to the location.

 A word of caution. If you have multiple sheets (usually when launched there will be three sheets). Delete the two extra sheets and just keep one sheet. You will get an error message if you have more than one sheet while saving it as a CSV file.

No comments: