Retrieve specific cell value from Excel into SSIS variable

Question

Retrieve specific cell value from Excel into SSIS variable

Viewing 15 posts - 16 through 30 (of 47 total)

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

RWillsie SSC Enthusiast Points: 144 More actions · Answer 1

I'll toss in my two cents. Date values in excel are stored as "serial numbers", not as dates per se. The integer portion can be considered the number of days since 12:00 am on 1 January, 1900. The decimal portion being the "hours/Minutes/microseconds (ticks)" since 12:00 am on that particular date.

For instance, if you type the value 42384 into an excel cell and then format the cell as short date your cell shows "01/15/2016".

If you type in 42384.500 then format the cell using a format that shows hours, minutes, and am/pm you will see "1/15/16 12:00 PM".

So, your excel value comes into sql as a number and you have to get it converted somehow into a valid TSQL date format.

This article will help with that step. http://stackoverflow.com/questions/13850605/t-sql-to-convert-excel-date-serial-number-to-regular-date

Another excellent article is here at: http://www.sqlservercentral.com/Forums/Topic1116041-391-1.aspx

MMartin1 One Orange Chip Points: 27790 More actions · Answer 2

In Excel, insert a column next to the one with the date. Assuming there is a value in B2 displaying like 12/31/2015 ... Enter a formula into C2 (the new column just inserted) as

=TEXT(B2, "mm/dd/yyyy")

This will convert the date (which is really a number ) into a string capturing the format displaying. Apply this formula to all applicable rows in your sheet in column C. In SSIS now instead try to import the value(s) in this column instead. If this errs, try to copy and paste as values so that there is no formula in the column; though I dont think you will have to do this. Please post back and let us know how it turns out.

----------------------------------------------------

Jeff Moden SSC Guru Points: 1004175 More actions · Answer 3

murtzd (12/17/2015)
Hi all,
I'm experiencing a very frustrating issue. I am using the guide below to read a cell value in Excel into a SSIS variable;
http://www.techbrothersit.com/2013/11/ssis-read-excel-cell-value-in-ssis.html
It works very well for anything but a cell that has a date in it! I have tried to change the data type of the SSIS variable to everything possible but it still doesn't work, and I keep getting this error message;
An error occured while assigning a value to variable "ValuationDate": "Unsupported data type on result set binding 0"
The cell I am trying to read is B4, and the SQL Statement I am using in the Execute SQL Task is SELECT * FROM [Sheet1$B4:B5]. I am happy this is correct because if I change the value in B4 to anything that isn't a date, it works.
Please help!!

You do realize that [Sheet1$[font="Arial Black"]B4:B5[/font]] is two cells, correct?

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

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 4

Jeff Moden (1/15/2016)
murtzd (12/17/2015)
Hi all,
I'm experiencing a very frustrating issue. I am using the guide below to read a cell value in Excel into a SSIS variable;
http://www.techbrothersit.com/2013/11/ssis-read-excel-cell-value-in-ssis.html
It works very well for anything but a cell that has a date in it! I have tried to change the data type of the SSIS variable to everything possible but it still doesn't work, and I keep getting this error message;
An error occured while assigning a value to variable "ValuationDate": "Unsupported data type on result set binding 0"
The cell I am trying to read is B4, and the SQL Statement I am using in the Execute SQL Task is SELECT * FROM [Sheet1$B4:B5]. I am happy this is correct because if I change the value in B4 to anything that isn't a date, it works.
Please help!!
You do realize that [Sheet1$[font="Arial Black"]B4:B5[/font]] is two cells, correct?

That is actually how the article recommends you write the query to reference a specific cell.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Jeff Moden SSC Guru Points: 1004175 More actions · Answer 5

Orlando Colamatteo (1/16/2016)
Jeff Moden (1/15/2016)
murtzd (12/17/2015)
Hi all,
I'm experiencing a very frustrating issue. I am using the guide below to read a cell value in Excel into a SSIS variable;
http://www.techbrothersit.com/2013/11/ssis-read-excel-cell-value-in-ssis.html
It works very well for anything but a cell that has a date in it! I have tried to change the data type of the SSIS variable to everything possible but it still doesn't work, and I keep getting this error message;
An error occured while assigning a value to variable "ValuationDate": "Unsupported data type on result set binding 0"
The cell I am trying to read is B4, and the SQL Statement I am using in the Execute SQL Task is SELECT * FROM [Sheet1$B4:B5]. I am happy this is correct because if I change the value in B4 to anything that isn't a date, it works.
Please help!!
You do realize that [Sheet1$[font="Arial Black"]B4:B5[/font]] is two cells, correct?
That is actually how the article recommends you write the query to reference a specific cell.

I see that is Step 3 but it makes no sense. They also say "have to" without saying why nor does it provide any supporting reference. The ACE drivers don't have such a requirement. I don't understand why SSIS would.

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

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 6

Jeff Moden (1/16/2016)
Orlando Colamatteo (1/16/2016)
Jeff Moden (1/15/2016)
murtzd (12/17/2015)
Hi all,
I'm experiencing a very frustrating issue. I am using the guide below to read a cell value in Excel into a SSIS variable;
http://www.techbrothersit.com/2013/11/ssis-read-excel-cell-value-in-ssis.html
It works very well for anything but a cell that has a date in it! I have tried to change the data type of the SSIS variable to everything possible but it still doesn't work, and I keep getting this error message;
An error occured while assigning a value to variable "ValuationDate": "Unsupported data type on result set binding 0"
The cell I am trying to read is B4, and the SQL Statement I am using in the Execute SQL Task is SELECT * FROM [Sheet1$B4:B5]. I am happy this is correct because if I change the value in B4 to anything that isn't a date, it works.
Please help!!
You do realize that [Sheet1$[font="Arial Black"]B4:B5[/font]] is two cells, correct?
That is actually how the article recommends you write the query to reference a specific cell.
I see that is Step 3 but it makes no sense. They also say "have to" without saying why nor does it provide any supporting reference. The ACE drivers don't have such a requirement. I don't understand why SSIS would.

I agree. I thought the query looked odd too, like a range. I am in the process of setting up my machine to test it out, needed the 15.0 ACE drivers, to see for myself.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 7

murtzd (12/17/2015)
Hi all,
I'm experiencing a very frustrating issue. I am using the guide below to read a cell value in Excel into a SSIS variable;
http://www.techbrothersit.com/2013/11/ssis-read-excel-cell-value-in-ssis.html
It works very well for anything but a cell that has a date in it! I have tried to change the data type of the SSIS variable to everything possible but it still doesn't work, and I keep getting this error message;
An error occured while assigning a value to variable "ValuationDate": "Unsupported data type on result set binding 0"
The cell I am trying to read is B4, and the SQL Statement I am using in the Execute SQL Task is SELECT * FROM [Sheet1$B4:B5]. I am happy this is correct because if I change the value in B4 to anything that isn't a date, it works.
Please help!!

This query:

SELECT * FROM [Sheet1$B4:B5]

is translated by the Driver as a request for a resultset containing the value of cell B5 with a column name of what is in cell B4. So, if you want the value in B4 then try this:

SELECT * FROM [Sheet1$B3:B4]

I played around with the query syntax a bit to see if I could get the value of cell B4 with the column name from what is cell B1 which I think would be a more traditional setup for a tabular set of data in Excel but I could not coax the query into the right form to do it. If I figure it out I'll post back.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 8

Orlando Colamatteo (1/17/2016)
murtzd (12/17/2015)
Hi all,
I'm experiencing a very frustrating issue. I am using the guide below to read a cell value in Excel into a SSIS variable;
http://www.techbrothersit.com/2013/11/ssis-read-excel-cell-value-in-ssis.html
It works very well for anything but a cell that has a date in it! I have tried to change the data type of the SSIS variable to everything possible but it still doesn't work, and I keep getting this error message;
An error occured while assigning a value to variable "ValuationDate": "Unsupported data type on result set binding 0"
The cell I am trying to read is B4, and the SQL Statement I am using in the Execute SQL Task is SELECT * FROM [Sheet1$B4:B5]. I am happy this is correct because if I change the value in B4 to anything that isn't a date, it works.
Please help!!
This query:
SELECT * FROM [Sheet1$B4:B5]
is translated by the Driver as a request for a resultset containing the value of cell B5 with a column name of what is in cell B4. So, if you want the value in B4 then try this:
SELECT * FROM [Sheet1$B3:B4]
I played around with the query syntax a bit to see if I could get the value of cell B4 with the column name from what is cell B1 which I think would be a more traditional setup for a tabular set of data in Excel but I could not coax the query into the right form to do it. If I figure it out I'll post back.

OK, figured it out. You must first change the properties of your Excel Connection Manager by unchecking the "First row has column names" option.

By changing this option the connection string properties will be changed where HDR is now set to NO. You can also manually change the connection string properties if you like.

Setting HDR to NO changes the behavior of the Driver by asking it to assign column names dynamically when referencing data whereas when HDR is YES the Driver expects the first cell in each column of the range to contain the column name.

Once you configure the Excel Connection Manager in this way you can use a query like this to get a specific cell.

SELECT F1 FROM [Sheet1$B4:B4]

Note the column name of F1. The Driver must refer to each column by name and it ordinally assigns F1...Fn when dynamically assigning column names.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Jeff Moden SSC Guru Points: 1004175 More actions · Answer 9

No pun intended but EXCELlent work, Orlando. Thanks.

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

Rahul Kumar-480494 Grasshopper Points: 16 More actions · Answer 10

try this, just pass the cell address in range.

http://sqlserversolutions.blogspot.in/2009/02/selecting-excel-range-in-ssis.html

silverbullettruck SSC Veteran Points: 272 More actions · Answer 11

I would recommend using the Excel Task from CozyRoc. With that component you can specify a worksheet and column and and it will return the value that can be stored into a variable. It's pretty painless.

Rahul Kumar-480494 Grasshopper Points: 16 More actions · Answer 12

try this, just provide cell address in range

http://sqlserversolutions.blogspot.in/2009/02/selecting-excel-range-in-ssis.html

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 13

A lot of folks have trouble getting third party tools brought into their environment under any circumstances but especially when a task can be done natively in SSIS.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

murtzd Ten Centuries Points: 1039 More actions · Answer 14

Thank you for all the input!

Orlando, I'll give your method a go. However I don't understand one thing, there doesn't seem to be a problem with my syntax because it works with any value that isn't a date, so the correct cell value in B4 is actually retrieved in SSIS.

Why does that syntax need changing when I am looking to import a date?

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 15

Chances are that while you see a date in the cell when you open the Excel file the data behind it is stored as a decimal. When you open the Worksheet, click on the cell. What do you see in the ribbon for "format?" While on the cell, press F2 to edit the cell contents. What do you see?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato