You are viewing limited content. For full access, please sign in.

Question

Question

Insert Data to Excel Error (Number stored as text)

Workflow
Updated May 12, 2014
asked on May 8, 2014

 I am retrieving a number value from a field submitted from a group of forms and creating a total token value that I am inserting into an excel workbook.  The value is being inserted properly but instead of the value being inserted as a number it is defaulting as text.  Because of this, I am unable to run a sum total on the Excel spreadsheet.

 

Has anyone ran into this?  The field value is set as a number and the excel columns are also set as a number.

0 0

Answer

SELECTED ANSWER
replied on May 12, 2014

I tried a number of ways to work around this. It comes down to the excel ODBC driver converting everything to text 'to be safe'. The most complex sample I tried was using a custom query that looked like:

 

INSERT INTO [Sheet1$] ([Numbers]) VALUES ({fn CONVERT (?, SQL_INTEGER})

I also tried passing in the parameter as an integer or double with the same result. Info on CONVERT function: http://msdn.microsoft.com/en-us/library/ms713608(v=vs.85).aspx

 

This article: http://spreadsheets.about.com/od/Text-Functions/ss/2011-05-26-excel-convert-text-to-numbers.htm specifies how to work around this issue inside the spreadsheet. It basically indicates to create another column that uses the VALUE function to cast the inserted values and then sum that column. I think this is the method Kenneth is suggesting. 

 

I recommend using a more robust database system then Excel when updating or inserting values is required. Ultimately it comes down to Excel not being a real database. Excel tends to not work well when multiple instances try to update or if it is updated while the spreadsheet is open.

3 0

Replies

replied on May 9, 2014

Which product are you using to insert the value into an Excel workbook?

0 0
View 1 previous reply
replied on May 12, 2014

I am having a similar issue. I have simply asked for the main formula's that the user wants in the spreadsheet and added it to the next column/row/worksheet in that document so they do not have to do the calculations manually, they are just already there.

0 0
replied on May 12, 2014

I am using workflow 9.1 to insert values into excel '10.

0 0
SELECTED ANSWER
replied on May 12, 2014

I tried a number of ways to work around this. It comes down to the excel ODBC driver converting everything to text 'to be safe'. The most complex sample I tried was using a custom query that looked like:

 

INSERT INTO [Sheet1$] ([Numbers]) VALUES ({fn CONVERT (?, SQL_INTEGER})

I also tried passing in the parameter as an integer or double with the same result. Info on CONVERT function: http://msdn.microsoft.com/en-us/library/ms713608(v=vs.85).aspx

 

This article: http://spreadsheets.about.com/od/Text-Functions/ss/2011-05-26-excel-convert-text-to-numbers.htm specifies how to work around this issue inside the spreadsheet. It basically indicates to create another column that uses the VALUE function to cast the inserted values and then sum that column. I think this is the method Kenneth is suggesting. 

 

I recommend using a more robust database system then Excel when updating or inserting values is required. Ultimately it comes down to Excel not being a real database. Excel tends to not work well when multiple instances try to update or if it is updated while the spreadsheet is open.

3 0
replied on May 12, 2014

Thank you Ed,

 

I will give the article a look and see if I can just use the work around.  I was aware of the short comings that  using Excel would bring but I wanted to shy away from using a real database since I would not need multiple instances updated.  Although I knew  some of the short comings, I was unware that the odbc driver converted everything to text but now that I do know I will make sure to use a something like an access db.

 

Thanks again,

 

Ramon

0 0
You are not allowed to follow up in this post.

Sign in to reply to this post.

Details
Related Posts
Loading ...