SSIS Import from Excel

Question

Post reply

SSIS Import from Excel

TSQL Tryer

SSCrazy Eights

Points: 8869
More actions
October 6, 2015 at 3:11 am

#308595

Firstly - sorry if this is the wrong forum.
The issue I have is I have inherited an Excel format that we email to customers, which we then import into SQL.
The issue is that the format is not something I've had experience in importing via SSIS in the past as it is not a "standard" format. I have attached the format and as you can see the data does not start until cell B9.
Up to column Q could have data in it and this could have up to hundreds of thousands of rows.
I can't see anywhere in the Excel source how I would get SSIS to just import from B9 - Q
But not only that - how do I get SSIS to know that the column names are actually on row 2 and start in column B (through to Q)
Hopefully the attached spreadsheet makes sense.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

tindog SSCarpal Tunnel Points: 4892 More actions · Answer 1

In the data flow source, use 'SQL Command' and do something like:

SELECT *

FROM [Sheet1$B9:Q]

TSQL Tryer SSCrazy Eights Points: 8869 More actions · Answer 2

Thanks for that.

That gives me the data that I require.

However it does not list the headers of the columns. How would I achieve that in the SQL statement?

The columns (as per attachment) start at B2

tindog SSCarpal Tunnel Points: 4892 More actions · Answer 3

I don't have the means to test at the moment - perhaps this would work?

SELECT *

FROM [Sheet1$B2:Q2]

UNION

SELECT *

FROM [Sheet1$B9:Q]

TSQL Tryer SSCrazy Eights Points: 8869 More actions · Answer 4

Appreciate your help with.

As I have the above UNION and the Excel Connection has "First Row has Column Names", so that the first select statement picks up the actual header names in the spreadsheet and not "Field1", "Field2"....

What is does is for my second select statement is that it imports a NULL value field in.

So I get an extra row imported which I don't want. Is there a way I can exclude that NULL value?

tindog SSCarpal Tunnel Points: 4892 More actions · Answer 5

Do you mean that it's importing a row consisting of entirely NULL values, or that when a certain field is NULL it shouldn't be imported? Is there a primary key on the destination table?

TSQL Tryer SSCrazy Eights Points: 8869 More actions · Answer 6

Hi There,

Yes there is a primary key called "id". And the row is all fields null. Which becomes id 1.

Thanks

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

TSQL Tryer (10/23/2015)
Appreciate your help with.
As I have the above UNION and the Excel Connection has "First Row has Column Names", so that the first select statement picks up the actual header names in the spreadsheet and not "Field1", "Field2"....
What is does is for my second select statement is that it imports a NULL value field in.
So I get an extra row imported which I don't want. Is there a way I can exclude that NULL value?

WHERE clause in the first SELECT?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RonKyle SSC-Dedicated Points: 31556 More actions · Answer 8

Do you mean that it's importing a row consisting of entirely NULL values, or that when a certain field is NULL it shouldn't be imported? Is there a primary key on the destination table?

This behavior is consistent with the row having been used at some point, at least as reckoned by Excel. If you manually highlighted however many rows are coming in that way from the end of the rows and deleted them completely, this wouldn't happen. In an automated setting this is of course impossible to do. I would suggest a cleanup query deleting all the rows that have NULLs in a column that shouldn't or checked all the fields for a NULL value.

TSQL Tryer SSCrazy Eights Points: 8869 More actions · Answer 9

Hi,

Thanks for both responses.

1. Jeff Moden you mention placing a WHERE clause. How do you do that when it is referencing a worksheet from Excel like below?

SELECT *

FROM [Sheet1$B2:Q2]

UNION

SELECT *

FROM [Sheet1$B9:Q]

2. RonKyle, I thought of just removing the NULL after but this now has a Primary ID of 1, which means any "real" data starts at ID 2. Is there anyway of re-iding the columns if I followed your route?

RonKyle SSC-Dedicated Points: 31556 More actions · Answer 10

2. RonKyle, I thought of just removing the NULL after but this now has a Primary ID of 1, which means any "real" data starts at ID 2. Is there anyway of re-iding the columns if I followed your route?

If it's that important, I would treat the first table as an initial table and then after the Null rows were cleared do a subsequent import. The amount of data might limit that, but I can't think of a time I've been particularly worried about the actual ID number.