Working with CSV files in Microsoft Excel When using Microsoft® Excel® to handle CSV files for use with BEACON, do not simply open the file by double-clicking it. Instead, use the DataFrom Text function (Windows) or the FileImport function (Mac OS) to bring the CSV file into Excel. This helps preserve the fidelity of your data. When using this method, an Import Wizard lets you choose a delimiter and determine how to treat data such as numbers and dates in your file. Setting all data to Text prevents large numbers from being displayed as floating point values, leading and trailing zeros from being dropped and dates from being displayed in non-ISO-compliant formats. When bringing Data Exchange files into BEACON on a regular basis, it is essential that data fidelity be maintained between imports. For example, if you open a file in Excel by double-clicking it, Excel will remove leading or trailing zeros on any field that uses them.
While that might not seem important for Register Resolution settings like 0.01, it is critical for Account IDs, Location IDs and Meter IDs such as 000123456. To BEACON, Account ID 123456 and Account ID 000123456 represent two different accounts. If one import includes leading zeros and a future import doesn’t, meter data will be routed incorrectly.
This effect can be amplified if different tools are used to handle CSV-format Data Exchange files. For example, Sublime Text will not drop leading or trailing zeros, where Excel can and will if used incorrectly. As a result, pay close attention to warnings generated by the system synchronization process. For Windows users: Follow these steps to import CSV files into Excel:.
Select FileNew to create a new file. Select DataFrom Text. Locate and select the file you want to import using your computer’s file system. In the Text Import Wizard popup menu, click the CSV radio button, set File Origin to Unicode (UTF-8) and click Next. Set the Delimiter and click Next. Shift-click all of the columns to select them and click the Text radio button to set the Data Format of the file and click Finish. Set where you want to put the data with the appropriate radio button and click OK.
![How to get csv to keep leading zeros in excel for mac download How to get csv to keep leading zeros in excel for mac download](/uploads/1/2/5/6/125602561/598079097.png)
When the import process is complete, dates will be properly formatted, any numbers that contain leading zeros will be preserved and long numbers will not turn into floating point values. For Mac OS users: Follow these steps to import CSV files into Excel:. Select FileNew Workbook to create a new file. Select FileImport. In the Import popup menu, select the CSV file radio button and click Import.
Locate the file you want to import using your computer’s file system and click Get Data. In the Text Import Wizard, make sure the Delimited radio button is selected and set File Origin to Unicode (UTF-8). Click Next. Set the Delimiters and click Next. Shift-click all of the columns to select them and click the Text radio button to set the Data Format of the file and click Finish.
Set where you want to put the data with the appropriate radio button and click OK. When the import process is complete, dates will be properly formatted, any numbers that contain leading zeros will be preserved and long numbers will not turn into floating point values.
The answer to your question is that if you want a number with leading zeros in Excel, you cannot have it - at least during the import. The Microsoft developers, for whatever reason built Excel to read in 03 as a number and truncate the leading zero, resulting in 3. There is no way to prevent this. The file contains columns: ProductModelID, Name, SpecialID, and ModifiedDate. The column SpeicalID containing data with leading 0 and it is specialized code so you would like to keep the leading 0s in Excel. Since it is a CSV file it is, by default, shown in your Windows Explorer as an Excel file: So you double-click the file and open the file in Excel as such: You can see that by default, Excel stripped out the leading 0s in column 3 “SpecialID?
Microsoft Excel can use data produced by other computer applications that is saved in the text-based Comma-Separated Value file format. Excel knows how to convert the lines of comma-, space- or tab-separated information within CSV files into standard spreadsheet rows and columns. Excel may, however, make incorrect assumptions about the way data it imports should be formatted.
For instance, Excel will strip leading zeros from numbers and dates because they are not mathematically significant. You can keep the leading zeros in CSV files by using Excel's Text Import Wizard.