Extracting data from specific cell in Excel using Script task

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Integration Services » Extracting data from specific cell in Excel...

Extracting data from specific cell in Excel using Script task

Rate Topic

Display Mode

Topic Options

Author

Message

rockstar283

Posted Friday, March 15, 2013 3:11 PM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 25, 2013 1:28 PM
Points: 84, Visits: 384

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

rockstar283

Posted Saturday, March 16, 2013 4:47 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 25, 2013 1:28 PM
Points: 84, Visits: 384

Any help guys..

Post #1431877

« Prev Topic | Next Topic »

Permissions