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