Tips for working with CSV files

Tips & Tricks for both Qarbon hosted and Self-Hosted

Moderators: James, Rado

Site Admin
User avatar
Posts: 51
Joined: Sat May 27, 2006 1:27 am
Location: Dolny Kubin, Slovakia

Tips for working with CSV files

Postby Rado » Fri Feb 03, 2012 12:55 pm

In ViewletCentral, you can export most of the data into CSV files. CSV stands for Comma Separated Values, and is one of the most universal file formats for data exchange between different applications and platforms. A CSV file is basically a plain-text file with rows representing records, and, as the name implies, individual fields on a row are separated by commas. CSV format is pretty flexible and allows you to use just about any character as field separator (semicolon, or tab separated fields are also common) but comma is the most often used one.

Popular spreadsheet applications such as Microsoft Excel or OpenOffice Calc support the CSV format and will open CSV files exported by ViewletCentral. Sometimes they need a little help though.

Microsoft Excel

If you're using English version of Excel (or more generally - if your computer's regional settings specify point as the decimal number separator) you can stop reading - Excel will open ViewletCentral files properly if you just use the File - Open command, or double-click CSV files in Explorer (assuming the CSV file extension is associated with Excel).

However, if you're using a localized version of Excel in a region that's using comma as the decimal separator, Excel will expect semicolon in place of the field separator in all CSV files. It will not give you an error when you open them though. Instead, it will attempt to open them, but most of the time you'll end up with all fields in the first cell of each row rather than having fields split into columns properly.

To make sure Excel opens CSV files properly, you'll need to use the Import command and specify comma as the field separator. To do this in Excel 2007, select "Data" and then click "From Text" to open the Text Import Wizard. In the first screen, select "Delimited" instead of "Fixed Width" and click "Next" to proceed. In the second screen, make sure you select "Comma" as the delimiter, and double quote (default) as the "Text Qualifier". At this point, you can click "Finish" to import the file, or you can click "Next" to customize data types for individual columns in the third screen of the wizard.

OpenOffice Calc

Whenever you open a CSV file in OpenOffice Calc, it will display a Text Import dialog that allows you to fine-tune the import settings. For best results, select:
  • "Unicode (UTF-8)" in the Character Set field
  • "Separated by" option instead of "Fixed Width"
  • "Comma" as the Field Separator
  • double quote (default) as the "Text Delimiter"
When done, click "OK" to import the CSV file. Screenshot below shows the recommended settings.

OpenOffice CSV settings

Return to FAQs - VCL

Who is online

Users browsing this forum: No registered users and 1 guest