Exclude rows from Excel before table import?

[MrPeds]

Hi,

this is another basic (?) newbie question.

I have successully set up a DTS step to import data from an Excel spreadsheet into a SQL server table.

However, is there any way of excluding data from the Excel spreadsheet? Basically if I have something like:

col1, col2, col3, col4, col5
ads cvb jhg excl jkjhj
mnb yts xcv jkl ert
dfg wer nbv excl sdfg
bvc rtt qwe zxc opi

I want to exclude rows where the value of col4='excl' so I would be left with rows 2 and 4 to be inserted into my table.

Is this possible? or do i need to import all the data into my SQL table, and then carry out a delete query?

thanks,

MrPeds

 

[JohnDTampaBay]

In the properties of the Data Transformation task on the Source tab, I believe you can specify a table or query as the source. You probably have table selected. You should be able to specify Query, then write the appropriate SELECT statement against the Excel spreadsheet:

SELECT * FROM [ExcelSheet] WHERE Col4 <> 'excl'


--Angel
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[MrPeds]

Thanks for this Angel.

I;ve had a look at doing the same sort of thing but using a CSV file instead, but when I look at the source tab it doesnt look as if i can carry out a query on a csv file.

In this case, what should I do? Do i need to open up the csv file first in an ActiveX script and then save it as an Excel file, or do i need to import all the data from the csv file into a table and then carry out a DELETE query where col4<>'excl' ?

What is best practise?

Thanks,

MrPeds

 

[JohnDTampaBay]

Such is the case with text files. IMHO, I would import the CSV to a staging table separate from the final destination table, then INSERT INTO the destination table SELECTing the rows FROM the staging table WHERE Col4 <> 'excl'. I prefer to do most of my work in SQL. You may be as comfortable using ActiveX and Excel. Six of one, half a dozen of the other.

--Angel
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)