Open report files in Excel without losing formatting – Support Center

Reports from the NextCentury Web Portal download as CSV (comma-separated values) files, which open in spreadsheet programs such as Microsoft Excel.

When Excel opens a CSV file, it guesses what kind of data each column holds. Some serial numbers look like scientific notation to it, so it changes them: 70143E7 becomes 7.01E+11, and the original serial number is lost. Just opening the file is enough to change it, and the changed file can then fail to upload to another system.

To keep serial numbers as they are, import the file into Excel as text instead of opening it.

Import a report into Excel

In a new Excel sheet, open the File menu and select Import.

Excel on a Mac with a new, empty sheet. The File menu is open from the menu bar at the top, and Import is highlighted in the middle of the menu, below Browse Version History and above Reduce File Size.

Select Text file, even though the report is a CSV, then select Import.

Excel's Import window, which asks you to select the type of file you want to import. Of the three choices, CSV file, HTML file and Text file, Text file is selected. The Import button is at the bottom right, next to Cancel.

Choose the report file, for example in your Downloads folder, and select Get Data.

The Mac file window that opens next, showing the Downloads folder. A report file (Property Backup.csv in this example) is selected, with a preview on the right labeled Comma Separated Spreadsheet (.csv). The Get Data button is at the bottom right, next to Cancel.

Make sure Delimited is selected, leave the other settings as they are, and select Next.

Under Delimiters, check only Comma, then select Next.

Text Import Wizard, Step 2 of 3. Under Delimiters, only Comma is checked; Tab, Semicolon, Space and Other are unchecked. The preview now shows the report split into columns: BUILDING, UNIT, UTILITY_TYPE, SERIAL_NUMBER, METER_ID, METER_TYPE, IMR, DESCRIPTION and more. The Next button is at the bottom right.

Select all the columns, choose Text as the column data format, then select Finish.

Text Import Wizard, Step 3 of 3. Under Column data format, Text is selected (the other choices are General, Date and Do not import column). In the preview, the columns are selected and highlighted in black, each with its format shown above it. The Finish button is at the bottom right.

Choose where to put the data. In a new sheet, leave the default and select OK.

Excel's Import Data window, which asks Where do you want to put the data? Existing sheet is selected, with =$A$1 in the box next to it. New sheet is the other choice. The OK button is at the bottom right.

The report opens with every value exactly as it was in the file.

The imported report in Excel, with columns BUILDING, UNIT, UTILITY_TYPE, SERIAL_NUMBER, METER_ID, METER_TYPE, IMR and EQUIPMENT_TYPE. Meter IDs that Excel would otherwise change, such as 202F523, 701453C and 7014541, are kept exactly as they were in the file.

Save it as a CSV again

If you change the report and need to upload it to another system, save it as a CSV first: select File > Save As and choose CSV as the file format.

Excel's Save As window, set to save in Downloads. File Format is set to CSV UTF-8 (Comma delimited) (.csv). The Save button is at the bottom right.