BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

 
Realtime CMS Reporting from Infolytik (Opens a new window)  

General Notice: No events within the next 45 days.

Excel ODBC - wrong data type of each column


 
Search this topic... | Search DI: Installation, Administration, and Connectivities... | Search Box
Register or Login to Post    Forum Index -> Data Integrator -> DI: Installation, Administration, and Connectivities  Previous TopicPrint TopicNext Topic
Author Message
kman
Forum Member
Forum Member



Joined: 11 Feb 2011

Posts: 6



PostPosted: Fri Feb 11, 2011 3:48 pm 
Post subject: Excel ODBC - wrong data type of each column

While trying to load Excel files, by default BobJ will consider first 8 rows to determine the data type of each column.

So, cases where the first 8-10 rows have empty data, the data type selected by BobJ may not be correct and we may face problems with uploading/transforming the data in these columns.

I know we can resolve this issue by adding "IMEX=1; MAXROWSTOSCAN=0" in the connection string of an ODBC but I didn't find any ODBC connection in BobJ.

Any ideas on the above people?

P.S. I have around 50 Excel Workbooks in my job.

Source: http://support.microsoft.com/kb/189897/en-us
Back to top
manoj_d
Forum Addict
Forum Addict



Joined: 01 Jan 2009

Posts: 2754



PostPosted: Fri Feb 11, 2011 4:40 pm 
Post subject: Re: Excel ODBC - wrong data type of each column

are you using Excel as reader in DataServices/DI ? or Bobj reporting tools (Webi, Crystal ) ?
Back to top
kman
Forum Member
Forum Member



Joined: 11 Feb 2011

Posts: 6



PostPosted: Mon Feb 14, 2011 8:22 am 
Post subject: Re: Excel ODBC - wrong data type of each column

I'm using Excel via DataServices. All columns are of datatype string.
Back to top
cvilliers
Senior Member
Senior Member



Joined: 05 Jun 2007

Posts: 34
Location: Paris


flag
PostPosted: Tue Feb 15, 2011 11:22 am 
Post subject: Re: Excel ODBC - wrong data type of each column

Hi,

have you try this note from microsoft

http://support.microsoft.com/kb/281517

It's in french but I think you can find it on english
Back to top
kman
Forum Member
Forum Member



Joined: 11 Feb 2011

Posts: 6



PostPosted: Thu Mar 10, 2011 5:20 pm 
Post subject: Re: Excel ODBC - wrong data type of each column

Thank you all for your suggestions.

- cvilliers, I speak French so reading the note wasn't a problem at all. icon_smile.gif

I finally found a fix for this. Here is the link to the SAP Notes:

https://websmp230.sap-ag.de/sap/support/notes/1408404

If you need more information, please reply to this thread and I will try to help.

Here an excerpt from the SAP Notes..

===========================

Cause

Data Service call Microsoft Jet engine to get the datatype of a column in an excel file and Jet engine defines a column datatype by scanning few rows of the data from the column of the excel file. During the reading the Excel file, Data Service will use Jet defined datatype to convert data to Data Service datatype.

Resolution

There are two solutions that customer can try to solve this issue:

+ Set up the ODBC excel file to scan more rows to define a correct datatype of a column. You can find out the documentation from Microsoft to see how to set up the value.

+ Insert a row in the beginning with character data in the facility ID column to force Jet engine to pick up the correct datatype in the column and filter out the row when you loading data.
Back to top
ganeshxp
Forum Addict
Forum Addict



Joined: 17 Jul 2008
ASUG Icon
Posts: 3674
Location: Denver, Colorado, USA


flag
PostPosted: Tue Oct 09, 2012 10:58 pm 
Post subject: Re: Excel ODBC - wrong data type of each column

Is there any new solution to this problem? 4.0 version?
_________________
Cheers
Ganesh
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Data Integrator -> DI: Installation, Administration, and Connectivities  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0455 seconds using 17 queries. (SQL 0.0028 Parse 0.0348 Other 0.0079)
CCBot/2.0 (http://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo