I need to import a table from excel, except this isn't a
regular table.

The actual table registers begin at the 10th excel line.
Before that, I have some variables in pre-fixed cells,
which together construct part of the primary key of the
registers in the table that begins at the tenth line.

I can´t change the format!
I can´t create a second sheet where the values are linked
from the previous sheet.

I need the DTS to "grab" some variables in pre-fixed cells
at the beginning and then it should "grab" the rest of the
table below prefixing these with the variables read
previously.

Can someone help!!!

Thanks so much for anyone who can spare some time.

You know, I just KNOW there's a better way to do this than what I'm about to
suggest, but this is what I would try.

Import the entire Excel table into a sort of "staging" table in SQL Server.
That would be done in the first Transform Data Task.

The next task would be an Execute SQL Task, sending a call to a stored
procedure.

That stored procedure would first pull in your "variables" from those first
few records. Offhand, I guess you could just hard-code the sp to know where
to look in the resulting cells for each particular variable.

The second step would be to pull in the rest of the data (the "real"
records) out of the staging table for insertion into the production table.
What you want, as you said, is to pull in everything after the tenth record,
so you'll need some way to use your WHERE clause. If the "actual" records
aren't different enough from the inital variables so that you can use some
sort of LIKE or SUBSTRING filter, then you'll need to use some sort of
row-marker, such as an identity field in your "staging" table.

Not elegant, I know. I hope someone posts a neater solution.


Best regards.



JOH



"anabela figueiredo" <anabela.figueiredo (AT) isp (DOT) pt> wrote


I need to import a table from excel, except this isn't a
regular table.

The actual table registers begin at the 10th excel line.
Before that, I have some variables in pre-fixed cells,
which together construct part of the primary key of the
registers in the table that begins at the tenth line.

I can´t change the format!
I can´t create a second sheet where the values are linked
from the previous sheet.

I need the DTS to "grab" some variables in pre-fixed cells
at the beginning and then it should "grab" the rest of the
table below prefixing these with the variables read
previously.

Can someone help!!!

Thanks so much for anyone who can spare some time.

Excel doesn't really care where the data starts. Try creating a spreadsheet
of your own and start your field names in Row 10, you'll see what I mean.
Excel uses named ranges or tables to import from.

As far as I can see this what happens

1. Excel will treat the whole sheet as a table so you will no doubt have a
great deal of whitespace (The majority of cells above row 10). Excel will
expect you to have headers for both the table and the Variables dotted
around the sheet. You can see this when you preview the data. If there are
no headers then some of the data disappears see here

Excel treats First Row of Data as Headers
(http://www.sqldts.com/default.aspx?255)

2. If the data is mixed then the Excel driver has an awful time deciding
what to do see here.

Excel Inserts Null Values
(http://www.sqldts.com/default.aspx?254)

After you have sorted this out I would import into a scratch table and use
TSQL to maniulate from there. You could also use an Active Script
transformation and manipulate the rows as they come through.

We could of course break out Excel automation but that would involove a
whole raft of issues as well so let's try keep away from there.


--


Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org