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

more reading
​https://www.howtogeek.com/348960/what-is-a-csv-file-and-how-do-i-open-it/
​
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 - (a 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, of how 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 row" identifying column names 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 the watch-outs below.

We assume the first row of any file is the column header row. Please don't have a summary or event name in the first row.

A column like "Emails" - is assessed as being full of Email data. We do not allow loading unless the email column has fully formed email data. This means the email data is like

text@something.xyz

This is important - if we allow "bad" emails - our system will be known as a SPAMMER. We need to protect the reputation of our system.

You can test this in any spreadsheet by using the function in google sheets. =isEmail()

Xl is a little more complex....and a sample is included at the end of this article.
​

When you save an excel file - you may create many BLANK 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.

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.

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!

​Big Numbers - "Scientific"

The other common error made is opening a BARCODE file in XL and saving it.

Barcodes are often big numbers like 12 -14 numbers. Like 567890123345

For example, if you have a barcode file - and you open it in XL make sure the barcode column is not converted like above - or the barcode is lost - Format it as text like 'ABC', not a number. Numbers when seen as text are not changed and thus your ticket owner will not be stopped at the gate :)

>>>>>

HERE IS AN EXEL FILE THAT WILL VALIDATE EMAILS AND CHECK FOR DUPLICATE KEY USE

** Remember to save as - CSV when you've added all your guests;

Save-as./....

Thanks for reading this and investing time in your skills which help us provide a useful platform !!!!
​
​