-
Excel import line by line
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
-
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.
-
ok still don't quite know
All I have is this so far
Code:
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
Code:
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
-
got it
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
-
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.
-
query
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
-
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.
-
Me?
What is me?
It should look something like this right?
Code:
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
-
'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.
Last edited by skhanal; 07-25-2003 at 02:33 PM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|