Excel import line by line

[Ghornet]

I am importing excel data in to my ms sql database, I would like to know how to read each line of the excel file to compare it to a line in the data base to see if it should be inserted or not, I am thinking that a DTS package with some activeX transformations will do the trick, anyone offer any advice or point me in the right direction.

Damian

[skhanal]

You are in the right track. You can do all sorts of data manipulation in activex script.

Or you could create a linked server to excel and use cursor to do row by row processing.

Third option would be to use OPENQUERY to select rows from excel without using linked server.

Books online has an example on doing this.

[Ghornet]

All I have is this so far

Function Main()

	DTSDestination("Model") = DTSSource("MODEL")
	DTSDestination("StockNo") = DTSSource("STOCK-NO")
	DTSDestination("VIN") = DTSSource("SERIAL")
	DTSDestination("Year") = DTSSource("YR")
	DTSDestination("Make") = DTSSource("MAKE")
	Main = DTSTransformStat_OK
End Function

some how I need it do something like this

Function Main()
if DTSSource("STOCK-NO") not in different table then
	DTSDestination("Model") = DTSSource("MODEL")
	DTSDestination("StockNo") = DTSSource("STOCK-NO")
	DTSDestination("VIN") = DTSSource("SERIAL")
	DTSDestination("Year") = DTSSource("YR")
	DTSDestination("Make") = DTSSource("MAKE")

	Main = DTSTransformStat_OK
end if

End Function

so how do I query to see if DTSSource("STOCK-NO") is in another table before i insert it into this one?

Damian

[Ghornet]

I used a lookup to return a value and based on that value I could insert it or not.. I think that it is working

[skhanal]

Lookup table method works but it is little slow.

If possible try to use a query as a source rather than a table and get only those rows that needs to be inserted by using where clause.

[Ghornet]

How do I make a query in a DTS package?

I am not doing too many uploads. (Maybe 1000 once a week)

I have an excel file. I would love to only grab the records that I need, but don't understand how to do it in DTS without a lookup.

Damian

[skhanal]

In your transform data task, in Source Tab, click on SQL Query and build the query to get data from Excel.

For example

SELECT [Sheet1$].*
FROM [Sheet1$]
WHERE (F3 = 'me')


Select all rows from sheet1 where third column has me.

[Ghornet]

What is me?
It should look something like this right?

SELECT     MAKE, MODEL, RO, SERIAL, [STOCK-NO], YR
FROM         [Service_History_Coach$]
where SERIAL <> (Make another select call here to the table in the database)

thanks for the help

Damian

[skhanal]

'me' is just an example.

I am such a narscicist that I searched for myself.

You are on the right track. The names will work if you have defined Name in Excel, otherwise you have to use F1, F2, .. to refer to columns.

You can use the query builder in transform data task to build your query and preview the data.