Formula: Leading Zeros in CSV Files

Thomas_937381Thomas_937381 Posts: 196
edited July 17 in Show and Tell

If passing a .csv file through the Excel: suite actions, and any of your columns' values contain leading zeros, they may be left off in the resulting output. You can use the below formula in a subsequent CSV: Apply formulas to columns step to reinsert them afterward. This requires that the character length of the value be fixed; in the case below, we assume six (6) characters.

if (columns['Column Name'].toString().length == 6) {
columns['Column Name'] = columns['Column Name'] }
else if (columns['Column Name'].toString().length == 5) {
columns['Column Name'] = '0'.concat(columns['Column Name']) }
else if (columns['Column Name'].toString().length == 4) {
columns['Column Name'] = '00'.concat(columns['Column Name']) }
else if (columns['Column Name'].toString().length == 3) {
columns['Column Name'] = '000'.concat(columns['Column Name']) }
else if (columns['Column Name'].toString().length == 2) {
columns['Column Name'] = '0000'.concat(columns['Column Name']) }
else if (columns['Column Name'].toString().length == 1) {
columns['Column Name'] = '00000'.concat(columns['Column Name']) }
Sign In or Register to comment.