Retrieve specific cell value from Excel into SSIS variable

Question

Post reply

Retrieve specific cell value from Excel into SSIS variable

Viewing 15 posts - 31 through 45 (of 47 total)

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

murtzd Ten Centuries Points: 1039 More actions · Answer 1

murtzd

Ten Centuries

Points: 1039

January 19, 2016 at 9:20 am

#1852822

It shows up as 19/01/2016

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

what about when you press F2? same?

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

Orlando Colamatteo (1/19/2016)
what about when you press F2? same?

I tried this in Excel 2010 and, whether the date is a 'proper' date or just a date entered as text, it still appears in xx/xx/yyyy format after pressing F2.

Another possible check is to highlight the cell and apply the General format to it. This will convert the display of what Excel thinks is a date to an int.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

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

Phil Parkin (1/19/2016)
Orlando Colamatteo (1/19/2016)
what about when you press F2? same?
I tried this in Excel 2010 and, whether the date is a 'proper' date or just a date entered as text, it still appears in xx/xx/yyyy format after pressing F2.
Another possible check is to highlight the cell and apply the General format to it. This will convert the display of what Excel thinks is a date to an int.

I cannot seem to recreate it doing that on my side but did have a cell setup like that at one point and when the underlying data is an Int (shown when pressing F2) then capturing the cell result in a scalar variable using an Execute SQL Task is a problem. I am able to handle it as a DataSet though.

If someone can post a worksheet that has a cell setup that way so Incan make sure my code works in all cases I'm happy to doc the solution.

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

MMartin1 One Orange Chip Points: 27795 More actions · Answer 5

MMartin1 (1/15/2016)
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.

Was this approach tried? I will give this a go to see if I can get a Excel date value into a SSIS Date variable a little later.

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

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

MMartin1 (1/19/2016)
MMartin1 (1/15/2016)
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.
Was this approach tried? I will give this a go to see if I can get a Excel date value into a SSIS Date variable a little later.

The display comes out correctly using a formula but I am looking to simulate a condition like this:

While trying to honor the idea that pre-processing the incoming Worksheet to change the formatting on the cell is not an option.

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 (1/19/2016)
It shows up as 19/01/2016

Can you delete all other data from your Worksheet, zip it and attach it to this thread? I think I need a real-world example to go any further since I am having trouble recreating the scenario on this side.

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

murtzd Ten Centuries Points: 1039 More actions · Answer 8

murtzd

Ten Centuries

Points: 1039

January 20, 2016 at 8:25 am

#1853164

File attached.

MMartin1 One Orange Chip Points: 27795 More actions · Answer 9

Just have to ask, you mentioned you tried all the data types in SSIS. I assume this includes the "double" type as well? I am surprised it would fail here.

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

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

murtzd (1/20/2016)
File attached.

Thanks. I'll give it a spin later tonight.

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 11

MMartin1 (1/20/2016)
Just have to ask, you mentioned you tried all the data types in SSIS. I assume this includes the "double" type as well? I am surprised it would fail here.

Using the Execute SQL Task trying to capture the one cell not even trying to store the scalar in a variable of type Object worked for me. Pretty sure you will need to use a resultset to get it but will post a solution (assuming I can find one) tonight.

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 12

murtzd (1/20/2016)
File attached.

I could not get this to work using a mapping to a scalar variable in the Execute SQL Task. It will work by pulling a Full Resultset and then reading row 0 and column 0 of the resulting DataTable into your desired variable. Let me know if you have any questions.

Script Task C# code:

OleDbDataAdapter oleDA = new OleDbDataAdapter();

DataTable dt = new DataTable();

oleDA.Fill(dt, Dts.Variables["User::Results"].Value);

var cell = dt.Rows[0][0].ToString();

Dts.Variables["User::Cell"].Value = Convert.ToDateTime(cell);

string strMsg = string.Empty;

strMsg = strMsg + dt.Columns[0].ColumnName + ": " + Dts.Variables["User::Cell"].Value.ToString() + Environment.NewLine;

MessageBox.Show(strMsg);

Dts.TaskResult = (int)ScriptResults.Success;

Edit: shrink images

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

murtzd Ten Centuries Points: 1039 More actions · Answer 13

I'll give this a go later, thank you so much!

Were you getting the same error as me with the other method? I just can't understand why it works for text but not for a date!

MMartin1 One Orange Chip Points: 27795 More actions · Answer 14

Brilliant work Orlando. It makes sense that the execute sql task is returning the results of a SELECT. That will be in the form of a result set, even if it is a one row one column result. Hence the need of a Object type variable.

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 15

MMartin1 (1/21/2016)
Brilliant work Orlando. It makes sense that the execute sql task is returning the results of a SELECT. That will be in the form of a result set, even if it is a one row one column result. Hence the need of a Object type variable.

I will second this sentiment: great work, Orlando.

Not sure I agree with the 'makes sense' comment though. The 'Single Row' ResultSet option should also work, IMO.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin