Leading Zeros in a CSV With Column Header Not in First Row

Options
Thomas_937381
Thomas_937381 Posts: 196
edited June 2020 in Questions

I have a .csv file that looks like this, with a header above the column headers:

Value 1=ABC,
Value 2=123,
,
Column 1,Column 2
A,1
B,12
C,123

In effect, the column headers are not in row 1, but e.g. row 4. I have a need for the numeric values in column 2 to always be e.g. four digits, with leading zeros. I'm trying to go from the above to what is below:

Value 1=ABC,
Value 2=123,
,
Column 1,Column 2
A,0001
B,0012
C,0123

I wrote a formula for CSV: Apply formulas to columns that looks like this, but so far two outcomes with my approach:

  1. I've gotten an error: Error evaluating formula code on row 1: SyntaxError: Unexpected token {

  2. Some of the text in my .csv is incorrectly enclosed with double quotes (and no leading zeros)

    if (columns[2] == 'Column 2' {
    columns[2] = 'Column 2' }
    else if (columns[2].toString().length == 4) {
    columns[2] = columns[2] }
    else if (columns[2].toString().length == 3) {
    columns[2] = '0'.concat(columns[2]) }
    else if (columns[2].toString().length == 2) {
    columns[2] = '00'.concat(columns[2]) }
    else if (columns[2].toString().length == 1) {
    columns[2] = '000'.concat(columns[2]) }
    else if (columns[2].toString().length == 0) {
    columns[2] = '' }

What am I doing wrong?

Tagged:

Best Answer

  • Thomas_937381
    Thomas_937381 Posts: 196
    Answer ✓
    Options

    @Kalin_126161 pointed out to me that there appear to be syntactical issues in my JavaScript formula. Once corrected we seem to have that working, provided column headers is set to false. Thanks, @Kalin_126161.

Categories