Skip to main content
Kofax

How Can I Check the Length of Data in a Column in csv Files Before Importing?

Article # 3029484 - Page views: 163

Article # 3029484 - Page views: 163

Issue

Are there any tips or suggestions for a way to check the length of text in a given column in my po_headers or po_lines csv files? (For example, if my data import is failing because the length of one of my columns is greater than what the column allows).

Customers may hit errors attempting to import csv files where column lengths are beyond what may be allowed. 

Examples: 

Solution

  1. Open the csv file in Excel.
  2. Insert a new column to the right of the column you want to check.
  3. Name your new column "LENGTH" or something like "COLUMN_NAME_LENGTH" to make it easier to identify when sorting.
  4. Create the following formula in the first row below the column header (Example: your new column is H and the column you want to evaluate is G, then in the H column where LENGTH will be displayed): =LEN(G2)
  5. Click in that G2 field and mouse over the lower right corner of the field. A + symbol will appear. Control double click and that should auto fill the entire column with the formula and calculate the length for each row of the column being evaluated. 
  6. Sort by the LENGTH column largest to smallest. This will show you the row with the largest length and you can then confirm if that length is too large for the column. For example, VendorSiteCode has a requirement to be 50 characters or fewer, so if you have a vendorsitecode that is too long, you can edit or just blank out the field. 

(In some cases, customers have tried incorrectly to insert an address in this field instead of an actual vendor site code for example). 

TIP: If you see 0 in all rows being evaluated, check that your formula is not evaluating the same column where you want the LENGTH to be displayed. 

 

Level of Complexity 

Moderate.

 

Applies to  

Product Version Build Environment Hardware
AP Agility 2.3+      

 

  • Was this article helpful?