SSIS Import from Excel

  • 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.

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

    SELECT *

    FROM [Sheet1$B9:Q]

  • 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

  • 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]

  • 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?

  • 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?

  • Hi There,

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

    Thanks

  • 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)

  • 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.

  • 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?

  • 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.

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

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