Preserve leading zeros in Excel imports

Microsoft Excel is a very powerful program that can be used to handle things like data exports from RICS. A common way of performing mass maintenance on Product or Customer data is to export the data, open it in Excel, edit the file, then save and re-import the data into RICS.

However, if not careful, this process can do more harm than good. When the user opens a .csv file there is potential for data being lost.

For example, a UPC of 019519002634 will become 19519002634 when opened in Excel. Since the numbers no longer match, RICS will not be able to determine that these two UPCs are actually meant to be the same.

Unfortunately, this is how Excel interprets the data, and there is nothing RICS can do. But a workaround is available.

Preserve Leading Zeros

Perform the steps below to prevent Excel from eliminating leading zeros.

1. Run the desired export from RICS through the Reporting :: Data Export menu.

2. Click on the file name from the Report History page in RICS and save the file.

3. Rename the file by changing the extension from .csv to .txt.

4. Open Excel.

Note: open Excel directly, not by clicking on a file.

5. Open the renamed .txt file by clicking File, Open from the main Excel menu bar. Then click Browse to locate the export file.

You may have to adjust the file options to All Files as shown here in order to locate the .txt file:

Click to open once the .txt file has been located.

6. A Text Import Wizard will appear.

  • Select Delimited, then click Next.

  • Check Comma as a Delimiter, then click Next

Click on each column and change the Column data format to Text. Then click Finish.

Note: This step must be completed for each Column.

7. Update the file as needed, save it as a .csv format when finished, and then import it to RICS as usual.

Have more questions? Submit a request

Comments

Powered by Zendesk