Data Shuttle - Out of Range Exception

ygoldgrab
ygoldgrab ✭✭✭✭✭
edited 06/14/22 in Add Ons and Integrations

Hi,

I keep getting this exception when importing data from my excel sheet to Smartsheet using data shuttle (aka data uploader).

Add rows to target sheet: "Unable to parse request. The following error occurred: Field \"value\" was not parsable. Numeric value (21081100066000000000) out of range of long (-9223372036854775808 - 9223372036854775807)\n at [Source: java.io.PushbackInputStream@717f9870; line: 1, column: 15215]."

It seems that some of my numbers are too long (this is one of my numbers 21081100066000000000). Is there any workaround for this. I really want to use data shuttle to get my data into Smartsheet.

Thanks

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @ygoldgrab

    This is a current limitation of the Smartsheet API; to pass a number through the API, it must be larger than -9223372036854775808 and smaller than 9223372036854775807. Please let the Product team know about your use-case and feedback through this form, here!

    If you want to use a number outside this range, you will need to convert it into a string by adding a non-numeric character to the field.

    For example, you can force the source data to convert the number to a string by adding an apostrophe to the front - i.e. '21081100066000000000.

    If you have a lot of numbers like this, you should be able to add another column and turn the number into string using a formula. Once the data is in Smartsheet, if you need it to be recognized as a numerical value, we can use another formula to wrap the VALUE function around this cell to convert it back. Does that make sense?

    Cheers,

    Genevieve

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @ygoldgrab

    This is a current limitation of the Smartsheet API; to pass a number through the API, it must be larger than -9223372036854775808 and smaller than 9223372036854775807. Please let the Product team know about your use-case and feedback through this form, here!

    If you want to use a number outside this range, you will need to convert it into a string by adding a non-numeric character to the field.

    For example, you can force the source data to convert the number to a string by adding an apostrophe to the front - i.e. '21081100066000000000.

    If you have a lot of numbers like this, you should be able to add another column and turn the number into string using a formula. Once the data is in Smartsheet, if you need it to be recognized as a numerical value, we can use another formula to wrap the VALUE function around this cell to convert it back. Does that make sense?

    Cheers,

    Genevieve

  • ygoldgrab
    ygoldgrab ✭✭✭✭✭