Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Extracting data from specific cell in Excel using Script task Expand / Collapse
Author
Message
Posted Friday, March 15, 2013 3:11 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 29, 2014 3:19 PM
Points: 115, Visits: 546
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?
Post #1431783
Posted Saturday, March 16, 2013 4:47 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 29, 2014 3:19 PM
Points: 115, Visits: 546
Any help guys..
Post #1431877
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse