How to make your CSV file not change your data when opening in Excel
Customise Excel to Leave Data Unchanged
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 example, the leading zero's are disappearing on an invoice number - 001257 is displayed as 1257 or on bank details.
Using Microsoft Excel
The first step is to not open your CSV file when it downloads to your computer. Just leave it where it is for the moment.
- Next you need to open a blank Excel file
- Click on Data> select 'From Text/CSV' > find your CSV file on your computer > Import. This will open a new pop up window.
- Under Delimiter section> Select Comma > Load
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.
If the data is still being changed, try setting Data Type Detection to 'Do not detect data types".
Using Google Sheets
For those who don't have Microsoft Excel, you can try to do so similarly using Google Sheets
- File > Import > Upload your selected file.
- Upon import, uncheck the "convert text to numbers, dates, formulas" > Import Data and you can review the data from Sheets.
- This will now hold your leading 0's. Once you're happy with your list, File > Download > csv.
Use that file to upload direct into Lightyear.
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 ...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 ...What Gets Redirected From Lightyear?
Where to add a redirect? If your Lightyear account receives an email that does not contain an invoice, we will forward the email to this address so that you don't lose anything. It's best to set this as an email account that you check regularly, just ...