How to read single Excel cell using SSIS Excel Source

Question

I have an excel sheet from which I want to read a single cell from a specific location. How can I read a single cell in SSIS

Solution

ZappySys Provides SSIS Excel Source which has many features along with some advanced features to read from a single cell. Basically you need to Set RangeEndCell={cell location}, RangeStartCell={cell location}, Headerless=True , MaxRows=1

Once you read the value you can use Set Variable Transform to save the value into a variable

NOTE: Make sure to attach with Trash destination to finish the flow else unconnected transform is removed at runtime by Performance optimization Engine in SSIS when you run via SQL Agent Job.

Here is how to do step by step.

Find out Excel Cell Location

1. Go to Excel you like to read. Open it and select the cell you like to read.
2. Find out excel Cell Location (E.g. A2)

Create SSIS Excel Connection

SSIS Excel File Source Connector (Advanced Excel Source) can be used to read the Excel file without installing any Microsoft Office Driver. You can extract using Table mode or query mode (SQL Command). Support for flexible metadata detection mode so you can get accurate datatypes

1. Firstly, You need to Download and Install SSIS ZappySys PowerPack.
2. Once you finished first step, Open Visual Studio and Create New SSIS Package Project.
3. Now, Drag and Drop SSIS Data Flow Task from SSIS Toolbox.
   
4. Double click on the Data Flow task to see Data Flow designer surface.
5. Here, In Visual Studio, drag and drop the ZS Excel Source in the design surface.
   
6. We need Excel File Connection. So lets create Excel File Connection.
7. Right click on Connection Managers Panel to Create New ZS-Excel Connection and Context Menu will appear, Select New Connection from the Context Menu.
   
8. Select ZS-EXCEL Connection Manager from the Connection Managers list and Click on Add Button
   
9. Now in opened excel connection manager window set Source Path with file name and suitable extension. Also Pattern is only applicable for Excel Source. If you use excel destination.. it must use single file.

   Note: If you want to operation with multiple files then use wildcard pattern as below (when you use wildcard pattern in source path then system will treat source path as folder regardless you end with slash).
   
   Examples:
   c:\SSIS\Excel\file123.xlsx (single file).
   c:\SSIS\Excel\file*.xlsx (all files starting with name file).
   c:\SSIS\Excel\subfolder\*.xlsx (all files with .xlsx Extension and located under folder subfolder).
   

   
   
10. Click OK to save Connection Manager setting UI.

Configure SSIS Excel Source

Now let's configure Excel Source to read just one cell. 

--Use Query --

You can also use Query (We used $first_sheet$ as table name to grab any first sheet 2nd Cell A2 but you can enter your sheet name.

SELECT * FROM [$first_sheet$] 
WITH(
FormattedColumns='Col1', 
HeaderLess=true, 
RangeStartCell='A2', 
RangeEndCell='A2' 
)

 

How to read multiple Excel cells value using Excel Source 

In upper section we read the single Excel Cell value, now we will see how to read the multiple excel cell data using Excel Source and store it in the variable.

Let's say if we want to read the A2 and F5 cell data. For that, we need to use the two different Excel Sources, first for the A2 and second for the F5.

Configure SSIS Excel Source to read the A2 cell data

Now let's configure Excel Source to read just one cell. 

--Use Query --

You can also use Query (We used $first_sheet$ as table name to grab any first sheet 2nd Cell A2 but you can enter your sheet name.

SELECT * FROM [$first_sheet$] 
WITH(
FormattedColumns='Col1', 
HeaderLess=true, 
RangeStartCell='A2', 
RangeEndCell='A2' 
)

Configure SSIS Set Variable Transform to set the A2 cell value in the variable.

First, create new two variables to store the cell value in it. 

Now add the ZS Set Variable Transform and select the desired variable

After that go to Input columns Tab and select the input column and click on OK.

That's it will read the A2 cell data from using Excel Source and store in the vA2 string type variable.

Same, way you need to configure the another Excel Source and Set Variable Transform to get the F5 Cell Value.

Reference

https://zappysys.com/products/ssis-powerpack/ssis-excel-file-source/

https://zappysys.com/onlinehelp/ssis-powerpack/scr/ssis-excel-source.htm

Contact Us

If you have more question(s) feel free to contact us via Live chat or email to [e-mail]

https://zappysys.com/support/