why is the container importing my excel file with blank (NULL) values for items with alpha numeric labels? additionally the columns have switched order?
Excel Output Connector Module Sample Run Original Excel FIle Import
why is the container importing my excel file with blank (NULL) values for items with alpha numeric labels? additionally the columns have switched order?
Excel Output Connector Module Sample Run Original Excel FIle Import
Sounds like maybe somewhere, the item code column's data type is being automatically assumed to be numeric based on the first x number of rows and it is thinking the alphanumeric values are failing the…
Change the data to a Table still have the issue.
Did the apostrophe just for the Numeric values and that worked.
Easier way is using the Text to Columns on the Data Ribbon.
I highlighted all of the Item…
You my friend are a gentleman and a scholar. Thank you!
Sounds like maybe somewhere, the item code column's data type is being automatically assumed to be numeric based on the first x number of rows and it is thinking the alphanumeric values are failing the numeric check and replacing it with null.
May need to poke around and see if you can specify the data type somewhere.
Another thing to consider, are you using any outer joins to another table that may not have the alphanumeric values thus returning columns from one table but not the other where a match is not found?
I am not using outer joins. I tried every data type in the properties tab and received the NULL return every time:
Hi, I can't replicate what your screen shots are depicting. This is a Sage Intelligence connector? How is the connector set up? Excel ODBC?
Is the workbook's column type set to Text?
What happens if you throw an alphanumeric value as the first data row (row two) in the workbook?
EDIT: These questions were meant for wtcrockett to rule out the possibility that he might have the column in workbook set up as a number with zero decimal places and left justification. It might appear correct in the workbook but when SI is reading it, it might be respecting the data type assigned to the column.
Hi David, My workbook's column type set to general. It is single table ODBC query from CI_Item saved as an XLS. The XLS is brought into Sage Intelligence via a custom connector via ODBC Excel Connection.
Yes Excel ODBC
I setup a workbook with the screen shot "Original Excel File Import".
Setup a new Excel Connection and added a Container using the workbook.
Was able to duplicate your issue.
In the workbook I moved the A/N rows to the top ran a sample and the A/N items showed and the numeric were Null.
So it seems whatever the 1st data row contains (A/N or Numeric) it expects the rest to be the same.
Messed with the Column formats and the Data Expression formats still the same results.
Call Sage Intelligence support with this. Seems to be a "bug"
I have tried number, general, and text formatting in the excel file. Same result.
*Community Hub is the new name for Sage City