Skip to content


Smartconnect Excel Addon Bug

klewis10367 asked 7 years ago
I ran into this scenario after reading your blog about the Blank line restriction, and added a coulple of formatted fields the query:  
I am connecting to an Excel 2013 spreadsheet via a 2013 ODBC Connection. I am then using the following Query:
Select *, Format([Debit],‘###0.00’) as ‘clcDebit’, Format([Credit],‘###0.00’) as ‘clcCredit’ from [GL_Import$] where Batch_Id <> ””
to remove Blank Lines and to format Debits and Credits to avoid the known rounding issues when importing from excel. This works fantastic if the map is run from the Smartconnect Application. But when it is run from the Excel Add on, the Smartconnect Service needs the Debit and Credit columns to be named  
_clcDebit_ and _clcCredit_ (Smartconnect puts the _ when creating this formatted field). This also works fine, but now, only from the Excel add-on. If run from the Smartconnect App it will not run because the Debit and Credit names have been changed. A vicious circle.
patrick answered 7 years ago
Seems like you or another guy created a case on this the other day. From a SmartConnect standpoint, we need the columns from either version of Excel to match what was initially set up.   No way around that – has to be the same and not missing anything. Testing this on my system, it looks like the Excel odbc driver won’t let us select the same column name as itself.  So format(debit) as debit won’t work – which is why you named it clcDebit in your testing I would imagine.  Because we can’t re-use the column names in the Excel ODBC driver, the only real good solution I see is NOT to select your Excel data in the statement as you are using now.  Instead, just do select * from [GL_Import$]. Then if we are concerned about rounding, make a calculation for debit/credit.  In that calculation, we can round or format the value in the calculation.  Now both methods use the same column name and both would get rounded/formatted correctly either way. Patrick eOne

If you would like to submit an answer or comment, please sign in to the eOne portal.