Back to Fred Mac Donald's Blog

Open CSV file in Excel or OpenOffice spreadsheet application

Open CSV file in Excel or OpenOffice spreadsheet application

Why we use CSV files and how to import and use the file into a Microsoft Excel or OpenOffice spreadsheet.

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.

Microsoft Excel and similar programs.

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.

Excel Spreadsheet source code

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.

CSV Files

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.

What is a CSV file?

As the name implies, the values in your data are separated by commas.

Consider this screenshot of a raw CSV data-set.

Raw CSV data

  • The first row is your column heading, followed by the rows of your data
  • Your column value id enclosed with a leading and trailing “ ”. e.g. “Name”
  • The columns are separated by a comma. e.g. “ Name”,”Surname”
Opening a CSV file with Excel

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.

Incorrect data format in Excel

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.

Excel Import wizard
  1. Start Excel with a blank workbook open.
  2. Select 'Data' on the ribbon, and then 'From Text'. (If the menu options are greyed out this could be because you do not have a workbook open).
    ​​​​​Select data import
  3. Browse for the *.csv file you want to open and click 'Import'.
  4. In the Text import wizard, ensure the 'Delimited' option is selected. Click Next.
    Select Delimited Option
  5. In the delimiters section, tick 'Comma'. The text qualifier box should show the double-quote symbol. Click Next.
    Delimited Options
  6. Your data can be previewed and you can adjust the delimiter if not correct
    Data Preview
  7. Click Finish to complete the import.
Opening a CSV file with OpenOffice
  1. When attempting to open the CSV file, you will hopefully be presented with the import wizard.
    OpenOffice CSV Import
  2. Select “Separated By”
  3. Select “comma”
  4. Set the “Text delimiter” to “
  5. You can preview the data in the same form and adjust as necessary.

 

Written by:  - 10 Apr, 2018  
comments powered by Disqus
flashy