If you've been working with computers for a period than you would have certainly come across data in EXCEL (XL) format and you may have also come across data as a "CSV" file.

So what's the difference between the two and why do systems tend to upload CSV or TXT files?

XL files

Excel - a Microsoft product - is a very rich and powerful tool - (Spreadsheet). It is an extremely useful tool for managing and generally updating and presenting data. For example, there are a huge number of formulas and functions that can be used in XL. Excel even has its own coding system inside the app! (visual basic)

CSV files
CSV (comma-separated values) files are basic text files where there are NO formats and the columns are separated by the text value of .... a comma ",".

Data in columns can be separated by any symbol or delimiter, It is simply stating this is the "start of one column data and end of the prior column..

Data sharing

Data tends to be shared as "rows of data" - or certainly, that's a significant way computer-to-computer sharing does occur (it's changing to cloud computing but there is still lots of data sharing this way).

Here is an example, a contact might be shared like this in XL.

But represented as a CSV file, it would become;

ID, firstName, lastName, company, title

1, Scott, Hyde, EventHub, COO

The above data has a "header" identifying data types and data separated by the "," symbol.

Conversion

All spreadsheets have the capability to SAVE-AS a CSV file. This will remove all formats and save a comma where there is a column split. Vica - a versa XL can load any CSV file. When doing this please NOTE watch-outs below.

Watch Outs

When you save an excel file - you may create any rows below the data set - this might simply be due to you you clicking on the cell below the data. This is another reason to always "control your data". Know the number of true data rows in your data set and keep it as a reference to control and verify when sharing.

The dreaded leading "O"

XL loves numbers - it's a spreadsheet :).

See my sample ID above of.... "1"
Some systems have IDs like "001" or "0000abc". If this is the case there is an error many people make.

If you open a CSV file in XL with data like "001" - XL will convert it to "1" !!

1 IS a number - 001 is not.


A text file will see it as "001".

There are many ways to deal with this - the simplest being - don't open a CSV file in XL. Alternatively open and make sure you don't lose these 0s by recognizing the column as "TEXT".

** This is also why data sharing via API (in an extremely limited example) is superior. APIs are designed to stop errors like this from happening!

Did this answer your question?