- Calculators & Maps
- CoolCA Challenge
- Resources & Materials
- Contact Us
Useful Excel trick- CSV data
CSV is known as Comma Separated Values. A CSV file is a specially formatted plain text file which stores spreadsheet or basic database-style information in a very simple format, with one record on each line, and each field within that record separated by a comma. It is often used as a simple way to transfer a large volume of spreadsheet or database information between programs. Microsoft Excel has made it really easy to import CSV files and to create one from spreadsheet data.
There are some other programs out there that may not be able to speak with Excel. That is when CSV comes into play. Any sorts of database or accounting programs, or Word or txt. documents can be exported as CSV extension files and be imported into Excel. From there you can export from all sorts of other programs to file types you wish to create.
If you have CSV data in a .doc document, copy all the dText to Column feature under the tab Data.
The Convert Text to Column Wizard will appear. Choose the file type that best describe your data, then set the appropriate delimiters (tabs, semicolon, comma etc.) and from there you can immediately see the effects on the data review screen to allow you to track your progress. Lastly, the last step involves setting the data format and de-selects those data that you do not wish to import.
If you have CSV data in a .txt document, you may open the .txt.file from Excel directly by using the Open command, Ctrl + O. The Text Import Wizard will appear and the procedures are the same.
For other extension file formats (such as Microsoft Access database, a web page, XML file, SQL Server), go to Get External Data from the Data menu and perform the same steps outlined above. Excel’s Data Connection Wizard (from the same drop down menu) would help you identify the types of files that it supports.
This is an incredibly useful instrument for data collection and analysis work as it allows for hassle-free conversion and transfer of different extension files.