0

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

  • What is `drSheet`? How do you get data into it? And anyway, there are Excel libraries like `EPPlus` and many others that will let you handle Excel data in C# much better, is there any reason why you don't want to use them? – Sach Aug 06 '20 at 17:46
  • Sorry @Sach I made a mistake drSheet is sheet name now i corrected. and My boss not allow to using third party dll so i can't use this bro. is there any other ways? – Saravanan Ponnusamy Aug 06 '20 at 17:59

0 Answers0