Retrieve specific cell value from Excel into SSIS variable

  • It shows up as 19/01/2016

  • what about when you press F2? same?

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

  • 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

  • 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 (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.

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

  • 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

  • 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

  • File attached.

  • 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.

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

  • 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

  • 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

  • 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

  • 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!

  • 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.

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

  • 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

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

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