How to prevent data shuttle from converting excel text to number

hkanner

An excel xlsx sheet has some data saved as text that data shuttle is converting to number even when the workflow column type is defined specifically as text. For example '2526 in excel is coming in as 2526 (without the leading ') and causes formulas not to work. The exact same excel sheet can be imported manually (not using data shuttle) into Smartsheet without this problem. Even forcing a leading ' with a helper column in the target sheet does not work. Does anyone have a solution or workaround for this?

kowal

hi @hkanner,

When you do the mapping between colums in datashuttle - you can chose how Smartsheet should treat the data as text or as number.

Hope this helps.

hkanner

I specifically used a "text" column definition during the mapping but it still converted '2526 to 2526, making it a number.

kowal

hi so for me it worked pretty well and when used text (despite i was importing numbers) it was adding the ' before and once i changed those to numbers it removed ' from the imported data.

hkanner

Thanks for the help/reply but my problem is the opposite. I am importing text but the data shuttle is converting to a number if the text "looks like" a number, even if there is a leading ' and even if I specific to treat the column as text. So in excel if I have a column that is text and contains several rows of data, like '4210, '0100, '2526, it imports the data having leading zeros as text but the other data without leading zeros like '4210 and '2526 are brought in as numbers.

kowal

so did you try while mapping to force for the number to be text? is it still importing as number?