June 28, 2010 at 1:01 am
Hi
I am putting together an SSIS package to upload data from an excel sheet. The sheet is sparsely populated. The sheet is wide, with at least 70 columns, with only a handful of columns populated in each row.
I do not want NULL values to appear in the table for all the empty cells.
What is an efficient way of bringing through all records, but removing NULLs along the way?
(the columns are datetime, varchar and float)
The only way I know is to do an ISNULL replacement for each column in a Derived Column trans. While this works okay, I have a number of different sheets with this same issue, and this is getting rather tedious!
Appreciate any ideas.
Thanks
K
June 28, 2010 at 12:48 pm
Firstly, what is wrong with NULL? If a cell has no value, and is empty or unknown, NULL is the correct representation of that.
If however you really do need to replace empty/NULL with 0 or "", and you have multiple spreadsheets which are not all the same format, you probably need a script component, as they can be much more flexible. For example you can loop over the columns of a row rather than having to define a separate rule for each column.
Here's a function I use to load an Excel sheet into a Table variable. Once you've got it there, you can check the headers, loop through the data, write it to a database table, or whatever else you want. If you use it in a Dataflow script source component, you can fill the output rows from it.
Public Function GetXLtoTable(ByVal File As String, Optional ByVal Sheetname As String = "Sheet1", Optional ByVal Where As String = "") As DataTable
' Load sheet from Excel file into a Table Variable, which is returned if there are no errors
' Optionally, you can specify the sheet name, and can include a WHERE clause to select only wanted rows.
GetXLtoTable = Nothing ' Return Nothing if no data read
Try
' Open an Excel workbook, and load the indicated sheet into a DataTable variable
Dim SQL As String = "SELECT * FROM [" & Sheetname & "$]"
If Where <> "" Then SQL = SQL & " WHERE " & Where ' Optionally, select only wanted records
Dim CS As String ' IMEX 1 means treat all fields as text - set to 0 if you want it to guess which fields are numeric
CS = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & File & "; Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;"""
If Not IO.File.Exists(File) Then ' Make sure the file exists
MsgBox("GetXLtoTable: File " & File & " does not exist.")
Else
Dim MyConnection As New System.Data.OleDb.OleDbConnection(CS)
Dim MyCommand As New System.Data.OleDb.OleDbCommand(SQL, MyConnection)
Dim myAdapter As New Data.OleDb.OleDbDataAdapter(MyCommand)
Dim DT = New System.Data.DataTable
myAdapter.Fill(DT)
GetXLtoTable = DT
MyConnection.Close()
End If
Catch ex As Exception
MsgBox("GetXLtoTable fail: " & Err.Description)
End Try
End Function
June 30, 2010 at 4:15 am
Thanks for the script - very useful.
cheers
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply