Blank rows in Export To Excel

Hi All,

Few days back we faced a problem when we were doing an Export to Excel of a report which was using sub-reports. After going thro. the knowledge base articles we came to know that SSRS currently doesn't suppot this option, an alternate is to use use List instead of tables, so we used List and did all the formatting with the list and the data was displayed correctly and it was Exporting to Excel also properly. But now we found out that after doing an Export to Excel, the Excel File is leaving blank rows between the data (i.e.,) If the report consists of two rows, then it displays the first row in the 10th row of excel and the second row in the 12th row of excel. The 11th row is blank and it appears as a small blank row between 10 and 12. The actual problem because of this is we are not able to do Auto filter in Excel, because Excel by default considers the values only until it encounters a blank row when we are doing auto filter. So is there a way to avoid this blank row while exporting to Excel, we have tried to remove the borders and all other stuffs but nothing seems to work. Have anyone encountered the same problem or is there any work around for this problem. Thanks in advance.

Have you tried removing padding for all the textboxes in your row in the list?

|||

We have removed all the padding for the textboxes but still the same problem is coming. The moment we add the subreport this is happening, until then it is fine.

Note: The sub reports are placed in a list inside another list.

|||

Maybe your subreport is still visible but with no data. Try hiding it based on some condition.

Also make sure that you dont have any gap vertically between all objects (textboxes, list, subreport etc) in your designer.

Shyam

Blank Row comming out from Excel Source?

Hi,

I use an excel datasource to populate some simple dimensions, but when i extract the excel file i get alot of blank rows from the excel files...

How can i overcome this issue? Is this normal?

I never had problems like this using DTS in the 2000 version

Best Regards,

Luis Sim?es

Perhaps, your Excel file has empty rows in the spreadsheet.

You should not see any difference when using DTS or SSIS wizard if the same source file is used.

Thanks.

|||

How do i know there are empty rows?

Blank fields in Excel file

I am trying to validate and import a Excel file into the database table using script component. The file contains some blank columns in the sheet. How can I handle the blank spaces while validating the file in the Script Component?
The code is as follows:

Dim excelcmd As OleDbCommand = New OleDbCommand("SELECT Item,TaxCode,ItemDescription FROM [Input$]WHERE LEN(Item)>=0 AND LEN(ItemDescription)>=0 AND LEN(TaxCode)>=0", excelConn)
Dim excelreader As OleDbDataReader = excelcmd.ExecuteReader()
Dim row As Integer = 0

While excelreader.Read()
NameValsBuffer.AddRow()
NameValsBuffer.ItemCode = CStr(IIf(excelreader.GetString(0).Length = 0, "#", excelreader.GetString(0)))
NameValsBuffer.TaxCode = CStr(IIf(excelreader.GetString(1).Length = 0, "#", excelreader.GetString(1)))
NameValsBuffer.ItemDescription = CStr(IIf(excelreader.GetString(2).Length = 0, "#", excelreader.GetString(2)))
NameValsBuffer.CompanyId = Me.Variables.CompanyId
NameValsBuffer.UserId = Me.Variables.UserId
End WhileThe thread title says "blank fields", while the text of the question references "blank columns", and lastly "blank spaces". Can you give a small pictorial representation of the problem? It sounds relatively easy to solve, but I'd like to make what the nature of the issue is first.|||

Item

ItemDescription

TaxCode

AAA123

Sample Item 1

XXXXXX

BBB255

Sample Item 2

AAAAA

CCC366

Sample Item 3

BBBBB

XDDD489

Sample Item 4

CCCCC

5EEE

Sample Item 5

DDDDDD

6FFF

Sample Item 6

EEEEEE

GGG

Sample Item 7

FFFFFFF

HHH

Sample Item 8

GGGGG

III

Sample Item 9

HHHHHH

JJJ

Sample Item 10

IIIIIIIIIII

KKK

Sample Item 11

ZZZZZZZZ

MMM

The value in the last row for the ItemDescription and TaxCode is null..so thats the problem I am facing when I am trying to validate and upload data.


|||First off, I'm not sure why you're using a script component, when an ADO.NET connection with the following connnection string will work fine:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"

In your dataflow, rather than script source, use a DataReader source and set your select statement in the SqlCommand property (e.g. SELECT * FROM [sheet1$] WHERE ...) . At that point, you can do whatever you want in the pipeline, including using conditional splits and/or derived columns as appropriate to handle the NULL ItemDescription and TaxCode pipeline columns, and adding your static columns via a derived column transform.

However, if for whatever reason you need to use a script component, you can use the following CreateNewOutput rows override to handle null cells. "OrElse" is used because its a short-curcuit operator, and .Item is used because it returns an Object, which can hold either a String or a DBNull object. Note there is no "WHERE" clause either, since nulls are handled in the while loop.

Public Overrides Sub

CreateNewOutputRows()
Dim

excelcmd As OleDbCommand = New OleDbCommand("SELECT

Item,TaxCode,ItemDescription FROM [Sheet1$]", excelConn)
Dim

excelreader As OleDbDataReader =

excelcmd.ExecuteReader()
Dim row

As Integer = 0

While

excelreader.Read()
With

NameValsBuffer
.AddRow()
.ItemCode = CStr(IIf(IsDBNull(excelreader.Item(0)) OrElse excelreader.GetString(0).Length = 0, "#", excelreader.Item(0)))
.TaxCode = CStr(IIf(IsDBNull(excelreader.Item(1))

OrElse excelreader.GetString(1).Length = 0, "#", excelreader.Item(1)))
.ItemDescription = CStr(IIf(IsDBNull(excelreader.Item(2)) OrElse excelreader.GetString(2).Length = 0, "#", excelreader.Item(2)))
End

With
End While
End Sub

|||Just to add to what jaegd mentioned, this may also be done with following components: Excel Source, Conditional Split, and your destination component.

In conditional split the condition could be set as follows:
ISNULL(ItemDescription) || ISNULL(TaxCode)

HTH..

Blank fields in Excel file

I am trying to validate and import a Excel file into the database table using script component. The file contains some blank columns in the sheet. How can I handle the blank spaces while validating the file in the Script Component?
The code is as follows:

Dim excelcmd As OleDbCommand = New OleDbCommand("SELECT Item,TaxCode,ItemDescription FROM [Input$]WHERE LEN(Item)>=0 AND LEN(ItemDescription)>=0 AND LEN(TaxCode)>=0", excelConn)
Dim excelreader As OleDbDataReader = excelcmd.ExecuteReader()
Dim row As Integer = 0

While excelreader.Read()
NameValsBuffer.AddRow()
NameValsBuffer.ItemCode = CStr(IIf(excelreader.GetString(0).Length = 0, "#", excelreader.GetString(0)))
NameValsBuffer.TaxCode = CStr(IIf(excelreader.GetString(1).Length = 0, "#", excelreader.GetString(1)))
NameValsBuffer.ItemDescription = CStr(IIf(excelreader.GetString(2).Length = 0, "#", excelreader.GetString(2)))
NameValsBuffer.CompanyId = Me.Variables.CompanyId
NameValsBuffer.UserId = Me.Variables.UserId
End WhileThe thread title says "blank fields", while the text of the question references "blank columns", and lastly "blank spaces". Can you give a small pictorial representation of the problem? It sounds relatively easy to solve, but I'd like to make what the nature of the issue is first.|||

Item

ItemDescription

TaxCode

AAA123

Sample Item 1

XXXXXX

BBB255

Sample Item 2

AAAAA

CCC366

Sample Item 3

BBBBB

XDDD489

Sample Item 4

CCCCC

5EEE

Sample Item 5

DDDDDD

6FFF

Sample Item 6

EEEEEE

GGG

Sample Item 7

FFFFFFF

HHH

Sample Item 8

GGGGG

III

Sample Item 9

HHHHHH

JJJ

Sample Item 10

IIIIIIIIIII

KKK

Sample Item 11

ZZZZZZZZ

MMM

The value in the last row for the ItemDescription and TaxCode is null..so thats the problem I am facing when I am trying to validate and upload data.


|||First off, I'm not sure why you're using a script component, when an ADO.NET connection with the following connnection string will work fine:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"

In your dataflow, rather than script source, use a DataReader source and set your select statement in the SqlCommand property (e.g. SELECT * FROM [sheet1$] WHERE ...) . At that point, you can do whatever you want in the pipeline, including using conditional splits and/or derived columns as appropriate to handle the NULL ItemDescription and TaxCode pipeline columns, and adding your static columns via a derived column transform.

However, if for whatever reason you need to use a script component, you can use the following CreateNewOutput rows override to handle null cells. "OrElse" is used because its a short-curcuit operator, and .Item is used because it returns an Object, which can hold either a String or a DBNull object. Note there is no "WHERE" clause either, since nulls are handled in the while loop.

Public Overrides Sub

CreateNewOutputRows()
Dim

excelcmd As OleDbCommand = New OleDbCommand("SELECT

Item,TaxCode,ItemDescription FROM [Sheet1$]", excelConn)
Dim

excelreader As OleDbDataReader =

excelcmd.ExecuteReader()
Dim row

As Integer = 0

While

excelreader.Read()
With

NameValsBuffer
.AddRow()
.ItemCode = CStr(IIf(IsDBNull(excelreader.Item(0)) OrElse excelreader.GetString(0).Length = 0, "#", excelreader.Item(0)))
.TaxCode = CStr(IIf(IsDBNull(excelreader.Item(1))

OrElse excelreader.GetString(1).Length = 0, "#", excelreader.Item(1)))
.ItemDescription = CStr(IIf(IsDBNull(excelreader.Item(2)) OrElse excelreader.GetString(2).Length = 0, "#", excelreader.Item(2)))
End

With
End While
End Sub

|||Just to add to what jaegd mentioned, this may also be done with following components: Excel Source, Conditional Split, and your destination component.

In conditional split the condition could be set as follows:
ISNULL(ItemDescription) || ISNULL(TaxCode)

HTH..