Importing CSV files with Leading Zeros from Local Drive

May 21, 2018

Importing CSV files with Leading Zeros from Local Drive

Importing Data from a flat file into Paxata is very straightforward.  Often, the default import settings work perfectly.  Sometimes a user may need to adjust a couple settings.

Today’s Tip of the Day focuses on handling flat files with leading zeros.  Paxata will not remove leading zeros during import, yet it is important to know how leading zeros are handled based on your import selections.

 

From the Library page, click the Import button
Click the Upload Local File link and either click or drag-and-drop (shown) the file to be imported.
The system will evaluate the the file and make some default settings.  As a user, you may over ride these settings when necessary.   In the upper right quadrant of the screen, information about the file format is presented and can be adjusted.
In the lower left quadrant are details on how to handle headers, delimiters and similar separator options.
Notice the option “Parse cells into Numbers, Dates, and Booleans” is set to TRUE – this will cause a datatype mismatch when a value has leading zeros in a numeric column.  (such is the case for Billing ID in rows 2, 3, 4, and 5 in the data preview)

A datatype mismatch is allowable – meaning the data will not be lost or modified during import – however, one should address any such datatype mismatches within a project.  Instead of having to address datatype mismatches, users frequently will import everything as text and then convert numbers, dates and booleans manually.

 

Once the option is set to FALSE, the Billing ID now is handled as a text field, which is a better treatment for items like Postal Codes, Invoice Numbers and other values which may have leading zeros.

There are many Separator Options to be leveraged depending on the unique qualities of each dataset.
Additional Options set the character encoding, define which rows should be treated as column headers, and limit the total number or rows imported.
 

 

 

 

Show Buttons
Hide Buttons