December 30, 2024 at 9:23 pm
Hi, after i copy in ssis my excel file to a secured folder, its looking more and more like i'll need to get data out of the destination spreadsheet at the cell level.
i'm looking at posts like the one at https://stackoverflow.com/questions/41702840/i-need-to-pull-specific-cells-from-a-single-excel-file-to-create-a-single-row-in but dont believe power query is the answer because 1) its an add in and i'd like to leave the spreadsheets alone, 2) if i were to publish a power query driven item to the pbi service i'd probably have no economical way to get ssis involved and i'd probably be creating a dataset which i believe requires more than the pbi pro licenses we live with today.
on the other hand the poster at that link says he found a way to use vba to extract cell level data. Outside of ssis. Can the community tell me where such vba would run from and how it would be awakened to do this? ive used it before i believe in both ssrs and excel but cant picture how it can be run externally to but using my spreadsheet as a source.
December 30, 2024 at 10:31 pm
Importing data from a specific Excel range is perfectly doable – and that range can be a single cell. No need to resort to PBI or VBA, just a standard data flow.
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
January 2, 2025 at 5:00 pm
thx phil do you mean data flow in the cloud service or more down to earth as in ssis? i noticed in a separate post you mentioned an ssis script task which would allow me to get .net in the picture. i'll look for a link on the web.
January 2, 2025 at 5:12 pm
i found this https://www.techbrothersit.com/2016/04/how-to-read-cell-value-from-excel-by.html . it iterates i believe thru an ado list of cells to extract.
lots of code (even beside the loop) to read a handful of cells. and it appears the sheet needs to be an excel table , not sure. but ours is not an excel table.
looking for something simpler.
January 2, 2025 at 5:17 pm
this looks simpler. may try it. https://stackoverflow.com/questions/13627337/read-excel-cell-values-with-ssis-script-task . not sure if sheet has to be a table or not in order for the select statement to work.
just saw the age of this post. may not try it. dont know if that connector is access.
January 2, 2025 at 6:06 pm
If you're not adverse to watching a video, there are several tutorials showing how to read a specific cell range from an Excel sheet available on youtube.com.
For example this short one: https://www.youtube.com/watch?v=UYDvldSnuE0
January 4, 2025 at 9:50 am
thx phil do you mean data flow in the cloud service or more down to earth as in ssis? i noticed in a separate post you mentioned an ssis script task which would allow me to get .net in the picture. i'll look for a link on the web.
Sorry for the slow response. I'm travelling & not yet back working (much).
I meant an SSIS data flow. Import from a spreadsheet as usual, after defining your Excel source.
Create a package parameter, eg, ExcelRange. Give that parameter a value which identifies the exact range you are interested in. How to do this is not completely obvious. First, you need to know the internal name of the sheet you are importing from, which may be different from what appears in the worksheet tab (I can expand on this if you don't know what I mean). Next, you need to define the cell-reference range.
One which I have used recently is Data$A:DM. This is columns A to DM in worksheet with internal name 'Data'. There are no numbers here, but they can be added to limit the number of rows defined by the range.
In the SSIS data flow, create an Excel source and set Data access mode to 'Table name or view name variable', using the parameter you have defined above.
When the DF is executed, only the range you have defined will be included in the data stream.
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply