Excel Source: not to import blank rows

I am trying to load data from an excel file, how do I use SSIS so that it won't import blank rows in the source excel file?

Thanks in advance!

As with other databases, you'll need to use a WHERE clause to exclude rows that you don't want. There is no such option available on the driver, the connection manager, or the Excel source or destination.

-Doug

|||

The easiest way ist to load all rows from thr excel file and then use in the DataFlow a Conditional Split transformation to split the blank rows.

Your condition may be ISNULL(xxxxxx).

|||

Thank both of you very much. Condition Split works.

I have another question:

SSIS is importing data from an EXCEL source file, which is on a password protected web site, like: http://www.abc.com/myexcel.xls

I always get validation error: Excel Source[649], The AcquireConnection method call to the connection manager "ExcelSource" failed with error code 0xC0202009. Please help!

Thanks again.

|||

If you mean a password-protected Excel file, the driver simply cannot open that.

If you're talking about a Web site that requires a login, the Excel Source certainly can't handle this, but I'm not certain what the solution is. One option might be to use a Script task that logged in and copied the file locally first...

-Doug

|||

Thanks for your reply, Doug.

Yes, what I meant is, the web site requires a login to access the excel file. Script task is an option, but any other suggestions is stilly highly appreciated.

excel source with optional columns

Hi:

I use a SSIS package to loop thro a folder and load data from multiple excel files to a SQL2005 table. Works fine except when an excel has a missing col.

Col names in xls are always a subset of col names in the table. The missing cols are random, else I would just have made another package:-)

Once a missing column is found, I get runtime and design time errors, and metadata problems. How can a get SSIS to ignore missing columns?

TIA

I recently solved this problem using a dynamically built select statement. Is it always just 1 column that's missing or do you need to load a dynamic number of columns? If it's a truly dynamic then the algorithm is a little more complex...|||

Thanks for your response. Request you tell me more aboout it.

I did the whole thing in BIDS in a SSIS project, using a ForEach container, a Excel Source and an OleDB destination. I was hoping to achieve my objectives with these objects and their settings :-).

|||I used a For Each Loop and then a For Loop to solve this problem.

The first For Each Loop iterates threw the columns names in the spreadsheet. It contains a script component that counts the columns storing the result in a variable. There might be a more efficient way to count columns but I couldn't figure out how.

The second For Loop container uses this counter variable to select and load each column one at a time. It contains 2 components; a script component that builds a select statement and a data flow task that actually moves the data using the select statement.

Here is the script code that dynamically builds each select statement:

Public Sub Main()
Dim SelectCommand As String
Dim WorksheetName As String
Dim ColumnLoopIndex As Integer
WorksheetName = Dts.Variables("WorksheetName").Value.ToString
ColumnLoopIndex = CInt(Dts.Variables("ColumnLoopIndex").Value)
SelectCommand = "Select F" & ColumnLoopIndex.ToString & " AS CurrentColumn from [" & WorksheetName & "]"
Dts.Variables("SelectCommand").Value = SelectCommand
Dts.TaskResult = Dts.Results.Success
End Sub

Please note: Depending on your data and how dynamic you want the

package to be you could skip the second For Loop and build a single select

statement that loads all of the columns. In this case your dynamically built select

statement would contain return fields like "SELECT F1, F2, NULL AS F3, NULL AS F4

FROM [myworksheetname]" to account for missing F3 and F4 columns.

Excel reader issues

Hi,

I need to load data from excel files which will be provided by a number (around 100 monthly) of external suppliers, so we don't get 100% control over the files themselves.

What my solution involves is copying the excel file to a common name (e.g. supplierExcel.xls), turning this into a pipe delimited txt and then loading the txt. I had trouble switching files when trying to load directly from excel.

All these files should arrive in the same format of 36 fields and of course in the right order; there will be rejections if they fail.

I've come across a problem extracting the data from excel where I'm getting 'the value could not be converted because of a potential loss of data' on field 1. It only happens on excel files where there is a quote mark as the first character and I have loaded other files quite happily without the quotemark.

Has anyone seen this before? Is this a known issue and how can I get around it, without recourse to manually changing the individual files?

Thanks

nathan

Hi,

It is difficult to answer, you should be more specific on a couple of things.

- Why cannot you load direct from Excel. this could be the only fix you need, You could use the File System task.

- How do you convert the Excel to text

- Do you use table load or SQL Command

- Do you need the quote when it is the initial char? In Excel it means you want to force the cell content to be text.

If you do not need the quote, strip it from the file.

declare @.sometext as varchar(255)

set @.sometext = '''There is an initial '' in this test string'

select substring(@.sometext, patindex('''%',@.sometext)+1,255)

if you need te quote, try to replace it by 3 quotes or make sure the entry is not too long, May be you could do this

declare @.sometext as varchar(255)

set @.sometext = '''There is an initial '' in this test string'

select case left(@.sometext,1) when char(39) then char(39) + char(39) + @.sometext else @.sometext end

.Philippe