MS Addins Query GLAMOUNTS amounts in excel as text -

Name	Points
Greg Moeller	4184
David Williams	3349
JonA	3288
Kat V	2984
Woozy	1973
Jimmy Chiu	1883
Kwane McNeal	1437
Ragu Raghavan	1348
Roger French	1311
mark.cook	1244

Sherry Shimek

Advanced Member

Posts: 43

3/25/2015 4:08 PM

We are querying the GLMAMOUNTS table using Lawson MS Addins MS Excel is treating all the monthly gl amounts as text.

We have tried setting the format tab to Accounting1-4 and applying to all rows, but this doesn't affect the amounts only the other fields such as the company and other accounting string numbers.

Is there a way in either MS Addins query wizard or excel to default these to numbers. In large queries, using excel to change convert to numbers takes quite a bit of time.

Thanks,

Sherry Shimek

Catholic Health Initiatives

JonA

Veteran Member

Posts: 1162

4/1/2015 11:01 AM

I don't know why that would happen. I've queried the table with and without the Accounting formatting and all the amounts display as numbers for me. But if you need to convert a column of text to numbers you can use the Text to Columns wizard in Excel. Highlight a column in Excel and go to Data > Text to Columns, Click Finish. Any cell in that column that looks like a number will convert to a number. You can only convert one column at a time.

Sherry Shimek

Advanced Member

Posts: 43

4/1/2015 4:35 PM

Thank you. My guess is that MS Excel formats the amounts fields based on some rule but we cannot find it yet.

We use the Text to Columns but in hundreds of thousands of rows of data an multiple periods of data that takes time we would prefer to conserve.

Thanks for your response.

dcaiani

Veteran Member

Posts: 52

6/12/2015 5:32 PM

I've never experianced numbers coming out as text.

Try Leaving the format at "none". Then click on "clear worksheet before send"

If you still get numbers coming out as text just highlight the row(s) and at the top of that highlighted row Excel will give you a little error box - use the drop down and click "convert to number"