Keeping Leading Zeros on Your Import and Export Files

Introduction

LoanPro offers users the ability to import and export their data in a .csv format. Users can import broad customer data and more general, system-wide data such as rules. However, some data might include leading zeros and Excel tends to drop them, leading to issues in both import and export processes. This is a common problem but it can be fixed easily. If you are having trouble with this issue, this article will describe how to solve it so your data is accurate and usable.

Background

What are .csv Files?

LoanPro's import and export features require files to be in the .csv format. A .csv file, short for comma-separated value, is a common spreadsheet file type that can be viewed and edited using programs like Excel, Google Sheets, SPSS, or OpenOffice Calc. These files store information in list form with each value separated by a comma.

Why Dropping a Leading Zero is a Problem

Data accuracy is crucial not only for your personal use but also to the LoanPro import process. Some types of your data may include one or many leading zeros—a 0 digit that comes before any nonzero digit. Data types such as ID's are a common place to see leading zeros.

But programs like Excel often drop leading zeros from values when they're entered manually or are included in files that are opened. Excel doesn't understand that the leading zeros are a crucial part of your data. Here is an example of some raw data and how Excel would likely display it upon loading a file:

Raw Data

Excel's Display of the Data

000123456

123456

09456

9456

007

7

Excel essentially changes your data when this happens. This is a problem if you import your data to Excel to format a file you intend on then importing into LMS as a .csv file. Doing this without ensuring Excel keeps leading zeros will cause a mismatch between the data you have and the data imported into LMS. It is also a problem if you export loan or credit data from LMS and open the file in Excel for your own use.

How to Keep Leading Zeros in Excel

Using a File

If you plan on using a .csv file to input your data in Excel, any data with leading zeros will be changed like shown in the table above. To get around this, there is an alternative way to import data from a .csv file into Excel that will keep your leading zeros.

Begin by locating the data you are opening in Excel. This can be your own .csv file that you plan on importing into LMS, or it can be a .csv file you just exported from LMS. Wherever your data is from, ensure you know where the file is located.

Launch Excel and open a new spreadsheet. Navigate to the Data tab on the top ribbon. Under the 'Get & Transform Data' section located on the far left, select 'From Text/CSV'.

Selecting this will open the file explorer so you can select a file. Once you have located your .csv file, select 'Import'.

Excel shows a preview of the .csv file you select. Ensure that the Delimiter option on the top of the window is set to 'Comma'. Next, click 'Load'.

Once your data is imported into Excel, it will display as a table. You can change this if you'd like by deselecting the 'Banded Rows' and 'Filter Button' checkboxes located under the 'Table Design' tab. Doing so will display your data normally. Using this method ensures Excel keeps leading zeros.

Manually Inputting Data

If you instead plan on manually inputting the data in your spreadsheet by typing, Excel will continue to drop leading zeros. This can be fixed as well.

To do so, simply open a new spreadsheet and select the cells that you plan on using for your data with leading zeros. With the cells selected, right-click and select 'Format Cells' in the menu. (You can also access this menu by selecting the corner arrow button located in the 'Number' tab.)

In the Format Cells window, select 'Text'. This option will allow you to type values with leading zeros.

Now all leading zeros will remain! At this point, you can confidently use your data.

If you'd like to learn more about LoanPro's import and export features, we have a few articles that explain them in more detail. For more information on importing data, check out The Import Process. If you'd like some more detail on the export capabilities, such as exporting credit reports, look into Report Credit.


How did we do?


Powered by HelpDocs (opens in a new tab)