SpreadSheetAddRows and leading zeros

SpreadSheetAddRows is either broken or just simply sheeitty.

I encountered the exact same problem you faced and came across this post :

http://cfsimplicity.com/16/forcing-values-to-be-inserted-into-spreadsheets-as-text

Using SpreadSheetSetCellValue after formatting it seems to do the trick.

@Adam, it's not an excel problem though. If you imported the CSV file you mentioned into Excel, formatted the columns as "text" with the Import Text Wizard, they will remain as is without the 0's being stripped off.

I tried playing with the SpreadsheetFormatColumn function to format the column as text. However, the SpreadsheetAddRows method overwrites any existing formatting. I've tried using both overwrite and insert. I've even tried inserting a blank row, formatting it, and the using SpreadsheetAddRows. I can set the format after I call SpreadsheetAddRows but by then the damage is done and the zeros are gone. All I get is a text column with numbers formatted as text but missing all of their leading zeros.

Format the column after you populate it! ;-)

This will work, but note it only changes how the numbers are displayed.  The actual data will still have had the leading zeros snipped.  This might or might not be a problem for you.

If this is truly a limitation of the new spreadsheet functions then it is a serious one. It would keep me from being able to write data to any existing spreadsheet because I'd have to reformat all of the cells that are written to. Does anyone know of a way to do this???

I think it's a limitation of Excel, not the CF functions.  This can be demonstrated by saving this as a CSV file and opening it in Excel:

1,0001

2,0002

3,0003

4,0004

5,0005

In Excel, you'll just get the single digits, not the leading zeros.  I'm guessing a similar thing is happening when CF sticks the data into the file.

If one saves the file as a.txt file and imports it, then there's a chance to dictate to Excel what the column format should be, and if one leaves it on "general" then the leading zeros are preserved.

This is just Excel trying to be "helpful", I guess.  And a good example where software should just do what it's been told, and not try to second-guess stuff like this!

--

Adam