Showing posts with label sheet. Show all posts
Showing posts with label sheet. Show all posts

Sunday, February 19, 2012

blank pages inserted on exported pdf and tif format

Hi,
After I export the report to PDF or TIF format, the pages became double. It
inserts a blank sheet on every page of the report. Does anyone know how to
fix this issue? Any help is appreciate it.
Thanks,
LianneLianne,
I had that problem too. Some of your report items are wider than the page.
Look on the ruler above your report page and make sure that you do not have
any report items that go to the right more than the width of your page
(usually it's 8.5 inches).
Good luck.
"Lianne Kwock" wrote:
> Hi,
> After I export the report to PDF or TIF format, the pages became double. It
> inserts a blank sheet on every page of the report. Does anyone know how to
> fix this issue? Any help is appreciate it.
>
> Thanks,
> Lianne|||Not greater than the width of the page when considering your margins plus
the ruler.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Simon Gold" <SimonGold@.discussions.microsoft.com> wrote in message
news:862C6173-C1F9-40B2-8C4E-1B1333BEA0ED@.microsoft.com...
> Lianne,
> I had that problem too. Some of your report items are wider than the page.
> Look on the ruler above your report page and make sure that you do not
> have
> any report items that go to the right more than the width of your page
> (usually it's 8.5 inches).
> Good luck.
> "Lianne Kwock" wrote:
>> Hi,
>> After I export the report to PDF or TIF format, the pages became double.
>> It
>> inserts a blank sheet on every page of the report. Does anyone know how
>> to
>> fix this issue? Any help is appreciate it.
>>
>> Thanks,
>> Lianne|||You will also need to make sure that your report respect the following:
Body:Width > (Report:PageWidth - Report:LeftMargin -
Report:RightMargin).
A similar restriction exists for Body:Height and Report:PageHeight.
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lianne Kwock" <LianneKwock@.discussions.microsoft.com> wrote in message
news:509E186D-CBBD-4725-B46C-47F175AEAA6F@.microsoft.com...
> Hi,
> After I export the report to PDF or TIF format, the pages became double.
> It
> inserts a blank sheet on every page of the report. Does anyone know how
> to
> fix this issue? Any help is appreciate it.
>
> Thanks,
> Lianne

Thursday, February 16, 2012

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