Sometimes you need to export your Ragic data onto CSV files due to personal preferences or system limitations (such as exceeding the 5000 record limit when exporting to Excel). However, when opening the CSV file using Excel, you may notice that the first zeroes of a field value have disappeared. Please refer to the following example:
Here are the Ragic records with phone numbers you try to export to a CSV file. The phone numbers are displayed completely and correctly.
But when you open the file directly using Excel, the first zeroes of the phone numbers are gone.
This is not a Ragic bug, but because Excel and CSV manage different types of data and data formats differently; CSV files save your data as plain text only, whilst Excel can organize your data as numbers, dates, text, etc.
If you use Notepad or TextEdit to open the CSV file, you’ll find that the first zeroes of the phone fields are correctly exported.
This means when opening this CSV file in Excel, the phone numbers or other text strings containing only consecutive numbers are considered as numerical values, so the first zero will be removed.
In this case, to display the first zeroes of your phone numbers using Excel, please follow the steps below:
Step 1: Opening the Text Import Wizard
Please open the file in Excel instead of directly opening the file using Excel.
When Excel detects that you are opening a CSV file, you will be guided to a Text Import Wizard window.
Ragic uses delimiters to distinguish every field, so you can click next without changing anything in this step.
Step 2: Selecting delimiters and previewing
In this step, you will need to select the delimiters according to the separator settings applied when exporting your Ragic data. The default separator on Ragic is a comma, but if you use other separators, you can modify the delimiter settings and preview the result.
If you see a solid vertical line, it means that the delimiters have successfully distinguished your fields. If your fields are distinguished correctly, you can click “Next” to proceed.
Step 3: Changing the Data Format
After distinguishing your fields, the text import wizard will ask for the format of every field. As mentioned earlier, the first zeroes are removed by Excel because it was considered as a number, and numeric values should not start with a zero.
To prevent this situation, please click the “Phone” column and then set its format as Text. This will allow Excel to keep the whole string.
Click “Finish” when you’re done, and Excel will open the CSV file without removing the first zeroes of phone numbers.