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

Register | Login 

Sunset Ride 
Sunset Ride 

General Notice: BOB is going to retire...please see details here. All BOB forums have been converted to "read only" as of September 1st.
General Notice: No events within the next 45 days.

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
Forum Enthusiast
Forum Enthusiast

Joined: 10 Jan 2007

Posts: 1113
Location: Wellington, NZ

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
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 

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0267 seconds using 17 queries. (SQL 0.0022 Parse 0.0008 Other 0.0237)
CCBot/2.0 (
Hosted by | Terms of Service
phpBB Customizations by the
Shameless plug for Moments of Light Logo