How to make your CSV file not change your data when opening in Excel

How to make your CSV file not change your data when opening in Excel

This guide is for people that are having to open their CSV file to edit something and they are finding that Excel is changing their data. For e.g the leading zero's are disappearing on an invoice number - 001257 is displayed as 1257 or  long numbers are being abbreviated - 8240053201820 is shown as: 8.24005E+12

Customise Excel to Leave Data Unchanged

1. The 1st step is to not open your CSV file when it downloads to your computer. Just leave it where it is for the moment.
2. Next you need to open a blank Excel file
3. Click on Data> select 'From Text' > find your CSV file on your computer & click Import> this will open a Text Import Wizard> from here select Delimited> click Next
4. Put a tick in the Comma box & remove other ticks> click Next
5. Next we need to highlight all the boxes at the bottom as we want to change them all to text.
To do this click on the 1st column and hold down the Shift key, scroll to the far right and click on the last column, then release the Shift key - this will turn all columns black. Then click on the word 'Text' above> then click Finish
6. A small Import Data box will pop-up in which you just click on OK
7. Your data file will then be imported into this new file & displayed correctly
8. You can now change what you need to and save it
To save it click on File> Save As>select where you want to save it> name the file and change the 'Save As Type' to be 'CSV (Comma delimited) (*.csv)'

You can now upload the CSV into your software

Remember, if you have opened a csv before this is set, it is more than likely that Excel will have changed the data in some way which will later affect the export.


    Check out our
    to stay up to date

      • Related Articles

      • Is my data secure ?

        Lightyear holds data security to the highest industry standards. We do so with our design of infrastructure, data encryption, ISO certification and additional security features within the Lightyear app. Read on below for more details.  Lightyear is a ...
      • How does Lightyear Data Extraction work?

        Data Extraction Explained Lightyear uses world class data extraction technology to minimise your workload. We strip all of the data, line by line, from your bills, credit notes and statements so you no longer have to spend long hours manually ...
      • What happens when I need to change my accounting software?

        Over time, you may come to a point where you need to change the way you do things. Naturally these things happen as your business needs change as well. One of these changes may involve switching your accounting software package. Lightyear can support ...
      • How to choose destination of a file download from Lightyear

        You may want to set a source file for your lightyear downloads, that is different from your regular downloads folder.    From within Google Chrome, you can select the option to choose a folder destination when you download a File from Lightyear.  ...
      • How do I change my Company name or Lightyear email address?

        Has your company name changed and you need to update your details in Lightyear? That's not a problem. You can change your 'Company Name' and/or 'Trading Name' via the Company Profile section. Navigate to the top right of your screen, you'll see your ...