CDATA import into Smartsheet cause apostrophe problem

Chris Savold

We are using an external tool from CDATA to import data into Smartsheet from MS SSIS. We have recently run into an issue where the data load inserts an apostrophe in front of the string value we are loading. We've checked the outbound flow from SSIS and the apostrophe is definitely not there, so Smartsheet is doing this. Any idea on how I can ensure this does not happen? BTW, we are actually loading a formula with this method, so the string being passed into Smartsheet is something like "=[FirstCol]1 + [SecondCol]1" and the apostrophe being added breaks the formula.

Ben Goldblatt

Hi Chris,

Typically when Smartsheet automatically inserts an apostrophe in a cell, it's evaluating an alphanumeric value rather than just a string of text or just a number. If the first character of a number is zero followed by another digit or by a letter, Smartsheet will prepend the value with an apostrophe and store it as text. It does this so that the leading zero will be retained and displayed with the number.

I haven't worked with CDATA specifically, but you may want to open a CDATA Support ticket (https://www.cdata.com/support/submit.aspx) to see if something can be altered on their side to ensure that the expected values populate correctly. Otherwise, if you're wanting to have the value populate as a number and be stored as a number in Smartsheet, you'll need to double-click the cell and remove the apostrophe (along with any leading zeros that may be showing).

I hope this helps!

Ben

Marvin

Output

I have the same issue not in CDATA but in POWER AUTOMATE where in I'm using Smartsheet Insert row

when I tested my flow my Input is Numeric but I got a String output instead.

Any help on this one. thank you.