SSIS - Excel - Get Data from specific cells

• mbrady5

  SSCrazy

  Points: 2835

  More actions

  January 14, 2019 at 2:26 pm

  #373549

  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: 63509

  More actions

  January 14, 2019 at 8:26 pm

  #2018896

  .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

  More actions

  January 15, 2019 at 5:49 am

  #2018921

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

• Phil Parkin

  SSC Guru

  Points: 246985

  More actions

  January 15, 2019 at 6:55 am

  #2018927

  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

  The absence of evidence is not evidence of absence.
  Martin Rees

  You can lead a horse to water, but a pencil must be lead.
  Stan Laurel