Apostrophe added on mass insert thru CDATA

kevanmueller

Hello,

We are currently using a third party tool CDATA to upload excel data to a smartsheet. When any numeric data is entered, an apostrophe is always added to the beginning of the string. Upon checking the logs, CDATA is not sending an apostrophe to Smartsheet. The only thing I can think of is Smartsheet is adding the apostrophe. Has anyone else seen this? I opened a case with Smartsheet and they referred me to the community.

 

Thanks,

 

Kevan

Eric Peery

I can't really test this without the program you use, but have you double-checked the data type for the column the numeric data is going into?  It might be a text formatting flag like it is in Excel.

kevanmueller

When being read into SSIS from excel, SSIS recognizes as currency data type. It is using the CDATA to insert into a Text/Number Smartsheet cell. It is formatted as a string before entering in Smartsheet as that is what SSIS recognizes a TExt/Number cell as in Smartsheet.

william.welch69791

I know this post is old but I was having the same issue where all my data was formatted correctly when getting sent to SS but it still added the apostrophe. Also what I was doing was getting the data from SS first, checking for certain conditions, correcting if it needed it, then sending the data back to SS. 

I did find that if I was pulling by displayValue if I switched to get the value instead, did either toString() or Number() and finally sent that back to SS it worked.

Just wanted to share my solution to this issue incase anyone else runs into it. 

Chris Savold

Kevan,

Was there ever a solution to your CDATA import issue? I have the same problem and am trying to find a solution.

Chris Savold

@william.welch69791 ,

Were you using the CDATA/SSIS integration? If so, can you explain a little more about the tostring() solution? We are already submitting data that is cast as a string, so I am unclear how this might be a solution. Any thoughts would really be appreciated.

Jeff Zumaris

Smartsheet to Excel, Excel to Smartsheet: Smartsheet Excel Add-In - CData Software

https://www.cdata.com/drivers/smartsheet/excel/?utm_source=google&utm_medium=cpc&utm_campaign=245275464&utm_content=25408777344&utm_term=%2Bsmartsheet%20%2Bexcel&gclid=Cj0KCQjw5auGBhDEARIsAFyNm9HZc5f-crs4DPuYlGjn2iSNHK0rL58Mvgkn0Rs-3pH0hYU5FPEeGR8aAsSpEALw_wcB

The Smartsheet Excel Add-In is a powerful tool that allows you to connect with live data from Smartsheet, directly from Microsoft Excel. Use Excel to connect to Smartsheet Sheets, Contacts, Folders, Groups, Users, etc. Perfect for Excel based data analysis, data cleansing & de-duplication, and more!

This is CDATA its for PC Only but I just installed it on a Virtual PC and yeah it works pretty slick sending info back and forth. I'm wondering if you got the apostrophe because you were sending something to a contact column? Was it all columns?