I am using ssis script task to load excel sheet into a data table. i am using below code to read the sheet. Here an InvoiceDate column cells formatted as Date(dd/mm/yyyy) in excel all the columns(30/04/2020) read correctly but invoicedate one cell have value 30.4.2020 return as null into data table.Anyone can you please help me to get out of this. My code is:
var directory = new DirectoryInfo(FolderPath);
FileInfo[] files = directory.GetFiles();
//Declare and initilize variables
string fileFullPath = "";
foreach (FileInfo file in files)
{
fileFullPath = FolderPath + "\\" + file.Name;
//Create Excel Connection
string ConStr;
string HDR;
HDR = "YES";
ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileFullPath + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=0\"";
OleDbConnection cnn = new OleDbConnection(ConStr);
//Get Sheet Name
cnn.Open();
DataTable dtSheet = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
DataRow drSheet = dtSheet.Rows[0];
string sheetname;
sheetname = drSheet["TABLE_NAME"].ToString();
//Load the DataTable with Sheet Data so we can get the column header for Customer
OleDbCommand Header_conn = new OleDbCommand("select top 1 * from [" + sheetname + "]", cnn);
OleDbDataAdapter Header_adp = new OleDbDataAdapter(Header_conn);
DataTable Header_dt = new DataTable();
Header_adp.Fill(Header_dt);
//Load excel data into datatable
string EBIXSQLColumnList = "[Billing Month],[CDW Invoice Date],[CDW Invoice No]";
OleDbConnection con = new OleDbConnection(ConStr);
con.Open();
OleDbCommand EBIX_conn1 = new OleDbCommand("select " + EBIXSQLColumnList + " from [" + sheetname + "]", con);
OleDbDataAdapter EBIX_adp1 = new OleDbDataAdapter(EBIX_conn1);
DataTable EBIX_dt1 = new DataTable();
EBIX_adp1.Fill(EBIX_dt1);
con.Close();
}
I think stackoverflow doesn't having excel file attachment option so i added screenshot of my excel sheet for your reference. enter image description here
Thanks In Advance