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

Register | Login 

Want to sponsor BOB? 
Want to sponsor BOB? (Opens a new window)  

General Notice: Upcoming Events: PGHBOUG: Nov 1.

Bug loading empty Excel workbook?


 
Search this topic... | Search DI: Designer and Job Design... | Search Box
Register or Login to Post    Forum Index -> Data Integrator -> DI: Designer and Job Design  Previous TopicPrint TopicNext Topic
Author Message
ErikR
Forum Enthusiast
Forum Enthusiast



Joined: 10 Jan 2007

Posts: 1111
Location: Wellington, NZ


flag
PostPosted: Mon Apr 01, 2019 4:39 pm 
Post subject: Bug loading empty Excel workbook?

I'm running into a bug, or unwanted behaviour, with DS 4.2 SP11 that I haven't seen before yet.

One of our data sources is an externally maintained and supplied Excel 2003 (XLSX) sheet with multiple workbooks.

The file is landed in our DS environment and we have separate Data Flows to process each workbook in the sheet as separate source into our staging tables.

The workbooks have a complex header structure and we have set our Excel File Formats to skip the first 8 rows and to skip all empty rows.

The workbooks do not always have to contain data - there are valid business rules for a workbook to be empty.

And therein lies the problem, when we have a workbook that doesn't contain any data, Data Services 4.2 goes into an endless loop and keeps reading hundreds of thousands of (empty) rows and it never completes - we have to abort the job.

The only way to fix this issue for us is to manually examine the sheet and put a 0 in the first column if a workbook is empty (which wouldn't be a valid entry there anyway) and then we filter this out in our Data Flow by reading all rows except WHERE Column1<>'0' .

Needless to say this manual workaround isn't ideal.

Has anyone seen this problem in DS 4.2 before?
Back to top
wil992
Forum Member
Forum Member



Joined: 22 Jun 2011

Posts: 26



PostPosted: Wed Jul 17, 2019 4:36 am 
Post subject: Re: Bug loading empty Excel workbook?

yes - I've seen this.

It's the end of file marker in excel. If the sheet is empty EOF is in row 1, as a result, if you skip that row bods never sees it so it carries on loading forever.
The way round it is not to skip rows, instead read all rows and add a gen rownum column, then in a second transform select only where rownum > however many rows you want to exclude.

in excel you can view the file metadata by renaming the file extension from .xlsx to .zip, then extract the contents of the zip file, this will show you where the eof marker is.
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Data Integrator -> DI: Designer and Job Design  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.0266 seconds using 17 queries. (SQL 0.0029 Parse 0.0008 Other 0.0229)
CCBot/2.0 (https://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