What is a CSV file?
A Comma Separated Values (CSV) file is a plain text file that contains a list of data. These files are often used for exchanging data between different applications. It's a standard text format that does not require a specific application to open or manipulate it. The idea is that you can export complex data from one application to a CSV file, and then import the data in that CSV file into another application such as Claromentis or an external spreadsheet program for your own further analysis.
Working with CSV file
Most spreadsheet applications can open the CSV file but Microsoft Excel is known for automatically applying some formatting that may invalidate data back when it comes to re-import. Here is the most common problem.
1. Character encoding
Most modern web applications and databases are encoded in UTF-8 but Excel often uses default machine encoding such as Western Europe or CP-1252 depending on the regional settings. Learn more about character encoding.
2. Data Auto formatting
When it comes to the date or financial value, Excel is trying to be helpful and recognise these types and convert them, it may be useful for viewing but may not be the best when it comes to reimporting data back.
3. Inclusion of extra rows
When saving an Excel spreadsheet as a CSV we have seen instances where Excel recognises the true 'end' of the sheet as far larger than the actual dataset, which results in thousands or millions of empty rows being included in the export.
When this file is attempted to upload to Claromentis it fails as the file is far too big. To avoid this, when saving Excel content as a CSV first select and delete all the rows below the data range that you want. (use Shift^Ctrl^Down from the first row below your active data range, and then select Edit / Delete / Entire Row from the menu bar)
Note: You may also want to delete unused columns to the right of the active data range if they are empty to optimise the file.
Tips for working with CSV file
Generally, avoid Excel when it comes to manipulating data in CSV format unless your settings can be tweaked to ensure appropriate formatting consistently.
Here are some alternative tools we use which you may want to consider:
1. LibreOffice
LibreOffice is an open-source alternative to Microsoft Excel, you can download it free and it handles Character set and CSV import pretty well.
2. Google Drive
Did you know you can upload CSV to Google Drive,
Manipulate it via Google Sheet and then export it back as CSV.
3. Notepad or Notepad+
You can use a standard text editor to open a CSV file because it is simply just a text file, the problem is the data is not easy to read. Here is an example:
4. CSV Manipulating Apps
Here is list of dedicated apps that can help you working with CSV like a pro, sometimes they are also paid apps:
