Post reply

Extracting data from specific cell in Excel using Script task

  • March 15, 2013 at 3:11 pm

    #147973

    Hi..I have an excel file with data and want to extract the value from C2 cell only..here is my code in script component:

    public void Main()

    {

    // TODO: Add your code here

    string fileToTest;

    string tableToTest;

    string connectionString;

    OleDbConnection excelConnection;

    DataTable excelTables;

    string currentTable;

    fileToTest = Dts.Variables["ExcelFile"].Value.ToString();

    tableToTest = Dts.Variables["ExcelTable"].Value.ToString();

    string tabName = "TBL_NAME$";

    //Dts.Variables["ExcelTableExists"].Value = false;

    if (File.Exists(fileToTest))

    {

    connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +

    "Data Source=" + fileToTest + ";Extended Properties=Excel 8.0";

    excelConnection = new OleDbConnection(connectionString);

    excelConnection.Open();

    excelTables = excelConnection.GetSchema("Tables");

    string strSQL = "Select * From [" + Dts.Variables["ExcelTable"].Value.ToString().Substring(1,16) + "C2:C2]";

    OleDbDataAdapter objAdapter = new OleDbDataAdapter(strSQL, excelConnection);

    DataSet ds = new DataSet();

    objAdapter.Fill(ds, tabName);

    DataTable dt = ds.Tables[tabName];

    MessageBox.Show(ds.Tables[tabName].Rows[1].ToString());

    foreach (DataRow row in dt.Rows)

    {

    Dts.Variables["From_Date"].Value = row.ItemArray[1].ToString();

    MessageBox.Show( Dts.Variables["From_Date"].Value.ToString());

    }

    //MessageBox.Show(Dts.Variables["From_Date"].Value.ToString());

    }

    Dts.TaskResult = (int)ScriptResults.Success;

    }

    I am not getting anything at all..the messagebox shows nothing..Can anyone please tell me what am I doing wrong?

  • March 16, 2013 at 4:47 am

    #1597798

    Any help guys..

Viewing 2 posts - 1 through 1 (of 1 total)

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