SSIS - Excel - Get Data from specific cells

  • mbrady5

    SSCrazy

    Points: 2835

    Hello,

    I'm hoping to get some ideas/links of the best way to accomplish the below requirement.

    I have a folder that contains roughly 1800 Excel Documents. Each document has data in specific cells that I need to extract.

    My requirement is to provide a final dataset that has the following in each row;

    Name of the Excel Doc |  Contents of Cell A1 for that document  |  Contents of Cell B3 for that document | Contents of Cell D1 for that document | etc......

    So, I would end up wit 1800 +/- rows with the title and the associated Excel Data from each individual file. Hope that makes sense..?

    Thanks

  • pietlinden

    SSC Guru

    Points: 62682

    .What if you automated Excel to write the values somewhere? Assign the values to variables (one for the file name too), and then maybe have a connection to your database and execute a stored procedure that does the insert, and just gets called once per sheet?
    Sounds to me like a VBA would be a more viable way to do it.

  • mbrady5

    SSCrazy

    Points: 2835

    Thanks for the reply. Seems like a viable solution. I will have to look into that.

  • Phil Parkin

    SSC Guru

    Points: 244004

    mbrady5 - Monday, January 14, 2019 2:26 PM

    Hello,

    I'm hoping to get some ideas/links of the best way to accomplish the below requirement.

    I have a folder that contains roughly 1800 Excel Documents. Each document has data in specific cells that I need to extract.

    My requirement is to provide a final dataset that has the following in each row;

    Name of the Excel Doc |  Contents of Cell A1 for that document  |  Contents of Cell B3 for that document | Contents of Cell D1 for that document | etc......

    So, I would end up wit 1800 +/- rows with the title and the associated Excel Data from each individual file. Hope that makes sense..?

    Thanks

    It is possible to import data from single cells, or ranges, of an Excel spreadsheet.

    For test purposes, create a string package variable and set it to Sheet1$A1:A1 (where Sheet1 is the name of the worksheet you are importing from).

    In your Excel source, set Data Access Mode to Table Name or View Name Variable and set the variable name to be the variable you created above.

    If that works, you need to expand the process
    a) To import all of the bits of data you need (which will require multiple data flows, or a single data flow which sucks in everything within a certain range and throws away anything you don't need).
    b) To loop round all of the files

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Viewing 4 posts - 1 through 4 (of 4 total)

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