Back to Fred Mac Donald's Blog
No one can argue against the fact that spreadsheets are the tool of choice to read, view and manipulate rows and columns of information.
With the onset of online databases the question arises how do can we export the information stored in a database and view/manipulate the information off-line.
Looking at this screenshot of the source code of an Excel spreadsheet you will see a lot of funny characters before the actual information is displayed.
The “funny characters” is the means Microsoft is storing the display and formatting information of the spreadsheet and if a data manager wants to export a dataset from a database to a spreadsheet format, it would be a requirement to understand how and what Microsoft is storing in the “funny” part. That will not only make the exporting program complex but adds an unknown number of extra things that can possibly go wrong and or corrupt your exported information.
The solution is to use a “comma-separated values” file (CSV). It is a well-established standard that is used to export and import information across most, if not all databases and the most efficient method to get data from a remote database into a spreadsheet.
As the name implies, the values in your data are separated by commas.
Consider this screenshot of a raw CSV data-set.
With a bit of luck, the CSV file will automatically be associated with your spreadsheet application. Problem is that it might not exactly understand what to do with it.
If your data looks like the following after you opened it in the spreadsheet, you will need to take some additional actions before your next attempt.
Basically, your spreadsheet application did not “understand” how the data is separated and stuck all the columns into the same column instead of separating them.