Skip to main content
All CollectionsTroubleshooting Operate
The Operate generated export file is removing the zeros
The Operate generated export file is removing the zeros

What to do if your export file removes leading 0's

Stuart avatar
Written by Stuart
Updated over a week ago

if your account ID's or nominal codes start with a 0 and you are using a file export from Operate, excel may remove these from the file.

This is because Excel recognises the column as a 'number' and automatically removes the zero, whereas ideally you want excel to recognise the column as 'Text' and then retain the zero.

To resolve this, firstly open a blank new workbook in excel

Then:-

1). Click the Data tab, then From Text/CSV next to the Get Data button. If you don’t see the Get Databutton, go to New Query > From File > From Text and browse to your text file, then press Import.

2). Excel will load your data into a preview pane. Press Edit in the preview pane to load the Query Editor.

3). If any columns need to be converted to text, select the column to convert by clicking on the column header, then go to Home > Transform > Data Type > select Text.

Tip: You can select multiple columns with Ctrl+Left-Click.

4). Next, click Replace Current in the Change Column Type dialog, and Excel will convert the selected columns to text.

5). When you're done, Click Close & Load, and Excel will return the query data to your worksheet.If your data changes in the future, you can go to Data > Refresh, and Excel will automatically update your data, and apply your transformations for you.

Did this answer your question?